Before agreeing to shard a relational database some engineers want a number - roughly how many write transactions per second can one well-provisioned primary commit? Work it out from first principles and say which assumption dominates the error.

The assumptions, stated

A single primary on local NVMe, synchronous commit on, one replica receiving the log asynchronously. The committing path is: write the transaction's records to the write-ahead log buffer, flush and sync the log to durable storage, then acknowledge. The sync is the only step with a floor set by hardware.

Planning numbers to work from, all orders of magnitude a practitioner should recognise: an NVMe device syncs in roughly 50–200 microseconds; a network-attached block volume in roughly 0.5–2 milliseconds; a small transaction writes on the order of a few hundred bytes to a few kilobytes of log; each secondary index adds its own log records and page writes.

The arithmetic

Without batching, the ceiling is one sync per commit: at 100 microseconds that is about 10,000 commits per second, and on a network volume at 1 millisecond it is about 1,000. That order-of-magnitude gap between local and network storage is the single most useful fact in this calculation.

Group commit changes the picture. Concurrent transactions arriving within one sync interval are flushed together, so the sync cost is amortised over the batch: with 50 transactions per flush at 100 microseconds, the sync budget supports 500,000 commits per second and the limit moves elsewhere. The new binding constraint is log bandwidth and CPU: at 1 KB of log per transaction, 100,000 transactions per second is 100 MB/s of log, which is comfortable for NVMe and is already 800 Mbps of replication traffic to each replica.

So the honest answer is a range with a condition: roughly 5,000–20,000 write transactions per second on network storage, and 50,000–200,000 on local NVMe with group commit effective, for small single-row transactions on a table with two or three indexes.

Which assumption dominates the error

Concurrency, because group commit only works if there is a batch to form. Fifty single-threaded clients issuing one transaction at a time give one commit per round trip and no batching at all: the ceiling collapses to the unbatched number regardless of hardware. The same database with 500 concurrent connections batches well. The write ceiling is a property of the client topology as much as of the server, which is why load tests with low concurrency produce pessimistic ceilings and why a connection pooler in front of the database raises throughput without touching the database.

Second in line is write amplification per transaction: a table with eight indexes and a trigger writes several times the log volume of a lean one, and the index page writes compete for the same device.

What the number rules in or out

It rules out sharding as the first answer to anything below roughly 10,000 writes per second, which covers the overwhelming majority of systems whose teams are discussing it. The order of intervention is: raise client concurrency so batching can work, move the log to local NVMe, remove indexes nobody queries, batch logically related writes into one transaction, then split the hottest table vertically. Shard when writes or working set genuinely exceed one machine, and expect the sharded system to do less, not more, per transaction: cross-shard transactions and resharding are the bill.

When this number is the wrong answer

The ceiling above is a steady-state figure, and in production the failure usually arrives from a different direction. Three things break before the commit rate does: replication lag, when a replica cannot apply the log as fast as the primary writes it and a read replica silently serves minutes-old data; vacuum or compaction falling behind, so the table bloats and the ceiling drops over days rather than seconds; and the log device filling because archiving stopped, which takes the primary down hard. Measure the sustained rate over an hour with replicas attached and housekeeping running, not the peak over a minute on an idle machine, or the number you quote in the sharding review will be roughly double what the system can hold.