Scrolls
4 min read
On this page

Common Concurrency Patterns

Real-world PostgreSQL applications need specific patterns for upserts, job queues, optimistic locking, and safe read-modify-write operations. These patterns solve problems that come up in almost every production system.

Upsert: INSERT ON CONFLICT

Insert a row if it does not exist, update it if it does. Before PostgreSQL 9.5 this required error handling or CTEs. Now it is a single atomic statement.

-- Basic upsert
INSERT INTO user_settings (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON CONFLICT (user_id)
DO UPDATE SET
    theme = EXCLUDED.theme,
    language = EXCLUDED.language;

EXCLUDED refers to the row that would have been inserted.

Upsert with Partial Index

-- Only conflict on active records
INSERT INTO subscriptions (user_id, plan, active)
VALUES (42, 'pro', true)
ON CONFLICT (user_id) WHERE active = true
DO UPDATE SET plan = EXCLUDED.plan;

Upsert That Tracks Changes

INSERT INTO inventory (product_id, quantity)
VALUES (101, 50)
ON CONFLICT (product_id)
DO UPDATE SET
    quantity = inventory.quantity + EXCLUDED.quantity,
    updated_at = now()
RETURNING *;

DO NOTHING: Skip Duplicates

-- Insert if not exists, silently skip if it does
INSERT INTO tags (name)
VALUES ('postgresql'), ('performance'), ('postgresql')
ON CONFLICT (name) DO NOTHING;

This is useful for bulk imports where you want to skip duplicates without updating.

SKIP LOCKED for Job Queues

The standard pattern for implementing a job queue directly in PostgreSQL, without external message brokers.

The Job Table

CREATE TABLE jobs (
    id bigserial PRIMARY KEY,
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    created_at timestamptz NOT NULL DEFAULT now(),
    started_at timestamptz,
    completed_at timestamptz
);

CREATE INDEX idx_jobs_pending ON jobs (created_at)
WHERE status = 'pending';

The Worker Pattern

-- Each worker grabs the next available job
BEGIN;

SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- If a row was returned, process it and mark as complete
UPDATE jobs
SET status = 'processing', started_at = now()
WHERE id = :job_id;

COMMIT;

SKIP LOCKED is the critical part. Without it, workers would block each other waiting for the same row. With SKIP LOCKED, each worker grabs a different job.

Batch Processing

-- Grab multiple jobs at once
BEGIN;

UPDATE jobs
SET status = 'processing', started_at = now()
WHERE id IN (
    SELECT id FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 10
    FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

COMMIT;

Handling Failed Jobs

-- Worker wraps processing in a try/catch
-- On failure:
UPDATE jobs
SET status = 'failed', completed_at = now()
WHERE id = :job_id;

-- Retry logic: reset failed jobs older than 5 minutes
UPDATE jobs
SET status = 'pending', started_at = NULL
WHERE status = 'processing'
  AND started_at < now() - interval '5 minutes';

Optimistic Locking with Version Columns

Optimistic locking does not hold database locks between the read and the write. Instead, it detects conflicts at write time using a version counter.

Schema

CREATE TABLE products (
    id serial PRIMARY KEY,
    name text NOT NULL,
    price numeric NOT NULL,
    version int NOT NULL DEFAULT 1
);

The Pattern

-- Step 1: Read the row and its version
SELECT id, name, price, version FROM products WHERE id = 42;
-- Returns: id=42, name='Widget', price=9.99, version=3

-- Step 2: Update only if version hasn't changed
UPDATE products
SET price = 12.99,
    version = version + 1
WHERE id = 42 AND version = 3;

-- Step 3: Check rows affected
-- If 0 rows updated, someone else changed the row — retry or report conflict

This is how most web frameworks implement optimistic locking. Rails uses a lock_version column. Django uses a similar pattern.

With RETURNING for Atomicity

UPDATE products
SET price = 12.99,
    version = version + 1
WHERE id = 42 AND version = 3
RETURNING *;

If the RETURNING clause returns no rows, the update did not happen — the version was stale.

Optimistic vs Pessimistic Locking

  • Optimistic: read without locks, detect conflicts on write. Good when conflicts are rare (most web apps).
  • Pessimistic (FOR UPDATE): lock on read, prevent conflicts entirely. Good when conflicts are frequent (financial transactions).

The Read-Modify-Write Race Condition

The most common concurrency bug in applications. Two transactions read the same value, compute a new value, and write it back. One update is lost.

The Problem

-- Session A reads the balance
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000

-- Session B reads the same balance
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000

-- Session A subtracts 100
UPDATE accounts SET balance = 900 WHERE id = 1;

-- Session B subtracts 200 (based on the stale read of 1000)
UPDATE accounts SET balance = 800 WHERE id = 1;

-- Expected: 700 (1000 - 100 - 200)
-- Actual: 800 (Session A's update was lost)

Fix 1: Atomic Update Expressions

Do not read the value into the application. Let the database do the arithmetic.

-- Session A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Session B
UPDATE accounts SET balance = balance - 200 WHERE id = 1;

-- Result: 700 (correct)

PostgreSQL serializes the two UPDATEs automatically. Session B waits for Session A's lock, then reads the updated value.

Fix 2: SELECT FOR UPDATE

When you must read the value into the application (e.g., for complex logic):

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- balance = 1000, row is locked

-- Application logic
-- new_balance = 1000 - 100 = 900

UPDATE accounts SET balance = 900 WHERE id = 1;
COMMIT;

Fix 3: Optimistic Locking

When you cannot hold a transaction open (e.g., the user is editing a form):

-- Read
SELECT balance, version FROM accounts WHERE id = 1;
-- balance = 1000, version = 5

-- ... user thinks for 30 seconds ...

-- Write with version check
UPDATE accounts
SET balance = 900, version = version + 1
WHERE id = 1 AND version = 5;

-- If 0 rows: conflict — tell the user to refresh and try again

Connection Pooling with PgBouncer

PgBouncer sits between your application and PostgreSQL, multiplexing many client connections onto fewer server connections.

Transaction Mode vs Session Mode

Transaction mode (recommended for most applications):

pool_mode = transaction

A server connection is assigned only for the duration of a transaction. Between transactions, the connection returns to the pool. This gives maximum efficiency.

Restrictions in transaction mode:

  • No session-level state: SET, prepared statements, LISTEN/NOTIFY, temp tables, and advisory locks do not persist between transactions.
  • Each transaction may get a different server connection.

Session mode:

pool_mode = session

A server connection is assigned when the client connects and held until the client disconnects. This supports all PostgreSQL features but provides less multiplexing benefit.

PgBouncer Configuration

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

server_idle_timeout = 600
client_idle_timeout = 0

log_connections = 1
log_disconnections = 1

Monitoring PgBouncer

-- Connect to PgBouncer's admin console (port 6432, database pgbouncer)
SHOW pools;
SHOW stats;
SHOW clients;
SHOW servers;

Application-Level Pooling

Most modern frameworks have built-in connection pools. Use them in addition to (or instead of) PgBouncer:

  • Java: HikariCP (default in Spring Boot).
  • Python: SQLAlchemy connection pool, psycopg pool.
  • Node.js: pg-pool for the pg driver.
  • Go: database/sql has built-in pooling.

The application pool limits the connections from each application instance. PgBouncer limits total connections to PostgreSQL across all application instances.

Common Pitfalls

  • Using INSERT ON CONFLICT DO UPDATE without understanding which rows get locked. Even when no conflict occurs, the conflicting index is checked and can cause contention under high concurrency.
  • Building a job queue without SKIP LOCKED. Without it, workers serialize on the same row and throughput collapses.
  • Forgetting to check the row count after an optimistic locking update. If you do not check, the conflict is silently lost.
  • Using SET commands with PgBouncer in transaction mode. Session state is lost between transactions. Use SET LOCAL inside a transaction instead.
  • Using read-modify-write patterns without any concurrency protection. This is the source of subtle data corruption bugs that only appear under load.
  • Not testing concurrency patterns under concurrent load. A pattern that works with one connection may break with 50.

Key Takeaways

  • INSERT ON CONFLICT is the standard upsert pattern. Use DO UPDATE for merge behavior and DO NOTHING for skip-duplicate behavior.
  • SELECT FOR UPDATE SKIP LOCKED is the foundation for PostgreSQL-based job queues. No external message broker needed for moderate workloads.
  • Optimistic locking (version columns) works well when conflicts are rare and transactions cannot be held open.
  • The read-modify-write race condition is the most common concurrency bug. Fix it with atomic expressions, FOR UPDATE, or optimistic locking.
  • PgBouncer in transaction mode is the standard for connection pooling. Be aware of session-state restrictions.
  • Choose your concurrency pattern based on conflict frequency: optimistic for rare conflicts, pessimistic for frequent ones.