How PostgreSQL Handles Concurrent Reads and Writes

When your PostgreSQL database handles reads and writes at the same time, ordinary SELECT queries often keep running without waiting for updates. PostgreSQL uses multiversion concurrency control (MVCC): it keeps row versions so a query can read the version that was committed and visible when the query began. That snapshot gives the query a consistent view of the data, even while another transaction changes a row.

A read returns data as it existed at a particular point in time. It does not keep a value unchanged while your code decides what to do. Knowing when PostgreSQL takes a snapshot and when it uses locks helps you choose how your code should handle decisions such as decrementing inventory or claiming a job. The PostgreSQL documentation on MVCC describes how snapshots let ordinary reads and writes coexist.

Each statement reads a snapshot

A transaction is a group of database statements that PostgreSQL treats as one unit. At PostgreSQL’s default isolation level, Read Committed, each statement gets its own snapshot. A statement sees changes committed before it began; a later statement in the same transaction can see newer committed changes.

For example, assume an inventory row has a quantity of 5. Session A starts a transaction and reads it:

BEGIN;

SELECT quantity
FROM inventory
WHERE sku = 'widget';
-- returns 5

While Session A remains open, Session B updates and commits the row:

UPDATE inventory
SET quantity = quantity - 1
WHERE sku = 'widget';

COMMIT;

Session A’s first SELECT can return 5 without waiting for Session B. If Session A runs the same SELECT again, it can see 4, because the second statement gets a newer snapshot. The first query did not see an uncommitted change; it saw the committed row version visible when that query began. PostgreSQL’s Read Committed rules explain how snapshots work across statements.

Plain reads let queries continue during many updates. If your code reads a value and decides what to do outside the database, another transaction can change the row before your code writes based on that value.

Put simple decisions into the write

For a stock decrement, combine the eligibility check and the change in one statement:

UPDATE inventory
SET quantity = quantity - 1
WHERE sku = 'widget'
  AND quantity > 0
RETURNING quantity;

If PostgreSQL returns a row, the decrement succeeded. If it returns no row, the item was unavailable when the update applied. When two transactions try to update the same row, one can wait for the other. At Read Committed, PostgreSQL then checks the waiting update’s WHERE condition against the updated row version before applying it. This helps prevent a second decrement from succeeding after the quantity has reached zero.

Use one conditional update for a simple rule you can express in SQL. If your code must read a row before carrying out several dependent steps, use a locking read or choose an isolation level that meets your consistency needs.

Locks protect contested work

MVCC reduces conflicts between ordinary reads and writes, but PostgreSQL still uses locks. When two transactions update the same row, one may have to wait for the other. A query such as SELECT ... FOR UPDATE locks selected rows, so another transaction cannot update or lock those rows in a conflicting way until the first transaction ends. PostgreSQL describes these behaviors in its explicit locking documentation.

A worker can use a locking read to claim a job before changing its status. Keep the transaction short: the row lock remains in place until commit or rollback, so other workers do not wait while the first worker handles slow external work or waits for user input. For queue-like workloads, FOR UPDATE SKIP LOCKED lets workers skip rows another transaction has locked. Because it omits those rows, it is unsuitable when a query needs a complete view of all matching records. The SELECT locking clauses document this behavior.

A plain SELECT also takes a table-level lock, but that lock permits ordinary reads and writes. An incompatible table-level lock, such as the one used by some schema operations, can block the query. MVCC therefore prevents many row-level read-write waits, but it does not prevent every kind of blocking.

Choose isolation for the whole workflow

Read Committed gives each statement a fresh snapshot. Use it when each statement can work with the latest committed data on its own. If several statements need to see one stable view, Repeatable Read provides a transaction-level snapshot. Serializable adds checks for unsafe interactions so committed transactions behave as if they ran one at a time.

With Repeatable Read or Serializable, PostgreSQL can reject a transaction with a serialization error when concurrent changes make the requested guarantee impossible. Application code must be prepared to retry the whole transaction, including its reads and writes, rather than retrying only the final statement. The transaction isolation documentation describes these guarantees and failure behavior.

Old row versions need cleanup

When an update or delete changes a row, PostgreSQL keeps the old version while an active snapshot might still need it. PostgreSQL's routine vacuuming later reclaims versions that no transaction can see. It can run alongside ordinary reads and writes, though its I/O uses resources. VACUUM FULL needs a stronger table lock and can block other work.

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