5 PostgreSQL Query Patterns That Changed How I Write SQL

Bad SQL is silent. It works, ships to production, and then silently murders your p99 latency. These five patterns saved me from that fate.

1. CTEs for Readable Complex Queries

WITH
  active_users AS (
    SELECT id, email FROM users WHERE last_active > NOW() - INTERVAL '30 days'
  ),
  user_stats AS (
    SELECT user_id, COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
  )
SELECT u.email, s.post_count
FROM active_users u
LEFT JOIN user_stats s ON s.user_id = u.id
ORDER BY s.post_count DESC NULLS LAST;

2. Window Functions for Rankings

SELECT 
  user_id,
  score,
  RANK() OVER (ORDER BY score DESC) as rank,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) OVER () as median
FROM leaderboard;

3. Partial Indexes for Filtered Queries

If you always query WHERE status = 'active', don't index everything — index only active rows.

CREATE INDEX idx_posts_active ON posts(created_at DESC)
WHERE status = 'active';

This index is tiny and lightning fast.