A return to Postgres isolation
Over the years, I’ve participated in a few system design interviews. Some went my way and others didn’t. One thing has stayed consistent: when I’m designing an online system, transaction isolation almost always comes up in the discussion.
When the requirements call for a CP system, I usually go 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 the reason is that I like thinking in Postgres. There’s also the usual interview move where a system starts at a modest scale and then the interviewer cranks it up by 100x for shits and giggles. With shared-nothing Postgres, I may suddenly need manual sharding or a different database. Either choice can change the transaction boundary and force a substantial redesign. NewSQL lets me keep the relational model and distributed transactions across a wider range of scales.
That still leaves the isolation question. I use Postgres as the 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. I can then describe the closest Postgres equivalent and explain the differences.
I use the same approach 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 don’t support a multi-statement transaction. A statement-level snapshot is closer to Postgres’s Read Committed behavior than to its Repeatable Read isolation, even if both use MVCC underneath. Think ClickHouse.
Postgres gives me a concrete reference model that is pretty 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.
| Database | Transaction and isolation model | Closest Postgres reference | Highlights |
|---|---|---|---|
| PostgreSQL | Interactive transactions across rows and tables. Read Committed is the default, Repeatable Read provides snapshot isolation, and Serializable uses SSI. | Baseline | Repeatable Read can allow write skew. Serializable may return 40001, requiring the application to retry the entire transaction. |
| Aurora DSQL | Interactive distributed transactions using strong snapshot isolation and optimistic concurrency control. | Repeatable Read | DSQL detects write-write conflicts but doesn’t validate ordinary read-write dependencies. Write skew can happen. Conflicts return 40001, and SELECT FOR UPDATE can make the relevant read dependencies participate in conflict detection. |
| MongoDB | Single-document operations are atomic. Multi-document transactions can span collections, databases, and shards. snapshot read concern with majority write concern provides a synchronized snapshot across shards. | Repeatable Read | MongoDB doesn’t have a general Serializable level. Read concern controls visibility, while write concern controls durability. Write skew can happen in snapshot transactions, and write conflicts require retries. |
| DynamoDB | TransactWriteItems and TransactGetItems provide one-shot transactions over at most 100 known items. Transactional operations are Serializable relative to other transactions and individual item operations. | Serializable over a bounded item set | The guarantee covers the items named in the request. Query, Scan, and BatchGetItem are Read Committed as aggregate operations. There’s no interactive read-compute-write transaction, and global tables don’t preserve transaction atomicity across regions. |
Cassandra is a useful edge case because there isn’t an isolation level to line 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. It only covers a conditional change inside one partition. Postgres can keep a wider invariant inside a Serializable transaction. With Cassandra, I have to keep that invariant in one partition or coordinate it in the application.
I don’t need to memorize every database as a separate isolation model. I start with Postgres, find the closest level, and spend the rest of the discussion on the differences. It’s worked well for me so far.
Snapshot isolation is my favorite isolation level in any database that offers it. In Postgres, that’s Repeatable Read. It detects write-write conflicts. Serializable does that too, but SSI also tracks the rows and predicates each transaction reads so it can detect read-write dependencies. This allows SSI to prevent write skew.
That bookkeeping grows with the read set, and broad reads can overlap more writes and cause
more serialization failures. When an invariant maps to existing rows, SELECT FOR UPDATE
can force competing transactions to conflict on those rows and prevent write skew. If the
invariant depends on missing rows or an arbitrary predicate, there may be nothing to lock,
so you’ll need to use Serializable. But I tend to avoid that kind of design in practice.
Marc Brooker has a fantastic post on why snapshot isolation makes sense for high-performance systems.