Scaling Database Writes: Throughput, Contention, Batching, and When to Shard

Scaling Database Writes: Throughput, Contention, Batching, and When to Shard

Write scaling is harder than read scaling. Read traffic can often be spread across caches and replicas. Writes usually have to preserve ordering, constraints, transactions, durability, and a single coherent version of the data.

That is why the right first response to a write bottleneck is not automatically sharding. Most systems should first understand where write time is being spent: transaction commits, indexes, lock contention, hot rows, connection pressure, storage latency, or unnecessary write volume.

This guide focuses on the write path itself. Database sharding and broad database-scaling architecture deserve separate treatment.

What does “write scaling” actually mean?

A database write consumes more than CPU time for an INSERT or UPDATE statement.

Depending on the database and durability configuration, a transaction may involve:

1. parsing and planning
2. acquiring locks or MVCC state
3. updating table/index pages
4. recording changes in a write-ahead/redo log
5. validating constraints
6. synchronizing durable log state
7. releasing locks
8. replicating changes to other nodes

A write bottleneck can therefore come from very different resources.

Before changing architecture, identify whether the constraint is:

• storage fsync latency
• WAL/redo generation
• CPU
• lock contention
• one hot row or index page
• too many secondary indexes
• connection/context-switch overhead
• replication lag
• network latency to a distributed quorum
• a single shard receiving most writes

“Database writes are slow” is not yet a diagnosis.

Start by measuring commit latency and throughput separately

Two important metrics tell different stories:

• write throughput — committed rows/transactions per second
• write latency — how long an individual transaction takes

You can improve throughput by batching more work into each transaction while making each transaction individually slower.

That may be excellent for ingestion and terrible for an interactive checkout API.

Define the target before tuning:

interactive order creation:
  prioritize bounded per-request latency

telemetry ingestion:
  prioritize sustainable rows/sec and batching

The same database can need very different write strategies for those workloads.

1. Remove unnecessary writes first

The cheapest write is the one you do not perform.

Common sources of avoidable write load include:

• updating a row even when no field changed
• writing “last seen” on every request
• incrementing analytics counters transaction-by-transaction
• persisting transient state that could expire elsewhere
• storing duplicate derived data synchronously
• chatty ORM behavior issuing many tiny updates

For example, instead of updating last_seen_at on every request, an application may update it only when the stored value is older than a threshold.

Reducing write amplification often gives more headroom than a larger database node.

2. Keep transactions small—but not absurdly tiny

Very large transactions can:

• hold locks longer
• generate large WAL/redo bursts
• increase rollback cost
• make replication lag spiky
• retain MVCC versions longer

But one-row autocommit transactions can also waste significant commit overhead when doing bulk ingestion.

The right batch size balances:

• commit/fsync cost
• lock duration
• memory usage
• retry cost
• user-visible latency

For bulk PostgreSQL loads, the official documentation recommends techniques such as using COPY and avoiding one commit per row. The exact mechanism differs by database, but the principle is general: amortize fixed transaction overhead when the workload permits it.

3. Batch writes for ingestion workloads

Suppose a service receives 10,000 events.

A weak design might perform:

10,000 requests
10,000 transactions
10,000 commits

A better ingestion path may group events into bounded batches:

batch 1: events 1-500
batch 2: events 501-1000
...

This reduces round trips and commit overhead.

Do not batch without limits. Huge batches can create latency spikes, large lock footprints, and expensive retries after one failure.

4. Understand durability cost before “optimizing” it away

Durable databases use write-ahead/redo logging so committed changes can survive crashes.

In PostgreSQL, WAL is central to crash recovery and replication. WAL flush behavior and checkpoints materially affect write performance. Current PostgreSQL documentation also describes group-commit behavior that can amortize durable flush cost across concurrently committing transactions.

It can be tempting to improve throughput by weakening durability settings.

That is a business decision, not a generic performance trick.

