\set SHOW_CONTEXT never CREATE EXTENSION pg_ivm; CREATE EXTENSION pg_partman; CREATE EXTENSION columnar; CREATE EXTENSION pg_cron; CREATE EXTENSION timeseries; CREATE TABLE simple (); SELECT enable_ts_table('simple'); CREATE TABLE hash ( username text ) PARTITION BY HASH (username); SELECT enable_ts_table('hash'); CREATE TABLE nullable ( anniversary date ) PARTITION BY RANGE (anniversary); SELECT enable_ts_table('nullable'); CREATE TABLE nondate ( userid integer NOT NULL ) PARTITION BY RANGE (userid); SELECT enable_ts_table('nondate'); CREATE TABLE multi ( userid integer, subid integer ) PARTITION BY RANGE (userid, subid); SELECT enable_ts_table('multi'); CREATE TABLE measurements ( metric_name text, metric_value numeric, metric_time timestamptz NOT NULL ) PARTITION BY RANGE (metric_time); SELECT enable_ts_table('measurements', partition_duration := '0 days'::interval); SELECT enable_ts_table('measurements', partition_lead_time := '0 days'::interval); SELECT enable_ts_table('measurements'); SELECT partition_duration, partition_lead_time FROM ts_config ORDER BY table_id::text; SELECT partition_interval, premake FROM part_config WHERE parent_table='public.measurements'; SELECT COUNT(*) > 10 AS has_partitions FROM ts_part_info; SELECT COUNT(*) > 0 AS "compressed?" FROM ts_part_info WHERE access_method = 'columnar'; SELECT set_ts_retention_policy('measurements', '90 days'); SELECT retention_duration FROM ts_config WHERE table_id='measurements'::regclass; SELECT retention FROM part_config WHERE parent_table='public.measurements'; SELECT clear_ts_retention_policy('measurements'); SELECT retention_duration FROM ts_config WHERE table_id='measurements'::regclass; SELECT retention FROM part_config WHERE parent_table='public.measurements'; SELECT set_ts_lead_time('measurements', '1 day'); SELECT partition_lead_time FROM ts_config WHERE table_id='measurements'::regclass; SELECT premake FROM part_config WHERE parent_table='public.measurements'; SELECT apply_compression_policy('measurements', '1 day'); SELECT COUNT(*) > 0 AS "compressed?" FROM ts_part_info WHERE access_method = 'columnar'; SELECT set_ts_retention_policy('measurements', '1 day'); SELECT run_maintenance(); SELECT COUNT(*) < 10 AS fewer_partitions FROM ts_part_info; CREATE TABLE events ( user_id bigint, event_id bigint, event_time timestamptz NOT NULL, value float ) PARTITION BY RANGE (event_time); SELECT enable_ts_table('events'); COPY events FROM STDIN WITH (FORMAT 'csv'); 1,1,"2020-11-04 15:51:02.226999-08",1.1 1,2,"2020-11-04 15:53:02.226999-08",1.2 1,3,"2020-11-04 15:55:02.226999-08",1.3 1,4,"2020-11-04 15:57:02.226999-08",1.4 1,5,"2020-11-04 15:58:02.226999-08",1.5 1,6,"2020-11-04 15:59:02.226999-08",1.6 2,7,"2020-11-04 15:51:02.226999-08",1.7 2,8,"2020-11-04 15:53:02.226999-08",1.8 2,9,"2020-11-04 15:55:02.226999-08",1.9 2,10,"2020-11-04 15:57:02.226999-08",2.0 2,11,"2020-11-04 15:58:02.226999-08",2.1 2,12,"2020-11-04 15:59:02.226999-08",2.2 \. SELECT first(value, event_time), user_id FROM events GROUP BY user_id; SELECT last(value, event_time), user_id FROM events GROUP BY user_id; SELECT first(event_id, event_time), user_id FROM events GROUP BY user_id; SELECT last(event_id, event_time), user_id FROM events GROUP BY user_id; SELECT last(user_id, value) top_performer, locf(avg(value)) OVER (ORDER BY event_time), event_time FROM date_bin_table(NULL::events, '1 minute', '[2020-11-04 15:50:00-08, 2020-11-04 16:00:00-08]') GROUP BY 3 ORDER BY 3; SELECT last(user_id, value) top_performer, locf(avg(value)) OVER (ORDER BY event_time), event_time FROM date_bin_table(NULL::events, '1 minute', '(2020-11-04 15:50:00-08, 2020-11-04 16:00:00-08)') GROUP BY 3 ORDER BY 3; CREATE VIEW events_5m AS SELECT user_id, date_bin('5 minutes', event_time, TIMESTAMPTZ '1970-01-01') AS event_time, max(value), min(value) FROM events GROUP BY 1, 2; CREATE VIEW events_totals AS SELECT user_id, sum(value), count(user_id) FROM events GROUP BY 1; SELECT make_view_incremental('events_5m'); SELECT make_view_incremental('events_totals'); SELECT * FROM events_5m ORDER BY 1, 2; SELECT * FROM events_totals ORDER BY 1; INSERT INTO events VALUES (3, 1, '2020-11-04 15:51:02.226999-08', 1.1); DELETE FROM events WHERE event_id = 12; SELECT * FROM events_5m ORDER BY 1, 2; SELECT * FROM events_totals ORDER BY 1;