postgresql

PostgreSQL Patterns

Advanced querying, indexing, and schema design for PostgreSQL 14+.

When to Activate

• Writing window functions, CTEs, or recursive queries
• Querying JSONB columns
• Designing indexes or diagnosing missing indexes
• Interpreting EXPLAIN ANALYZE output
• Handling concurrent writes (upsert, locking, transactions)
• Full-text search without Elasticsearch
• Planning schema migrations safely

---

Window Functions

Window functions compute values across rows related to the current row — without collapsing them like GROUP BY.

-- ROW_NUMBER: unique rank per partition
SELECT
  user_id,
  order_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders;

-- Get each user's latest order
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;

-- RANK vs DENSE_RANK vs ROW_NUMBER
-- RANK:       1,2,2,4   (gaps after tie)
-- DENSE_RANK: 1,2,2,3   (no gaps)
-- ROW_NUMBER: 1,2,3,4   (always unique)

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

-- Moving average (last 7 days)
SELECT
  date,
  value,
  AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM metrics;

-- LAG/LEAD: access previous/next row
SELECT
  date,
  revenue,
  LAG(revenue)  OVER (ORDER BY date) AS prev_revenue,
  LEAD(revenue) OVER (ORDER BY date) AS next_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS day_over_day
FROM daily_revenue;

-- NTILE: divide rows into buckets
SELECT user_id, spend,
  NTILE(4) OVER (ORDER BY spend DESC) AS quartile  -- 1=top 25%
FROM user_spend;

---

CTEs (Common Table Expressions)

-- Basic CTE — improves readability, reuse within query
WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE status = 'active' AND last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
  SELECT user_id, COUNT(*) AS order_count, SUM(total) AS lifetime_value
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
)
SELECT
  u.name,
  u.email,
  COALESCE(o.order_count, 0) AS orders,
  COALESCE(o.lifetime_value, 0) AS ltv
FROM active_users u
LEFT JOIN user_orders o ON u.id = o.user_id
ORDER BY o.lifetime_value DESC NULLS LAST;

-- Recursive CTE — hierarchies, trees, paths
WITH RECURSIVE category_tree AS (
  -- Anchor: start from roots
  SELECT id, name, parent_id, 0 AS depth, ARRAY[id] AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive: join children
  SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.path || c.id
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;

-- Writable CTEs (INSERT/UPDATE/DELETE in CTE)
WITH deleted_sessions AS (
  DELETE FROM sessions
  WHERE expires_at < NOW()
  RETURNING user_id, session_id
)
INSERT INTO audit_log (user_id, action, metadata)
SELECT user_id, 'session_expired', jsonb_build_object('session_id', session_id)
FROM deleted_sessions;