Scaling Databases

Having replicas is useless if your application still sends all queries to the primary. You need a way to split traffic: writes go to the primary, reads go to replicas. There are three ways to do this, from simple to sophisticated.

Option A: Application-level routing. Your code explicitly decides. This is what most small teams start with. In your data access layer, you have two connection strings — one for the primary, one for replicas — and your code picks which to use based on the operation:

# Pseudocode — your app decides where each query goes
primary_conn = connect("host=pg-primary dbname=myapp")
replica_conn = connect("host=pg-replica-1 dbname=myapp")

def get_user(user_id):
    # Reads go to replica
    return replica_conn.execute("SELECT * FROM users WHERE id = %s", user_id)

def update_user(user_id, name):
    # Writes go to primary
    primary_conn.execute("UPDATE users SET name = %s WHERE id = %s", name, user_id)

Simple, but fragile. What if a replica goes down? What about load balancing across multiple replicas? What about read-your-writes consistency?

Option B: Proxy-level routing with ProxySQL (MySQL) or PgBouncer + HAProxy (PostgreSQL). This is what GitHub, Shopify, and most mid-size companies use. A proxy sits between your app and the databases, inspects each query, and routes it automatically. GitHub uses ProxySQL with MySQL to route reads to replicas and writes to the primary — the application code just sends queries to one endpoint, and ProxySQL figures out where they go.

# ProxySQL routes queries by inspecting the SQL
mysql_servers =
(
  { address="primary.db" , port=3306, hostgroup=0 },    # Writes (hostgroup 0)
  { address="replica-1.db", port=3306, hostgroup=1 },   # Reads (hostgroup 1)
  { address="replica-2.db", port=3306, hostgroup=1 },   # Reads (hostgroup 1)
  { address="replica-3.db", port=3306, hostgroup=1 }    # Reads (hostgroup 1)
)

mysql_query_rules =
(
  { match_pattern="^SELECT", destination_hostgroup=1 },  # All SELECTs → replicas
  { match_pattern=".*",      destination_hostgroup=0 }   # Everything else → primary
)

Option C: Cloud-native routing. AWS RDS has built-in reader endpoints. When you create read replicas, AWS gives you a single "reader endpoint" URL that automatically load-balances across all replicas using DNS round-robin. Your app connects to mydb-cluster.cluster-ro-xxxx.us-east-1.rds.amazonaws.com for reads and mydb-cluster.cluster-xxxx.us-east-1.rds.amazonaws.com for writes. Zero proxy setup, zero configuration — but you get less control over routing logic.

Here's a problem most people don't think about until it bites them: every database connection costs memory. A PostgreSQL connection takes about 5-10 MB of RAM on the server. If you have 20 application servers, each with 50 threads, each thread opening its own database connection — that's 1,000 connections consuming 5-10 GB of RAM just for connection overhead. RAM that isn't available for caching your data.

Worse, PostgreSQL uses a process-per-connectionUnlike MySQL (which uses threads), PostgreSQL forks a new operating system process for every client connection. Each process gets its own memory space. This is safer (a crash in one connection doesn't kill others) but more expensive — each process needs its own copy of query plans, temp buffers, and sort memory. This is why connection limits matter more in PostgreSQL than in MySQL. model. Each connection is a separate OS process. Context-switching between 1,000 processes is expensive. The CPU spends more time switching between connections than actually executing queries.

The fix is a connection pooler — a lightweight proxy that maintains a small pool of actual database connections and shares them across all your application requests. The most popular one for PostgreSQL is PgBouncerA lightweight connection pooler for PostgreSQL. It sits between your app and PostgreSQL, managing a small pool of real database connections. Written in C, it uses only ~2 KB of memory per client connection (vs 5-10 MB for a real PostgreSQL connection). It can handle 10,000+ client connections mapped to just 25 actual database connections..

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# THE KEY SETTINGS:
max_client_conn = 1000       # Accept up to 1,000 connections from your app
default_pool_size = 25       # But only open 25 real connections to PostgreSQL
reserve_pool_size = 5        # 5 extra connections for spikes
pool_mode = transaction      # Share connections between requests (recommended)

That's it. Your 1,000 application connections now share 25 real database connections. The database server sees only 25 processes instead of 1,000. Memory usage drops from 5-10 GB to ~125 MB. CPU context-switching drops by 40x.

Your primary database just crashed. Maybe a disk failed. Maybe the kernel panicked. Maybe an engineer ran a bad migration. Whatever the cause, your primary is gone and every write is failing. What happens next depends entirely on whether you planned for this moment.

Manual failover means a human (you, at 3 AM) gets paged, logs into the infrastructure, promotes a replica to primary, updates connection strings, and restarts the application. This takes 10-30 minutes. During those minutes, your application can serve reads from replicas but can't process any writes — no new orders, no sign-ups, no state changes.

Automatic failover means software detects the primary is down and promotes a replica within seconds. There are two popular approaches:

AWS RDS Multi-AZ: Amazon maintains a synchronous standby in a different Availability Zone. When the primary dies, RDS automatically promotes the standby and updates the DNS endpoint. Your app reconnects within 60-120 seconds. You don't touch anything. This is what most teams on AWS use — it costs roughly 2x the price of a single instance but gives you automatic failover with zero data loss.

Patroni (self-managed PostgreSQL): If you're running PostgreSQL on bare metal or EC2 instances, PatroniAn open-source tool by Zalando that automates PostgreSQL failover. It uses a distributed consensus store (etcd, ZooKeeper, or Consul) to elect a new primary when the current one fails. Patroni handles promotion, DNS updates, and replication reconfiguration automatically. GitHub, GitLab, and Zalando all use Patroni in production. is the standard tool. It uses etcdA distributed key-value store used for consensus. Multiple Patroni nodes use etcd to agree on which PostgreSQL instance is the primary. If the primary stops sending heartbeats to etcd, Patroni triggers an election and promotes the most up-to-date replica. etcd is the same technology Kubernetes uses for its control plane. as a consensus store, monitors the primary's health, and automatically promotes the most up-to-date replica if the primary stops responding. Failover takes 5-30 seconds depending on configuration.

Here's the most common bug teams hit after setting up read replicas: a user updates their profile name, the write goes to the primary, the page refreshes, the read goes to a replica that's 200ms behind, and the user sees their old name. They click "save" again. Now they have a duplicate write. They refresh again. Still the old name. They think the app is broken.

This is the read-your-writes consistencyA consistency guarantee that says: after you write something, any subsequent READ by the same user should see that write. It doesn't mean OTHER users see it immediately (that's a different guarantee called "strong consistency"). Read-your-writes is about YOU seeing YOUR OWN changes without delay. problem. The write succeeded, but the user can't see their own change because the read was served by a replica that hasn't caught up yet.

