Why Reads Scale More Easily Than Writes: The Single-Writer Ceiling

A database can have fifteen read replicas and still have exactly one place where every order, payment, balance change, and inventory reservation must commit. Replication copies data; it does not automatically divide write ownership.

Situation

A relational database usually begins as the correct architecture. One PostgreSQL or MySQL instance provides transactions, constraints, indexes, backups, and a simple operating model. As the application grows, the database instance grows with it. Read-heavy endpoints move to replicas. Connection pooling protects the server. Slow queries are tuned. Large tables are partitioned. Storage and memory are increased.

This progression can produce a database topology that looks distributed while its write path remains centralized:

flowchart TD
  App[applications — orders, payments, inventory] --> Writer[one writer — canonical state]
  Writer --> ReaderA[reader one — read-only copy]
  Writer --> ReaderB[reader two — read-only copy]
  Writer --> ReaderC[reader three — read-only copy]
  Reports[reports and search] --> ReaderA
  Api[read APIs] --> ReaderB
  Batch[batch reads] --> ReaderC

The replicas can absorb product lookups, order history, reporting, and other tolerant reads. They can also improve availability by becoming failover candidates. They do not normally give the application another independent owner for the same rows.

The problem is not limited to globally famous platforms. A regional retailer can reach it because every checkout updates several indexes. A financial platform can reach it because balance changes contend on a small set of accounts. A software provider can reach it because a few tenants generate most of the writes. Write intensity, transaction shape, and contention matter as much as customer count.

This article uses one explicit design exercise throughout: RetailCo, a multi-tenant retail platform whose transactions history has grown to 40 TB. The number is a scenario input, not a claimed production benchmark. Each transaction belongs to a tenant_id; most customer-facing transactions are tenant-local, while finance and risk reporting span tenants.

The Problem

A read can often run against a copy because it observes state without changing the authority for that state. The application may accept a small amount of replica lag, route strict read-after-write requests back to the writer, or tolerate a slightly old report.

A write has a different job. Before acknowledging a commit, the database may need to:

• find and lock the affected rows;
• validate unique, foreign-key, and check constraints;
• order concurrent changes;
• update table and index pages;
• record the change in WAL or the binlog;
• satisfy the configured durability policy;
• expose one committed outcome after a crash or failover.

That coordination is the value of a relational transaction. It is also why adding another writable node is not equivalent to adding another read replica.

flowchart TD
  Request[transaction request] --> Route[locate authoritative data]
  Route --> Lock[coordinate concurrent changes]
  Lock --> Rules[validate constraints]
  Rules --> Log[record durable commit]
  Log --> Replicate[replicate committed state]
  Replicate --> Ack[acknowledge one outcome]

The diagram is deliberately simplified. The exact implementation differs across PostgreSQL, MySQL, distributed SQL systems, and NoSQL databases. The invariant is the important part: every system must decide who owns a write, how conflicting writes are ordered, and what survives failure.

This creates four distinctions that architecture reviews often blur:

Capability	What it changes	What it does not prove
Read replica	Adds capacity for eligible reads	More write throughput
Failover replica	Provides a promotion target	Concurrent independent writers
Table partitioning	Divides one logical table for pruning and lifecycle operations	Independent database compute
Multi-primary mode	Allows more than one node to accept writes under a coordination protocol	Linear write scaling for one contested dataset

Reads are not free or perfectly easy. Replica lag can violate read-after-write expectations. Long queries can conflict with replay. A stale reader can make an application believe an update disappeared. The narrower claim is that read-only work is usually easier to spread because it does not need to establish a new global truth.

The core question is therefore not, “How do we add another writer?” It is:

Can the workload be divided into ownership boundaries so most transactions coordinate locally instead of globally?

Write Scaling Is a Data-Ownership Decision

The most useful unit of write scalability is not the server. It is the write domain: the smallest set of data that must commit together under one consistency decision.

For RetailCo, a tenant-local checkout might update an order, its line items, a tenant inventory reservation, and an idempotency record. If all four tables use the same ownership key, that transaction can remain local to one shard. A risk report that aggregates every tenant does not need to be part of that commit. It can use a separate analytical path.

flowchart TD
  Checkout[checkout request with tenant identifier] --> Router[ownership router]
  Router --> ShardA[writer domain A — tenant group one]
  Router --> ShardB[writer domain B — tenant group two]
  Router --> ShardC[writer domain C — tenant group three]
  ShardA --> Stream[change stream]
  ShardB --> Stream
  ShardC --> Stream
  Stream --> Analytics[reporting and risk platform]

This design adds write capacity because the three writer domains own different data. A transaction in Shard A does not normally need Shard B to approve its commit. The architecture has not eliminated coordination; it has reduced the scope of coordination.

That is the common mechanism behind several different-looking solutions:

• Application sharding routes each tenant, account, or customer to a database cluster.
• Distributed relational databases use a distribution key and database-managed routing to place data across nodes.
• Domain decomposition moves orders, payments, inventory, or other consistency domains into separately owned databases.
• Partition-oriented NoSQL databases place records by partition or shard key and provide consistency guarantees within defined boundaries.