For example, asynchronous commit can reduce some commit waiting but permits recently acknowledged transactions to be lost after an operating-system crash. Disabling stronger durability controls can increase the failure window further.

Never trade away durability without explicitly documenting what acknowledged data may be lost.

5. Too many indexes tax every write

Indexes accelerate reads, but most indexes must also be maintained when relevant rows are inserted, updated, or deleted.

A table with many overlapping indexes can have a surprisingly expensive write path.

For every secondary index, ask:

• which query uses it?
• is that query important enough to justify write/storage cost?
• does another index already cover the same useful prefix?
• is the indexed column updated frequently?

Do not drop indexes blindly. Measure query plans and read requirements first.

The important architectural point is that read optimization creates write amplification.

6. Hot rows can cap throughput even on powerful hardware

Some workloads concentrate writes on one logical record:

UPDATE counters SET value = value + 1 WHERE id = 'global';

Every writer contends on the same row.

Examples of hot state include:

• one global counter
• one inventory row under extreme demand
• a single tenant/account balance
• one sequence-like coordination record
• one “current state” row for a popular resource

Adding CPU may not help because the problem is serialization around one logical item.

Possible redesigns include:

• partitioned/sharded counters and periodic aggregation
• append-only events instead of in-place updates
• per-entity rather than global state
• queues to serialize an intentionally single-writer workflow
• application-specific reservation models

You cannot horizontally parallelize a requirement that genuinely demands one strictly serialized value without changing the consistency model or data model.

7. Hot indexes and sequential identifiers can matter too

Contention is not limited to rows.

Write-heavy workloads can concentrate activity on specific index pages, partitions, ranges, or storage structures depending on the database and key distribution.

The fix is database-specific, so avoid universal advice like “always use random UUIDs.” Random identifiers can reduce one kind of locality while increasing index size, fragmentation, and cache pressure.

Choose keys with the actual database engine and query pattern in mind.

8. Connection count is not write scalability

Opening thousands of database connections does not create thousands of useful write workers.

Excessive connections can increase:

• memory usage
• scheduling overhead
• lock contention
• cache churn
• latency variance

Use a sensible connection pool and bound concurrent work.

When the database is saturated, allowing even more requests to execute concurrently can make every transaction slower.

Backpressure is often a scaling feature.

9. Queue bursty writes when the product allows asynchronous completion

Some writes do not need to finish inside the user's request.

Examples:

• analytics events
• email/audit events
• image-processing metadata
• non-critical counters
• background projections

Instead of forcing every producer to write directly to the primary database during a traffic spike:

producer -> durable queue -> bounded workers -> database

This does not increase the database's maximum sustainable write rate by itself. It smooths bursts, applies backpressure, and lets workers write in efficient bounded batches.

The queue must be durable enough for the data being deferred, and consumers should be idempotent because retries can occur.

Do not put a queue in front of writes that must synchronously participate in the user's transaction unless the product semantics actually support asynchronous completion.

10. Separate the command from derived work

One business action often triggers many secondary writes:

create order
update analytics
update search document
send event
refresh cache
build notification

If every derived side effect runs inside the primary transaction, write latency and failure coupling grow quickly.

A cleaner design often commits the authoritative business state first, then propagates derived work asynchronously.

Common approaches include:

• transactional outbox
• change data capture
• durable domain events

The core requirement is consistency: the system must not commit the business change and silently lose the event needed for downstream processing.

11. Replicas usually do not scale the primary write path

Read replicas are useful for read offload, but ordinary primary-replica architectures still send authoritative writes to the primary.

Adding replicas can even increase replication work and network volume.

Use replicas for:

• read scaling
• redundancy
• failover architecture
• backups/analytics in suitable designs

Do not describe them as horizontal write scaling unless the specific database architecture genuinely supports writes across nodes.

12. Partitioning can improve manageability without adding write nodes

