postgres-tips

Advanced PostgreSQL cheat sheet and runnable query library. Window functions, CTEs, JSONB, full-text search, EXPLAIN ANALYZE, indexing strategies, and performance tuning. Copy-paste ready SQL for real-world use.

Contents

Topic	File	What's covered
Window Functions	sql/window_functions/examples.sql	ROW_NUMBER, RANK, LAG/LEAD, running totals, NTILE, percentiles
CTEs	sql/ctes/examples.sql	Basic CTEs, recursive (org chart, tree traversal), date series, writeable CTEs
JSONB	sql/jsonb/examples.sql	Operators, filtering, arrays, aggregation, GIN indexes, updates
Performance	sql/performance/explain_analyze.sql	Reading EXPLAIN plans, common bad patterns, pg_stat_statements
Indexing	sql/indexing/index_strategies.sql	B-tree, Hash, GIN, GiST, BRIN, partial, composite, covering, expression indexes

---

Quick Reference

Window Functions

-- Running total
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

-- Top 1 per group (latest order per user)
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

-- Month-over-month comparison
SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM monthly_revenue;

JSONB

-- Extract value
SELECT metadata ->> 'brand' FROM products;           -- text
SELECT metadata -> 'specs' ->> 'ram' FROM products;  -- nested

-- Filter by value
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE (metadata ->> 'price')::numeric > 500;

-- Update a key
UPDATE products SET metadata = jsonb_set(metadata, '{specs,ram}', '32') WHERE id = 1;

-- GIN index (make @> fast)
CREATE INDEX ON products USING GIN (metadata);

CTEs

-- Recursive tree traversal
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT LPAD('', depth*2, ' ') || name FROM tree;

-- Fill date gaps with generate_series
WITH dates AS (SELECT generate_series('2024-01-01'::date, '2024-12-31'::date, '1 day') AS day)
SELECT d.day, COALESCE(SUM(o.amount), 0) AS revenue
FROM dates d LEFT JOIN orders o ON o.created_at::date = d.day
GROUP BY d.day ORDER BY d.day;

Indexing

-- Partial index (index only what you query)
CREATE INDEX idx_pending ON orders(user_id, created_at) WHERE status = 'pending';

-- Covering index (avoid table lookup)
CREATE INDEX idx_covering ON orders(user_id) INCLUDE (status, amount);

-- Expression index (case-insensitive email lookup)
CREATE INDEX idx_email_lower ON users(lower(email));

-- Concurrent (no table lock in production)
CREATE INDEX CONCURRENTLY idx_name ON table(column);

-- Find unused indexes
SELECT indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;

EXPLAIN ANALYZE

-- Full diagnostic
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

Performance Tips

-- Update stale statistics
ANALYZE tablename;

-- Check table/index sizes
SELECT pg_size_pretty(pg_total_relation_size('orders'));

-- Find missing indexes (tables with high seq scan ratio)
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables
WHERE seq_scan > idx_scan AND n_live_tup > 10000;

-- Increase memory for a complex query (per session)
SET work_mem = '256MB';

-- Parallel query tuning
SET max_parallel_workers_per_gather = 4;

Useful System Queries

-- Active connections and queries
SELECT pid, query_start, state, left(query, 80) FROM pg_stat_activity WHERE state != 'idle';

-- Kill a long-running query
SELECT pg_cancel_backend(pid);      -- gentle
SELECT pg_terminate_backend(pid);   -- forceful

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC;

-- Lock waits
SELECT wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event IS NOT NULL;

-- Bloat estimate
SELECT tablename, n_dead_tup, n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY dead_pct DESC;

-- Run VACUUM on bloated tables
VACUUM ANALYZE tablename;

License

MIT © bhupendra05