Composite Partition Keys: Tenant and Time

Some workloads have two dimensions that both matter: queries filter by tenant, retention runs by month, and writes concentrate on the newest data for the largest tenants. A composite key serves both — at the cost of a partition count that multiplies. This guide builds one carefully, choosing the level order from the query mix and keeping the leaf count bounded. It extends Partition Key Selection & Design within Database Partitioning Fundamentals & Architecture.

Prerequisites

• PostgreSQL 11+ (sub-partitioning of a partitioned table), or MySQL 8.0 with SUBPARTITION BY HASH
• Measured query shapes from pg_stat_statements — which predicates appear, and how often
• The retention policy, including whether it differs per tenant
• Current and projected row counts per tenant, so the fan-out can be sized
• A staging environment where plan shapes can be compared before and after

Step 1 — Decide the level order from the evidence

The top level is the one nearly every query filters on; the second level breaks up whatever remains too large.

-- how often does each candidate appear as a predicate?
SELECT count(*) FILTER (WHERE query ~* 'occurred_at\s*[><=]')        AS with_time,
       count(*) FILTER (WHERE query ~* 'tenant_id\s*=')              AS with_tenant,
       count(*) FILTER (WHERE query ~* 'occurred_at\s*[><=]'
                          AND query ~* 'tenant_id\s*=')              AS with_both,
       count(*)                                                      AS total
FROM   pg_stat_statements
WHERE  query ILIKE '%from events%';

 with_time | with_tenant | with_both | total
-----------+-------------+-----------+-------
       412 |         388 |       371 |   441

Both predicates appear in 84% of statements, which is the profile that justifies a composite key. If only one had appeared, a single-level key plus an index on the other column would be the simpler and better answer.

Operational note: Retention overrides the query evidence when the two disagree. A per-tenant retention policy forces tenant to the top level even if time appears in more queries, because only a top-level partition can be dropped independently.

DBA tip: Count statements, not executions, when deciding level order — then weight by executions to sanity-check. A single high-frequency query can justify a layout that the statement count alone would not.

Step 2 — Create the two-level structure with a bounded fan-out

CREATE TABLE events (
    id           bigint GENERATED BY DEFAULT AS IDENTITY,
    tenant_id    bigint      NOT NULL,
    occurred_at  timestamptz NOT NULL,
    payload      jsonb,
    PRIMARY KEY (id, tenant_id, occurred_at)
) PARTITION BY RANGE (occurred_at);

-- one month, itself partitioned by hash on tenant
CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01')
    PARTITION BY HASH (tenant_id);

DO $$
BEGIN
  FOR i IN 0..7 LOOP
    EXECUTE format(
      'CREATE TABLE events_2026_08_h%s PARTITION OF events_2026_08
         FOR VALUES WITH (MODULUS 8, REMAINDER %s)', i, i);
  END LOOP;
END $$;

Operational note: Every unique constraint must contain both levels’ keys, which is why the primary key is (id, tenant_id, occurred_at). That constraint propagates to every leaf automatically.

DBA tip: Fix the modulus at creation and never change it. Changing the hash fan-out later re-assigns every tenant, which is a full data movement of that month.

Step 3 — Verify pruning for each query shape

Two levels mean four possible pruning outcomes, and all four should be checked:

-- both predicates: prunes to one leaf
EXPLAIN (COSTS OFF) SELECT count(*) FROM events
WHERE occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01' AND tenant_id = 8842;

-- time only: prunes to one month, scans its 8 leaves
EXPLAIN (COSTS OFF) SELECT count(*) FROM events
WHERE occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01';

-- tenant only: cannot prune the top level at all
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE tenant_id = 8842;

 Aggregate
   ->  Index Only Scan using events_2026_08_h3_pkey on events_2026_08_h3 events
         Index Cond: ((tenant_id = 8842) AND (occurred_at >= '2026-08-01') AND (occurred_at < '2026-09-01'))

Operational note: The tenant-only shape is where composite layouts disappoint people. It is not a bug: no key ordering makes both single-predicate shapes cheap, which is the fundamental trade of a two-dimensional layout.

SRE tip: Add a default time window in the application for admin queries — “last 90 days unless specified” — rather than letting them scan the full history by accident.

Step 4 — Keep retention working

With time on top, retention is unchanged from a single-level layout:

-- detaching the month detaches all eight sub-partitions with it
ALTER TABLE events DETACH PARTITION events_2026_02 CONCURRENTLY;
DROP TABLE events_2026_02;         -- children go with it

With tenant on top, retention iterates:

DO $$
DECLARE t record;
BEGIN
  FOR t IN SELECT relname FROM pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
            WHERE i.inhparent = 'events'::regclass
  LOOP
    EXECUTE format('ALTER TABLE %I DETACH PARTITION %I_2026_02', t.relname, t.relname);
    EXECUTE format('DROP TABLE %I_2026_02', t.relname);
  END LOOP;
END $$;

Operational note: Dropping a parent partition drops its children automatically — no separate cleanup, and no orphaned leaves.

DBA tip: Whichever order you choose, generate the retention statements from the catalog rather than from a naming convention. Names drift; pg_inherits does not.

Verification

Confirm the leaf count is what you expect and that no leaf is disproportionate:

SELECT count(*) AS leaves,
       pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS total,
       pg_size_pretty(max(pg_total_relation_size(c.oid))) AS largest,
       round(max(pg_total_relation_size(c.oid))::numeric
             / nullif(avg(pg_total_relation_size(c.oid)), 0), 2) AS skew_ratio
FROM   pg_class c
WHERE  c.relkind = 'r' AND c.relname ~ '^events_\d{4}_\d{2}_h\d+$';

 leaves | total  | largest | skew_ratio
--------+--------+---------+------------
     96 | 1188 GB| 19 GB   |       1.54

A skew ratio near one means the hash level is doing its job. A ratio above two means one tenant dominates a bucket and deserves its own list partition instead.

Failure mode table

FAQ

Related

• Partition Key Selection & Design — the parent topic and the four tests a candidate key must pass
• Composite List-Range Partitioning for Regional Time-Series Data — the same idea with region rather than tenant on the first level
• Tuning Planner Settings for Large Partition Counts — what to do when the multiplied leaf count starts costing planning time