Postgres as an isolation reference model

I’ve done my share of system design interviews over the years. Some went my way, and others didn’t. One thing stayed the same: transaction isolation almost always came up whenever the problem involved an online system.

When the requirements call for a CP system, I usually reach for a PostgreSQL-compatible NewSQL database such as Aurora PostgreSQL, DSQL, or CockroachDB. Andy Pavlo and Matthew Aslett define NewSQL as relational databases that aim for NoSQL-like scale while keeping SQL and ACID transactions.

Part of that is because I like thinking in Postgres. There’s also the usual interview move: the system starts at a modest scale, then the interviewer cranks it up by 100x for shits and giggles. With regular Postgres, that may mean manual sharding or switching databases. Either option can change the transaction boundary and force a substantial redesign. NewSQL lets me keep the relational model and distributed transactions as the system grows.

That still leaves the isolation question. Postgres is my reference model. If MongoDB, DynamoDB, Cassandra, or another database fits the problem better, I compare its guarantees with Postgres’s Read Committed, Repeatable Read, and Serializable isolation levels. From there, I can name the closest Postgres equivalent and explain where the guarantees differ.

The same approach works outside interviews. When I run into an unfamiliar OLTP or SQL OLAP database, I compare its snapshot semantics and conflict handling with Postgres before trusting the isolation-level name. This is especially useful for analytical databases that offer snapshot reads but no multi-statement transactions. Even when both use MVCC, a statement-level snapshot behaves more like Postgres’s Read Committed than Repeatable Read. ClickHouse is a good example.

Postgres gives me a concrete reference model that’s close to the SQL standard.

• Read Committed takes a new snapshot for each statement, so two reads in the same transaction can see different committed states.
• Repeatable Read uses one snapshot for the transaction and prevents non-repeatable and phantom reads, but serialization anomalies such as write skew can happen.
• Serializable adds dependency tracking through Serializable Snapshot Isolation (SSI) and aborts transactions that can’t be ordered serially.

Here is how I stack a few common databases against that model.

Cassandra is a useful edge case because there isn’t an isolation level that lines up with Postgres. A normal CQL read or write stands on its own. You can’t open a Read Committed or Repeatable Read transaction and run several statements against one snapshot. Batches group writes, but isolation stops at the partition boundary. Lightweight transactions use Paxos for linearizable compare-and-set, which is the closest match to Serializable. That guarantee covers only a conditional change inside one partition. Postgres can enforce a wider invariant inside a Serializable transaction. With Cassandra, I have to keep that invariant in one partition or coordinate it in the application.

Rather than learn every database’s isolation semantics from scratch, I map its behavior to the closest Postgres level, then focus on gaps in guarantees and transaction scope. This has worked well for me so far.

Snapshot isolation is my favorite isolation level in any database that offers it. It catches write-write conflicts. Serializable catches those too, along with read-write dependencies. That stronger guarantee prevents write skew, which snapshot isolation allows.

But that stronger guarantee comes with more bookkeeping and can lower throughput. SSI tracks the rows and predicates each transaction reads. Large read sets and broad predicates can overlap more writes and cause more serialization failures. Avoiding those failures often means narrowing the transaction scope or skipping reads that would make the code easier to reason about.

With snapshot isolation, I can often prevent write skew directly. If an invariant maps to existing rows, SELECT FOR UPDATE can lock them and force competing transactions to conflict. If the invariant depends on missing rows or an arbitrary predicate, there may be nothing to lock, so I use Serializable. I tend to avoid designs like that. In practice, snapshot isolation with targeted locks gives me what I need without paying the full cost of Serializable. Marc Brooker makes a similar case in this fantastic post.