How does PostgreSQL implement the REPEATABLE_READ isolation level?

The REPEATABLE_READ transaction isolation level of PostgreSQL 12 prevents dirty reads, non-repeatable reads, and phantom reads. In contrast to the READ_COMMITTED isolation level, the REPEATABLE_READ isolation level prevents non-repatable reads and phantom reads.

I guess that this comes with a cost, otherwise one would just make both equal. How does postgres guarantee that those 3 read phenomena don’t occur?

Answer

READ COMMITTED and REPEATABLE READ are using the same technology: a snapshot that determines which of the versions of a row in the table a transaction can see. The difference is that with READ COMMITTED, the snapshot is taken at the start of each statement, so that each new statement can see everything that has been committed before, while a REPEATABLE READ transaction uses the same snapshot for all statements.

There are two consequences:

• if anything, REPEATABLE READ is cheaper than READ COMMITTED, because it takes fewer snapshots

• REPEATABLE READ provides even higher isolation than required by the SQL standard – the database does not seem to change at all

The price you are paying for REPEATABLE READ is different:

• you risk serialization errors, which force you to repeat the transaction

• VACUUM cannot clean up rows marked dead after the REPEATABLE READ transaction started