PostgreSQL DISTINCT ON

Tip: First Row Per Group

SELECT DISTINCT ON (user_id) user_id, order_id, created_at
FROM orders ORDER BY user_id, created_at DESC;

Gets the most recent order per user.

Gotcha: ORDER BY Must Match

The first ORDER BY columns must match the DISTINCT ON columns.

Tip: Last Row Per Group

SELECT DISTINCT ON (category_id) *
FROM posts ORDER BY category_id, created_at DESC;

Gets the latest post per category.

Gotcha: Not Standard SQL

DISTINCT ON is PostgreSQL-specific. Not portable to other databases.

Tip: With Additional Columns

SELECT DISTINCT ON (user_id) user_id, name, email, last_login
FROM users ORDER BY user_id, last_login DESC;

Gotcha: Performance

DISTINCT ON requires sorting. Index the ORDER BY columns for best performance.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL's DISTINCT ON extension is invaluable for 'latest record per group' queries. Instead of window functions or subqueries, SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC returns the most recent order per user in a single, readable query. The critical rule: the ORDER BY must start with the DISTINCT ON expressions. PostgreSQL enforces this strictly and will reject non-conforming queries.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)