Postgres patterns

PostgreSQL Patterns

JSONB Queries

-- JSONB containment and path operators
SELECT * FROM events WHERE payload @> '{"type": "click"}';
SELECT payload->>'user_id' AS user_id FROM events;
SELECT payload#>>'{meta,source}' AS source FROM events;

-- JSONB indexes
CREATE INDEX idx_events_payload_gin ON events USING GIN (payload);
CREATE INDEX idx_events_type ON events ((payload->>'type'));

-- JSONB update
UPDATE events SET payload = payload || '{"processed": true}' WHERE id = 1;
UPDATE events SET payload = payload - 'temp_field';

Index Types

-- B-tree (default) — equality & range
CREATE INDEX ON orders (created_at DESC);

-- GIN — JSONB, arrays, full-text
CREATE INDEX ON articles USING GIN (to_tsvector('english', body));
CREATE INDEX ON products USING GIN (tags);

-- GiST — geometric, range types, full-text
CREATE INDEX ON locations USING GIST (coordinates);
CREATE INDEX ON reservations USING GIST (during); -- tsrange

-- BRIN — large sequential tables (logs, time-series)
CREATE INDEX ON logs USING BRIN (created_at) WITH (pages_per_range = 128);

-- Partial index
CREATE INDEX ON orders (user_id) WHERE status = 'pending';

-- Covering index (INCLUDE)
CREATE INDEX ON orders (user_id) INCLUDE (total, status);

CTEs and Window Functions

-- Recursive CTE (org hierarchy)
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY depth;

-- Window functions
SELECT
  user_id,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank,
  LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount
FROM transactions;

-- DISTINCT ON (keep first per group)
SELECT DISTINCT ON (user_id) user_id, status, created_at
FROM orders ORDER BY user_id, created_at DESC;

Partitioning

-- Range partitioning by month
CREATE TABLE events (
  id BIGSERIAL,
  created_at TIMESTAMPTZ NOT NULL,
  payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Auto-create partitions with pg_partman
SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');

VACUUM and Maintenance

-- Check bloat
SELECT relname, n_dead_tup, n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

-- Force vacuum analyze
VACUUM (ANALYZE, VERBOSE) orders;

-- Check autovacuum settings per table
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01);

pg_stat Queries

-- Slow queries
SELECT query, calls, total_exec_time/calls AS avg_ms,
  rows/calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- Index usage
SELECT relname, indexrelname, idx_scan, idx_tup_fetch
FROM pg_stat_user_indexes ORDER BY idx_scan ASC;

-- Table sizes
SELECT relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;

-- Active connections
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- Blocking queries
SELECT pid, query, wait_event_type, wait_event
FROM pg_stat_activity WHERE wait_event IS NOT NULL;

Performance Tips

• Use EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) for detailed plans
• Set work_mem per session for sort-heavy queries: SET work_mem = '256MB'
• Use connection_limit on roles, PgBouncer for pooling
• shared_buffers = 25% RAM; effective_cache_size = 75% RAM
• Prefer COPY over INSERT for bulk loads
• Use UNLOGGED TABLE for ephemeral staging data