CODEWITHSUNDEEP
postgresqlperformancetransactionsdatabase-performance
Erick T
Erick T

Reputation: 7449

Performance/scalability difference between different transaction isolation levels

I recently did some work around wrapping a set of select queries into a transaction. I was looking at both Repeatable Read and Serializable/Snapshot isolation.

Postgres has done some work on performance of deferrable read-only queries (Serializable Snapshot Isolation (SSI) and Predicate Locking). An earlier stack overflow question on this topic is Does PostgreSQL run some performance optimizations for read-only transactions .

The problem that I am trying to solve is figuring out the relative impact of the two. The previous stack overflow question had discussion around the optimization, but nothing on how to figure out which approach is better. Metrics are good, but it's hard to measure this under sufficient load to demonstrate the impact of locking.

Upvotes: 2

Views: 58

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

The REPEATABLE READ isolation level has even less direct impact on performance than the default READ COMMITTED isolation level. The reason for that is that with READ COMMITTED, every statement has to take a snapshot of the database, while with REPEATABLE READ, only the first statement in a transaction takes a snapshot, which is retained for the whole transaction.

The only consideration is that long read-only REPEATABLE READ transactions (unlike READ COMMITTED) block the progress of autovacuum, which can lead to table bloat, which in turn can affect performance. So make sure that you don't have long-running transactions.

SERIALIZABLE will incur a performance cost because of the additional predicate locks.

Don't forget that with both REPEATABLE READ and SERIALIZABLE you can get serialization errors, which require you to repeat transactions. Having to repeat transactions frequently can also affect performance.

Upvotes: 1

Related Questions