Scale PostgreSQL with Read Replicas, Pooling & Caching

Updated 4 November 2026 · Standard formula, shown below

This guide walks you through scaling a PostgreSQL database for higher read traffic using read replicas, connection pooling, and caching. You’ll learn how to set up each layer, configure your application to use them, and avoid common pitfalls. By the end, you’ll have a practical roadmap to reduce primary database load and improve response times.

What You’ll Need

A PostgreSQL instance (primary) with data you want to scale.
Access to create replicas (either on the same server or separate machines).
An application that uses PostgreSQL (e.g., a web app).
Basic familiarity with SQL and your application’s configuration files.
Optional: A load balancer or DNS service to distribute connections.

Step-by-Step

1. Set Up Read Replicas

Configure the primary for replication: Ensure wal_level = replica (or logical if you need logical replication) and max_wal_senders is set to at least the number of replicas you plan to add. Set hot_standby = on on replicas to allow read queries.
Create a base backup: Use pg_basebackup to copy the primary’s data directory to each replica server. Example: pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replication_user -P
Configure the replica: In the replica’s postgresql.conf, set hot_standby = on and add a primary_conninfo line in recovery.conf (or postgresql.conf for PostgreSQL 12+) pointing to the primary.
Start the replica: Start PostgreSQL on the replica. It will catch up to the primary’s transaction log (WAL). Verify with pg_isready and check pg_stat_replication on the primary.
Add more replicas: Repeat steps 2-4 for each additional replica. You can also set up cascading replication for better scalability.

2. Implement Connection Pooling

Choose a pooler: Popular options include PgBouncer and Pgpool-II. PgBouncer is lightweight and great for transaction-level pooling; Pgpool-II offers more features like load balancing and caching.
Install the pooler: Use your system’s package manager (e.g., apt install pgbouncer on Debian/Ubuntu).
Configure PgBouncer: Edit pgbouncer.ini. Set listen_addr and port (default 6432). Define databases in [databases] section, pointing to the primary and replicas. Use pool_mode = transaction for most web workloads.
Set up auth: Create a userlist.txt with usernames and passwords (or use auth_type = md5 and reference the PostgreSQL users).
Point your app to the pooler: Change your application’s database connection string to use the pooler’s host and port instead of the direct PostgreSQL port. For example: postgresql://user:pass@pooler_host:6432/dbname.
Load balance reads: If using Pgpool-II, enable load balancing so read queries are distributed across replicas. For PgBouncer alone, you’ll need a separate load balancer or use a DNS round-robin.

3. Add Caching

Identify cacheable data: Look for frequently read, rarely updated data (e.g., user profiles, product lists, configuration). Avoid caching time-sensitive or user-specific data unless you handle invalidation.
Choose a cache: Redis or Memcached are common. Redis offers more data structures and persistence options; Memcached is simpler and faster for pure key-value.
Install and run the cache: Install Redis (apt install redis-server) or Memcached, and ensure it’s running.
Integrate into your app: Use your language’s Redis/Memcached client. Before querying PostgreSQL, check the cache. If hit, return cached data. If miss, query the database, store the result in cache with a TTL (time-to-live), and return it.
Invalidate on writes: When your application updates data that is cached, delete or update the corresponding cache entries. This can be done in the same transaction or via a message queue.

Common Mistakes

Not using connection pooling with replicas: Each app connection to a replica consumes resources. Pooling is essential to avoid exhausting connections.
Writing to replicas: Read replicas are read-only. Attempts to write will fail. Ensure your app routes all writes to the primary.
Ignoring replication lag: Replicas may lag behind the primary. If your app reads from replicas, you might serve stale data. Monitor lag and consider using synchronous replication for critical data.
Caching without invalidation: If you cache data and never invalidate, users will see outdated information. Always have a strategy to update or expire cached items.
Pooling with session-level features: If your app uses session-level settings (e.g., SET search_path), transaction pooling may break. Use statement-level pooling or adjust your app.

Tips

Monitor everything: Use tools like pg_stat_statements to track query performance, and monitor replica lag and cache hit rates.
Start small: Begin with one replica and a pooler, then add caching. Measure the impact before adding more complexity.
Use a load balancer: For multiple replicas, a load balancer (like HAProxy) can distribute read traffic evenly.
Consider managed services: If you don’t want to manage this yourself, cloud providers offer managed PostgreSQL with read replicas and built-in pooling (e.g., Amazon RDS Proxy).
Test failover: If your primary fails, you need a plan to promote a replica. Practice this scenario.

Troubleshooting

Replica not syncing: Check pg_stat_replication on the primary. Ensure network connectivity and that the replica’s primary_conninfo is correct. Look at the replica’s logs for errors.
Connection pooler errors: Verify the pooler is running and listening on the expected port. Check authentication settings in userlist.txt. Test with psql -h pooler_host -p 6432.
Cache not working: Ensure the cache server is running and accessible. Check that your app is actually using the cache (add logging). Verify TTLs are set correctly.
High latency on reads: If reads are slow, check if they are hitting the primary instead of replicas. Review your connection string and load balancing rules.
Data inconsistency: If you see stale data, check replica lag. If it’s high, consider reducing load on the primary or using synchronous replication for critical transactions.

Frequently Asked Questions

Can I use read replicas for write scaling?

No, read replicas are read-only. For write scaling, you need sharding or a distributed database like Citus.

What is the difference between PgBouncer and Pgpool-II?

PgBouncer is a lightweight connection pooler focused on performance. Pgpool-II offers pooling, load balancing, and query caching, but is heavier and more complex.

How do I handle cache invalidation?

You can invalidate cache entries when a write occurs. This can be done by directly deleting/updating the cache in your application code, or by using a message queue to notify other services.

What is replication lag and why does it matter?

Replication lag is the time delay between a write on the primary and it appearing on a replica. If you read from replicas, you might see old data. Monitor lag and decide if it’s acceptable for your use case.

Do I need a load balancer if I have multiple replicas?

Not strictly, but it helps distribute connections evenly. Without one, your app might connect to one replica more than others, causing uneven load.

Can I cache entire database tables?

You can, but it’s risky. If the table is large or frequently updated, caching may not help and could cause memory pressure. Cache only what you need and set appropriate TTLs.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.