The GROUP BY model: rows into buckets

Grouping is bucketing

GROUP BY takes the rows that survived WHERE and sorts them into buckets by the value of the grouping key. All rows with the same key land in the same bucket. Then — this is the whole idea — each bucket collapses into exactly one output row.

Picture five orders. Group by status:

SELECT status, COUNT(*) AS n, SUM(total) AS revenue
FROM orders
GROUP BY status;

Postgres partitions the rows into one bucket per distinct status (paid, pending, cancelled, …) and emits one row per bucket. The output is no longer “orders” — it’s “statuses”, one row each.

The iron rule, and why it’s not arbitrary

Once a bucket holds three orders, ask: what is the id of that bucket? There isn’t one — there are three. What’s the created_at? Three again. The bucket has one defined status (that’s the key) but no single value for any other raw column. So Postgres faces a contradiction: you asked for one output row per bucket, but SELECT id demands a value the bucket doesn’t have.

That’s the rule, and it falls straight out of the collapse:

Every expression in the SELECT list must be either listed in GROUP BY (so it has one value per bucket) or wrapped in an aggregate (which computes one value from the bucket).

COUNT(*), SUM(total), MAX(created_at), array_agg(id) — every aggregate takes the whole bucket and returns one scalar. Anything not aggregated must be constant within the bucket, which is exactly what putting it in GROUP BY guarantees. The “must appear in GROUP BY or be used in an aggregate” error is Postgres refusing to silently pick one arbitrary row’s value — which is what MySQL’s old default did, and what silently produced wrong reports for a decade.

-- WRONG: status is neither grouped nor aggregated
SELECT user_id, status, SUM(total)
FROM orders
GROUP BY user_id;            -- ERROR: status must appear in GROUP BY or an aggregate

-- RIGHT, option A: group by it too (one row per user+status pair)
SELECT user_id, status, SUM(total)
FROM orders
GROUP BY user_id, status;

-- RIGHT, option B: aggregate it away (one row per user)
SELECT user_id, MAX(status) AS some_status, SUM(total)
FROM orders
GROUP BY user_id;

The functional-dependency exception

There is one place Postgres relaxes the rule, and it’s not a loophole — it’s correctness. If you GROUP BY a table’s primary key, every other column of that table is functionally dependent on the key: the key uniquely determines the row, so each bucket holds exactly one row of that table, and every column has a single well-defined value. Postgres knows this from the catalog and lets you select those dependent columns ungrouped:

-- u.id is the PK of users, so u.name and u.email are functionally
-- dependent on it — Postgres allows them without listing them in GROUP BY:
SELECT u.id, u.name, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;               -- not u.id, u.name, u.email — the PK is enough

This is part of the SQL standard (the SQL:1999 functional-dependency rule), and Postgres implements it for primary keys and other not-null unique constraints. It saves you from padding GROUP BY with a dozen columns just to print them.

How the collapse actually runs

The bucketing is not free — and the planner has two ways to do it. On a 12M-row orders table, GROUP BY user_id into ~2M buckets shows up in EXPLAIN ANALYZE as one of two nodes. HashAggregate builds an in-memory hash table keyed by user_id, one entry per bucket holding the running aggregate state — no sorted input needed, but it must hold every distinct key in memory at once. GroupAggregate needs its input already sorted by user_id (usually via a preceding Sort or an index scan), then streams one bucket at a time, emitting and discarding each before the next — near-constant memory, but it pays for the sort. The planner picks HashAggregate when it estimates the hash table fits in work_mem, and switches to sort-based GroupAggregate when it doesn’t.