Postgres Isn't Slow: Your Storage Is Holding It Back

When a PostgreSQL cluster grows to hundreds of gigabytes or terabytes, the storage subsystem often becomes the bottleneck that manifests as several distinct scaling symptoms. Understanding how each symptom maps to storage behavior is essential before applying any tuning or architectural changes.

• Slow ingestion – Workloads dominated by UPDATE and UPSERT statements rely on frequent page writes and WAL flushes. As the working set exceeds the OS page cache, each write must wait for physical I/O. On network‑attached storage, the added latency (often tens of milliseconds) stretches a job that previously completed in seconds into minutes.
• Inconsistent read latency – Most queries may remain fast, but a subset experiences tail latency spikes. This occurs when cache misses force reads from slower storage; the P95 latency can jump from milliseconds to seconds because the storage queue is saturated and I/O requests compete for limited IOPS.
• Autovacuum lag – Vacuum processes need to read and rewrite pages to reclaim space. If the storage cannot keep up with the combined load of user updates and vacuum I/O, autovacuum falls behind, allowing table bloat to grow from gigabytes to terabytes.
• Longer checkpoints – A checkpoint writes dirty buffers to disk and forces WAL files to be flushed. When storage bandwidth is constrained, checkpoints compete with foreground queries for the same I/O budget, turning an otherwise invisible operation into a noticeable latency spike.
• Logical replication lag – Decoding WAL for downstream systems (e.g., Kafka, ClickHouse) requires reading recent WAL segments. Slow storage prolongs WAL reads and WAL‑fsync, causing the replication slot to accumulate backlog and increasing end‑to‑end replication delay.

All five symptoms share a common root: the storage layer cannot deliver the required throughput, latency, or queue depth. Physical I/O latency (e.g., 19 ms on gp3 EBS versus 0.3 ms on local NVMe) directly adds to transaction time, while IOPS limits cause sessions to wait on IO:DataFileRead events. When storage is the limiting factor, CPU remains under‑utilized because processes spend most of their time blocked on I/O.

Practical mitigation starts with measuring storage metrics (latency, IOPS, queue depth) and matching workload characteristics to the appropriate storage class. For ingestion‑heavy pipelines, local NVMe can reduce page‑write latency from milliseconds to microseconds, allowing autovacuum and checkpoints to run concurrently without starving user queries. Similarly, faster WAL flushes improve logical replication throughput, keeping replication lag within acceptable bounds.

To isolate storage as the sole variable, the benchmark constructed eight PostgreSQL clusters that were identical in every respect except the underlying block device. Each cluster ran on an m6id.4xlarge instance, providing 16 vCPUs, 64 GiB of RAM, and a shared_buffers setting of 16 GiB. Four clusters used the instance‑store NVMe volume, and the remaining four used Amazon gp3 EBS provisioned at 3,000 IOPS.

The database schema was a single pgbench_accounts table sized at 482 GiB and populated with 3.3 billion rows. This size ensures the working set exceeds the memory allocated to PostgreSQL, forcing the system to read and write data from the storage layer during the test.

• Instance type: m6id.4xlarge (16 vCPU, 64 GiB RAM)
• Storage configurations: 4 × local NVMe, 4 × gp3 EBS @ 3,000 IOPS
• Database size: 482 GiB pgbench_accounts table, 3.3 billion rows
• Workload: Random UPDATE of abalance column
• Concurrency: 64 client connections, 16 worker threads per cluster
• Duration: 5 minutes per run

The transactional workload was executed with pgbench using the following script, which selects a random aid and increments the balance:

BEGIN;
SELECT aid FROM pgbench_accounts ORDER BY random() LIMIT 1 INTO aid;
UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid = aid;
COMMIT;

All clusters were started simultaneously, and performance metrics (transactions per second, median latency, and backend wait states) were collected via pg_stat_activity and pg_stat_bgwriter. By keeping CPU, memory, and PostgreSQL configuration constant, any observed differences can be attributed directly to the storage subsystem’s I/O latency, bandwidth, and queue depth.

This design enables engineers to quantify the impact of storage on high‑concurrency update workloads, evaluate the trade‑offs of local NVMe versus network‑attached EBS, and make data‑driven decisions about hardware provisioning for latency‑sensitive PostgreSQL services.

Running PostgreSQL on instance‑store NVMe delivers microsecond‑scale I/O, but the storage disappears when the EC2 instance terminates. Production readiness therefore depends on three orthogonal controls: (1) quorum‑based synchronous replication across availability zones (AZs), (2) continuous WAL‑G archiving to an external object store, and (3) capacity planning that respects the fixed NVMe size of the chosen instance type.

