BRIN index

As explained in Just Use Postgres!

A BRIN index stores summaries of value ranges for physical table blocks to support efficient scans of naturally ordered data.

A BRIN (block range index) stores compact summaries for ranges of physical table pages instead of indexing every row. It works best when column values follow the table’s physical order, such as append-only timestamps in a time-series table. Queries use matching summaries to skip unrelated page ranges, then scan the remaining pages. section 9.7.2 section 10.6

Use a BRIN index when these conditions fit:

• Choose columns whose values are strongly correlated with their physical location, especially timestamps in append-only tables. section 9.7.2
• Prefer BRIN when index size matters on large tables; its range summaries can be far smaller than equivalent B-tree indexes. section 9.7.2 section 10.6
• Expect less benefit when values are stored without a useful physical order, because more page ranges may need scanning. section 10.6

Create and inspect a time-series BRIN index

Create the index with an explicit access method:\n\nCREATE INDEX heart_rate_brin_idx\nON watch.heart_rate_measurements\nUSING brin (recorded_at);\n\nA size query can compare index relations across TimescaleDB chunks. The documented output shows BRIN indexes at 24 kB while listed B-tree indexes range from 1,432 kB to 3,896 kB. After removing the B-tree index, an execution plan for a one-day timestamp filter uses a Bitmap Heap Scan and reports 65,452 rows removed by index recheck, illustrating that BRIN narrows page ranges rather than identifying every matching row directly. section 9.7.2

Figure 9.1 The measurement time (recorded_at column) is always greater in rows added last to the page.

Visualizes how increasing measurement times accumulate within a physical page, making page-range summaries useful. section 9.7.2

How Just Use Postgres! covers it

How deep it goes

Explains page-range summaries, index creation, size comparisons, and execution behavior without detailing BRIN maintenance internals.

Examples

Uses heart-rate measurements, recorded_at timestamps, page diagrams, CREATE INDEX SQL, size output, and EXPLAIN plans.

What it assumes

Assumes familiarity with PostgreSQL tables and CREATE INDEX while teaching physical pages, index categories, and access-method selection.

What you can do after

Apply it via PostgreSQL SQL by creating a BRIN index, comparing index sizes, and interpreting timestamp-filter execution plans.

How it connects

Connects BRIN with time-series data, geospatial data, B-tree indexes, GiST, SP-GiST, and physical table layout.

liveBookmanning.com

Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

• index typekind of · An index type is a database indexing method defined by the structure and access strategy it uses to support data retrieval.
• B-treealternative to · A B-tree is a balanced multiway search tree that maintains ordered keys and supports efficient searching, insertion, deletion, and sequential access.
• SQLuses · SQL is a standardized declarative language for defining, querying, and manipulating data and structures in relational database systems.
• bitmapproduces · A bitmap is a representation that stores information as a grid or set of bits, such as pixel values or selected storage locations.
• GiST indexcontrasts with · A GiST index is a PostgreSQL extensible index structure that supports generalized searches over diverse data types and predicates.

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.