How PostgreSQL Handles Phantom Reads

In PostgreSQL, a query sees an inserted row based on the transaction’s isolation level and when the query starts. At the default Read Committed level, PostgreSQL gives each statement a fresh view of committed data, so a later query in the same transaction sees rows committed since the earlier statement began. At Repeatable Read and Serializable, PostgreSQL gives the transaction a stable view, so later queries do not see rows committed after PostgreSQL established that view. PostgreSQL’s transaction isolation rules describe these differences.

A phantom read happens when a transaction repeats a query for rows that meet a condition and another transaction’s committed change alters which rows meet it. For example, a second query for open orders might return an extra row that another transaction inserted and committed after the first query. This matters when code counts matching rows, checks a rule before writing, or fetches results across multiple pages.

How a phantom appears

Suppose the orders table contains two open orders. Session A counts them, while Session B inserts and commits another open order:

-- Session A, using PostgreSQL's default READ COMMITTED level
BEGIN;

SELECT count(*) FROM orders WHERE status = 'open';
-- 2

-- Session B
BEGIN;
INSERT INTO orders (status) VALUES ('open');
COMMIT;

-- Back in Session A
SELECT count(*) FROM orders WHERE status = 'open';
-- 3

COMMIT;

At Read Committed, each SELECT sees rows committed before that statement began. Session A’s two queries therefore use different snapshots, or views of the database at different points in time. Starting a transaction does not give both queries the same view.

If Session A uses a Repeatable Read transaction, its first query sets the snapshot that later queries use. The second count remains 2, even after Session B commits. PostgreSQL’s Repeatable Read implementation prevents phantom reads, although the SQL standard’s minimum guarantees allow them at that level. PostgreSQL’s SET TRANSACTION documentation explains how to set the transaction level.

What each isolation level shows

PostgreSQL uses multiversion concurrency control, or MVCC, which keeps versions of rows so queries can read a snapshot while other transactions make changes. The isolation level determines when PostgreSQL takes that snapshot. The PostgreSQL MVCC introduction explains this approach.

The table reflects PostgreSQL’s behavior, which is stronger than the SQL standard’s minimum guarantee for Repeatable Read. PostgreSQL documents that its Repeatable Read level prevents phantoms.

At Serializable isolation, PostgreSQL adds protection beyond a stable snapshot by tracking read/write dependencies with predicate locks. These locks record which data a transaction read without blocking other transactions from changing that data. When concurrent transactions could produce a result inconsistent with any serial order, PostgreSQL aborts one transaction. The application must then retry the entire transaction after a serialization failure.

Counts, business rules, and pagination

A count under Read Committed is accurate for the statement that ran it, but another transaction can change the matching rows before the code acts on the count. For example, two requests could each count fewer than ten open reservations, then both insert one. Repeatable Read gives each request a stable view, but that view alone does not guarantee the shared limit. PostgreSQL warns that Repeatable Read can still allow serialization anomalies and that application-level consistency checks need careful concurrency control. See PostgreSQL’s guidance on application-level consistency checks.

Use a database constraint when the rule can be expressed as one. When a rule depends on a broader check-and-write sequence, use a deliberate protection strategy, such as locking a shared guard row or running the transaction at Serializable isolation. If PostgreSQL reports a serialization failure, retry all of the transaction’s reads and writes, rather than only the statement that failed.

When each page request runs as a separate Read Committed statement, later pages can see a different set of committed rows than earlier pages. With LIMIT and OFFSET, a new row near the start of an ordered result can shift which rows appear at a given offset. Use a deterministic ORDER BY to make row order predictable, but separate requests still do not share one snapshot. Keyset pagination asks for rows after the sort-key value of the last row on the previous page. This avoids some offset shifts, but it does not give separate requests a fixed view. These effects follow from PostgreSQL’s statement snapshots and its documented transaction isolation behavior.

If users must browse one consistent result set, the code needs a way to share a snapshot, such as keeping a transaction open while fetching pages or using PostgreSQL’s snapshot-sharing features. Long-lived transactions have operational costs, so choose an approach that fits how the code serves pages.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com