If one PostgreSQL session deletes a row but has not committed, another session can still read the row’s earlier committed version. The row is not simply “there” or “gone” for every reader: visibility depends on the snapshot used by that statement. Multiple versions can coexist while PostgreSQL decides which one each transaction may see.
Why can a reader see a row another transaction deleted?
Suppose Session A deletes a row inside a transaction but has not committed. Session B runs a normal query using a snapshot that predates that deletion. B can still see the earlier committed version because A’s deletion is not visible to B’s snapshot. If A later commits, that does not retroactively change the snapshot B already took.
As an Amazon Associate I earn from qualifying purchases.
PostgreSQL describes the principle this way: “each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.” That is from the PostgreSQL 17 documentation, section 13.1.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →This is the core of multiversion concurrency control (MVCC): readers and writers can work with different versions of a row rather than requiring every reader to wait for every writer. It does not mean PostgreSQL never uses locks. Writers can conflict with writers, and explicit row- or table-level locks are available.
#1 Best Overall
How does a row’s version history work?
For teaching purposes, imagine a logical row as a chain of versions. An insert creates a version; an update closes one version and creates another; a delete marks the current version as deleted. This is a simplified representation, not PostgreSQL’s literal internal data structure.
| Operation | Educational version-history effect |
|---|---|
| Insert | Create a row version with a creating transaction ID. |
| Update | Mark the old version as ended by the updating transaction, then create a new version. |
| Delete | Mark the existing version as ended by the deleting transaction; no replacement row version is created. |
A compact TypeScript model can represent a version with fields such as xmin for the transaction that created it and xmax for the transaction that deleted or superseded it. These names are teaching labels based on PostgreSQL tuple metadata, not a claim that PostgreSQL stores rows as this TypeScript object.
Rank #2
Why do transaction states matter?
A transaction ID by itself is not enough to decide visibility. The model also needs to know whether the transaction committed, is still in progress, or aborted. An uncommitted insertion should not appear to another transaction, and an aborted insertion should never become visible as a committed row.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstall- Committed creator: its version may be visible if the snapshot includes that transaction’s effects.
- In-progress creator: other transactions generally must not treat that new version as committed data.
- Aborted creator: its version is ignored.
- In-progress or aborted deleter: another observer must not treat the deletion as a committed end to the old version.
What does a snapshot record?
A snapshot is a rule set for deciding which transactions count as having happened for one observer; it is not a copied table. In a small teaching model, it can record a transaction-ID boundary and the set of transactions that were active when the snapshot was taken.
The active set handles a subtle case. If transaction T was still active when a snapshot was created, T is outside that snapshot even if T commits a moment later. Otherwise a query’s answer could change partway through its life just because another transaction finished.
PostgreSQL’s actual snapshot details are more involved than this minimal model. The PostgreSQL 16 training material from Postgres Professional describes transaction boundaries and active transaction lists as part of snapshot visibility; the TypeScript tutorial intentionally reduces that machinery to the pieces needed to understand the idea.
How does the model decide whether a version is visible?
Visibility has two parts: whether the version’s creation counts for this observer, and whether its deletion counts for this observer. Jagdish Parihar summarizes the teaching rule as: “A version is visible when its birth is inside your snapshot and its death is not.”
Recommended Free Tools
- Check the creating transaction. If it is not committed for this snapshot—or is still in the snapshot’s active set—the version is not visible to this observer.
- Check the deleting transaction, if there is one. If that deletion is committed and counts in the snapshot, the old version is no longer visible.
- If creation counts and deletion does not, this version is the one the snapshot can see.
The toy model also needs special handling for a transaction’s own changes: a transaction should be able to see its own writes, and its own delete should affect what it sees. Those rules are useful to demonstrate, but a small example should not be mistaken for PostgreSQL’s full statement- and command-level visibility rules.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When does PostgreSQL take a new snapshot?
Snapshot timing depends on the isolation level. The following comparison reflects PostgreSQL behavior described in Postgres Professional’s PostgreSQL 16 DBA training material; it is not the schedule implemented by the compact TypeScript model.
| Isolation level | Snapshot timing | Practical effect |
|---|---|---|
| Read Committed | A fresh snapshot for each statement. | Later statements in one transaction can see transactions that committed after an earlier statement began. |
| Repeatable Read | A transaction-wide snapshot established by the first statement and held through transaction completion. | Repeated reads use a stable view; PostgreSQL’s Repeatable Read also prevents phantom reads as described by the training material. |
| Serializable | A transaction-wide snapshot established by the first statement and held through transaction completion. | PostgreSQL additionally monitors conflicts to prevent serialization anomalies and can abort a transaction. |
Repeatable Read snapshot behavior is not the same thing as full serializability. PostgreSQL’s Serializable implementation uses Serializable Snapshot Isolation (SSI) to detect dangerous read-write conflict structures; if necessary, it aborts a transaction rather than allowing an outcome inconsistent with a serial execution. The PostgreSQL Wiki’s Serializable overview provides supporting context for SSI.
What the 100-line model leaves out
A small version-chain and snapshot example is valuable because it isolates the visibility rule. It is not a PostgreSQL implementation, and adding more fields to the TypeScript objects would not by itself make it one.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →- Writer-writer conflicts: the model does not implement production conflict handling for concurrent updates or deletes.
- Command IDs and statement details: PostgreSQL has finer-grained visibility behavior within transactions than a simple transaction-ID snapshot captures.
- Serializable conflict detection: SSI and the possibility of serialization failures are outside the model.
- Transaction-status and hint-bit optimizations: PostgreSQL uses additional metadata and performance mechanisms beyond a basic active-transaction set.
- Cleanup: old versions are not retained forever. VACUUM and autovacuum reclaim versions when they are no longer needed by snapshots, subject to PostgreSQL’s cleanup rules.
The useful lesson is narrower and durable: concurrent activity can leave more than one row version available, while a snapshot determines which version a query may treat as current. Understanding that rule makes the apparently contradictory “deleted, or not?” case much easier to reason about.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