The most common fix is simple: after a write, read from the primary for a short window. The user writes to the primary, and for the next 5 seconds, all their reads also go to the primary. After 5 seconds, the replicas have caught up (typical replication lag is 1-100ms), and reads switch back to replicas.

# After a write, pin reads to primary for 5 seconds
WRITE_PIN_SECONDS = 5

def handle_write(user_id, query):
    primary.execute(query)
    # Set a timestamp in Redis (or a cookie) marking this user's last write
    redis.set(f"last_write:{user_id}", time.now(), ex=WRITE_PIN_SECONDS)

def handle_read(user_id, query):
    last_write = redis.get(f"last_write:{user_id}")
    if last_write and (time.now() - last_write) < WRITE_PIN_SECONDS:
        # User wrote recently — read from primary to guarantee consistency
        return primary.execute(query)
    else:
        # Safe to read from replica
        return replica.execute(query)

For PostgreSQL, you can also enforce read-only connections on replicas to prevent accidental writes:

-- Run on replica connections to prevent accidental writes
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

-- Now any INSERT/UPDATE/DELETE on this connection will fail:
-- ERROR: cannot execute INSERT in a read-only transaction

And you can measure the actual replication lag on any replica, right now:

-- PostgreSQL: check how far behind this replica is
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;
-- Output: 00:00:00.047  (47 milliseconds behind — totally fine)

-- Run on replica:
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

-- Run on primary (see all replicas):
SELECT client_addr,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- Run on replica:
SHOW REPLICA STATUS\G

-- Look for this field:
-- Seconds_Behind_Source: 0
-- (0 means fully caught up, any positive number is lag in seconds)

-- MySQL 8.0.22+ also has:
-- CHANGE REPLICATION SOURCE TO SOURCE_HOST='primary', SOURCE_PORT=3306;

# Run on Redis primary:
redis-cli INFO replication

# Look for:
# role:master
# connected_slaves:2
# slave0:ip=10.0.1.52,port=6379,state=online,offset=123456,lag=0
# slave1:ip=10.0.1.53,port=6379,state=online,offset=123450,lag=1
#                                                            ^ 1 second behind

# Also check for disconnected replicas:
# master_link_down_since_seconds: 0  (0 = connected, >0 = trouble)

So far we've assumed all our databases are in the same data center (or AWS region). But what happens when your users are worldwide? A user in Tokyo connecting to a database in Virginia experiences ~150ms of network latency on every query. For a page that fires 10 database queries, that's 1.5 seconds of pure network wait — before the database even starts working.

The fix: put read replicas near your users. Run replicas in us-east-1 (Virginia), eu-west-1 (Ireland), and ap-northeast-1 (Tokyo). Each user reads from the nearest replica. Writes still go to the primary in one region, but reads are served locally.

The challenge: replication lag increases across regions. Within the same data center, lag is 1-50ms. Across the Atlantic, the physical speed of light adds ~70ms of minimum latency (New York to London). Across the Pacific, it's ~120ms. Your replicas in Tokyo will always be at least 120ms behind the primary in Virginia. For reads that need strong consistency (showing a user their own just-submitted data), you might need to route those specific reads back to the primary.

