List Partitioning for Multi-Tenant SaaS Schemas

This walkthrough builds a tier-based list partitioning layout for a multi-tenant SaaS platform — dedicated partitions for enterprise tenants, shared pools for everyone else — as part of the broader partitioning implementation patterns and routing discipline. The design pairs a tenant_directory mapping table with a routing function, so tenants can be onboarded to dedicated partitions or offboarded via DETACH PARTITION without schema redesign. If you are still deciding what column to partition on, read choosing a partition key for multi-tenant workloads first.

Prerequisites

• PostgreSQL 14+ (DETACH PARTITION CONCURRENTLY and cross-partition UPDATE row movement both required)
• Table-owner or superuser privileges on the target schema
• pgcrypto or PostgreSQL 13+ built-in gen_random_uuid() available
• Application write path that can call a routing function (or cache its result) before INSERT
• A staging environment seeded with production-scale tenant counts to validate planner behaviour
• Python 3.10+ with psycopg2 for the onboarding script

Step 1 — Create the tenant directory mapping table

The directory is the single source of truth for which partition owns each tenant. Every routing decision, onboarding job, and offboarding job reads or updates this table, so the schema stays stable even as tenants move between tiers.

CREATE TABLE tenant_directory (
  tenant_id     TEXT        PRIMARY KEY,
  tier          TEXT        NOT NULL DEFAULT 'standard'
                            CHECK (tier IN ('standard', 'enterprise')),
  partition_key TEXT        NOT NULL,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX ON tenant_directory (partition_key);

-- Seed the launch tenants
INSERT INTO tenant_directory (tenant_id, tier, partition_key) VALUES
  ('acme',    'enterprise', 'acme'),
  ('globex',  'enterprise', 'globex'),
  ('smallco', 'standard',   'pool_00'),
  ('tinyinc', 'standard',   'pool_01');

Operational note: Keep the directory in the same database as the partitioned table, not in an external service. Routing decisions must be transactionally consistent with the rows they route — an out-of-band cache that lags a tier promotion sends writes to the old partition and splits a tenant’s data across two segments.

DBA tip: The partition_key column is deliberately decoupled from tenant_id. Enterprise tenants use their own slug as the key; pooled tenants share synthetic keys like pool_00. This indirection is what makes promotion a metadata update instead of a schema migration.

Step 2 — Create the tiered partition layout

The fact table is list-partitioned on partition_key, not on tenant_id. Enterprise tenants get one partition each; small tenants share a fixed set of pools; a DEFAULT partition catches anything unmapped. The partition key must be part of the primary key, and every partition needs a composite index led by tenant_id so per-tenant reads inside a shared pool stay index-driven.

CREATE TABLE usage_events (
  event_id      UUID        NOT NULL DEFAULT gen_random_uuid(),
  tenant_id     TEXT        NOT NULL,
  partition_key TEXT        NOT NULL,
  action        TEXT        NOT NULL,
  quantity      BIGINT      NOT NULL DEFAULT 1,
  recorded_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (event_id, partition_key)
) PARTITION BY LIST (partition_key);

-- Index template: propagated to every partition created afterwards
CREATE INDEX ON usage_events (tenant_id, recorded_at DESC);

-- Dedicated tier: one partition per enterprise tenant
CREATE TABLE usage_acme    PARTITION OF usage_events FOR VALUES IN ('acme');
CREATE TABLE usage_globex  PARTITION OF usage_events FOR VALUES IN ('globex');

-- Pooled tier: fixed set of shared partitions for small tenants
CREATE TABLE usage_pool_00 PARTITION OF usage_events FOR VALUES IN ('pool_00');
CREATE TABLE usage_pool_01 PARTITION OF usage_events FOR VALUES IN ('pool_01');
CREATE TABLE usage_pool_02 PARTITION OF usage_events FOR VALUES IN ('pool_02');
CREATE TABLE usage_pool_03 PARTITION OF usage_events FOR VALUES IN ('pool_03');

-- Safety net for unmapped keys
CREATE TABLE usage_default PARTITION OF usage_events DEFAULT;

Operational note: Creating the parent-level index before the child partitions means PostgreSQL builds a matching index on every partition automatically, including future ones. If you add the index later, the parent CREATE INDEX cascades to existing children but takes a lock on each — schedule it off-peak on a live system.

DBA tip: Start with four pools even if two would hold the data. Pool count is painful to change later (rebalancing means moving rows), while empty partitions cost almost nothing. Four pools also give the autovacuum workers independent, smaller targets — one of the main wins over a monolithic table.

Step 3 — Implement the mapping-driven routing function

The routing function resolves a tenant to its partition key, lazily assigning unknown tenants to a pool by hashing the tenant id across the pool set. The application calls it once per session or caches the result — not inline in every query.

CREATE OR REPLACE FUNCTION resolve_partition_key(p_tenant TEXT)
RETURNS TEXT
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  v_key TEXT;
BEGIN
  SELECT partition_key INTO v_key
  FROM tenant_directory
  WHERE tenant_id = p_tenant;

  IF v_key IS NULL THEN
    -- Deterministic pool assignment for brand-new tenants: hash across 4 pools
    v_key := 'pool_' || lpad((abs(hashtext(p_tenant)) % 4)::text, 2, '0');
    INSERT INTO tenant_directory (tenant_id, tier, partition_key)
    VALUES (p_tenant, 'standard', v_key)
    ON CONFLICT (tenant_id) DO NOTHING;
  END IF;

  RETURN v_key;
END;
$$;

-- Write path: resolve once, then bind the literal key
INSERT INTO usage_events (tenant_id, partition_key, action, quantity)
VALUES ('smallco', resolve_partition_key('smallco'), 'api.call', 1);

Operational note: Never put resolve_partition_key() in a WHERE clause. The function is VOLATILE (it may insert a directory row), so the planner cannot evaluate it at plan time and pruning is lost — every partition gets scanned. Reads must resolve the key first, then filter with WHERE partition_key = $1 AND tenant_id = $2 as plain bind parameters.

DBA tip: Cache the tenant-to-key mapping in the application with a short TTL (30–60 seconds) and invalidate it explicitly during tier promotions. The directory lookup is a primary-key hit and cheap, but at tens of thousands of inserts per second even cheap lookups add up; a cache turns routing into a purely in-process operation.

Step 4 — Onboard an enterprise tenant out of the pool

When a pooled tenant upgrades, create their dedicated partition, repoint the directory so new writes route immediately, then drain historical rows out of the pool in bounded batches. PostgreSQL’s cross-partition UPDATE moves each row as a delete-plus-insert under the hood, so no manual copy is needed.

from psycopg2 import connect, sql

def promote_to_enterprise(dsn: str, tenant: str, batch: int = 10_000) -> int:
    """Give `tenant` a dedicated partition and drain its rows from the pool."""
    part = sql.Identifier(f"usage_{tenant}")
    moved_total = 0
    with connect(dsn) as conn, conn.cursor() as cur:
        # 1. Read the current pool assignment
        cur.execute(
            "SELECT partition_key FROM tenant_directory WHERE tenant_id = %s",
            (tenant,),
        )
        old_key = cur.fetchone()[0]

        # 2. Create the dedicated partition (new key = tenant slug)
        cur.execute(
            sql.SQL(
                "CREATE TABLE {} PARTITION OF usage_events FOR VALUES IN (%s)"
            ).format(part),
            (tenant,),
        )

        # 3. Repoint the directory: all new writes now route to the new partition
        cur.execute(
            """UPDATE tenant_directory
               SET tier = 'enterprise', partition_key = %s, updated_at = NOW()
               WHERE tenant_id = %s""",
            (tenant, tenant),
        )
        conn.commit()

        # 4. Drain history from the pool in bounded batches (row movement)
        while True:
            cur.execute(
                """UPDATE usage_events SET partition_key = %s
                   WHERE event_id IN (
                     SELECT event_id FROM usage_events
                     WHERE partition_key = %s AND tenant_id = %s
                     LIMIT %s
                   )""",
                (tenant, old_key, tenant, batch),
            )
            moved_total += cur.rowcount
            conn.commit()
            if cur.rowcount < batch:
                return moved_total

Operational note: Commit between batches. A single-transaction drain of fifty million rows holds locks for the full duration, bloats the pool partition with dead tuples all at once, and risks replication lag spikes. Batched commits let autovacuum and the replicas keep pace.

DBA tip: Run ANALYZE usage_<tenant> after the drain completes, and check that reads issued during the drain use WHERE partition_key IN ($old, $new) AND tenant_id = $t — the tenant’s rows legitimately span two partitions until step 4 finishes, and an equality filter on only the new key silently misses history.

Step 5 — Offboard a tenant with DETACH

Offboarding an enterprise tenant is the payoff of this layout: remove the routing entry, detach the partition without blocking concurrent queries, archive, and drop. No DELETE storm, no vacuum debt.

-- 1. Stop routing: the tenant can no longer resolve to a partition
DELETE FROM tenant_directory WHERE tenant_id = 'globex';

-- 2. Detach without blocking readers (PostgreSQL 14+; runs outside
--    a transaction block and takes only a brief SHARE UPDATE EXCLUSIVE lock)
ALTER TABLE usage_events DETACH PARTITION usage_globex CONCURRENTLY;

-- 3. The detached table is standalone: rename for the archival job, then drop
ALTER TABLE usage_globex RENAME TO usage_globex_offboarded_2026_07;

-- After the archival job confirms the export:
DROP TABLE usage_globex_offboarded_2026_07;

Operational note: For a pooled tenant the partition is shared, so offboarding falls back to a batched DELETE FROM usage_events WHERE partition_key = 'pool_02' AND tenant_id = 'tinyinc' — this is the structural trade-off of pooling. Keep tenants with contractual hard-delete SLAs in the dedicated tier for exactly this reason.

DBA tip: If DETACH PARTITION CONCURRENTLY is interrupted (crash, cancelled statement), the partition is left in a half-detached state and inserts to it fail. Recover with ALTER TABLE usage_events DETACH PARTITION usage_globex FINALIZE; — check pg_inherits joined to pg_class.relispartition if you suspect a stuck detach.

Verification

Confirm pruning routes a tenant-scoped query to exactly one partition:

EXPLAIN (COSTS OFF)
SELECT count(*)
FROM usage_events
WHERE partition_key = 'acme'
  AND tenant_id = 'acme'
  AND recorded_at > NOW() - INTERVAL '7 days';

Expected output — a single partition scan, no Append over siblings:

Aggregate
  ->  Index Scan using usage_acme_tenant_id_recorded_at_idx on usage_acme usage_events
        Index Cond: ((tenant_id = 'acme'::text) AND (recorded_at > ...))

Then check the physical distribution and that nothing is leaking into the DEFAULT partition:

SELECT tableoid::regclass AS partition, count(*) AS rows
FROM usage_events
GROUP BY 1
ORDER BY 2 DESC;

   partition   |   rows
---------------+----------
 usage_acme    | 41208330
 usage_pool_00 |  6220914
 usage_pool_01 |  5904112
 usage_pool_02 |  6110457
 usage_pool_03 |  5987631
 usage_globex  |  3310220
 usage_default |        0

usage_default must stay at or near zero; any growth means a write bypassed the routing function or a directory row points at a partition key with no matching partition.

Failure mode table

FAQ

Related

• List Partitioning Techniques — parent guide covering list-partition DDL, pruning behaviour, and automated partition provisioning
• Handling Hot Keys in List Partitioned Tables — hash sub-partitioning when a promoted tenant is still too hot for a single partition
• Choosing a Partition Key for Multi-Tenant Workloads — the key-selection analysis that justifies the tiered layout used here