Why Postgres and Cassandra Made Opposite Bets on Storage Engines

A write-heavy ingestion table starts slow on Postgres. Not immediately, but a few weeks in: inserts that used to take a millisecond now take ten, autovacuum is perpetually behind, and EXPLAIN ANALYZE shows time going into index maintenance nobody remembers configuring. The instinct is to blame the schema, or the hardware, or "Postgres doesn't scale." None of those are quite right. The real answer is a decision Postgres made in the 1990s, long before this table existed: it keeps its indexes as B-Trees, and every one of them has to stay sorted, on every write.

---

Every Write Has to Land Somewhere

Strip a database down to its storage engine and the job is always the same: take a write, put it on disk in a shape that makes future reads fast, and don't lose it. There are two dominant answers to how to do that, and most databases you've heard of lean on one.

B-Trees keep some part of the data sorted on disk, in place, at all times. MySQL's InnoDB and SQLite go all the way: the table itself is a B-Tree, keyed by its primary key or rowid, so the table and its main index are the same structure (a "clustered index"). Postgres is more layered: the table (the heap) is an unordered file with no sort order of its own, rows just go wherever the free space map says there's room, but every index on that table, including the primary key, is a separate B-Tree that has to stay sorted and has to be updated on every write that touches an indexed column.

LSM-Trees, log-structured merge trees (Cassandra, RocksDB, LevelDB, and CockroachDB's Pebble storage engine), take the opposite approach everywhere: a write never touches its final sorted position immediately. It gets appended to whatever's currently open, and getting everything back into sorted order is a job for later, done in the background, in bulk.

That fork, sorted-in-place versus sorted-later, explains almost every practical difference in how these two families of databases behave under load.

B-Trees: Pay at Write Time, Save at Read Time

Think of each B-Tree index as a filing cabinet that's always perfectly alphabetized. Every entry goes directly into its correct folder, in its correct position, the moment it arrives. Finding anything later is fast and predictable, you walk straight to the folder. But filing it correctly in the first place means locating the right spot, possibly shifting other entries out of the way, and writing to a specific place on disk rather than just the next free spot.

Here's what a Postgres insert actually does. The row itself is appended to the heap, close to a sequential write, wherever the free space map finds room. But every index on that table, the primary key, any unique constraint, any column you've indexed for lookups, is a B-Tree, and each one needs a new entry written to its correct leaf page, wherever that page happens to sit on disk. Two indexes on a table means one heap append plus two separate random writes, every single insert.

Updates are the more expensive case, and this is where MVCC changes the arithmetic. Postgres never overwrites a row in place: an UPDATE marks the old row version dead and writes an entirely new version elsewhere in the heap. That means an UPDATE costs everything an INSERT costs, a new heap entry, plus a new entry in every index, plus it leaves a dead tuple behind that autovacuum eventually has to clean up.

Why this shows up in production: early in a table's life, its hot pages and index pages fit comfortably in shared_buffers, so those "random" writes are really just writes to RAM, flushed to disk lazily and cheaply. Once the table and its indexes outgrow memory, every insert or update has a real chance of touching an index page that isn't cached, turning a cheap write into a disk seek, on every indexed column, on every write. Add autovacuum needing to revisit the dead tuples MVCC leaves scattered across heap pages, and you get the exact symptom this post opened with: a table that used to be fast getting steadily slower with no code change, just growth, and just more indexes to maintain per write.

LSM-Trees: Write First, Sort Later

An LSM-Tree handles the same problem by refusing to do that work up front. Picture an inbox tray instead of a filing cabinet: every new entry just gets dropped on top of the pile. Filing is instant, because there's no filing, you're not finding anything, you're just adding to a stack. Periodically, someone takes the whole pile, sorts it properly, and merges it into the already-sorted sections below it.

Mechanically: writes go into an in-memory structure (a memtable) and are also logged to a commit log for durability, both sequential operations. When the memtable fills up, it's flushed to disk as an immutable sorted file (an SSTable). Over time you accumulate many of these sorted files, and a background process called compaction merges them together, discarding values that have been overwritten or deleted along the way.

Deletes are handled the same indirect way updates are avoided: a delete doesn't remove anything immediately, it writes a tombstone, a marker saying "this key is deleted as of this timestamp." The actual old data only disappears once compaction physically rewrites the SSTables that contained it.

Why this shows up in production: writes are cheap and consistently fast, because they're always sequential appends, regardless of how big the dataset gets or where the "right place" for the data would eventually be. That's why Cassandra-style databases are the default reach for write-heavy workloads: time-series ingestion, event logs, metrics pipelines. The cost is deferred, not eliminated. A read might have to check the memtable and several SSTables before it can be sure it has the latest version of a key, that's read amplification, and it's the mirror image of the index-maintenance cost a B-Tree pays on every write. Compaction itself is a real, ongoing background cost too: it consumes disk I/O and CPU that has to be budgeted for, and if compaction ever falls behind sustained write load, both read latency and disk usage climb, in a way that looks a lot like Postgres autovacuum falling behind, just triggered by the opposite kind of pressure.

"Just Switch to Cassandra" Isn't the Fix It Sounds Like

It's tempting to read the above and conclude LSM-Trees are strictly better for anything write-heavy. They're not, they're differently shaped, with their own failure modes:

• Read amplification is real. A point lookup that's a single B-Tree traversal in Postgres might mean checking a memtable plus several SSTables in an LSM-Tree, unless bloom filters and careful compaction keep that bounded.
• Compaction is not free. It's extra write I/O (rewriting data that was already written once), and a compaction strategy mismatched to the workload (size-tiered vs. leveled) can make total write cost in an LSM-Tree worse than a B-Tree's, not better.
• Tombstones linger. Until compaction actually removes the old versions and their tombstones, deleted data still occupies space and still gets scanned past on reads, and in Cassandra specifically, an excess of tombstones scanned in a single read can trip query-side warnings or failures.
• Transactional guarantees differ. Postgres gives you multi-row ACID transactions and foreign keys as first-class features because the B-Tree indexes, MVCC, and WAL were co-designed for exactly that. Most LSM-based systems trade some of that away for horizontal write scale, which is a real cost if your workload actually needs cross-row consistency, not just fast ingestion.

Migrating a workload from Postgres to Cassandra to solve a write-scaling problem, without checking whether the workload also needs the guarantees Postgres was giving up in the trade, is how you end up rebuilding transactional consistency by hand in application code, which is a worse position than the one you started in.

When Each Model Actually Wins

The honest framework isn't "which database is better," it's "which cost am I willing to pay":

• Choose a B-Tree-indexed engine (stay on Postgres, tune it) when reads dominate, or when transactional consistency and relational integrity across tables matter more than raw write throughput. Most application backends, most OLTP workloads, most systems where "this row is correct right now" matters, belong here.
• Choose an LSM-Tree engine when the workload is write-dominated and append-heavy by nature, event streams, time-series metrics, logs, and reads can tolerate either eventual consistency or a slightly higher read cost in exchange for writes that don't degrade as the dataset grows.
• Or, more often than either extreme: the write-heavy table causing the pain doesn't need to be in Postgres at all. Partitioning it, moving it to a purpose-built time-series or log store, or reducing the number of indexes it maintains, frequently solves the actual problem without touching the primary transactional database's engine at all.

Postgres isn't losing to Cassandra when a table gets slow under heavy writes. It's honoring the trade it made on day one: keep every index sorted and correct on every write, so reads stay cheap and consistent forever. Whether that's still the right trade for a specific table, and how many indexes it really needs, is a workload question, not a verdict on the database.