The product choice matters, but the ownership decision comes first. A poor key can place the busiest tenants on one shard, split transactions across nodes, or force every request through distributed coordination. A good key keeps the dominant write path local, has enough cardinality to spread load, and can be carried through related tables and requests.

Five exits from the ceiling

The writer reaching a limit does not automatically justify sharding. There are five distinct responses, and they solve different problems.

1. Remove avoidable work and scale up

If the writer is spending capacity on missing indexes, excessive indexes, repeated small commits, connection storms, accidental full scans, or synchronous reporting, fix those first. A larger writer can provide useful headroom while the data model is still fundamentally sound.

This is the lowest-complexity option. It is also finite. Scaling up delays the ownership decision; it does not change a one-writer architecture.

2. Partition tables inside the existing database

Time partitioning can make retention, archival, vacuum scope, and partition-pruned queries easier to operate. It is valuable for RetailCo’s 40-TB history. But all partitions can still share the same database instance, WAL path, buffer pool, and commit capacity.

Native partitioning is therefore an operational companion to write scaling, not proof of it. The key must match the access pattern, as discussed in Partitioning Is Not a Performance Feature by Default.

3. Shard by an ownership key

Sharding creates independent write domains. For RetailCo, tenant_id is a credible candidate because the reference workload says most OLTP transactions are tenant-local. Each shard can have its own writer and replicas.

The cost is explicit: routing, tenant placement, resharding, cross-shard queries, schema rollout, backups, and incident response now span multiple clusters. A very large tenant can still create a hot shard. Cross-tenant uniqueness and foreign keys no longer come for free.

4. Use a distributed relational database

Systems such as Citus, Vitess 23, Spanner, TiDB 8.5, and Aurora PostgreSQL Limitless Database move some routing, placement, and distributed transaction machinery into the database platform. They can reduce application-managed sharding work.

They do not remove data-model physics. Citus still needs a distribution column. Spanner warns that poor primary-key design can hotspot a split. Vitess needs a sharded keyspace and VSchema. Compatibility, transaction behavior, operational control, and migration effort differ substantially. Those distinctions deserve individual articles and lab plans rather than one generic “distributed SQL” label.

5. Decompose the write path by business domain

Orders, payments, inventory, customer identity, and audit data do not always require one transaction boundary. Separating them can give each domain an independent writer and failure boundary.

The trade is local ACID for explicit coordination between domains. Idempotency, outbox records, event delivery, compensation, and reconciliation become part of the application contract. Decomposition should follow consistency boundaries, not an arbitrary microservice count.

Diagnose the ceiling before choosing

An architecture review needs evidence that the writer is the constraint and evidence about what consumes it. Capture at least:

• write transactions per second and their mix;
• commit latency at P50, P95, and P99;
• CPU, storage latency, throughput, and queue depth;
• WAL or binlog generation rate;
• lock wait time, deadlocks, and hot rows;
• index count and write amplification on the busiest tables;
• connection count and time waiting for a connection;
• percentage of transactions that are tenant-local, account-local, or domain-local;
• replica lag and the volume of reads still sent to the writer.

PostgreSQL exposes cumulative database and WAL counters that can support an interval-based baseline:

SELECT
    datname,
    xact_commit,
    xact_rollback,
    tup_inserted,
    tup_updated,
    tup_deleted,
    temp_bytes,
    deadlocks,
    stats_reset
FROM pg_stat_database
WHERE datname = current_database();

SELECT
    wal_records,
    wal_fpi,
    wal_bytes,
    stats_reset
FROM pg_stat_wal;

Take deltas over a known interval and correlate them with latency, locks, and storage metrics. These queries do not prove that sharding is necessary. They prevent an expensive architecture change from being justified by a vague statement such as “the database is slow.”

In Practice

Context: PostgreSQL documents hot standby as a strictly read-only mode. Normal data-changing statements are not accepted, and queries on the standby can be canceled when they conflict with WAL replay: PostgreSQL hot standby.

Action: Use a standby for eligible read traffic, but identify endpoints that require read-after-write consistency and route them according to an explicit consistency policy.

Result: Read capacity and failover coverage can increase without changing the database’s authoritative write location.

Learning: A readable standby is a copy of committed state, not another independent owner of that state.

Context: Amazon Aurora documents a cluster architecture with one primary writer and up to 15 read-only Aurora Replicas: High availability for Amazon Aurora.

Action: Route tolerant SELECT traffic to the reader endpoint and reserve the writer for transactions that must change state.

Result: The documented architecture scales eligible reads and provides promotion candidates, while the traditional cluster’s write path still terminates at its writer.

Learning: A large replica count can coexist with a single-writer ceiling.

Context: MySQL documents traditional source-to-replica replication as asynchronous by default. It separately documents Group Replication in single-primary and multi-primary modes, where concurrent transactions are checked and certified and conflicts can be rolled back: MySQL replication architecture, Group Replication modes, and Group Replication.

