Postgres on NVMe: Speeding Up Transactions

The NVMe Advantage for PostgreSQL

The article effectively highlights the dramatic performance gains achievable by leveraging local NVMe storage for PostgreSQL, particularly for transactional workloads that exceed memory capacity. The benchmark results, demonstrating a 9.2x throughput increase and a significant reduction in latency, are compelling. The explanation of how faster storage mitigates common scaling bottlenecks like slow ingestion, inconsistent reads, and VACUUM lag is clear and well-supported by technical details. The authors also proactively address the perceived limitation of local NVMe's ephemerality by proposing robust solutions like quorum HA with streaming replication and continuous WAL archival, effectively decoupling durability from compute. This approach offers a pragmatic path to achieving high performance and resilience.

However, the article primarily focuses on transactional (OLTP) workloads and implicitly positions PostgreSQL for this domain, while suggesting a separate system like ClickHouse for analytical (OLAP) workloads. While this is a common and often pragmatic architecture, it might overlook potential advancements or configurations within PostgreSQL itself that could bridge the gap for certain analytical use cases, especially with the advent of columnar extensions or optimized table structures. The discussion on analytical workloads could be expanded to explore how PostgreSQL, even with its row-store nature, might be optimized or integrated with other technologies for mixed workloads more holistically. Furthermore, while the article mentions WalShadow for sub-second replication, a deeper dive into its implementation details, potential overheads, and comparison with existing CDC solutions like PeerDB would add further value. The focus on open-source solutions is a significant positive, encouraging adoption.

Key Points

• Running PostgreSQL on local NVMe storage dramatically improves transactional performance by reducing I/O latency, especially when the working set exceeds RAM.
• Common scaling issues in PostgreSQL (slow ingestion, read inconsistency, VACUUM lag) are often caused by storage I/O bottlenecks when data overflows memory.
• Local NVMe storage reduces cache miss latency to microseconds, making the system behave as if it has more RAM and enabling predictable performance.
• Ephemerality of local NVMe can be mitigated by combining it with PostgreSQL's quorum synchronous streaming replication across Availability Zones and continuous WAL archival to object storage (like S3) for durability and recoverability.
• While NVMe boosts transactional performance, it doesn't fundamentally change PostgreSQL's row-store architecture, making it less ideal for analytical workloads that require scanning large datasets.
• For analytical workloads, a columnar database like ClickHouse is more suitable, and a common architecture involves using Change Data Capture (CDC) to stream data from PostgreSQL to ClickHouse for near real-time analytics.
• Open-source tools like PeerDB (or the newer WalShadow), pg_clickhouse, WAL-G, and Patroni/repmgr are crucial for building this combined transactional and analytical data platform.

---

📖 Source: Postgres on NVMe: performance and the convergence of transactions and analytics