When a database stops accepting writes, the seconds stretch into an eternity. For one team at a mid-size retail company, those stalls came from two very different sources: Cassandra's compaction backlog and PostgreSQL's transaction ID wraparound protection. Over two years, they instrumented both systems, set alert thresholds, and developed runbooks that turned unpredictable freezes into manageable events. This is what they learned.

When the Write Path Freezes: Two Very Different Stalls

Cassandra compaction stalls and PostgreSQL vacuum freeze events share a surface similarity: both are maintenance operations that, under load, can block all writes to a database. But the mechanics differ sharply. In Cassandra, compaction is a background process that merges immutable SSTables on disk. When writes arrive faster than compaction can merge, the number of SSTables grows, read amplification spikes, and eventually the write path backs up. The team observed stalls lasting 10–30 minutes during peak traffic.

PostgreSQL's vacuum freeze serves a different purpose. Transaction IDs in PostgreSQL are 32-bit counters that wrap around after roughly 2 billion transactions. To prevent data loss from wraparound, the database must freeze old tuple XIDs before the counter exhausts its available IDs. This freeze pass is single-threaded and CPU-bound. On large tables, the team saw freeze stalls of 5–15 minutes, during which all write transactions were queued behind the vacuum process.

Both stalls are maintenance-induced, but their timing differs. Cassandra's compaction stalls correlate with write throughput—the more you write, the more compaction you need. PostgreSQL's freeze stalls correlate with transaction age and table size, not instantaneous write rate. A quiet database can still hit a freeze stall if autovacuum has been deferred. The team learned to monitor different leading indicators for each system.

Cassandra's Compaction: A Necessary Evil

Cassandra's LSM-tree storage engine writes incoming data to an in-memory memtable, then flushes it to an immutable SSTable on disk. Over time, multiple SSTables accumulate for the same key range. Compaction merges them into a single sorted SSTable, discarding tombstones and overwritten data. Without compaction, read performance degrades linearly with the number of SSTables.

The team used size-tiered compaction (STCS) by default. Under STCS, compaction triggers when a certain number of SSTables of similar size exist. During a traffic spike, flushes outpaced compaction, and the number of small SSTables grew from a typical 20–30 to over 200. At that point, Cassandra's internal write-backpressure kicked in, throttling writes to allow compaction to catch up. Writes queued, and latency spiked.

Stalls were most common after a burst of writes—for example, a flash sale or a bulk data load. The team observed stalls lasting 10–30 minutes, with p99 write latency rising from 5ms to over 30 seconds. The compaction process itself was not stuck; it was simply overwhelmed by the backlog. The team's key metric was the number of pending compactions per node, which they graphed alongside write latency.

Switching to leveled compaction (LCS) reduced stall frequency but increased write amplification. LCS organizes SSTables into levels, each level having a fixed number of overlapping SSTables. Compaction runs continuously in the background, keeping the number of SSTables per read path low. The team adopted LCS for tables with high read-to-write ratios, accepting the extra I/O overhead for more predictable latency.

PostgreSQL Vacuum Freeze: The Ticking Clock

PostgreSQL's transaction ID (XID) is a 32-bit unsigned integer. The database compares tuple XIDs against a cutoff to determine visibility. When the XID counter approaches the wraparound limit (roughly 2 billion transactions from the current XID), the database must freeze old tuples—mark them as visible to all future transactions—to reclaim the XID space. This is the vacuum freeze operation.

Autovacuum normally handles freeze passes incrementally. But if a table accumulates many dead tuples or if autovacuum is too conservative, a single large freeze pass can block writes. The team saw freeze stalls on their largest table, a 500 GB order history table, when the age of the oldest unfrozen XID exceeded 150 million. The freeze pass scanned the entire table, reading every page, and took 5–15 minutes. During that time, all write transactions on that table were blocked by the vacuum process's ShareUpdateExclusiveLock.

The stall was single-threaded and CPU-bound. The team observed 100% CPU on a single core during the freeze, with disk I/O at 80–100 MB/s. Write latency p99 jumped from 2ms to over 60 seconds. Unlike Cassandra, where the stall affected only nodes with the compaction backlog, PostgreSQL's freeze stall affected all connections writing to that table—including those on replicas, because vacuum runs on each replica independently.

