louis's Blog

louis's Avatar Image
Software developer #Go #CommonLisp #JS #SQL. #LispWorks user. Soft spots for #Emacs #SmallWeb. Recently becoming #OpenBSD enthusiast.

Author of the Tuner app for Linux.

Other hobbies: #Running #FireFighter #StarTrek
← All posts

Why did it take me a decade to discover BRIN indexes in PostgreSQL? Holy cow, this suddenly solved some big problems we had with huge time-series tables.

BRIN (Block Range Indexes) are ideal for big tables with timestamps when you need to fetch/count a range of columns. They are super small, fast to create and speed up query execution (in our case) by factor 3 compared to a traditional BTREE index.

CREATE INDEX idxtsbrin ON mybigtable
USING BRIN(ts);

There is no equivalent in MySQL/MariaDB. BRIN indexes were developed for and partially funded by the European Union.

https://en.wikipedia.org/wiki/Block_Range_Index

#postgresql

To like or reply, open original post on Emacs.ch