postgres-advanced-patterns

Postgres Advanced Patterns

Overview

Postgres supplies the primitives; the application must define ownership, crash recovery, idempotency, retries, and operational bounds.

1. Atomically claim queued work

Claim and mark a batch atomically. SKIP LOCKED lets concurrent workers select disjoint rows:

UPDATE jobs AS j
SET status = 'running',
    lease_owner = $1,
    lease_expires_at = clock_timestamp() + interval '5 minutes',
    attempts = attempts + 1
FROM (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
  ORDER BY priority DESC, id
  FOR UPDATE SKIP LOCKED
  LIMIT $2
) AS claim
WHERE j.id = claim.id
RETURNING j.*;

If selection and update are separate statements, they must share one explicit transaction; otherwise commit releases the row locks before ownership is recorded.

Always use SKIP LOCKED for competing queue workers. Plain FOR UPDATE makes workers wait on rows another worker is claiming instead of moving to available work.

Keep the claim path small with a partial index:

CREATE INDEX CONCURRENTLY idx_jobs_pending_claim
ON jobs (priority DESC, id)
WHERE status = 'pending';

Recover crashes with expiring leases. Workers extend only leases they own; a sweeper returns expired work to pending with an attempt limit and dead-letter policy. Effects must be idempotent because a worker can finish after lease expiry.

UPDATE jobs
SET status = 'pending', lease_owner = NULL, lease_expires_at = NULL
WHERE status = 'running' AND lease_expires_at < clock_timestamp()
RETURNING id;

2. Batch writes

For high-rate bulk ingestion, follow this order:

1. Use PostgreSQL COPY—pgx CopyFrom in Go—for bulk load specifically. It is the preferred path when loading many compatible rows; do not stop at a larger multi-row INSERT or statement batch.
2. Use bounded multi-row inserts or driver batches when COPY does not fit. Bound batch size to control memory, WAL bursts, and lock duration.
3. If the group must be atomic, wrap it in an explicit transaction; never assume a driver's batch API is implicitly transactional.
4. Close every pgx BatchResults, check statement errors, and check the final close error. Never fire-and-forget a batch.