2021-10-27 https://postgresweekly.com/issues/428 ¶
How to scale Postgres for time series data with Citus ¶
Managing time series data at scale can be a challenge.
PostgreSQL offers many powerful data processing features such as indexes, COPY and SQL—but the high data volumes and ever-growing nature of time series data can cause your database to slow down over time.
Fortunately, Postgres has a built-in solution to this problem: Partitioning tables by time range .
Partitioning with the Postgres declarative partitioning ( https://www.postgresql.org/docs/current/ddl-partitioning.html ) feature can help you speed up query and ingest times for your time series workloads.
Range partitioning lets you create a table and break it up into smaller partitions, based on ranges (typically time ranges).
Query performance improves since each query only has to deal with much smaller chunks. Though, you’ll still be limited by the memory, CPU, and storage resources of your Postgres server.
The good news is you can scale out your partitioned Postgres tables to handle enormous amounts of data by distributing the partitions across a cluster.
How ? By using the Citus ( https://github.com/citusdata/citus ) extension to Postgres.
In other words, with Citus you can create distributed time-partitioned tables. To save disk space on your nodes, you can also compress your partitions—without giving up indexes on them.
Even better: the latest Citus 10.2 open-source release makes it a lot easier to manage your partitions in PostgreSQL.
This post is your “how-to” guide to using Postgres with Citus and pg_cron for time series data—effectively transforming PostgreSQL into a distributed time series database.
By using Postgres and Citus together, your application will be more performant at handling the ever-coming massive amounts of time series data—making your life easier.
Improve performance and manageability of large PostgreSQL tables by migrating to partitioned tables on Amazon Aurora and Amazon RDS ¶
In this post, we provide a repeatable solution demonstrating how to do the following:
-
Split large tables into small parent/child tables for performance with minimal downtime
-
Achieve easier data lifecycle management after the table is partitioned