BRIN
E97114
BRIN (Block Range Index) is a lightweight PostgreSQL index type optimized for very large tables by summarizing ranges of physical data blocks instead of indexing every individual row.
Statements (56)
| Predicate | Object |
|---|---|
| instanceOf | PostgreSQL index type ⓘ |
| abbreviationOf | Block Range Index ⓘ |
| benefit |
faster index creation on huge tables
ⓘ
lower maintenance overhead ⓘ reduced storage requirements ⓘ |
| category | block-range index ⓘ |
| contrastedWith |
B-tree index
ⓘ
GIN index ⓘ GiST index ⓘ GiST ⓘ
surface form:
SP-GiST index
|
| definedIn | PostgreSQL documentation ⓘ |
| designedFor | very large tables ⓘ |
| doesNotIndex | every individual row ⓘ |
| fullName | Block Range Index ⓘ |
| implementedIn |
PostgreSQL
ⓘ
surface form:
PostgreSQL core
|
| indexes | ranges of physical data blocks ⓘ |
| indexingStrategy | block range summarization ⓘ |
| indexSize | very small compared to B-tree ⓘ |
| introducedInVersion | PostgreSQL 9.5 ⓘ |
| limitation |
less selective than B-tree for random data
ⓘ
performance degrades if data is not physically correlated ⓘ |
| optimizedFor |
append-only workloads
ⓘ
log tables ⓘ tables with natural physical ordering ⓘ time-series data ⓘ |
| parameter |
autosummarize
ⓘ
autovacuum settings ⓘ pages_per_range ⓘ |
| partOf | PostgreSQL ⓘ |
| sqlSyntaxExample | CREATE INDEX idx ON table USING brin(column); ⓘ |
| stores | summary tuples per block range ⓘ |
| summaryIncludes |
maximum values
ⓘ
minimum values ⓘ null presence information ⓘ other aggregate metadata ⓘ |
| supports |
CLUSTER
ⓘ
REINDEX ⓘ VACUUM ⓘ custom operator classes ⓘ equality queries ⓘ index-only scans ⓘ multi-column indexes ⓘ operator classes ⓘ parallel index scans ⓘ partitioned tables ⓘ pg_dump and pg_restore ⓘ range queries ⓘ |
| supportsDataTypes |
boolean
ⓘ
date and time types ⓘ geometric types ⓘ network address types ⓘ numeric types ⓘ text types ⓘ |
| tradeoff | lower index size vs less precise lookups ⓘ |
| useCase |
historical archival tables
ⓘ
large fact tables in data warehouses ⓘ |
Referenced by (2)
Full triples — surface form annotated when it differs from this entity's canonical label.