Choosing a Partition Key for Multi-Tenant Workloads

This walkthrough evaluates three candidate partition keys — tenant_id, hash(tenant_id), and the composite (tenant_id, created_at) — for a SaaS events table, applying the framework from Partition Key Selection & Design within Database Partitioning Fundamentals & Architecture: profile the tenant distribution, quantify the biggest-tenant problem, prototype all three layouts, benchmark pruning with EXPLAIN, and record the decision.

Prerequisites

• PostgreSQL 13 or later (improved partition pruning and REINDEX CONCURRENTLY)
• A staging database with a recent copy or representative sample of the production events table
• CREATE privilege on the staging database (the prototypes live in their own schemas)
• pg_stat_statements enabled, for confirming which predicates production queries actually carry
• psql access and permission to run EXPLAIN (ANALYZE, BUFFERS)
• Roughly 30 minutes of staging runtime for data loads on a ~10M-row sample

Step 1 — Profile the tenant size distribution

Everything downstream depends on one question: how unevenly are rows spread across tenants? Pull the per-tenant row counts and the distribution percentiles in one pass.

WITH per_tenant AS (
  SELECT tenant_id, COUNT(*) AS row_count
  FROM events
  GROUP BY tenant_id
)
SELECT
  (SELECT COUNT(*) FROM per_tenant)                              AS tenant_count,
  (SELECT SUM(row_count) FROM per_tenant)                        AS total_rows,
  MAX(row_count)                                                 AS biggest_tenant,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY row_count)::bigint AS p99_tenant,
  PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY row_count)::bigint AS median_tenant,
  ROUND(MAX(row_count)::numeric / AVG(row_count), 1)             AS skew_ratio
FROM per_tenant;

A typical B2B result looks like this: 1 840 tenants, 96M rows, biggest tenant 21M rows, median tenant 4 100 rows, skew ratio 402×. That shape — a few whales, thousands of minnows — is the default in multi-tenant SaaS, and it is why the naive “one partition per tenant” answer needs checking before it ships.

Operational note: Run the profile twice — once over the whole table and once restricted to the last 90 days (WHERE created_at > NOW() - INTERVAL '90 days'). A tenant whose share of recent rows is triple its historical share is still growing, and the layout must fit next year’s distribution, not last year’s.

DBA tip: On very large tables, GROUP BY tenant_id is a full scan. Get a fast approximation first from planner statistics: SELECT most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 'events' AND attname = 'tenant_id'; — if the top frequency is above a few percent, you already know skew is material before paying for the exact scan.

Step 2 — Quantify the biggest-tenant problem

Skew only matters through what it does to partition sizes. Project each candidate layout’s worst partition directly from the profile, including where the whale lands under a 16-way hash.

WITH per_tenant AS (
  SELECT tenant_id, COUNT(*) AS row_count
  FROM events
  GROUP BY tenant_id
),
hashed AS (
  SELECT ABS(HASHTEXT(tenant_id)) % 16 AS bucket, SUM(row_count) AS rows
  FROM per_tenant
  GROUP BY 1
)
SELECT 'list: one partition per tenant'   AS layout,
       MAX(row_count)                     AS worst_partition_rows,
       (SELECT COUNT(*) FROM per_tenant)  AS partition_count
FROM per_tenant
UNION ALL
SELECT 'hash: 16 buckets',
       MAX(rows),
       16
FROM hashed
UNION ALL
SELECT 'composite: biggest tenant / month',
       (SELECT MAX(row_count) / 24 FROM per_tenant),  -- ~24 active months
       (SELECT COUNT(*) + 23 FROM per_tenant);         -- whale gains monthly children

With the sample numbers above: the flat list layout’s worst partition is 21M rows; the hash layout’s worst bucket is ~27M rows (the whale plus every other tenant sharing its bucket — hashing made the hot spot worse, not better); the composite layout caps the worst leaf near 900K rows.

Operational note: The hash projection uses HASHTEXT as an approximation of PostgreSQL’s internal partition hashing — bucket membership will differ from real PARTITION BY HASH assignments, but the shape of the projection (whale dominates one bucket) is what the decision needs, and that is hash-function-independent.

