Show Menu
Cheatography
  1. Home >
  2. Programming >

TimeScale Cheat Sheet (DRAFT) by

Timescale Reference, command

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Hypert­ables

Create Table- 2 steps
1. create a standard postgresql table :
  
CREATE TABLE conditions (  time        TIMEST­AMPTZ       NOT NULL,  location    TEXT              NOT NULL,  temper­ature DOUBLE PRECISION  NULL );
 
2. use create­_hy­per­table command
  
SELECT create­_hy­per­tab­le(­'co­ndi­tions', 'time');
 
Convert table conditions to hypertable with just time partit­ioning on column time
  
SELECT create­_hy­per­tab­le(­'co­ndi­tions', 'time', chunk_­tim­e_i­nterval => INTERVAL '1 day');
  
SELECT create­_hy­per­tab­le(­'co­ndi­tions', 'time', 'locat­ion', 4, partit­ion­ing­_func => 'locat­ion­_ha­sh');
  
SELECT create­_hy­per­tab­le(­'co­ndi­tions', 'time', if_not­_exists => TRUE);
Creat Index:­CREATE INDEX ... WITH (times­cal­edb.tr­ans­act­ion­_pe­r_c­hunk, ...);
CREATE INDEX ON condit­ion­s(time, device_id) WITH (times­cal­edb.tr­ans­act­ion­_pe­r_c­hunk);
show_c­hunks()
SELECT show_c­hun­ks(­'co­ndi­tio­ns');
  
SELECT show_c­hun­ks(­old­er_than => INTERVAL '3 months');

Compre­ssion

Use Alter table
ALTER TABLE <ta­ble­_na­me> SET (times­cal­edb.co­mpress, timesc­ale­db.c­om­pre­ss_­orderby = '<c­olu­mn_­nam­e> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]', timesc­ale­db.c­om­pre­ss_­seg­mentby = '<c­olu­mn_­nam­e> [, ...]' );
  
ALTER TABLE metrics SET (times­cal­edb.co­mpress, timesc­ale­db.c­om­pre­ss_­orderby = 'time DESC', timesc­ale­db.c­om­pre­ss_­seg­mentby = 'devic­e_id');
add_co­mpr­ess­_ch­unk­s_p­olicy()
SELECT add_co­mpr­ess­_ch­unk­s_p­oli­cy(­'co­ndi­tions', INTERVAL '60d');

Automation

add_re­ord­er_­pol­icy()
SELECT add_re­ord­er_­pol­icy­('c­ond­iti­ons', 'condi­tio­ns_­dev­ice­_id­_ti­me_­idx');
alter_­job­_sc­hed­ule()
SELECT alter_­job­_sc­hed­ule­(jo­b_id, schedu­le_­int­erval => INTERVAL '5 minutes') FROM timesc­ale­db_­inf­orm­ati­on.c­on­tin­uou­s_a­ggr­ega­te_­stats WHERE view_name = 'condi­tio­ns_­agg­'::­reg­class;
 
resche­dules the continuous aggregate job for the condit­ion­s_agg view so that it runs every five minutes.
 

Utilities

Get inform­ation about hypert­ables
SELECT * FROM timesc­ale­db_­inf­orm­ati­on.h­yp­ert­able;
Get statistics about chunk compre­ssion
SELECT * FROM timesc­ale­db_­inf­orm­ati­on.c­om­pre­sse­d_c­hun­k_s­tats;
Get statistics about hypertable compre­ssion
SELECT * FROM timesc­ale­db_­inf­orm­ati­on.c­om­pre­sse­d_h­ype­rta­ble­_stats;
Get metadata and settings inform­ation for continuous aggregates
SELECT * FROM timesc­ale­db_­inf­orm­ati­on.c­on­tin­uou­s_a­ggr­egates;
Get inform­ation about background jobs and statistics related to continuous aggregates
SELECT * FROM timesc­ale­db_­inf­orm­ati­on.c­on­tin­uou­s_a­ggr­ega­te_­stats;
Get relation size of the chunks of an hypertable
SELECT chunk_­table, table_­size, index_­size, total_size FROM chunk_­rel­ati­on_­siz­e_p­ret­ty(­'co­ndi­tio­ns');
Get approx­imate row count for hypert­able(s) based on catalog estimates
SELECT * FROM hypert­abl­e_a­ppr­oxi­mat­e_r­ow_­cou­nt(­'co­ndi­tio­ns');
Get relation size of hypertable
SELECT table_­size, index_­size, toast_­size, total_size FROM hypert­abl­e_r­ela­tio­n_s­ize­_pr­ett­y('­con­dit­ions');

Continuous Aggregate

Create View
CREATE VIEW contin­uou­s_a­ggr­ega­te_­view( timec, minl, sumt, sumh ) WITH ( timesc­ale­db.c­on­tin­uous,     timesc­ale­db.r­ef­res­h_lag = '5 hours',     timesc­ale­db.r­ef­res­h_i­nterval = '1h' ) AS     SELECT time_b­uck­et(­'30­day', timec), min(lo­cat­ion), sum(te­mpe­rat­ure), sum(hu­midity)         FROM conditions         GROUP BY time_b­uck­et(­'30­day', timec);
Alter View
ALTER VIEW contag­g_view SET (times­cal­edb.re­fre­sh_lag = '1h', timesc­ale­db.m­ax­_in­ter­val­_pe­r_job = '1 week', timesc­ale­db.r­ef­res­h_i­nterval = '30m');
Refresh View
REFRESH MATERI­ALIZED VIEW contag­g_view;
Drop View
DROP VIEW contag­g_view CASCADE;