The team traced root causes to two settings: autovacuum_vacuum_scale_factor (default 0.2) and autovacuum_freeze_max_age (default 200 million). With the scale factor at 0.2, autovacuum didn't trigger until 20% of a table's rows were dead. For a 500 GB table, that meant vacuuming only after 50 GB of churn. They lowered the scale factor to 0.01 and set a per-table autovacuum_freeze_max_age of 100 million. Freeze stalls dropped from monthly to quarterly.

What the Team Tracked: Metrics That Mattered

The team built a unified dashboard for both databases, tracking five primary metrics. For Cassandra, the number of pending compactions per node was the leading indicator. A count above 50 typically preceded a stall within 15 minutes. They also tracked SSTable count per node, which correlated with read latency. For PostgreSQL, the age of the oldest unfrozen XID (in millions) gave 24–48 hours of warning before a freeze stall. They also tracked the number of dead tuples in each table, normalized by table size.

Write latency p99 was the common outcome metric. For Cassandra, they measured client-side write latency via the DataStax driver. For PostgreSQL, they measured query latency at the application layer. Any sustained p99 above 500ms triggered a review. The team also tracked disk I/O utilization during maintenance operations. During a Cassandra compaction, disk write throughput often reached 200–300 MB/s per node, while PostgreSQL freeze reads peaked at 100–150 MB/s.

Alert thresholds were set empirically. The team started with conservative values—pending compactions above 20, XID age above 100 million—and tuned upward as they gained confidence. The final thresholds were: pending compactions above 50 for more than 5 minutes, and XID age above 150 million. Both triggered a page to the on-call engineer. The team also built a runbook that included steps to force compaction or manually trigger vacuum freeze.

One surprising finding: the team's alert for disk I/O saturation was less useful than expected. Both compaction and freeze are I/O-intensive, but the stalls themselves were not I/O-bound—they were caused by queuing effects and lock contention. Disk I/O was a symptom, not a root cause. The team stopped alerting on disk I/O and focused on queue depth (pending compactions) and transaction age instead.

Mitigation Strategies That Actually Worked

For Cassandra, the most effective mitigation was throttling compaction via concurrent_compactors. The default of 4 compactors per node could saturate disk I/O during a backlog. The team reduced it to 2 for write-heavy workloads, which smoothed out I/O and prevented the compaction queue from growing faster than it could drain. They also increased compaction_throughput_mb_per_sec from 16 to 64 MB/s, allowing compaction to catch up faster during low-traffic windows.

For PostgreSQL, the key tuning was vacuum_cost_limit and autovacuum_vacuum_cost_limit. The default cost limit of 200 caused vacuum to sleep frequently, prolonging freeze passes. The team raised it to 1000, which reduced freeze duration by 40%. They also set autovacuum_naptime to 30 seconds (down from 1 minute) to make autovacuum more responsive. Combined with a scale factor of 0.01, these changes reduced freeze stalls by 80%.

Both systems benefited from scheduling maintenance during low-traffic windows. For Cassandra, the team used cron jobs to trigger compaction on specific tables at 3 AM UTC, when write rates were lowest. For PostgreSQL, they manually issued VACUUM FREEZE on large tables once a month during a maintenance window, forcing the freeze pass to happen at a predictable time. This proactive approach eliminated surprise stalls entirely for the PostgreSQL side.

One operational decision the team debated was whether to switch from Cassandra to a different NoSQL store. After evaluating alternatives, the team concluded that the compaction stalls, while annoying, were well-understood and manageable. Their investment in monitoring and automation made the system reliable enough. The total engineering time spent on compaction issues was about 40 hours per quarter, which they considered acceptable given Cassandra's write throughput advantages. Others in similar situations might reach a different conclusion depending on their tolerance for operational overhead.

Why One Architecture Isn't Clearly Better

Comparing Cassandra and PostgreSQL on stall behavior reveals a fundamental trade-off. Cassandra's distributed design spreads the risk: a compaction stall on one node doesn't affect others, and clients can retry on a different replica. However, the complexity of tuning compaction strategies and managing SSTable counts requires ongoing attention. The team spent more time on Cassandra maintenance than on PostgreSQL, but the impact of any single stall was localized.

