Database Scaling: Read Replicas, Connection Limits, and Partitioning
Understand how to scale a database beyond a single server using read replicas, connection pooling, and partitioning/sharding, recognize replication lag pitfalls, and see how caching and a single writable primary shape the real limits of database scalability.
You can horizontally scale as many stateless PHP servers as you want, but they usually all still talk to the same database — and a database is much harder to scale horizontally than a web server, because writes must ultimately be ordered and consistent somewhere. This lesson covers the standard techniques for scaling a database's read capacity, controlling how many connections it has to manage, splitting enormous tables across multiple machines, and understanding why a single writable primary is so often the real ceiling on how far a system can scale.
Read replicas: scaling reads, not writes
A read replica is a copy of your primary database that continuously receives a stream of the primary's changes (via the database's built-in replication mechanism) and can serve read queries independently. Since most applications are read-heavy — far more SELECTs than INSERT/UPDATE/DELETE — adding replicas and routing read traffic to them can dramatically increase how much read load a system handles, without touching write capacity at all.
In PHP, this typically means maintaining two PDO connections (or a connection-routing layer that picks one automatically): one pointed at the primary for all writes, and one pointed at a replica (or load-balanced across several) for reads. Frameworks like Laravel support this natively via a 'read' and 'write' connection configuration.
Replication lag: the sharp edge of read replicas
Replication is asynchronous by default in most setups, meaning there's a small delay — usually milliseconds, but sometimes seconds under load — between a write committing on the primary and that same change appearing on a replica. This is replication lag, and it's the single most common bug read replicas introduce.
The classic failure: a user submits a form (write goes to the primary), the page immediately redirects and re-queries for a fresh view of that data (read goes to a replica), and the replica hasn't caught up yet — so the user sees their own change appear to have not saved, even though it did. The fix is to either read from the primary immediately after a write on the same request (a 'read-your-writes' pattern), or to explicitly wait for the replica to catch up, depending on the database's replication API.
Connection limits and pooling
Every database connection consumes real memory and resources on the database server, and databases like PostgreSQL and MySQL have a hard cap on total concurrent connections (often in the low hundreds by default). Because traditional PHP-FPM opens a fresh connection per request (unless using persistent connections carefully), a fleet of many PHP servers under load can easily exceed that cap, causing new connections — and the requests that need them — to fail outright.
A connection pooler sits between your application servers and the database, maintaining a smaller, stable pool of actual database connections and multiplexing many application-level 'connections' onto them. PgBouncer is the standard tool for PostgreSQL; conceptually it means your 50 PHP-FPM workers across 5 servers (250 potential connections) might all be served through a pool of just 20 real database connections, because most connections are idle most of the time between queries.
Partitioning and sharding
Partitioning splits one very large table into smaller physical pieces while keeping it queryable as a single logical table — commonly by range (e.g. one partition per month of an events table) or by hash (spreading rows evenly across N partitions by hashing a key). This keeps individual partitions small enough for indexes and queries to stay fast, and lets you drop old partitions (like last year's logs) cheaply instead of running a slow DELETE.
Sharding takes this further by splitting data across entirely separate database servers, not just separate tables on one server — for example, users 1-1000000 live on shard A's database, users 1000001-2000000 on shard B's. This scales both storage and write throughput horizontally, but at real application complexity cost: cross-shard queries (like 'find all orders across all users') become much harder, and you need a reliable way to route each query to the correct shard.
Caching to offload the database, and the write bottleneck
Before reaching for replicas or sharding, a well-placed cache (Redis, Memcached) in front of expensive or frequently-repeated queries often removes far more database load for far less complexity — a query result that's read 10,000 times per minute but changes once per hour is a much better caching candidate than a scaling candidate.
Ultimately, though, read replicas, pooling, and caching all scale read capacity — they do nothing for write throughput, because writes must go to one place to stay consistent (or to a sharded system with real complexity). This single writable primary is very often the actual ceiling on how far a system can scale: you can add read replicas indefinitely, but if your write volume outgrows what one primary can handle, only sharding (or a fundamentally different data architecture) solves it, and that's a far bigger undertaking than adding a replica.
Routing reads to a replica and writes to the primary with PDO
All INSERT/UPDATE/DELETE statements go through write(), which connects to the primary. Read-only SELECTs go through read(), which connects to a replica, spreading read load off the primary. This naive example is vulnerable to replication lag if a read immediately follows a write for the same data — see the read-your-writes pattern below.
final class DbRouter
{
private ?PDO $writeConnection = null;
private ?PDO $readConnection = null;
public function write(): PDO
{
return $this->writeConnection ??= new PDO(
getenv('DB_PRIMARY_DSN'),
getenv('DB_USER'),
getenv('DB_PASS')
);
}
public function read(): PDO
{
return $this->readConnection ??= new PDO(
getenv('DB_REPLICA_DSN'),
getenv('DB_USER'),
getenv('DB_PASS')
);
}
}
$db = new DbRouter();
$db->write()->prepare('INSERT INTO orders (user_id, total) VALUES (?, ?)')
->execute([$userId, $total]);
$orders = $db->read()->query('SELECT * FROM orders WHERE user_id = ' . (int) $userId)->fetchAll();Code Examples
This repository deliberately routes the immediate post-write confirmation read to the primary, since the replica might lag behind by anywhere from milliseconds to a few seconds and could return stale or missing data for an order that was just created. Ordinary browsing/listing reads that don't need up-to-the-millisecond freshness are still routed to the replica, keeping most read load off the primary while avoiding the classic 'my change didn't save' bug. Expected result: Creating an order and immediately viewing its confirmation page always shows correct, fresh data, because that specific read bypasses the replica; the order history list (a less time-sensitive read) uses the replica and may lag by a few hundred milliseconds without anyone noticing.
return $this->primary->prepare('SELECT * FROM orders WHERE id = ?');
Common mistake: Routing every read to a replica indiscriminately, including immediately after a write in the same request, intermittently shows users a 'missing' or stale version of the exact thing they just created or updated — a hard-to-reproduce bug because it depends on replication timing.
A leaderboard query that aggregates across a huge activity_log table is exactly the kind of expensive, frequently-requested, slowly-changing query where caching gives far more benefit than infrastructure scaling. Caching the result for 30 seconds means the expensive GROUP BY runs at most twice a minute no matter how many thousands of users load the leaderboard, taking essentially all of that read load off the database entirely — no replica or shard needed for this particular problem. Expected result: 10,000 requests per minute for the leaderboard result in at most 2 actual database queries per minute; the other 9,998 are served entirely from Redis. This is usually the first scaling technique to reach for, before replicas or sharding, because it's far simpler to implement and often removes more load per unit of engineering effort.
$cache->setex($cacheKey, 30, json_encode($leaderboard));
<?php
declare(strict_types=1);
final class OrderRepository
{
public function __construct(
private readonly PDO $primary,
private readonly PDO $replica
) {}
public function create(int $userId, float $total): int
{
$stmt = $this->primary->prepare(
'INSERT INTO orders (user_id, total, created_at) VALUES (?, ?, NOW())'
);
$stmt->execute([$userId, $total]);
return (int) $this->primary->lastInsertId();
}
/**
* Reads normally go to the replica, but immediately after a write in the
* SAME request we deliberately read from the primary instead, because the
* replica may not have caught up to the write yet (replication lag).
*/
public function findFreshAfterWrite(int $orderId): ?array
{
$stmt = $this->primary->prepare('SELECT * FROM orders WHERE id = ?');
$stmt->execute([$orderId]);
return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
}
public function findForListing(int $userId): array
{
// ordinary listing reads are fine on a replica -- small staleness is acceptable here
$stmt = $this->replica->prepare('SELECT * FROM orders WHERE u
…<?php
declare(strict_types=1);
function getLeaderboard(PDO $replica, Redis $cache): array
{
$cacheKey = 'leaderboard:top100';
$cached = $cache->get($cacheKey);
if ($cached !== false) {
return json_decode($cached, associative: true);
}
// expensive aggregate query -- fine to run occasionally, not on every request
$stmt = $replica->query(
'SELECT user_id, SUM(points) AS total_points
FROM activity_log
GROUP BY user_id
ORDER BY total_points DESC
LIMIT 100'
);
$leaderboard = $stmt->fetchAll(PDO::FETCH_ASSOC);
// recompute at most once every 30 seconds, no matter how many requests arrive
$cache->setex($cacheKey, 30, json_encode($leaderboard));
return $leaderboard;
}Read replicas, pooling, and the write bottleneck
Replicas and pooling scale read capacity outward; write throughput still funnels through a single primary unless the system is sharded.
Writes Reads
| |
v v
+---------------+ +--------------------------+
| Connection | | Connection Pooler |
| Pooler | | (e.g. PgBouncer) |
+---------------+ +--------------------------+
| / | \
v v v v
+---------------+ +-----------+ +-----------+ +-----------+
| PRIMARY |--->| Replica 1 | | Replica 2 | | Replica 3 |
| (all writes, | +-----------+ +-----------+ +-----------+
| single point) | (reads only, replicated with some lag)
+---------------+
All write scaling ultimately funnels through this ONE box.Important Rules
Route write queries (INSERT/UPDATE/DELETE) to the primary and route ordinary read queries to replicas to spread read load.
Never assume a read immediately following a write on a replica reflects that write — replication is asynchronous and can lag; read from the primary for read-your-writes scenarios.
Use a connection pooler (PgBouncer or equivalent) when many PHP-FPM workers across many servers could otherwise exceed the database's hard connection limit.
Reach for caching before reaching for replicas or sharding — it's usually simpler and removes more load per unit of complexity for read-heavy, slowly-changing data.
Remember that replicas, pooling, and caching all scale reads, not writes — a single writable primary is often the true ceiling on scalability, and only sharding (or a different architecture) increases write throughput.
Consider partitioning (splitting one huge table into smaller physical pieces by range or hash) before full sharding, since it solves many single-table performance problems without the cross-shard query complexity.
Where this shows up
Large-scale platforms like GitHub and Shopify run exactly this stack: a primary database for writes, a fleet of read replicas for the much larger volume of reads, PgBouncer-style pooling in front of both, aggressive caching for expensive aggregate queries, and partitioning or sharding only once a single database's write throughput genuinely becomes the bottleneck — because sharding is usually the last resort due to its application complexity, not the first tool reached for.
$db->write()->exec("INSERT INTO orders ..."); $order = $db->read()->query("SELECT * FROM orders WHERE id = {$newId}")->fetch(); // may not exist yet on replica!
$db->write()->exec("INSERT INTO orders ..."); $order = $db->write()->query("SELECT * FROM orders WHERE id = {$newId}")->fetch(); // read from primary right after writing
Reading from a replica immediately after writing to the primary can return stale or missing data because replication is asynchronous — the write may not have propagated yet. Reading from the primary for that specific follow-up query avoids the race.
// Every PHP-FPM worker opens its own fresh PDO connection per request, across 20 servers x 50 workers = 1000 potential connections, against a database capped at 200
// A connection pooler (e.g. PgBouncer) sits between the app fleet and the database, multiplexing 1000 app-level connections onto ~50 real database connections
Without pooling, a traffic spike across a horizontally scaled fleet can exceed the database's hard connection limit, causing new connections (and the requests needing them) to be refused outright — a pooler absorbs that fan-out safely.
[Multiple Choice] What does adding read replicas primarily help scale? A. Write throughput B. Read throughput, by distributing SELECT queries across multiple database copies C. Disk space on the primary D. The number of tables in the database
B. Read throughput, by distributing SELECT queries across multiple database copies — Read replicas are copies that serve read (SELECT) queries; they don't accept writes, so they scale read capacity, not write throughput, which still funnels through the primary.
[True / False] A query that reads from a replica immediately after a write to the primary is guaranteed to see that write. A. True B. False
B. False — False. Replication is typically asynchronous, so there can be a lag between a write committing on the primary and it appearing on a replica — an immediate follow-up read on a replica can miss it.
[Multiple Choice] Why is a single writable primary often described as the real ceiling on database scalability? A. Because primaries are always slower hardware than replicas B. Because read replicas, connection pooling, and caching all scale read capacity but do nothing to increase write throughput, which still goes through one primary C. Because primaries cannot be backed up D. Because PHP cannot connect to more than one database server
B. Because read replicas, connection pooling, and caching all scale read capacity but do nothing to increase write throughput, which still goes through one primary — Replicas, pooling, and caching are all techniques for handling more reads or more connections efficiently; none of them add write capacity, since writes must be ordered somewhere consistent — only sharding (splitting writes across multiple independent databases) increases write throughput, at real complexity cost.
A checkout flow inserts a new order into the primary database, then immediately redirects to an order-confirmation page that queries for that same order from a read replica. Users occasionally see a 'order not found' error on the confirmation page, which resolves itself if they refresh a second later. Diagnose the cause and describe the fix.
The cause is replication lag: the order was inserted on the primary, but the confirmation page's read query hit a replica that hadn't yet received that write, so the row appeared not to exist. Refreshing a moment later works because the replica has caught up by then. The fix is a read-your-writes pattern: route the confirmation page's query (or any read immediately following a write for the same data) to the primary instead of a replica, at least for that specific request.
Think about the time gap between a write committing on the primary and that write becoming visible on a replica.