How to Speed Up PostgreSQL Timestamp Queries on Large Tables
The fastest win for PostgreSQL timestamp queries on a 100M+ row table with WHERE created_at BETWEEN ... AND ... is almost always a BRIN index if rows arrive in timestamp order, or a composite B-tree on (filter_col, created_at DESC) if you also filter by something else. For tables that keep growing, pair the index with declarative range partitioning on created_at so the planner can prune old partitions, and you can archive or drop them cheaply.
Before you change anything, confirm what the planner is actually doing with EXPLAIN (ANALYZE, BUFFERS).
-- Inspect the real plan, not just EXPLAIN.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, user_id, payload
FROM events
WHERE created_at >= '2024-01-01'
AND created_at < '2024-02-01';If you see Seq Scan with billions of buffers read, you have no usable index on created_at. If you see Index Scan but thousands of heap fetches per row, your index is fine, but the table is bloated or the query returns too much data. The fix depends on which case you are in.
Case 1: Append-Only Table With Rows in Timestamp Order
This is the common case for events, logs, audit tables, and metrics. Use BRIN. It is roughly 1000x smaller than a B-tree and perfect when physical row order correlates with created_at.
-- BRIN shines on naturally ordered data.
-- pages_per_range=32 is a good default for 100M+ rows.
CREATE INDEX CONCURRENTLY events_created_at_brin
ON events USING brin (created_at)
WITH (pages_per_range = 32);
-- Check correlation; BRIN only helps if this is near 1.0 or -1.0.
SELECT correlation
FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';A BRIN index on a 100M row table is typically under 10 MB, fits in shared buffers trivially, and drops range-scan latency from minutes to seconds. The catch: if correlation drops below roughly 0.9 (because of backfills, out-of-order inserts, or UPDATE-heavy workloads that shuffle rows), BRIN becomes useless. Always check pg_stats.correlation before you commit to it.
Case 2: You Filter by Something Else and a Time Range
For queries like WHERE user_id = 42 AND created_at >= now() - interval '7 days', BRIN is the wrong tool. Use a composite B-tree with the equality column first and the range column second.
-- Equality column first, range column second, DESC for recency queries.
CREATE INDEX CONCURRENTLY events_user_created_idx
ON events (user_id, created_at DESC);
-- Optional: include columns to enable an index-only scan.
CREATE INDEX CONCURRENTLY events_user_created_covering
ON events (user_id, created_at DESC)
INCLUDE (payload_id, status);The column order matters. (user_id, created_at) lets PostgreSQL seek directly to the user's rows and then walk the time range. (created_at, user_id) forces it to scan every row in the time window and filter by user afterward, which is dramatically slower when one user owns a tiny fraction of rows.
The INCLUDE clause enables index-only scans, so the planner never touches the heap. This is the single biggest win for wide tables, but it only works if VACUUM has run recently enough that the visibility map is up to date.
-- Force visibility map refresh so index-only scans actually trigger.
VACUUM (ANALYZE) events;
-- For high-churn tables, tune autovacuum per-table.
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_analyze_scale_factor = 0.01
);Case 3: The Table Keeps Growing Forever
After you cross 500M rows or 500 GB, even a good index starts hurting writes, and vacuums take hours. Switch to declarative range partitioning by month or week. Each partition gets its own small, fast index, and queries with a time predicate automatically prune to one or two partitions.
-- Create a partitioned parent table.
CREATE TABLE events (
id bigserial,
user_id bigint NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
-- One partition per month.
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');
-- Index on each partition, not the parent, to keep maintenance cheap.
CREATE INDEX ON events_2024_01 USING brin (created_at);Use pg_partman to automate partition creation and retention instead of writing cron jobs. Dropping a month of data then becomes DROP TABLE events_2023_01, which is instant and reclaims disk immediately, versus a DELETE that bloats the table and requires aggressive vacuuming.
Gotchas That Bite People in Production
Three things routinely ruin timestamp query performance even when the index looks right.
First, timezone casts kill indexes. WHERE created_at::date = '2024-01-15' cannot use an index on created_at. Rewrite it as a half-open range.
-- Bad: function on indexed column prevents index use.
SELECT * FROM events WHERE created_at::date = '2024-01-15';
-- Good: sargable range predicate.
SELECT * FROM events
WHERE created_at >= '2024-01-15'
AND created_at < '2024-01-16';
-- Also good: functional index if you truly need date equality.
CREATE INDEX ON events ((created_at::date));Second, now() parameters defeat plan caching in bad ways. Prepared statements with created_at >= $1 can pick a generic plan that assumes a huge range. Force a custom plan with plan_cache_mode = force_custom_plan on the session, or pass an explicit timestamp from the application.
Third, ORDER BY created_at DESC LIMIT N needs the index sorted in the matching direction. If your index is ascending and you query descending, PostgreSQL can still use it (backward scan), but a partial index like WHERE created_at > now() - interval '30 days' combined with DESC ordering is dramatically faster for dashboard queries that only look at recent data.
-- Partial index: only indexes hot recent rows, tiny and fast.
-- Recreate periodically as the window slides.
CREATE INDEX CONCURRENTLY events_recent_idx
ON events (created_at DESC)
WHERE created_at > '2024-01-01';Which Approach to Pick
If your table is append-only and queries are pure time ranges, use BRIN and stop there. If you filter by a user, tenant, or device alongside the timestamp, use a composite B-tree with the equality column first. If the table is growing without bound or you need cheap data retention, partition by range and put a small BRIN or B-tree on each partition. Most large production systems end up doing all three: a partitioned table, BRIN on created_at inside each partition, and a composite B-tree for the hot access pattern.
Always verify with EXPLAIN (ANALYZE, BUFFERS) before and after. A plan that drops from 2 million buffer reads to 200 is the signal that you actually fixed the problem, rather than adding an index that the planner ignores.