BRIN Indexes – Overview

Apr 19, 2016·
Adrien Nayrat
Adrien Nayrat
· 2 min read
post Linux

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

 1abstime
 2bigint
 3bit
 4bit varying
 5box
 6bytea
 7character
 8"char"
 9date
10double precision
11inet
12inet
13integer
14interval
15macaddr
16name
17numeric
18pg_lsn
19oid
20any range type
21real
22reltime
23smallint
24text
25tid
26timestamp without time zone
27timestamp with time zone
28time without time zone
29time with time zone
30uuid

  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
Authors
Adrien Nayrat (he/him)
PostgreSQL DBA Freelance

For seven years, I held various positions as a systems and network engineer. And it was in 2013 that I first started working with PostgreSQL. I held positions as a consultant, trainer, and also production DBA (Doctolib, Peopledoc).

This blog is a place where I share my findings, knowledge and talks.