How to Speed Up PostgreSQL created_at Queries on Huge Tables
On a 100M+ row table where rows are inserted roughly in created_at order, a BRIN index on created_at is almost always the right answer for range scans. It's tiny (kilobytes instead of gigabytes), cheap to maintain, and matches the physical layout of append-only data. Use a B-tree only when you need point lookups, small result sets, or ordering with LIMIT, and consider declarative partitioning after the table crosses a few hundred million rows.
Start by creating a BRIN index with a tuned pages_per_range. The default of 128 is often too coarse for selective time ranges.
-- Create a BRIN index tuned for narrower time ranges.
CREATE INDEX CONCURRENTLY events_created_at_brin
ON events
USING BRIN (created_at)
WITH (pages_per_range = 32);
-- Keep summary info current after heavy inserts.
SELECT brin_summarize_new_values('events_created_at_brin');Why BRIN Wins on Append-Only Tables
A B-tree on created_at for a 100M row table can easily reach 3 to 5 GB. Every insert touches the rightmost leaf page, and range scans still have to walk a huge structure. BRIN stores only the min and max created_at per range of heap pages (32 pages with the setting above), so the index is typically under 1 MB. When you query WHERE created_at BETWEEN ... AND ..., Postgres eliminates entire page ranges without reading them. The catch is that BRIN only works when the column correlates with physical row order. If you update created_at or bulk-load out of order, run CLUSTER or pg_repack first, then rebuild the index.
Why Your Existing Index Is Being Ignored
A common frustration is having a B-tree on created_at that EXPLAIN refuses to use. Check for these three causes with an actual execution plan.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload
FROM events
WHERE created_at >= now() - interval '7 days'
AND created_at < now()
ORDER BY created_at DESC
LIMIT 100;First, wrapping the column in a function kills index use. Writing WHERE date(created_at) = '2024-01-15' forces a seq scan. Rewrite it as a half-open range: created_at >= '2024-01-15' AND created_at < '2024-01-16'. Second, if your filter returns more than roughly 5 to 10 percent of the table, the planner correctly chooses a sequential scan because random I/O on millions of heap pages is slower than streaming reads. Third, a timezone mismatch between a timestamptz column and a timestamp literal forces a cast that disables the index. Always compare timestamptz to timestamptz.
When B-Tree Is the Right Choice
If your query also filters by another selective column, or you need ORDER BY created_at DESC LIMIT N to run in milliseconds, build a composite B-tree. The column order matters: put the equality filter first, then the range column.
-- Good for: WHERE tenant_id = ? AND created_at >= ? ORDER BY created_at DESC.
CREATE INDEX CONCURRENTLY events_tenant_created_at
ON events (tenant_id, created_at DESC);
-- Good for: dashboards showing the latest N rows globally.
CREATE INDEX CONCURRENTLY events_created_at_desc
ON events (created_at DESC)
INCLUDE (id, status);The INCLUDE clause turns this into a covering index, which means Postgres can run index-only scans and never touch the heap. That single change often drops query latency from seconds to under 10 milliseconds. Keep autovacuum aggressive on the table, because a stale visibility map causes index-only scans to silently fall back to heap fetches.
Partitioning Past 500M Rows
After maintenance operations (VACUUM, reindex, backups) become painful, declarative range partitioning by month or week is the real fix. You can drop old partitions instantly, each partition gets smaller and more cacheable indexes, and the planner prunes partitions that fall outside the query range.
CREATE TABLE events (
id bigserial,
tenant_id bigint NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Each partition gets its own small BRIN index.
CREATE INDEX ON events_2024_01 USING BRIN (created_at);
CREATE INDEX ON events_2024_02 USING BRIN (created_at);Decision Rule
Pick BRIN when the table is append-only and queries scan hours or days of data. Pick a composite B-tree when you filter by another high-cardinality column alongside the timestamp, or when you need fast ORDER BY ... LIMIT. Partition the table when it exceeds a few hundred million rows or when you need to expire old data cheaply. Always verify with EXPLAIN (ANALYZE, BUFFERS), because the Buffers: shared read= line tells you whether your index actually avoided the heap.