\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'); ERROR: could not enable time-series enhancements DETAIL: Target table was not partitioned HINT: Recreate table using PARTITION BY RANGE CREATE TABLE hash ( username text ) PARTITION BY HASH (username); SELECT enable_ts_table('hash'); ERROR: could not enable time-series enhancements DETAIL: Target table not range-partitioned HINT: Recreate table using PARTITION BY RANGE CREATE TABLE nullable ( anniversary date ) PARTITION BY RANGE (anniversary); SELECT enable_ts_table('nullable'); ERROR: could not enable time-series enhancements DETAIL: Partition column nullable HINT: Use ALTER TABLE to add a NOT NULL constraint to the partition column. CREATE TABLE nondate ( userid integer NOT NULL ) PARTITION BY RANGE (userid); SELECT enable_ts_table('nondate'); ERROR: could not enable time-series enhancements DETAIL: Partition column was not a time type HINT: Only timestamp(tz) and date partition columns are supported CREATE TABLE multi ( userid integer, subid integer ) PARTITION BY RANGE (userid, subid); SELECT enable_ts_table('multi'); ERROR: could not enable time-series enhancements DETAIL: Partition key not single-column HINT: Recreate table using a single-column partition key 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); ERROR: unusable partition duration DETAIL: Partition duration must be positive HINT: Provide a positive interval for partition duration (width). SELECT enable_ts_table('measurements', partition_lead_time := '0 days'::interval); ERROR: unusable partition lead time DETAIL: Partition lead time must be positive HINT: Provide a positive interval for partition creation lead time. SELECT enable_ts_table('measurements'); enable_ts_table ----------------- (1 row) SELECT partition_duration, partition_lead_time FROM ts_config ORDER BY table_id::text; partition_duration | partition_lead_time --------------------+--------------------- @ 7 days | @ 1 mon (1 row) SELECT partition_interval, premake FROM part_config WHERE parent_table='public.measurements'; partition_interval | premake --------------------+--------- @ 7 days | 5 (1 row) SELECT COUNT(*) > 10 AS has_partitions FROM ts_part_info; has_partitions ---------------- t (1 row) SELECT COUNT(*) > 0 AS "compressed?" FROM ts_part_info WHERE access_method = 'columnar'; compressed? ------------- f (1 row) SELECT set_ts_retention_policy('measurements', '90 days'); set_ts_retention_policy ------------------------- (1 row) SELECT retention_duration FROM ts_config WHERE table_id='measurements'::regclass; retention_duration -------------------- @ 90 days (1 row) SELECT retention FROM part_config WHERE parent_table='public.measurements'; retention ----------- @ 90 days (1 row) SELECT clear_ts_retention_policy('measurements'); clear_ts_retention_policy --------------------------- @ 90 days (1 row) SELECT retention_duration FROM ts_config WHERE table_id='measurements'::regclass; retention_duration -------------------- (1 row) SELECT retention FROM part_config WHERE parent_table='public.measurements'; retention ----------- (1 row) SELECT set_ts_lead_time('measurements', '1 day'); set_ts_lead_time ------------------ @ 1 mon (1 row) SELECT partition_lead_time FROM ts_config WHERE table_id='measurements'::regclass; partition_lead_time --------------------- @ 1 day (1 row) SELECT premake FROM part_config WHERE parent_table='public.measurements'; premake --------- 1 (1 row) SELECT apply_compression_policy('measurements', '1 day'); apply_compression_policy -------------------------- (1 row) SELECT COUNT(*) > 0 AS "compressed?" FROM ts_part_info WHERE access_method = 'columnar'; compressed? ------------- t (1 row) SELECT set_ts_retention_policy('measurements', '1 day'); set_ts_retention_policy ------------------------- (1 row) SELECT run_maintenance(); run_maintenance ----------------- (1 row) SELECT COUNT(*) < 10 AS fewer_partitions FROM ts_part_info; fewer_partitions ------------------ t (1 row) 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'); enable_ts_table ----------------- (1 row) COPY events FROM STDIN WITH (FORMAT 'csv'); SELECT first(value, event_time), user_id FROM events GROUP BY user_id; first | user_id -------+--------- 1.7 | 2 1.1 | 1 (2 rows) SELECT last(value, event_time), user_id FROM events GROUP BY user_id; last | user_id ------+--------- 2.2 | 2 1.6 | 1 (2 rows) SELECT first(event_id, event_time), user_id FROM events GROUP BY user_id; first | user_id -------+--------- 7 | 2 1 | 1 (2 rows) SELECT last(event_id, event_time), user_id FROM events GROUP BY user_id; last | user_id ------+--------- 12 | 2 6 | 1 (2 rows) 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; top_performer | locf | event_time ---------------+--------------------+------------------------------ | | Wed Nov 04 15:50:00 2020 PST 2 | 1.4 | Wed Nov 04 15:51:00 2020 PST | 1.4 | Wed Nov 04 15:52:00 2020 PST 2 | 1.5 | Wed Nov 04 15:53:00 2020 PST | 1.5 | Wed Nov 04 15:54:00 2020 PST 2 | 1.6 | Wed Nov 04 15:55:00 2020 PST | 1.6 | Wed Nov 04 15:56:00 2020 PST 2 | 1.7 | Wed Nov 04 15:57:00 2020 PST 2 | 1.8 | Wed Nov 04 15:58:00 2020 PST 2 | 1.9000000000000001 | Wed Nov 04 15:59:00 2020 PST | 1.9000000000000001 | Wed Nov 04 16:00:00 2020 PST (11 rows) 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; top_performer | locf | event_time ---------------+--------------------+------------------------------ 2 | 1.4 | Wed Nov 04 15:51:00 2020 PST | 1.4 | Wed Nov 04 15:52:00 2020 PST 2 | 1.5 | Wed Nov 04 15:53:00 2020 PST | 1.5 | Wed Nov 04 15:54:00 2020 PST 2 | 1.6 | Wed Nov 04 15:55:00 2020 PST | 1.6 | Wed Nov 04 15:56:00 2020 PST 2 | 1.7 | Wed Nov 04 15:57:00 2020 PST 2 | 1.8 | Wed Nov 04 15:58:00 2020 PST 2 | 1.9000000000000001 | Wed Nov 04 15:59:00 2020 PST (9 rows) 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'); make_view_incremental ----------------------- (1 row) SELECT make_view_incremental('events_totals'); make_view_incremental ----------------------- (1 row) SELECT * FROM events_5m ORDER BY 1, 2; user_id | event_time | max | min ---------+------------------------------+-----+----- 1 | Wed Nov 04 15:50:00 2020 PST | 1.2 | 1.1 1 | Wed Nov 04 15:55:00 2020 PST | 1.6 | 1.3 2 | Wed Nov 04 15:50:00 2020 PST | 1.8 | 1.7 2 | Wed Nov 04 15:55:00 2020 PST | 2.2 | 1.9 (4 rows) SELECT * FROM events_totals ORDER BY 1; user_id | sum | count ---------+------+------- 1 | 8.1 | 6 2 | 11.7 | 6 (2 rows) 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; user_id | event_time | max | min ---------+------------------------------+-----+----- 1 | Wed Nov 04 15:50:00 2020 PST | 1.2 | 1.1 1 | Wed Nov 04 15:55:00 2020 PST | 1.6 | 1.3 2 | Wed Nov 04 15:50:00 2020 PST | 1.8 | 1.7 2 | Wed Nov 04 15:55:00 2020 PST | 2.1 | 1.9 3 | Wed Nov 04 15:50:00 2020 PST | 1.1 | 1.1 (5 rows) SELECT * FROM events_totals ORDER BY 1; user_id | sum | count ---------+-----+------- 1 | 8.1 | 6 2 | 9.5 | 5 3 | 1.1 | 1 (3 rows)