Are these the right index types?

The situation

The events table receives a steady stream of new rows and is never updated: five million today, growing by a million a month. Three queries are slow. An AI assistant opened a pull request.

-- daily report: count events per day
CREATE INDEX ON events (created_at);

-- work queue: newest pending events (about 1% of rows)
CREATE INDEX ON events USING hash (status);

-- support tool: look up a user's events by email
CREATE INDEX ON events USING hash (email);

CREATE INDEX ON events (created_at) WHERE status = 'pending';

CREATE INDEX CONCURRENTLY ON events USING brin (created_at);
CREATE INDEX CONCURRENTLY ON events (created_at) WHERE status = 'pending';
CREATE INDEX CONCURRENTLY ON events (email);

How to decide

Start with B-tree. It serves equality, ranges, sorting and IS NULL, and it deduplicates repeated values. Choose something else only for a reason.

Prefer BRIN for very large tables whose values follow insertion order, typically timestamps on append-only data, unless the queries need single rows or the order gets scrambled by updates.

Prefer a partial index when queries always target a small, well-defined subset of rows, like a queue of pending work.

Prefer hash only for equality on unique or nearly unique values, and measure it against a B-tree first. On repeated values it loses.

Use GIN or GiST when the operator demands it: containment and arrays for GIN, overlaps and nearest-neighbour for GiST.

Check yourself

1. Why was a hash index on status so slow to build?

   Show answer

   status has only two values, so all rows land in two hash buckets, each becoming a very long chain of overflow pages.

   I could explain this without looking

2. What makes created_at on this table a good fit for BRIN?

   Show answer

   The table is append-only and rows arrive in time order, so each block range covers a narrow time span and its minimum and maximum let whole ranges be skipped.

   I could explain this without looking

3. Which queries can use CREATE INDEX ON events (created_at) WHERE status = 'pending'?

   Show answer

   Queries whose WHERE clause guarantees status = 'pending', such as the work-queue query. Queries for other statuses cannot use it.

   I could explain this without looking