Table partitioning divides data inside a database into partitions based on rules such as date or tenant/range.

It can help with:

• retention and partition dropping
• maintenance
• pruning
• local indexes
• operational isolation

But partitioning inside one primary does not automatically turn the system into multi-node write scaling.

It can remove certain bottlenecks and prepare a clean data model for future distribution, but it should not be confused with sharding.

13. Sharding is the major architectural step for write scale-out

When one database node truly cannot sustain the write workload or dataset, sharding distributes different records across independent write owners.

Example:

tenants 1-1000    -> shard A
tenants 1001-2000 -> shard B
tenants 2001-3000 -> shard C

Now writes for different shards can progress independently.

But sharding introduces major costs:

• shard-key selection
• routing
• rebalancing
• cross-shard queries
• cross-shard transactions
• global uniqueness
• migrations
• hotspots
• operational complexity

That is why sharding should solve an observed single-node limit, not a hypothetical future problem.

14. A bad shard key recreates the bottleneck

Horizontal write scaling works only if traffic distributes well.

A shard key should be evaluated for:

• cardinality
• traffic distribution
• tenant size skew
• query locality
• growth over time
• rebalancing behavior

If one tenant produces half of all writes and you shard purely by tenant, that one tenant can still overload one shard.

Sometimes a large tenant needs internal partitioning or an architecture that treats exceptionally hot customers differently.

15. Multi-primary and distributed SQL change the trade-offs

Some databases support multi-primary or distributed consensus-based writes.

These systems can remove the single-primary architecture for many workloads, but they do not make coordination free.

Strongly consistent distributed writes may require consensus across replicas, which introduces network coordination and can increase latency—especially across regions.

Evaluate distributed databases by the actual requirement:

• single-region vs multi-region
• consistency level
• transaction scope
• write locality
• failure tolerance
• latency budget

“Writes can go to any node” is not the same as “every write scales linearly with node count.”

16. Cross-region writes are a latency decision

If a transaction requires synchronous agreement across distant regions, physics becomes part of the commit path.

For globally distributed systems, decide whether the product needs:

• one primary write region
• region-local ownership of different data
• eventual convergence
• strongly consistent multi-region writes

There is no universal answer. Banking-like state and social-feed reactions may reasonably choose different consistency/latency trade-offs.

17. Observe the actual write path

Useful write-scaling metrics include:

• transactions/sec
• rows written/sec
• p50/p95/p99 commit latency
• WAL/redo bytes/sec
• WAL flush/sync time
• checkpoint behavior
• lock waits and deadlocks
• buffer/cache hit behavior
• storage latency and IOPS
• replication lag
• connection-pool saturation
• queue depth if writes are buffered
• per-tenant or per-shard write rate

For PostgreSQL specifically, facilities such as pg_stat_wal, wait events, lock views, and statement statistics help separate WAL pressure from query, lock, and application problems.

Measure before deciding that the answer is “more shards.”

A practical write-scaling sequence

For many applications, a sensible progression is:

1. Remove unnecessary writes.
2. Fix slow statements and schema/index problems.
3. Reduce lock/hot-row contention.
4. Use correct connection pooling and bounded concurrency.
5. Batch ingestion where latency semantics permit it.
6. Move derived/non-critical writes out of synchronous transactions.
7. Tune database durability/checkpoint/WAL behavior with measured evidence, without weakening guarantees accidentally.
8. Scale the machine/storage when it remains economical.
9. Partition for lifecycle/manageability where useful.
10. Shard or adopt a distributed write architecture only when one write owner is the demonstrated limit.

Write scaling is ultimately about reducing or distributing serialization points. More hardware helps only until the workload reaches a lock, log, storage, or data-model boundary that cannot execute in parallel.

References

• PostgreSQL — Reliability and Write-Ahead Log
• PostgreSQL — WAL configuration
• PostgreSQL — Performance tips
• PostgreSQL — Table partitioning