The article recounts how a PostgreSQL instance on a modest 2‑CPU, 4 GB VM handling 1.2 billion rows was dramatically accelerated by adding BRIN indexes, scheduling maintenance with pg_cron, applying query folding and tuning memory and parallel settings, achieving sub‑second query times without additional hardware.
This guide shows how to combine PostgreSQL declarative time‑range partitioning with Citus distributed sharding to create a scalable time‑series database, covering table creation, distribution, automatic monthly partition generation, columnar archiving, maintenance with pg_cron, and performance benefits such as faster inserts, queries, and data expiration.
This guide walks through planning a distribution strategy, adding and back‑filling a distribution key, preparing a development Citus cluster, updating schema and queries for multi‑tenant workloads, securing connections, monitoring cross‑node traffic, and finally migrating production data using either pg_dump/pg_restore for small databases or Citus Warp for large deployments.