PostgreSQL's single-node freeze stall is simpler to debug but more catastrophic when it occurs. Because all writes to a table are blocked, a freeze stall can cascade into application timeouts and connection pool exhaustion. The team's largest outage—a 22-minute write outage—was caused by a PostgreSQL freeze stall on the orders table during a Black Friday sale. The recovery was straightforward (wait for vacuum to finish), but the blast radius was total.

Operational complexity vs. predictability is the real axis. Cassandra requires more moving parts: nodetool commands, compaction strategy decisions, and per-node monitoring. PostgreSQL's vacuum is a single setting, but its behavior under load is harder to predict without deep knowledge of the table's write pattern. The team found that PostgreSQL's freeze stalls were more reproducible—they could simulate them in staging with a synthetic workload—while Cassandra's compaction stalls depended on real traffic patterns.

Neither architecture is clearly superior. The choice depends on your tolerance for complexity vs. your tolerance for rare but severe outages. The team's advice: pick the database that matches your team's operational maturity. If you have the staffing to manage Cassandra's compaction tuning, its distributed fault isolation is a net win. If you prefer simpler operations and can accept occasional freeze stalls, PostgreSQL is the safer bet.

Practical Takeaways for Production Teams

First, monitor stall metrics before they cause outages. For Cassandra, track pending compactions and SSTable count per node. For PostgreSQL, track the age of the oldest unfrozen XID and the number of dead tuples. Set alerts at thresholds that give you time to react—at least 15 minutes of lead time. The team's freeze stall alerts often came 24 hours before the actual stall, allowing them to schedule a manual vacuum during off-peak hours.

Second, test compaction and vacuum under synthetic write load. The team built a simple load generator that wrote at 2x the expected peak rate for 30 minutes. They recorded the number of pending compactions, write latency, and freeze XID age. This test revealed that their default autovacuum settings were too conservative, leading to the freeze stalls they later mitigated. Testing in staging prevented a production outage.

Third, document recovery steps. For Cassandra, the runbook included: identify the node with highest pending compactions via nodetool compactionstats, reduce write load by routing traffic away from that node, and increase concurrent_compactors temporarily (if disk I/O permits). For PostgreSQL, the runbook was simpler: run VACUUM FREEZE on the affected table, monitor progress via pg_stat_progress_vacuum, and be prepared for 5–15 minutes of write blocking. Automation of these steps reduced mean time to recovery from 45 minutes to 12 minutes.

Finally, consider a hybrid approach. Use Cassandra for high-write-throughput workloads—like session storage or event logging—where occasional compaction stalls are acceptable. Use PostgreSQL for transactional consistency—like order processing or inventory—where write blocking is more damaging but rarer. The team's architecture evolved to split workloads across both databases, a choice that balanced the trade-offs. The two-year tracking project gave them the data to make that call with confidence, but the decision remains context-dependent.

Case Study: A Black Friday Freeze Stall

To illustrate the real-world impact, consider the team's most severe PostgreSQL freeze stall, which occurred during Black Friday. The orders table, already under heavy write load from the sale, had accumulated a large number of dead tuples because autovacuum had been deferring freeze passes due to the high write rate. At 2:15 PM, the XID age crossed the 200 million threshold, and PostgreSQL initiated a full-table freeze pass. Within seconds, all write queries on the orders table began queuing. The team's pager fired, but by the time the on-call engineer logged in, write latency p99 had already exceeded 60 seconds.

The freeze pass took 22 minutes to complete because the table was 500 GB and the vacuum cost limit was still at the default. During those 22 minutes, the application's connection pool filled with blocked writers, causing cascading timeouts in downstream services. The checkout flow failed entirely, and the team had to redirect traffic to a read-only fallback page. After the freeze finished, writes resumed, but it took another 10 minutes for the connection pool to drain and latency to return to normal. The total outage duration was 32 minutes.

Post-mortem analysis revealed that the autovacuum scale factor of 0.2 was too high for a table with such high churn. The team had previously tuned the scale factor to 0.01 for other tables but had missed the orders table because it was added in a recent migration. They also discovered that the autovacuum_freeze_max_age default of 200 million was too close to the wraparound limit, leaving no buffer for spikes. After the incident, they set a per-table autovacuum_freeze_max_age of 100 million for all large tables and added a dashboard alert for XID age crossing 150 million. The freeze stall never recurred during subsequent sales events.