Before you split your database into a distributed cluster—introducing two-phase commit overhead, distributed deadlocks, and cross-node network latency—you should know this:
A single optimized PostgreSQL instance on modern hardware can sustain over 100,000 ACID writes per second.
Most engineering teams leap prematurely into horizontal sharding (Citus, Vitess, Spanner) when their bottleneck is not PostgreSQL’s relational engine, but naive connection concurrency, single-row INSERT patterns, and uncalibrated Write-Ahead Log (WAL) disk flush barriers.
Here is the exact production engineering playbook to push PostgreSQL to 100,000+ writes per second on a single machine.
1. The Physics of 100k Writes/sec: The Latency Budget
To achieve 100,000 writes per second:
\[\text{Throughput} = \frac{\text{Batch Size}}{\text{Round-Trip Network Latency} + \text{Disk WAL Flush Time}}\]If every write is an isolated INSERT INTO events VALUES (...) with synchronous round-trip acknowledgment:
- Even at 1ms local network ping, single-threaded throughput caps at 1,000 writes/sec.
- With 100 concurrent connections, you hit a hard wall at 10,000 to 15,000 writes/sec due to WAL lock contention.
To reach 100,000 writes/sec, we must transition from single-statement row-by-row transactions to micro-batched ingest pipelines.
2. Kernel & Storage Architecture: Eliminating the WAL Wall
The primary bottleneck for relational database writes is the storage barrier: fsync().
| Configuration Setting | Default Value | 100k Writes/sec Target | Technical Rationale |
|---|---|---|---|
synchronous_commit |
on |
off or local |
Acknowledges transaction once committed in memory buffer; flushes asynchronously every wal_writer_delay (prevents per-transaction I/O stall). |
wal_writer_delay |
200ms |
10ms |
Tightens the asynchronous flush window, limiting maximum potential data loss window to 10ms of transactions upon sudden power severance. |
commit_delay |
0 |
20 to 50 (microseconds) |
Enforces group commit: batches concurrent transactions into a single physical NVMe flush. |
commit_siblings |
5 |
10 |
Minimum concurrent transactions required before commit_delay engages. |
max_wal_size |
1GB |
64GB - 128GB |
Eliminates frequent background checkpoint spikes that stall incoming writes. |
checkpoint_completion_target |
0.9 |
0.95 |
Smooths out disk I/O over the full checkpoint window. |
3. High-Throughput Ingestion Patterns
A. The Binary COPY Protocol (250,000+ rows/sec)
Never use ORM save() loops or plain INSERT statements for high-throughput ingestion. Utilize the PostgreSQL binary streaming COPY FROM STDIN (FORMAT binary) protocol via pgx (Go) or asyncpg (Python).
B. Micro-Batching with UNNEST()
If application semantics require parameterized SQL:
INSERT INTO sensor_telemetry (device_id, payload, recorded_at)
SELECT * FROM UNNEST(
$1::uuid[],
$2::jsonb[],
$3::timestamptz[]
);
Passing arrays of 500 to 2,000 elements transforms 1,000 round-trips into a single parse-bind-execute cycle.
4. Indexing Discipline: The Write Amplification Tax
Every index on a table multiplies the write amplification factor:
- 1 Table with 5 Indexes = 1 heap write + 5 separate B-tree balance updates.
- Under 100,000 writes/sec, 5 indexes generate 600,000 storage modifications per second.
Production Rules:
- Drop Unused Indexes: Keep only primary key and critical point-lookup query paths.
- Use BRIN Instead of B-Tree for Append-Only Time-Series: Block Range Indexes (BRIN) take 99% less memory and write overhead than standard B-Trees by indexing page min/max values rather than every row.
- Partition by Range (Daily/Weekly): Eliminates B-tree re-balancing lock contention on massive multi-gigabyte index trees.
5. Summary Checklist Before You Shard
- Verify NVMe Direct-I/O Bandwidth: Ensure your storage delivers >400,000 random write IOPS.
- Deploy PgBouncer in Transaction Pooling Mode: Cap active PostgreSQL worker processes to 2x CPU core count to eliminate context switching.
- Adopt Binary COPY or Micro-Batching: Never send single-row INSERT statements over the wire.
- Tune WAL Group Commit: Let concurrent writes share physical disk flush cycles.
Get Weekly AI Architect Cost & Strategy Updates
Join 14,000+ developers receiving weekly, data-driven cost-reduction blueprints and production-ready agent guidelines.