PostgreSQL psql Top N: 5 Proven Per-Group Queries Fast

It's 5 PM and someone asks for the top 3 products per category from an 8M-row table. Your first attempt — a subquery per group — is still running at 5:02. The dashboard times out, the requester pings again, and you're reading EXPLAIN output under pressure.

Both halves of this problem are daily Postgres life. You can't write the query if you can't see the schema fast, and you can't rank per group efficiently without window functions. psql's backslash commands handle the first; ROW_NUMBER handles the second.

Quick wins ahead. You'll get the dozen psql commands that replace dashboard-clicking, then five top-N patterns ranked by speed — including the 380ms rewrite that saved a launch-day leaderboard.

psql Introspection: See Everything in 60 Seconds

Connect (psql -h localhost -U shop shopdb) and orient with five commands: \dt shows tables, \d orders describes one table (columns, types, indexes), \di lists indexes, \df lists functions, \dn lists schemas. Sixty seconds, full map.

Go deeper without leaving the terminal: \d+ orders adds sizes and descriptions; \x on pivots wide rows readable; \timing times every query so optimizations are measurable, not vibes.

When backslash commands run out, query the catalogs directly: information_schema.columns for portable column lists, pg_stat_user_tables for seq-vs-index scan counts. Structure from \d, behavior from catalogs — the pair answers 'what exists' and 'what's slow.'

psql -h localhost -U shop shopdb -c '\dt'              # all tables
psql -h localhost -U shop shopdb -c '\d orders'          # one table: cols + indexes
psql -h localhost -U shop shopdb -c '\di'                # all indexes
psql -h localhost -U shop shopdb -c '\df'                # functions
psql -h localhost -U shop shopdb -c 'SELECT tablename, seq_scan, idx_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 5;'

Pattern 1: ROW_NUMBER — the Default Winner

ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY sales DESC) AS rn numbers each seller's listings 1..N by sales; the outer WHERE rn <= 5 keeps the top 5 per seller in one pass. Add id as tiebreaker (ORDER BY sales DESC, id) for determinism.

It handles ties explicitly (vs RANK's gaps), pages cleanly (rn BETWEEN 6 AND 10), and extends to latest-N-per-user with ORDER BY created_at DESC. One shape, many questions.

Cost is one sort per partition set — which the (seller_id, sales DESC) composite index largely eliminates. This is the pattern to reach for first and the one to benchmark others against.

-- Top 5 listings per seller: one pass, deterministic ties
SELECT id, seller_id, title, sales
FROM (
  SELECT id, seller_id, title, sales,
         ROW_NUMBER() OVER (
           PARTITION BY seller_id
           ORDER BY sales DESC, id
         ) AS rn
  FROM listings
) ranked
WHERE rn <= 5
ORDER BY seller_id, rn;

-- Supporting index (build concurrently in prod)
CREATE INDEX CONCURRENTLY idx_listings_seller_sales
  ON listings (seller_id, sales DESC);

Pattern 2: DISTINCT ON — Shortest, Postgres-Only

SELECT DISTINCT ON (seller_id) seller_id, id, sales FROM listings ORDER BY seller_id, sales DESC, id picks the first row per seller under that ordering. For top-1-per-group it's unbeatable: short, fast with the composite index, idiomatic Postgres.

Top-N beyond 1 needs a trick (DISTINCT ON doesn't take N) — which is why ROW_NUMBER owns N > 1. And the ORDER BY must lead with the DISTINCT expressions, or Postgres errors; mismatching them is the top DISTINCT ON bug.

Use it for latest-order-per-user, current-price-per-product, newest-event-per-device. One row per group, minimal SQL, maximum clarity — as long as you never need N = 5.

-- Newest order per user (top-1-per-group sweet spot)
SELECT DISTINCT ON (user_id) user_id, id, total, created_at
FROM orders
ORDER BY user_id, created_at DESC, id;

-- Top-1 product per category by sales
SELECT DISTINCT ON (category_id) category_id, id, title, sales
FROM listings
ORDER BY category_id, sales DESC, id;

Patterns 3-4: LATERAL and Correlated (Know the Cliff)

LATERAL joins run a subquery per outer row with access to it: SELECT s., t. FROM sellers s, LATERAL (SELECT * FROM listings l WHERE l.seller_id = s.id ORDER BY sales DESC LIMIT 5) t. With few sellers and LIMIT-pushed indexes, it's excellent — each inner query is a tiny indexed lookup.

The cliff is group count: 12k sellers means 12k subplans per page view. Fine at 20 groups, fatal at 12k. Rule of thumb: LATERAL under hundreds of groups, ROW_NUMBER above.

Plain correlated subqueries (WHERE sales = (SELECT max...)) are worse — no LIMIT pushdown, full inner scans. They read naturally and scale poorest; treat them as prototypes to replace, not patterns to ship.

Pattern 5: Ties, Pages, and Freshness Correctly

Ties need policy: ROW_NUMBER picks arbitrarily among equals (add id tiebreak), RANK/DENSE_RANK share ranks with gaps or without. Leaderboards with prizes want DENSE_RANK (ties share the prize tier); feeds want ROW_NUMBER (stable order). Choose deliberately and test with tied fixtures.

Paging per group uses the rn column (rn BETWEEN 6 AND 10) — stable across inserts unlike LIMIT/OFFSET on the raw table. Freshness comes from WHERE created_at > now() - interval '30 days' inside the window subquery, so ranking considers only recent rows.

Cache the result: top-N leaderboards change slowly and cost real compute. The launch fix added 60s Redis caching — database load fell 97% on top of the 380ms query. Fast query plus short cache beats either alone.

-- Shared ranks for prize tiers (ties share rank 1)
SELECT seller_id, id, sales,
       DENSE_RANK() OVER (PARTITION BY seller_id ORDER BY sales DESC) AS rnk
FROM listings;

-- Page 2 per seller (rows 6-10), recent listings only
SELECT id, seller_id, title, sales FROM (
  SELECT id, seller_id, title, sales,
         ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY sales DESC, id) AS rn
  FROM listings
  WHERE created_at > now() - interval '30 days'
) r WHERE rn BETWEEN 6 AND 10;

EXPLAIN Before You Ship (5-Minute Habit)

Run EXPLAIN (ANALYZE, BUFFERS) on production-sized staging and read three lines: the join/sort method (Seq Scan on 8M rows is the red flag), actual rows vs estimates (10x gaps mean stale stats — run ANALYZE), and buffers hit vs read (disk-bound sorts need the index, not more RAM).

Compare shapes head-to-head with \timing: correlated vs ROW_NUMBER on the same snapshot, before/after the composite index. Numbers beat opinions — the 90s-to-380ms story was two EXPLAIN outputs pasted into the incident doc.

Make it a gate: leaderboard PRs include EXPLAIN ANALYZE output and row counts. Queries that can't show their plan don't merge. Five minutes per query prevents launch-day cascades.