Action: Evaluate multi-primary operation with the actual contention pattern, conflict rate, commit latency, and network topology. Do not use node count as the expected write-scaling factor.

Result: The test distinguishes additional write entry points from independent ownership domains.

Learning: Multi-primary is a replication and coordination topology. Horizontal write scaling still depends on how the workload is partitioned.

Context: Citus documents that the distribution column determines how rows are assigned to shards and that colocating related tables makes joins and foreign keys local where possible: Citus data modeling. Google Cloud similarly documents that Spanner’s primary-key design determines data distribution and that a poor key can hotspot a split: Spanner primary keys.

Action: Model the ownership key across the full transaction before selecting the platform. Verify that related rows carry the key and that the key spreads the busiest tenants or accounts.

Result: The database can route the common transaction to a bounded set of nodes instead of turning it into routine distributed coordination.

Learning: Distributed write capacity begins with data placement, not with the number of database nodes.

Where It Breaks

Option	Good fit	Where it breaks	Operational cost
Tune and scale up	Avoidable writer work or near-term headroom	Instance ceiling remains; larger failures have larger impact	Low to medium
Read replicas	Read-heavy endpoints that tolerate replica semantics	Writer CPU, commit path, and hot-row contention remain	Low to medium
Native table partitioning	Retention, pruning, and large-table maintenance	One database still owns all commits	Medium
Application sharding	Stable tenant or account key with local transactions	Cross-shard joins, global constraints, tenant movement	High
Distributed relational database	Relational workload needing managed data distribution	Compatibility gaps, distributed coordination, key hotspots	Medium to high
Domain decomposition	Clear business boundaries with different consistency needs	Cross-domain workflows, retries, reconciliation	High application complexity
Multi-primary replication	Availability or geographically placed write entry under controlled conflict patterns	Certification conflicts and shared-data coordination	Medium to high
Partition-oriented NoSQL	Access patterns fit partition-local operations and documented consistency guarantees	Relational joins and broad multi-record transactions may require redesign	Medium to high

Three failure patterns deserve particular attention.

First, the wrong shard key converts scale-out into a hotspot. A monotonically increasing key, one dominant tenant, or a low-cardinality brand identifier can direct too much traffic to one place.

Second, cross-shard transactions can rebuild the original bottleneck at the coordination layer. If checkout routinely updates multiple ownership domains, more shards may add network and failure modes without adding useful concurrency.

Third, moving existing data is part of the architecture. A 40-TB table cannot be treated as if every row appears in the new topology at once. Backfill, dual-read or controlled cutover, change capture, validation, rollback, and tenant-by-tenant migration need explicit designs.

No architecture should be approved without answers for backup consistency, shard loss, router failure, split-brain prevention, schema rollout, tenant relocation, and recovery of one mistakenly deleted tenant.

What to Do Next

For the RetailCo reference scenario, the recommended direction is tenant-based write ownership, with time partitioning inside each shard—but only after workload evidence confirms that the dominant OLTP transactions are tenant-local.

The staged recommendation is:

1. Remove avoidable writer work and move eligible reads and reporting away from the writer.
2. Preserve native time partitioning for retention and manageable table operations; do not count it as independent write capacity.
3. Route tenant-local orders, line items, inventory reservations, and idempotency records by tenant_id to independent writer domains.
4. Move cross-tenant risk and finance reporting to a change-fed analytical path instead of querying every shard in the checkout path.
5. Decompose payments or inventory only where their consistency and recovery boundaries are genuinely different.
6. Choose between application sharding and a distributed relational platform after testing compatibility, resharding, failure recovery, and the exact cross-shard transaction rate.

For this scenario, adding more read replicas is useful but insufficient. Multi-primary replication is not the first recommendation because RetailCo needs separated write ownership, not merely more nodes that can receive writes to shared data. Replacing the relational system with Cassandra or MongoDB is also not the first recommendation; that decision would require the access patterns and consistency contract to fit those databases, not merely a desire to escape the current writer.

Good fit: tenant-local sharding with time partitioning inside each shard.

Poor fit: a low-cardinality brand_id shard key, routine cross-tenant OLTP transactions, or a design that routes every distributed commit through one coordinator.

Main advantage: write capacity can grow by adding independently owned tenant groups.

Main limitation: routing, movement, recovery, and cross-shard operations become explicit platform responsibilities.

Operational complexity: high enough to require automation and failure testing before migration.

Migration complexity: high for 40 TB, so the unit of migration should be a tenant or tenant group with measurable reconciliation.

• Problem: Read replicas have reduced query pressure, but every state change still commits through one writer.
• Solution: Define a stable ownership key and keep the dominant transaction inside one write domain; use partitioning, sharding, or decomposition for the problem each one actually solves.
• Proof: Vendor documentation across PostgreSQL, Aurora, MySQL, Citus, and Spanner shows the same boundary: replicas copy state, while scalable writes depend on routing and coordinating ownership.
• Action: Measure the top twenty write transactions, record every table and ownership key they touch, and calculate what percentage can commit within one tenant, account, or business domain. That number should drive the next architecture—not the product label.