DBA tip: Translate rows to bytes before judging: SELECT pg_size_pretty(pg_total_relation_size('events') / (SELECT COUNT(*) FROM events) * 21000000); gives the whale partition’s projected on-disk size including index overhead. A 21M-row partition at 250 bytes/row is ~5 GB — fine; at 2 KB/row with JSONB payloads it is ~40 GB and vacuum on it will dominate the maintenance window.

Step 3 — Prototype the three layouts

Build all three side by side in separate schemas so the EXPLAIN comparison in Step 4 runs against identical data. Layout C uses the standard PostgreSQL construction for a composite key: list by tenant, range sub-partitions by month, the same pattern used in list partitioning techniques.

-- Layout A: LIST (tenant_id) — one partition per tenant
CREATE SCHEMA layout_a;
CREATE TABLE layout_a.events (
  event_id   UUID        DEFAULT gen_random_uuid(),
  tenant_id  VARCHAR(32) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  payload    JSONB,
  PRIMARY KEY (event_id, tenant_id)
) PARTITION BY LIST (tenant_id);
CREATE TABLE layout_a.events_whale   PARTITION OF layout_a.events FOR VALUES IN ('whale-corp');
CREATE TABLE layout_a.events_acme    PARTITION OF layout_a.events FOR VALUES IN ('acme');
CREATE TABLE layout_a.events_default PARTITION OF layout_a.events DEFAULT;

-- Layout B: HASH (tenant_id) — 16 fixed buckets
CREATE SCHEMA layout_b;
CREATE TABLE layout_b.events (LIKE layout_a.events INCLUDING DEFAULTS)
  PARTITION BY HASH (tenant_id);
DO $$
BEGIN
  FOR i IN 0..15 LOOP
    EXECUTE format(
      'CREATE TABLE layout_b.events_h%s PARTITION OF layout_b.events
         FOR VALUES WITH (MODULUS 16, REMAINDER %s)', i, i);
  END LOOP;
END $$;

-- Layout C: composite (tenant_id, created_at) — list + monthly range
CREATE SCHEMA layout_c;
CREATE TABLE layout_c.events (
  event_id   UUID        DEFAULT gen_random_uuid(),
  tenant_id  VARCHAR(32) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  payload    JSONB,
  PRIMARY KEY (event_id, tenant_id, created_at)
) PARTITION BY LIST (tenant_id);
CREATE TABLE layout_c.events_whale PARTITION OF layout_c.events
  FOR VALUES IN ('whale-corp') PARTITION BY RANGE (created_at);
CREATE TABLE layout_c.events_whale_2026_07 PARTITION OF layout_c.events_whale
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE layout_c.events_default PARTITION OF layout_c.events DEFAULT;

Load the same sample into each (INSERT INTO layout_a.events SELECT * FROM events TABLESAMPLE SYSTEM (10), repeated per schema), then ANALYZE all three.

Operational note: Note what each layout demands from the primary key. Layout C must include created_at in the PK because every partition-level key column must appear in every unique constraint — if your application relies on event_id uniqueness alone, that guarantee weakens to per-leaf scope and needs enforcing at generation time (UUIDs already do).

DBA tip: Keep the DEFAULT partitions in layouts A and C even in the prototype. Loading real samples surfaces tenants your DDL forgot, and watching what falls into events_default during the trial is a free rehearsal of the production provisioning gap.

Step 4 — Benchmark pruning with EXPLAIN

Run the two hottest production query shapes — a tenant-scoped 30-day window and a cross-tenant daily aggregate — against all three layouts and compare how many partitions each plan touches.

-- Q1: tenant dashboard, 30-day window (the dominant workload)
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*), MAX(created_at)
FROM layout_c.events
WHERE tenant_id = 'whale-corp'
  AND created_at >= '2026-07-01' AND created_at < '2026-08-01';

-- Q2: cross-tenant aggregate, single day (the reporting workload)
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT tenant_id, COUNT(*)
FROM layout_c.events
WHERE created_at >= '2026-07-05' AND created_at < '2026-07-06'
GROUP BY tenant_id;

Read the plans for one number: partitions scanned. For Q1, layout A scans the whole 21M-row whale partition and filters by date; layout B scans one ~27M-row mixed bucket; layout C scans exactly one ~900K-row monthly leaf. For Q2, layouts A and B scan every partition (neither key carries time); layout C scans one monthly leaf per tenant — still a fan-out, but each leaf is small and index-covered.

