Applies when writing or reviewing PostgreSQL queries and schemas. Covers JSONB, UPSERT, GIN/GiST/BRIN indexes, RETURNING, CTEs, advisory locks, and autovacuum.
BIGINT GENERATED ALWAYS AS IDENTITY for internal PKs. gen_random_uuid() (PG 13+) or UUIDv7 for external IDs.JSONB over JSON. Binary decomposed, indexable (GIN), supports @>, <@ operators. JSON only preserves formatting.TEXT over VARCHAR(n) unless you need a length constraint. Identical performance; VARCHAR(n) adds overhead.TIMESTAMPTZ for all temporal data. Never bare TIMESTAMP for wall-clock events.TSTZRANGE, INT4RANGE) for intervals. Pair with EXCLUDE USING gist for overlap prevention.INTEGER[], TEXT[]) for small multi-value columns that don't need joins.ON CONFLICT DO UPDATE for upserts:
INSERT INTO inventory (product_id, qty) VALUES (101, 5)
ON CONFLICT (product_id) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
RETURNING after mutations to avoid a separate SELECT:
INSERT INTO orders (customer_id, total) VALUES (42, 99.99)
RETURNING id, created_at;
RETURNING with CTEs for multi-step mutations (DELETE → INSERT archive).CREATE INDEX ... USING gin (data jsonb_path_ops).CREATE INDEX ix_orders_pending ON orders (created_at) WHERE status = 'pending'.CREATE INDEX ix_lower_email ON users (LOWER(email)).CREATE INDEX CONCURRENTLY in production. Cannot run inside a transaction but avoids write locks.pg_trgm for fuzzy text search and LIKE '%partial%' with GIN indexes.pg_stat_statements for identifying slow and frequent queries. Query it regularly.pgcrypto for gen_random_uuid() (pre-PG13) and crypt() for password hashing.ANALYZE after large data loads to update planner statistics.idle_in_transaction_session_timeout to kill idle-in-transaction connections that block vacuum.max_connections 100-300.FOR UPDATE SKIP LOCKED for job-queue patterns. Without SKIP LOCKED, workers deadlock.TRUNCATE acquires ACCESS EXCLUSIVE lock. For production deletes, batch DELETE ... LIMIT or drop partitions.NOT MATERIALIZED hint (PG 12+) or inline subqueries for performance.