SQL Composite Index

An index across more than one column — for queries that filter on several columns together.

What & Why

A composite (or "multi-column") index covers two or more columns in a single index structure, sorted first by the first column, then by the second within ties, and so on.

See How It Works

BUSINESS QUESTION

A recurring event query filters by event name and a recent created-at range.

id	user_id	event_name	created_at	properties
1001	101	signup	2024-01-15 09:12:00+00	{"source":"organic"}
1002	101	activated	2024-01-16 14:25:00+00	{"step":"workspace"}
1003	102	signup	2024-02-10 11:08:00+00	{"source":"paid"}
1004	103	report_viewed	2024-03-12 16:40:00+00	{"report":"retention"}

EXAMPLE QUERY

SELECT
  user_id,
  event_name,
  created_at
FROM growth.events
WHERE event_name = 'signup'
  AND created_at >= TIMESTAMPTZ '2024-01-01 00:00:00+00'
ORDER BY created_at DESC;

RESULT — signup rows for the composite predicate

user_id	event_name	created_at
102	signup	2024-02-10 11:08:00+00
101	signup	2024-01-15 09:12:00+00

Both signup events satisfy the fixed date boundary and are ordered from newest to oldest.

Advanced business practice