Postgres Advanced Patterns

---
name: postgres-advanced-patterns
description: Guides production Postgres patterns when implementing multi-worker job queues and leases, batching writes, managing unbounded time-series partitions, or moving data between large live tables.
---

# 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:

```sql
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:

```sql
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.

```sql
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.