How to scale Postgres for time series data with Citus
summary
Shows how to turn Postgres partitioned time-series into a distributed, compressed store using Citus UDFs and pg_cron. Demonstrates create_time_partitions and drop_old_time_partitions to auto-create daily/minute partitions, then uses alter_old_partitions_set_access_method (Citus Columnar) to compress older partitions; demo creates 15 minute-partitions (7 past, 7 future + current), drops partitions older than 5 minutes and compresses ones older than 3 minutes, and uses pg_cron, citus_add_node and rebalance_table_shards to scale out.
AI-written summary · about
chapters (8)
- 0:00Introduction
- 1:31Timeseries data and workload
- 3:39Postgres built-in partitioning
- 7:25Easy partition management with Citus
- 9:36Citus Columnar
- 14:44Automated partition management with pg_cron
- 18:16Sharding & partitioning
- 22:41Demo
related talks
40mBeginner's Guide to Partitioning vs. Sharding in Postgres
51mPostgreSQL, The Time-Series Database You Want
25mData-intensive PostgreSQL: Three ways to scale
49mPostgreSQL, The Time-Series Database You Actually Want
44mDistributed Postgres with Citus
51mBuilding a scalable time-series database on PostgreSQL
37mPostgres at Any Scale
39mTimescaleDB: Re-engineering PostgreSQL as a time-series database