Back Home

CREATE TABLE conditions (
 TIME        TIMESTAMPTZ       NOT NULL,
 location    TEXT              NOT NULL,
 temperature DOUBLE PRECISION  NULL
);

BRIN Index For Sensor Data

PostgreSQL 9.5 introduced a feature called block range indexes (aka BRIN) that is incredibly helpful in efficiently searching over large time series data and has the benefit of taking up significantly less space on disk than a standard B-tree index. A block range index entry points to a page (the atomic unit of how PostgreSQL stores data) and stores two values: the page's minimum value and the maximum value of the item to be indexed.

In fact, when used appropriately, a BRIN index will not only outperform a B-tree but will also save over 99% of space on disk!

CREATE INDEX scans_created_at_brin_idx ON scans USING brin(created_at);
VACUUM FREEZE ANALYZE;

Good post that shows the difference https://info.crunchydata.com/blog/postgresql-brin-indexes-big-data-performance-with-minimal-storage

 
timescaledb.txt · Last modified: 2019/02/28 13:40 by root
 
RSS - 200 © CrosswireDigitialMedia Ltd