postgres-advanced-patternslisted

# Postgres Advanced Patterns ## Overview Four production patterns that Postgres handles natively: row-reservation queues, high-throughput batch writes, time-series partitioning, and zero-downtime data migrations between large tables. ## When to Use - Implementing a job/work queue or distributing leases across worker instances. - Writing many rows and query overhead is the bottleneck. - Storing time-series/event data that grows without bound. - Moving data between two large tables while writes continue. ## 1. `FOR UPDATE SKIP LOCKED` — queues & leases Reserve rows for this worker without blocking other workers. Each worker skips rows already locked by another, so N workers pull disjoint work with no contention. ```sql -- one worker claims a pending job; other workers skip this row SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1; ``` Use cases: job queues, independent row updates, distributing leases across distributed app instances. ## 2. Batch writes — ~10× throughput Every query pays overhead: network round-trip, pool acquisition, internal locking. Send many rows in one batch instead of one query per row. ```go // Go / pgx: queue all inserts, flush in a single round-trip (implicit transaction) batch := &pgx.Batch{} for _, r := range rows { batch.Queue("INSERT INTO events (tenant_id, payload) VALUES ($1, $2)", r.Tenant, r.Payload) } br := conn.SendBatch(ctx, batch) defer br.Close() ``` Batching can deliver roughly **10× throughput**