1. Quorum‑based synchronous replication

PostgreSQL’s synchronous replication can be configured with a ANY 1 (standby1, standby2) quorum. A transaction commits once any one standby acknowledges the WAL write, allowing the primary to avoid the latency of the slower replica while still guaranteeing durability.

• AZ distribution: Deploy the primary in AZ A and the two standbys in AZ B and AZ C. This topology survives an entire AZ outage because the remaining standby still satisfies the quorum.
• Failover path: If the primary node fails, the surviving standby that satisfied the quorum can be promoted with pg_ctl promote or via a cluster manager such as Patroni.
• Example configuration snippet:

  synchronous_standby_names = 'ANY 1 (standby1, standby2)'
  primary_conninfo = 'host=primary port=5432 user=replicator password=***'
  

2. Continuous WAL‑G backups

WAL‑G provides base‑backup creation and incremental WAL archiving to an object‑storage bucket (e.g., Amazon S3). The workflow is:

1. Run walg backup-push on a schedule (e.g., every 30 minutes) to capture a consistent snapshot.
2. Configure archive_command = 'walg wal-push %p' so each completed WAL segment is uploaded immediately.
3. Retain backups in a separate failure domain and optionally replicate them to a second region for disaster recovery.

This approach yields an RPO measured in seconds and supports point‑in‑time recovery, branch creation for testing, and seeding of new read replicas.

3. Capacity planning for instance‑store NVMe

Instance‑store NVMe capacity is fixed per instance type and cannot be expanded after launch. Engineers must therefore:

• Calculate the expected data growth (including tables, indexes, and WAL) against the NVMe size (e.g., 1 TB on an i3en.large).
• Reserve a safety margin (typically 20‑30 %) for temporary files generated by VACUUM, autovacuum, and checkpoint activity.
• Monitor pg_stat_bgwriter and OS‑level I/O metrics to detect when the working set exceeds the NVMe cache, which would re‑introduce latency spikes.

When the margin is exhausted, the recommended actions are to scale to a larger instance class or to add a second NVMe‑backed node and redistribute data via partitioning or sharding.

By combining quorum‑based synchronous replication, WAL‑G continuous archiving, and disciplined capacity sizing, PostgreSQL on local NVMe can meet enterprise durability, availability, and performance requirements without relying on network‑attached storage.

When PostgreSQL workloads grow beyond the RAM‑resident working set, the storage subsystem becomes the primary bottleneck. Reads that miss the OS page cache must wait on physical I/O, and writes—including WAL flushes, checkpoint activity, and vacuum—compete for the same bandwidth and IOPS. The result is higher average latency, longer tail latency (P95), and background tasks that fall behind, all of which can manifest as ingestion slow‑downs, erratic query response times, and replication lag.

Instacart publicly described moving a latency‑sensitive search service to PostgreSQL backed by local NVMe. By placing the data files on instance‑store NVMe, the service reduced read‑latency from the tens‑of‑milliseconds range typical of network‑attached storage to microsecond‑scale access. This change eliminated the I/O queuing that previously caused unpredictable query spikes during peak search traffic.

Datadog has similarly reported deploying PostgreSQL instances on local NVMe for workloads that demand sub‑millisecond response times. Their architecture pairs the fast local storage with synchronous replication to standby nodes, ensuring that the performance gains of NVMe do not compromise high availability.

The benchmark presented at POSETTE reinforces these observations: the same 3.3‑billion‑row workload achieved a median throughput of 16,030 TPS on NVMe versus 1,734 TPS on gp3 EBS, and median transaction latency dropped from 36.9 ms to 4.0 ms. The majority of the EBS latency originated from page reads (≈19 ms) and WAL fsync (≈11 ms), confirming that storage latency—not CPU processing—was the dominant factor.

Based on these real‑world examples and the benchmark data, teams confronting storage‑induced PostgreSQL slowdown should consider the following actions:

• Profile query latency and identify I/O‑bound phases (e.g., page reads, WAL flushes) using pg_stat_activity and pg_stat_io.
• Evaluate the feasibility of migrating hot tables or the entire cluster to local NVMe, ensuring sufficient instance‑store capacity.
• Implement quorum‑based synchronous replication (e.g., ANY 1 of two standbys) to retain durability while benefiting from low‑latency local storage.
• Offload durability to object‑storage‑based backups (e.g., WAL‑G to S3) for disaster recovery and point‑in‑time restores.
• Continuously test failover and backup restoration paths to verify that performance gains do not introduce availability gaps.