Composite List-Range Partitioning for Regional Time-Series Data

This walkthrough builds a two-level PostgreSQL layout for regional telemetry: list partitioning by region (eu, us, apac) for data-residency isolation, then range partitioning by month inside each region — one of the composite patterns from the wider partitioning implementation patterns and routing guide. You get pruning on both dimensions, monthly sub-partition creation automated across all regions, and retention that drops old months in one region without touching the others.

Prerequisites

• PostgreSQL 13+ (pg_partition_tree() and multi-level declarative partitioning; 14+ recommended for DETACH ... CONCURRENTLY)
• Table-owner privileges plus rights to create functions in the target schema
• pg_cron extension installed on the primary (CREATE EXTENSION pg_cron;), or an external scheduler that can run psql
• Server timezone policy agreed — all boundary literals in this guide assume timezone = 'UTC'
• bash 4+ with psql in $PATH on the operations host for the retention script
• A staging database to rehearse detach/drop sequences before production

Step 1 — Create the two-level layout

The parent is list-partitioned on region. Each region partition is itself a partitioned table — PARTITION BY RANGE (recorded_at) — and only the monthly leaves store rows. Both partition columns must appear in the primary key, because uniqueness can only be enforced within a single leaf.

CREATE TABLE sensor_readings (
  reading_id  BIGINT           GENERATED ALWAYS AS IDENTITY,
  region      TEXT             NOT NULL,
  device_id   UUID             NOT NULL,
  metric      TEXT             NOT NULL,
  value       DOUBLE PRECISION NOT NULL,
  recorded_at TIMESTAMPTZ      NOT NULL,
  PRIMARY KEY (reading_id, region, recorded_at)
) PARTITION BY LIST (region);

-- Index template inherited by every leaf created below
CREATE INDEX ON sensor_readings (device_id, recorded_at DESC);

-- Level 1: one partition per region, each partitioned again by month
CREATE TABLE readings_eu   PARTITION OF sensor_readings
  FOR VALUES IN ('eu')   PARTITION BY RANGE (recorded_at);
CREATE TABLE readings_us   PARTITION OF sensor_readings
  FOR VALUES IN ('us')   PARTITION BY RANGE (recorded_at);
CREATE TABLE readings_apac PARTITION OF sensor_readings
  FOR VALUES IN ('apac') PARTITION BY RANGE (recorded_at);

-- Level 2: the current month in every region (half-open [start, end) bounds)
CREATE TABLE readings_eu_2026_07   PARTITION OF readings_eu
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE readings_us_2026_07   PARTITION OF readings_us
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE readings_apac_2026_07 PARTITION OF readings_apac
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

Operational note: With a timestamptz partition column, boundary literals like '2026-07-01' are interpreted in the session’s timezone at DDL time. Pin timezone = 'UTC' in the connection that runs partition DDL (and in the automation function below), or two months created from differently configured sessions will have misaligned boundaries.

DBA tip: For residency isolation on shared hardware, add TABLESPACE ts_eu to the EU leaves so European rows live on EU-mounted volumes. Because the region split is the outermost level, the tablespace assignment is a per-subtree decision — impossible if you had put RANGE on the outside.

Step 2 — Automate monthly sub-partition creation across regions

Every new month needs three leaves, one per region, with identical boundaries. A single set-returning loop keeps the regions in lockstep so no region ever lacks a landing partition. Schedule it with pg_cron a few days before month end; the pattern generalises to the pipelines described in automated partition creation workflows.

CREATE OR REPLACE FUNCTION create_next_month_subpartitions()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  region_name TEXT;
  month_start DATE := (date_trunc('month', now() AT TIME ZONE 'UTC')
                       + INTERVAL '1 month')::date;
  month_end   DATE := (date_trunc('month', now() AT TIME ZONE 'UTC')
                       + INTERVAL '2 months')::date;
  leaf_name   TEXT;
BEGIN
  PERFORM set_config('timezone', 'UTC', true);  -- deterministic boundaries

  FOREACH region_name IN ARRAY ARRAY['eu', 'us', 'apac'] LOOP
    leaf_name := format('readings_%s_%s',
                        region_name, to_char(month_start, 'YYYY_MM'));
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
      leaf_name, 'readings_' || region_name, month_start, month_end
    );
    RAISE NOTICE 'ensured leaf %', leaf_name;
  END LOOP;
END;
$$;

-- Run at 03:00 UTC on the 25th, giving ~5 days of slack before rollover
SELECT cron.schedule(
  'regional-subpartition-rollover',
  '0 3 25 * *',
  'SELECT create_next_month_subpartitions()'
);

Operational note: CREATE TABLE IF NOT EXISTS ... PARTITION OF makes the job idempotent — a retried or double-scheduled run is harmless. What it does not survive is a skipped month: the function only creates next month, so if the job host is down across the 25th–1st window, inserts start failing at rollover. Alert on the verification query below, not on cron exit codes alone.

DBA tip: pg_cron runs on the primary only, and partition DDL replicates to physical standbys automatically. If you later split regions into separate instances, move the schedule into an external orchestrator so one canonical job creates each region’s months on its own primary with the same boundary arithmetic.

Step 3 — Write queries that prune on both levels

Pruning is hierarchical: a region equality predicate eliminates entire region subtrees, and a half-open recorded_at range eliminates months inside the survivors. Queries carrying both predicates touch exactly one leaf; time-only queries touch one leaf per region; region-only queries scan every month of that region.

