Source-linked AI summary
Serializable Snapshot Isolation in PostgreSQL
Dan R. K. Ports, Kevin Grittner
TL;DR
The paper addresses how to provide true serializability in PostgreSQL without the cost of traditional two-phase locking. It implements and extends SSI, integrates it with PostgreSQL features, and reports performance close to snapshot isolation while outperforming two-phase locking on read-intensive workloads. The implementation also introduces bounded-memory handling and optimizations for read-only transactions.
Problem
PostgreSQL previously offered snapshot isolation as its highest level, which provides performance but allows anomalies, while serializability is difficult to implement without substantial overhead.
Method
The paper implements SSI in PostgreSQL using runtime conflict detection, a new predicate lock manager, transaction summarization, and read-only optimizations.
Results
The resulting serializable mode has performance similar to snapshot isolation and considerably outperforms strict two-phase locking on read-intensive workloads.
Takeaways & Limitations
SSI made serializability a practical PostgreSQL option by combining true serializability with performance benefits over traditional locking on read-intensive workloads.
Takeaways & Limitations
PSSI was rejected because eliminating all false positives would require tracking additional dependencies and consume more memory, conflicting with the goal of a small bounded footprint.
Abstract
from arXiv · showhide
This paper describes our experience implementing PostgreSQL's new serializable isolation level. It is based on the recently-developed Serializable Snapshot Isolation (SSI) technique. This is the first implementation of SSI in a production database release as well as the first in a database that did not previously have a lock-based serializable isolation level. We reflect on our experience and describe how we overcame some of the resulting challenges, including the implementation of a new lock manager, a technique for ensuring memory usage is bounded, and integration with other PostgreSQL features. We also introduce an extension to SSI that improves performance for read-only transactions. We evaluate PostgreSQL's serializable isolation level using several benchmarks and show that it achieves performance only slightly below that of snapshot isolation, and significantly outperforms the traditional two-phase locking approach on read-intensive workloads.
1. OVERVIEW
PostgreSQL 9.1 introduced SSI-based serializable isolation to provide true serializability while retaining much of snapshot isolation’s performance. The implementation addressed production integration, bounded memory, snapshot-based conflict tracking, and read-only transaction optimization.
- Serializable isolation lets developers write transactions as though they execute sequentially, whereas snapshot isolation offers higher performance but permits anomalies.
- SSI runs transactions using snapshot isolation, detects runtime conflicts, and aborts transactions when anomalies are possible.
- PostgreSQL’s implementation was the first SSI deployment in a production database release and required integration with replication, two-phase commit, and subtransactions.
- Because PostgreSQL lacked existing predicate-locking infrastructure, the implementation built a new lock manager optimized for tracking SSI read dependencies.
- SSI was extended with safe snapshots and deferrable transactions so certain read-only transactions avoid SSI overhead when snapshot anomalies cannot occur.
- Performance cost was less than 7% relative to snapshot isolation, while serializable mode significantly outperformed two-phase locking on some workloads.
2. SNAPSHOT ISOLATION VERSUS SERIALIZABILITY
Snapshot isolation provides efficient consistent views but permits anomalies that violate serializability, including write skew and anomalies involving read-only transactions. Serializable execution avoids these behaviors and simplifies application development, especially where concurrency interactions are difficult to analyze.
- Serializability requires transaction effects to be equivalent to execution in some serial order, allowing users to treat transactions in isolation.
- Snapshot isolation uses consistent snapshots and tuple-level write locks, but it does not guarantee serializable behavior.
- 2.1.1 Example 1: Simple Write Skew: In the two-transaction write-skew example, snapshot isolation lets both transactions remove different doctors after observing the same initial state, violating the on-call invariant.
- 2.1.2 Example 2: Batch Processing: The three-transaction batch example shows that snapshot isolation can omit a receipt from a report even though the receipt received the previous batch number.
- 2.1.2 Example 2: Batch Processing: The batch anomaly requires all three transactions, including a read-only report transaction, demonstrating that read-only transactions can participate in snapshot-isolation anomalies.
- 2.2 Why Serializability?: Serializable isolation simplifies development because identifying possible anomalies requires analyzing interactions among all transactions that may run concurrently.
3. SERIALIZABLE SNAPSHOT ISOLATION
SSI provides serializability by extending snapshot isolation with runtime conflict checks, allowing more concurrency than stricter locking approaches. PostgreSQL’s implementation also required new theory, locking infrastructure, and practical controls for false positives and memory use.
- SSI runs transactions under snapshot isolation, detects potentially anomalous conflicts at runtime, and aborts transactions when necessary.
- Serialization anomalies correspond to cycles in a multiversion serialization graph formed from wr-, ww-, and rw-antidependencies.
- Every anomaly contains adjacent rw-antidependencies, with the latter transaction committing first in the cycle.
- SSI permits some rw-conflicts that S2PL and classic OCC would prevent, provided they do not form the dangerous structure associated with anomalies.
- PSSI removes false positives but was rejected because tracking full dependency graphs increases memory use, while evaluated workloads had serialization-failure rates below 1%.
4. READ-ONLY OPTIMIZATIONS
PostgreSQL extends SSI with theory-based optimizations for read-only transactions, including snapshot ordering, safe snapshots, and deferrable execution. These mechanisms reduce false positives and can eliminate SSI overhead for qualifying read-only workloads, while deferrable transactions may wait for safety.
- A read-only snapshot-ordering rule reduces false-positive aborts by disregarding a dangerous structure unless its relevant read/write transaction committed before the snapshot.
- Safe snapshots let read-only transactions perform arbitrary queries without serialization-failure risk, aborts, or SIREAD locks.
- Long-running read-only transactions increase SSI overhead because they acquire many SIREAD locks and delay cleanup, potentially exhausting memory.
- Deferrable read-only transactions wait until they obtain a safe snapshot, avoiding SIREAD locks, aborts, and interference with concurrent lock cleanup.
- Deferrable transactions are not guaranteed to obtain a safe snapshot within a fixed time, although experiments reported typical waits of 1–6 seconds and none above 20 seconds.
5. IMPLEMENTING SSI IN POSTGRESQL
PostgreSQL implemented SSI as a new serializable isolation level without a pre-existing lock-based serializable mode. The implementation required a new lock manager, MVCC-based conflict detection, predicate-read support, and application-specific conflict handling.
- Implementation context: PostgreSQL's first production SSI implementation required new infrastructure because the database lacked a prior serializable mode and predicate-locking mechanisms.The implementation therefore differed from earlier SSI systems built on databases with strict two-phase locking.
- Conflict detection: SSI combines snapshot isolation with runtime conflict checks, using MVCC data when writes precede reads and SIREAD locks when reads precede writes.The latter case also requires predicate-read tracking to detect phantoms.
- Lock manager: The new SSI lock manager stores nonblocking SIREAD locks and supports predicate reads through index-range and gap locking.B+-tree locks were acquired at page granularity, with planned refinement to next-key locking.
- Lock manager: SIREAD locks must remain current across schema changes because table rewrites can invalidate locks identified by physical tuple or page locations.DDL operations such as RECLUSTER and ALTER TABLE can move tuples and require lock maintenance.
- Conflict tracking: An application-level split across transactions can evade dependency tracking and permit anomalies unless causal dependencies between communicating clients are tracked.The paper notes that properly tracking such dependencies requires substantial application support.
6. MEMORY USAGE MITIGATION
SSI can require retained locks and dependency state beyond transaction completion, creating a memory-management problem. PostgreSQL bounds memory with fixed storage, cleanup, lock promotion, safe snapshots, and transaction summarization, while tolerating higher false-positive abort rates when information is lost.
- Memory pressure: SSI memory usage can grow because locks persist until concurrent transactions commit and dependency-graph state may be needed even longer.This differs from merely handling a large number of locks held by one transaction.
- Design requirements: The implementation must use fixed-size lock and dependency storage while continuing to accept transactions during long-running workloads.Graceful degradation may increase false-positive serialization failures rather than rejecting new transactions.
- Mitigation techniques: PostgreSQL limits memory through safe snapshots, deferrable transactions, granularity promotion, aggressive cleanup, and summarization of committed transactions.Fine-grained locks can be combined into coarse-grained locks, while obsolete committed state is removed or compressed.
- State retention: Committed-transaction SIREAD locks can be cleaned when the oldest active transaction commits, but selected conflict-graph information must remain longer.Active transactions may still need to determine whether a committed transaction conflicted with a third transaction.
- Summarization: When storage for committed transactions is exhausted, summarization preserves conflict existence and commit-order information while discarding transaction-specific graph detail.The summarized state can be represented with one 64-bit integer per transaction and swapped to disk through PostgreSQL's LRU mechanism.
7. FEATURE INTERACTIONS
Integrating SSI with PostgreSQL features exposed limitations involving prepared transactions, replication, subtransactions, and index access methods. Some cases require restrictions or future support, while safe snapshots provide a path for read-only replica queries.
- Two-phase commit: Two-phase commit requires SIREAD locks to survive crash recovery, but prepared transactions cannot be aborted, which can defeat safe retry.A retried transaction may conflict again with the still-uncommitted prepared transaction.
- Replication: Log-shipping replicas cannot provide serializable behavior for SSI read-only transactions because replica reads do not communicate their dependencies to the master.PostgreSQL therefore disallows serializable transactions on slaves in the described implementation.
- Replication: Safe snapshots offer a planned way to run read-only serializable transactions on replicas without tracking read dependencies or communicating them to the master.Replica transactions would use identified safe snapshots, potentially at the cost of staleness or waiting.
- Savepoints and subtransactions: Subtransaction rollback prevents an optimization that drops a SIREAD lock after a later write, because rollback can release the subtransaction's write lock.The top-level transaction could otherwise retain neither a write lock nor a SIREAD lock.
- Index types: Only B+-tree indexes supported predicate locking among PostgreSQL's built-in access methods, leaving GiST, GIN, and hash support for planned work.Unsupported methods fall back to relation-level index locks.
8. EVALUATION
Across SIBENCH, DBT-2++, and RUBiS, SSI retained performance close to snapshot isolation and generally exceeded strict two-phase locking, especially where read/write conflicts made locking costly. Its remaining costs were primarily CPU overhead and occasional serialization retries.
- Evaluation design: The evaluation compared SSI with snapshot isolation and strict two-phase locking across SIBENCH, DBT-2++, and RUBiS workloads.The benchmarks used hardware configurations designed to expose both CPU and disk bottlenecks.
- RUBiS: SSI achieved performance comparable to snapshot isolation on RUBiS because dangerous structures were rare and transactions were rarely aborted.S2PL incurred significant lock-contention overhead and occasional deadlock-related serialization failures.
- Read-only optimization: Deferrable transactions can avoid SSI overhead for long-running analytic queries by using snapshot isolation on safe snapshots.The trade-off is that they may wait until a safe snapshot is detected.
9. CONCLUSION
PostgreSQL 9.1 made serializable isolation practical by using SSI to provide true serializability with performance close to snapshot isolation, while outperforming strict two-phase locking on read-intensive workloads. The implementation required substantial engineering, including bounded-memory transaction summarization, integration with PostgreSQL features, and read-only optimizations.
- PostgreSQL 9.1’s SSI-based serializable mode provides performance similar to snapshot isolation and considerably outperforms strict two-phase locking on read-intensive workloads.
- The implementation was the first production use of SSI and the first in a database without a previous serializable isolation level.
- Implementing SSI required a new predicate lock manager, integration with existing PostgreSQL features, and transaction summarization to bound memory usage.
- The implementation also introduced optimizations for read-only transactions, helping make serializable isolation acceptable to the PostgreSQL community.