Back Home

TimescaleDB Docker

Get the latest docker

docker pull timescale/timescaledb

and to run on a different port from traditional postgres

 run -d --name some-timescaledb -p 5433:5432 timescale/timescaledb

Tables

Create a standard table

CREATE TABLE public.mule_metrics (
	metric_timestamp timestamptz NULL,
	metric int4 NULL,
	flowname VARCHAR NULL,
	message text NULL
);
SELECT create_hypertable ('mule_metrics', 'metric_timestamp' );

insert into mule_metrics values ( NOW() , 1, 'flow1', 'test data')

select * from mule_metrics order by metric_timestamp desc

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 and Forecasting

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