-- Both levels prune: one leaf (readings_eu_2026_07)
SELECT device_id, avg(value) AS avg_value
FROM sensor_readings
WHERE region = 'eu'
  AND recorded_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00'
  AND recorded_at <  TIMESTAMPTZ '2026-08-01 00:00:00+00'
GROUP BY device_id;

-- Multi-region, one month: IN lists still prune at the LIST level
SELECT region, count(*)
FROM sensor_readings
WHERE region IN ('eu', 'us')
  AND recorded_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00'
  AND recorded_at <  TIMESTAMPTZ '2026-08-01 00:00:00+00'
GROUP BY region;

-- Anti-pattern: wrapping the range column defeats month-level pruning
-- WHERE date(recorded_at) = '2026-07-03'        -- scans all months
-- Rewrite as a half-open range on the raw column instead:
--   recorded_at >= '2026-07-03' AND recorded_at < '2026-07-04'

Operational note: Predicates using stable expressions like now() - INTERVAL '7 days' cannot be pruned at plan time, but PostgreSQL applies execution-time pruning: EXPLAIN (ANALYZE) shows the skipped leaves as (never executed). That is fine operationally — just do not judge pruning from a bare EXPLAIN, which appears to list every month.

DBA tip: Keep the region predicate as a plain literal or bind parameter of the column’s exact type. An expression such as upper(region) = 'EU' or a join that derives region from another table blocks LIST-level pruning entirely, which multiplies the scanned leaf count by the number of regions.

Step 4 — Apply per-region retention

Regulation rarely aligns across jurisdictions: here EU telemetry is kept 6 months while US and APAC keep 13. Because months are nested inside regions, dropping old EU data is a subtree-local operation — the other regions’ partitions are never locked or touched.

#!/usr/bin/env bash
# retention.sh — detach and drop expired monthly leaves, per region
set -euo pipefail
DB="postgresql://[email protected]:5432/telemetry"

declare -A RETENTION_MONTHS=( [eu]=6 [us]=13 [apac]=13 )

for region in eu us apac; do
  cutoff=$(date -u -d "$(date -u +%Y-%m-01) -${RETENTION_MONTHS[$region]} months" +%Y_%m)

  # Leaf names are zero-padded YYYY_MM, so lexical < is chronological <
  expired=$(psql "$DB" -At -c "
    SELECT c.relname
    FROM pg_inherits i
    JOIN pg_class c ON c.oid = i.inhrelid
    WHERE i.inhparent = 'readings_${region}'::regclass
      AND c.relname < 'readings_${region}_${cutoff}'
    ORDER BY c.relname")

  for leaf in $expired; do
    echo "[$region] retiring $leaf (cutoff ${cutoff})"
    psql "$DB" -v ON_ERROR_STOP=1 \
      -c "SET lock_timeout = '5s';" \
      -c "ALTER TABLE readings_${region} DETACH PARTITION ${leaf} CONCURRENTLY;"
    psql "$DB" -v ON_ERROR_STOP=1 -c "DROP TABLE ${leaf};"
  done
done

Operational note: Detach-then-drop is deliberate. DETACH ... CONCURRENTLY removes the leaf from the tree while queries keep running; the subsequent DROP acts on a standalone table nobody routes to. Dropping an attached leaf directly needs an ACCESS EXCLUSIVE lock on the region parent and will queue behind (and then block) live traffic.

DBA tip: Insert an archival hop between detach and drop when audit rules require it — pg_dump --table=<leaf> to object storage, verify the checksum, then drop. Since the leaf is already detached, the export runs at full speed without holding any lock on the live tree.

Verification

Inspect the physical tree first — every region should show as an intermediate partitioned table with its monthly leaves beneath it:

SELECT relid::text AS relation, parentrelid::text AS parent, isleaf, level
FROM pg_partition_tree('sensor_readings')
ORDER BY level, relation;

       relation        |     parent      | isleaf | level
-----------------------+-----------------+--------+-------
 sensor_readings       |                 | f      |     0
 readings_apac         | sensor_readings | f      |     1
 readings_eu           | sensor_readings | f      |     1
 readings_us           | sensor_readings | f      |     1
 readings_apac_2026_07 | readings_apac   | t      |     2
 readings_eu_2026_07   | readings_eu     | t      |     2
 readings_us_2026_07   | readings_us     | t      |     2

Then confirm dual-level pruning collapses a region-plus-month query to a single leaf:

EXPLAIN (COSTS OFF)
SELECT count(*)
FROM sensor_readings
WHERE region = 'eu'
  AND recorded_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00'
  AND recorded_at <  TIMESTAMPTZ '2026-07-15 00:00:00+00';

Aggregate
  ->  Seq Scan on readings_eu_2026_07 sensor_readings
        Filter: ((recorded_at >= '2026-07-01 00:00:00+00'::timestamp with time zone)
             AND (recorded_at < '2026-07-15 00:00:00+00'::timestamp with time zone)
             AND (region = 'eu'::text))

No Append node and no sibling leaves in the plan means both levels pruned. Finally, spot-check row placement: SELECT tableoid::regclass, count(*) FROM sensor_readings GROUP BY 1; should list only leaves for months you expect to hold data.

Failure mode table

FAQ

Related

• List Partitioning Techniques — parent guide covering LIST partition DDL, pruning mechanics, and hot-key mitigation
• Range Partitioning Strategies — boundary design and lifecycle management for the RANGE level of this layout
• Automated Partition Creation Workflows — scheduling patterns and pre-creation buffering that generalise the Step 2 automation