PostgreSQL Cheat Sheet: SQL, Indexes, EXPLAIN & psql

-- See the query plan without running it
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Run it and show actual timing
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Full output with buffers (shows cache hits)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42;

-- Basic CTE (readable subquery)
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
recent_orders AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, COALESCE(o.order_count, 0) AS orders_last_30_days
FROM active_users u
LEFT JOIN recent_orders o ON o.user_id = u.id
ORDER BY orders_last_30_days DESC;

-- Recursive CTE — walk a tree (e.g. org chart, file system)
WITH RECURSIVE org_tree AS (
  -- base case: top-level managers
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive case: their reports
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth, name;

-- Rank users by order count, partitioned by country
SELECT
  name,
  country,
  order_count,
  RANK() OVER (PARTITION BY country ORDER BY order_count DESC) AS country_rank
FROM user_stats;

-- Running total
SELECT
  created_at::date AS day,
  revenue,
  SUM(revenue) OVER (ORDER BY created_at::date) AS running_total
FROM daily_revenue;

-- Previous row value
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

-- Row number per group
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) sub
WHERE rn = 1;  -- most recent order per user

-- Create table with JSONB column
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attributes JSONB
);

-- Insert
INSERT INTO products (name, attributes) VALUES
  ('Laptop', '{"brand": "Dell", "ram": 16, "tags": ["electronics", "computers"]}');

-- Query by JSONB field
SELECT * FROM products WHERE attributes->>'brand' = 'Dell';

-- Query nested
SELECT * FROM products WHERE attributes->'specs'->>'cpu' = 'M3';

-- Contains check (uses GIN index)
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';

-- Array contains
SELECT * FROM products WHERE attributes->'tags' ? 'electronics';

-- Index for JSONB (required for @> and ? to be fast)
CREATE INDEX ON products USING GIN(attributes);

-- Update a field inside JSONB
UPDATE products
SET attributes = jsonb_set(attributes, '{ram}', '32')
WHERE id = 1;

-- Show running queries
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Kill a long-running query
SELECT pg_cancel_backend(pid);   -- graceful
SELECT pg_terminate_backend(pid); -- force kill

-- Table sizes
SELECT
  relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Index usage (find unused indexes)
SELECT
  relname AS table,
  indexrelname AS index,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

-- Locks
SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE relation IS NOT NULL;

# Dump a database
pg_dump -U postgres mydb > mydb_backup.sql

# Dump compressed
pg_dump -U postgres -Fc mydb > mydb_backup.dump

# Restore from SQL
psql -U postgres mydb < mydb_backup.sql

# Restore from compressed dump
pg_restore -U postgres -d mydb mydb_backup.dump

# Dump specific table
pg_dump -U postgres -t users mydb > users_backup.sql

# Dump all databases
pg_dumpall -U postgres > all_databases.sql