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.

Both COPY and batching remove per-row round trips; measure batch size under production-like load.

3. Maintain time-based partitions

Partition unbounded event/log tables by the retention and pruning column. A mass DELETE creates dead tuples and does not return relation space to the filesystem. Dropping or detaching old partitions avoids that dead-tuple and WAL load; partitions vacuum independently.

Partitioning adds planning, indexing, uniqueness, and maintenance costs. Automate creation ahead of writes and retention after safety checks. Use native declarative partitioning with a scheduled job or pg_partman; never rely on manual creation. Monitor how out-of-range rows fail or enter a default partition.

For an existing huge unpartitioned table, do not present partitioning as greenfield DDL. Create the partitioned target, capture concurrent writes, backfill bounded time/key ranges in separate transactions, reconcile, cut over, and retain a rollback window. Use the live-table move workflow below.

4. Move data between live large tables

1. Create the target with a uniqueness constraint.
2. Capture writes with an idempotent trigger or durable change stream.
3. Backfill bounded key ranges in separate transactions.
4. Reconcile content, then switch readers and writers.
5. Retire capture and source only after a rollback window.

Silent DO NOTHING can hide divergent rows. Follow writing-safe-migrations for live DDL and tuning-autovacuum-and-bloat for backfill impact.

Common Mistakes

• Selecting a job and committing before updating its status.
• Using leases without expiry, heartbeats, idempotency, or a retry ceiling.
• Letting batches grow without bounds.
• Creating time partitions by hand after writes have already reached the boundary.
• Migrating a large table in one transaction or dropping the source before reconciliation.