BRIN Indexes – Overview

Adrien Nayrat
19 Apr 2016 2 min read Linux, Postgres

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:

Introduction

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

  1. SELECT amname FROM pg_am; or http://www.postgresql.org/docs/current/static/indexes-types.html ↩︎

  2. 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. ↩︎

  3. The index also contains two boolean which indicate if the range contains NULL values (hasnulls) or if it contains only NULL values (allnulls). ↩︎

BRIN Index Postgres
Adrien Nayrat
Adrien Nayrat
PostgreSQL DBA Freelance

Postgres and opensource passionate

Related