postgres-advanced-patternslisted
Install: claude install-skill pumarogie/claude-postgres-skills
# 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