postgres-advanced

Advanced PostgreSQL Operations

Expert-level PostgreSQL patterns for production systems: analytical queries, JSON handling, search optimization, and performance tuning.

Window Functions

Perform calculations across row sets without collapsing results.

BAD: Self-join for ranking

-- Inefficient, creates cartesian product
SELECT e1.name, e1.salary, COUNT(e2.salary) as rank
FROM employees e1
LEFT JOIN employees e2 ON e1.salary <= e2.salary
GROUP BY e1.name, e1.salary;

GOOD: ROW_NUMBER and RANK

-- Efficient window function
SELECT
  name,
  salary,
  department,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;

BAD: Subquery for previous value

-- Poor performance, scalar subquery per row
SELECT
  date,
  revenue,
  (SELECT revenue FROM sales s2
   WHERE s2.date < s1.date
   ORDER BY date DESC LIMIT 1) as prev_revenue
FROM sales s1;

GOOD: LAG/LEAD for row comparison

-- Single scan with window function
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) as prev_revenue,
  LEAD(revenue, 1) OVER (ORDER BY date) as next_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) as revenue_change
FROM sales;

Running totals and moving averages

-- Cumulative sum and 7-day moving average
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d,
  FIRST_VALUE(amount) OVER (ORDER BY date) as first_amount,
  LAST_VALUE(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_amount
FROM transactions;

Common Table Expressions (CTEs)

BAD: Nested subqueries

-- Unreadable, hard to maintain
SELECT * FROM (
  SELECT * FROM (
    SELECT user_id, COUNT(*) as cnt
    FROM orders
    GROUP BY user_id
  ) t1 WHERE cnt > 5
) t2 JOIN users ON t2.user_id = users.id;