BRIN Indexes – Overview
PostgreSQL 9.5 released in january 2016 brings a new kind of index: BRIN indexes for Bloc Range INdex. They are recommanded when tables are very big and correlated with their physical location. I decided to devote a series of articles on these indexes:
- BRIN Indexes - Overview
- BRIN Indexes - Operation
- BRIN Indexes - Correlation
- BRIN Indexes - Performances
PostgreSQL provides several kind of access method: B-Tree, GIN, GiST, SP-GiST, Hash 1
Discissions started in 2008: Segment Exclusion
Followed by this RFC which suggest “ Minmax indexes” renamed later with BRIN.
At physical level, a table is composed of 8Ko blocks which contains tuples2. BRIN idea is to store the maximal and minimal value of an attribute from a bloc range3. Then, it is possible to exclude a range of blocks if you search a value which is not stored in the range.
Note : BRIN indexes are similar to storage indexes in Oracle ( Exadata Storage Indexes).
Here are supported types: Built-in Operator Classes
abstime bigint bit bit varying box bytea character "char" date double precision inet inet integer interval macaddr name numeric pg_lsn oid any range type real reltime smallint text tid timestamp without time zone timestamp with time zone time without time zone time with time zone uuid
SELECT amname FROM pg_am;or http://www.postgresql.org/docs/current/static/indexes-types.html ↩︎
If a tuple do not fit in a bloc, it is stored separately in TOAST (The Oversized-Attribute Storage Technique). More precisely, if a tuple is more than 2Ko. ↩︎
The index also contains two boolean which indicate if the range contains NULL values (hasnulls) or if it contains only NULL values (allnulls). ↩︎