Tune Postgres for event queries
Event tables grow append-only by block, and queries are almost always a block range for one address or topic. Index for that access pattern, not for the primary key alone.
Procedure
- Add a BRIN index on
block_number; it is cheap to maintain and ideal for append-only, naturally ordered data:CREATE INDEX CONCURRENTLY logs_block_brin ON raw_logs USING brin (block_number); - Add targeted btree indexes for the filters actually used:
(address, block_number)and an expression index on(block_number, topic0). - Confirm the planner uses them and see row estimates against actuals:
Expect an index scan and buffers read close to rows returned, not a seq scan.EXPLAIN (ANALYZE, BUFFERS) SELECT block_number, log_index FROM raw_logs WHERE block_number BETWEEN 19000000 AND 19000100 AND address = '\xabc'; - Partition by block range so a query touches only relevant partitions and old ranges can be detached.
- Keep wide
topics/datacolumns out of the hot scan by selecting only needed columns. VACUUM (ANALYZE)after large backfills so statistics drive the planner correctly.
Pitfalls
- A btree on
block_numberalone is huge and slow for range scans; BRIN is the right tool for ordered append-only data. SELECT *over raw logs pulls uncalleddatablobs and blows the buffer cache; select narrow columns.- Missing statistics after a backfill makes the planner choose a seq scan; analyze after bulk load.
- Indexing a low-cardinality column like
topic0alone rarely helps; pair it withblock_number.
Verification
EXPLAIN (ANALYZE, BUFFERS) SELECT block_number, log_index FROM raw_logs WHERE block_number BETWEEN 19000000 AND 19000100 AND address = '\xabc';
# expect an Index Scan / Bitmap Index Scan with "rows removed by filter" small
Report the index used, buffers read, and estimated versus actual rows for the hot range query.