People use the words "partitioning" and "sharding" interchangeably. They shouldn't. Arpit BhayaniA software engineer known for deep-dive content on system design and databases. Former engineer at Amazon and Unacademy. His YouTube channel and newsletter cover database internals, distributed systems, and real-world architecture decisions with a focus on understanding the WHY behind every design choice. draws a sharp distinction that clears up the confusion:

Partitioning splits a large table into smaller pieces, but all pieces live on the same database server. Think of it as organizing files into folders on the same hard drive. PostgreSQL's built-in PARTITION BY does this. It helps query performance (the planner skips irrelevant partitions) but doesn't help with server capacity — you're still on one machine.

Sharding splits data across different database servers. Each server holds a subset of the data. This is true horizontal scaling — adding more machines to handle more traffic. But it comes with enormous complexity: cross-shard queries, distributed transactions, rebalancing when you add/remove shards.

Shopify's real sharding: Shopify uses VitessAn open-source database clustering system originally built at YouTube/Google. It sits in front of MySQL instances and handles sharding transparently. Your application sends queries to Vitess (which looks like a single MySQL), and Vitess routes them to the correct shard. It handles shard splitting, online schema changes, and query routing. Shopify migrated to Vitess in 2020 to handle their scale. to shard their MySQL databases. Each merchant's data lives on a specific shard. When a merchant's traffic grows, Vitess can split their shard transparently. The application code doesn't know about shards — it sends queries to Vitess, which routes them to the right MySQL instance.

Instagram stores billions of photos, likes, and comments. Early on, they ran on a single PostgreSQL database. When that maxed out, they didn't jump straight to sharding. They first added read replicas to absorb the "show me my feed" traffic. But eventually, even the write load overwhelmed the primary — too many likes, comments, and follows per second.

Their solution: shard by user_id. Every user's data (photos, likes, comments, follows) lives on a specific shard determined by user_id % num_shards. They use pglogicalA PostgreSQL extension for logical replication that lets you selectively replicate specific tables or subsets of data between PostgreSQL instances. Unlike physical replication (which copies everything byte-for-byte), pglogical decodes changes into row-level operations. Instagram used it to migrate data between shards and keep cross-shard references eventually consistent. to replicate reference data (like user profiles) across all shards, so a feed query on Shard 3 can still resolve usernames without cross-shard JOINs.

GitHub runs on MySQL (not PostgreSQL) and handles over 10,000 queries per second across their fleet. Every application server talks to ProxySQLA high-performance MySQL proxy that sits between your application and MySQL. It can route read queries to replicas, manage connection pools, cache frequent queries, and perform query rewriting. GitHub runs ProxySQL as a sidecar on every app server, so the app connects to localhost:3306 and ProxySQL handles the rest. running as a local sidecar. The app connects to localhost:3306 and ProxySQL transparently routes queries to the right backend.

ProxySQL inspects every query: SELECT queries go to the nearest healthy replica. INSERT, UPDATE, DELETE go to the primary. If a replica fails a health check, ProxySQL removes it from the pool within seconds. The app never knows a replica went down.

CricBuzz handles millions of concurrent users during major cricket matches. But here's the thing — live match data isn't that big. A complete match state (score, overs, batsmen, bowlers, commentary) fits in roughly 12 KB of JSON. And it only changes once every 30 seconds (when a ball is bowled).

Their insight: don't hit the database for live scores at all. Instead, one background worker writes the match state to RedisAn in-memory data store that can serve reads in microseconds (vs milliseconds for PostgreSQL). Data lives in RAM, making it incredibly fast for hot data that's read millions of times. Redis can handle 100K+ reads/sec on a single instance. The tradeoff: it's volatile (data can be lost on crash unless persistence is configured), and it's more expensive per GB than disk. every ~30 seconds. All user-facing reads come from Redis. The actual PostgreSQL database is only used for permanent storage (match history, player records, analytics).

The takeaway: sometimes the best way to scale your database is to not hit it at all. If your data is small, changes infrequently, and is read millions of times, put it in an in-memory cache and let the database handle only durable storage.

Slack used PostgreSQL for years. Their messages table was the hottest table in the system — every message send, every channel load, every search hit it. As Slack grew to millions of workspaces, the single messages table became the bottleneck. Read replicas helped for a while, but the write load (every sent message = an INSERT) eventually overwhelmed the primary.

Slack migrated their messages to VitessAn open-source database clustering system built at YouTube. It puts a proxy layer in front of MySQL, handles sharding transparently, and supports online schema changes and shard splitting. Your application connects to Vitess as if it were a normal MySQL, and Vitess routes each query to the correct shard based on the sharding key you configure. — the same system YouTube and Shopify use. Vitess sits in front of MySQL instances, handles sharding by workspace_id, and makes the whole cluster look like a single database to the application. The migration took months of dual-writes and shadow reads, but the result was a system that could handle 10x the message volume without sweating.