Building Scalable Time‑Series with Distributed PostgreSQL (Citus) – Official Example

Why Partition Time‑Series Data

Time‑series workloads often need fast access to recent records while regularly archiving older data. A single‑node PostgreSQL database typically solves this by partitioning a large time‑ordered table into multiple child tables, each covering a specific time range.

Storing data in many physical tables speeds up data expiration because dropping an entire partition is a constant‑time operation, unlike scanning and deleting rows from a monolithic table. Smaller per‑partition indexes also make recent‑data queries faster, especially when a hot index can fit in memory, and inserts update fewer index entries.

Combining Partitioning with Citus Sharding

By pairing PostgreSQL's declarative range partitioning with Citus's distributed sharding, you can build a horizontally scalable time‑series store. The example uses a GitHub events dataset and distributes the table by repo_id, so events for the same repository reside on the same shard.

-- declaratively partitioned table
CREATE TABLE github_events (
  event_id bigint,
  event_type text,
  event_public boolean,
  repo_id bigint,
  payload jsonb,
  repo jsonb,
  actor jsonb,
  org jsonb,
  created_at timestamp
) PARTITION BY RANGE (created_at);

After creating the table, distribute it with Citus:

SELECT create_distributed_table('github_events', 'repo_id');

Citus creates shards named github_events_N and propagates the partition key RANGE (created_at) to each shard, effectively turning the partitioned table into a distributed view.

Automatic Monthly Partition Creation

Citus provides the helper function create_time_partitions() to generate a batch of monthly partitions:

SELECT create_time_partitions(
  table_name         := 'github_events',
  partition_interval := '1 month',
  end_at             := now() + '12 months'
);

The time_partitions view can be queried to inspect the created partitions.

Maintenance with pg_cron

Set up a regular job (e.g., using the pg_cron extension) to keep partitions up‑to‑date and purge old ones:

-- ensure partitions for the next 12 months
SELECT cron.schedule('create-partitions', '0 0 1 * *', $$
  SELECT create_time_partitions(
      table_name         := 'github_events',
      partition_interval := '1 month',
      end_at             := now() + '12 months'
  )
$$);

-- optionally drop partitions older than one year
SELECT cron.schedule('drop-partitions', '0 0 1 * *', $$
  CALL drop_old_time_partitions(
      'github_events',
      now() - interval '12 months' /* older_than */
  );
$$);

Once these jobs run, partitions are maintained automatically.

Archiving to Columnar Storage

For data that becomes immutable (e.g., logs, click‑streams), you can move older row partitions into Citus’s columnar tables, which provide high compression. Create a columnar table mirroring the original schema:

CREATE TABLE github_columnar_events (LIKE github_events)
PARTITION BY RANGE (created_at);

Create two‑hour partitions for a sample range and load data:

SELECT create_time_partitions(
  table_name         := 'github_columnar_events',
  partition_interval := '2 hours',
  start_from         := '2015-01-01 00:00:00',
  end_at             := '2015-01-01 08:00:00'
);

COPY github_columnar_events FROM 'github_events.csv' WITH (format CSV);

Convert older partitions to columnar storage:

CALL alter_old_partitions_set_access_method(
  'github_columnar_events',
  '2015-01-01 06:00:00' /* older_than */,
  'columnar'
);

Check the access method of each partition:

SELECT partition, access_method
FROM time_partitions
WHERE parent_table = 'github_columnar_events'::regclass;

Run VACUUM VERBOSE to see compression ratios (≈8× for the sample partitions).

VACUUM VERBOSE github_columnar_events;

The columnar table can be queried like any regular table; a WHERE clause on the partition key filters to the appropriate row partitions, allowing updates or deletions on recent data.

Archiving Filled Row Partitions

When a row partition is full, schedule a monthly pg_cron job to convert it to columnar storage:

SELECT cron.schedule('compress-partitions', '0 0 1 * *', $$
  CALL alter_old_partitions_set_access_method(
    'github_columnar_events',
    now() - interval '6 months',
    'columnar'
  );
$$);

For more details on columnar storage, refer to the Citus documentation.