Operational note: Repeat Q1 with the tenant id supplied as a bind parameter (PREPARE q1(varchar) AS ...; EXPLAIN EXECUTE q1('whale-corp');) — production traffic uses prepared statements, and generic plans defer pruning from plan time to execution time. Confirm the plan line says Subplans Removed rather than listing all children as scanned.

DBA tip: BUFFERS is the honest metric. shared read blocks for Q1 should drop by an order of magnitude between layout A and layout C; if they do not, the date index inside the whale partition is doing the work pruning was supposed to do, and the composite layout’s extra complexity is buying less than the plan shape suggests.

Step 5 — Pick the layout and document the decision

For the skewed profile measured here, the composite key wins: layout C is the only one that bounds the worst partition, keeps per-tenant deletion (DROP the tenant’s subtree), and prunes both hot query shapes. Record the decision where the next engineer will find it — in the database, alongside the schema it governs.

CREATE TABLE IF NOT EXISTS schema_decisions (
  decided_on   DATE         NOT NULL DEFAULT CURRENT_DATE,
  object_name  TEXT         NOT NULL,
  decision     TEXT         NOT NULL,
  evidence     JSONB        NOT NULL,
  revisit_when TEXT         NOT NULL
);

INSERT INTO schema_decisions (object_name, decision, evidence, revisit_when)
VALUES (
  'events',
  'Composite key: PARTITION BY LIST (tenant_id), whale tenants sub-partitioned by RANGE (created_at) monthly',
  '{"tenant_count": 1840, "skew_ratio": 402,
    "worst_partition_rows": {"list": 21000000, "hash16": 27000000, "composite": 900000},
    "q1_partitions_scanned": {"list": 1, "hash16": 1, "composite": 1},
    "q1_buffers_read": {"list": 412000, "hash16": 530000, "composite": 38000}}',
  'Skew ratio changes by >2x, tenant count exceeds 800 named partitions, or a second tenant exceeds 10M rows'
);

Only whales need range sub-partitioning; keep the many small tenants in flat list partitions (or grouped shared partitions) to hold total partition count down.

Operational note: The revisit_when column is the important one. Partition key decisions rot silently — schedule a quarterly job that re-runs the Step 1 profile and compares it against the recorded evidence, alerting when a trigger condition is met.

DBA tip: Committing to the composite layout also commits you to two-dimensional partition automation: new tenant → new list partition; new month → new range leaf under every whale. Wire both into the provisioning pipeline on day one, because a missing monthly leaf sends the whale’s inserts into an error or a default partition at month rollover.

Verification

Confirm pruning end-to-end on the chosen layout with the production query shape:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*)
FROM layout_c.events
WHERE tenant_id = 'whale-corp'
  AND created_at >= '2026-07-01' AND created_at < '2026-08-01';

Expected output — a single leaf scan, no Append fan-out across tenants or months:

Aggregate (actual time=41.318..41.319 rows=1 loops=1)
  Buffers: shared hit=1204 read=36755
  ->  Index Only Scan using events_whale_2026_07_pkey
        on events_whale_2026_07 events
        (actual time=0.055..38.912 rows=884213 loops=1)
        Index Cond: ((tenant_id = 'whale-corp') AND
                     (created_at >= '2026-07-01') AND (created_at < '2026-08-01'))
Planning Time: 0.412 ms
Execution Time: 41.401 ms

If the plan instead shows Append with multiple events_whale_* children, the range predicate is not prunable — check for type mismatches (::date vs timestamptz) or function-wrapped columns (date_trunc('month', created_at)), both of which defeat pruning.

Failure mode table

FAQ

Related

• Partition Key Selection & Design — the full decision framework this walkthrough applies: alignment audits, cardinality targets, immutability checks
• List Partitioning for Multi-Tenant SaaS Schemas — implementation depth on the tenant-per-partition pattern chosen here: DDL templates, provisioning, and tenant offboarding
• Handling Hot Keys in List Partitioned Tables — mitigation workflows when a whale tenant outgrows even its sub-partitioned layout