List Partitioning Techniques: Configuration, Routing & Workflows

List partitioning maps discrete categorical values to dedicated storage segments, giving the query planner exact routing information for every INSERT and SELECT. Unlike range partitioning strategies, which divide a continuous domain at numeric or temporal boundaries, or hash routing algorithms, which scatter rows uniformly across nodes, list partitioning locks each row to a named partition based on an exact value match. This page is part of the Partitioning Implementation Patterns & Routing guide; it covers production DDL, partition pruning, hot-key mitigation, composite routing, and the failure modes engineers encounter most often.

Problem Framing

A multi-tenant SaaS platform ingests 50 million event rows per day into a single tenant_events table. Reporting queries from individual tenants scan the full table despite filtering on tenant_id, because the planner has no metadata that limits eligible pages. Index scans help at moderate data volumes but degrade past a few hundred million rows when autovacuum cannot keep pace with dead-tuple accumulation. Archival is painful: purging a churned tenant means a full sequential scan and DELETE with no easy way to drop a bounded data set atomically.

List partitioning solves all three: the planner prunes to the relevant partition, autovacuum runs on smaller per-tenant segments independently, and tenant archival becomes a single DETACH PARTITION plus DROP TABLE.

The approach works when the partition key column has a bounded, manageable set of discrete values. It struggles when values are high-cardinality unbounded strings (thousands of tenants added continuously) or when write distribution across values is heavily skewed — both of which the sections below address.

Step 1 — Define the Parent Table and Partitions

Declare the parent table with PARTITION BY LIST and create one child partition per value set. Always add a DEFAULT partition; without it, any INSERT for an unmapped value raises an error immediately and halts pipelines.

-- PostgreSQL 14+: categorical routing with a DEFAULT fallback
CREATE TABLE tenant_events (
  event_id    UUID         DEFAULT gen_random_uuid(),
  tenant_id   VARCHAR(32)  NOT NULL,
  event_type  VARCHAR(50)  NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  PRIMARY KEY (event_id, tenant_id)          -- partition key must appear in PK
) PARTITION BY LIST (tenant_id);

CREATE TABLE events_acme    PARTITION OF tenant_events FOR VALUES IN ('acme');
CREATE TABLE events_globex  PARTITION OF tenant_events FOR VALUES IN ('globex');
CREATE TABLE events_initech PARTITION OF tenant_events FOR VALUES IN ('initech');
CREATE TABLE events_default PARTITION OF tenant_events DEFAULT;

Build indexes on each child partition, not on the parent. PostgreSQL propagates index definitions to new partitions created after the parent index exists, but existing partitions created before an index require explicit CREATE INDEX statements.

CREATE INDEX CONCURRENTLY ON events_acme   (created_at DESC);
CREATE INDEX CONCURRENTLY ON events_globex (created_at DESC);
CREATE INDEX CONCURRENTLY ON events_initech(created_at DESC);
CREATE INDEX CONCURRENTLY ON events_default(created_at DESC);

ORM note: SQLAlchemy’s postgresql_partition_by argument is informational metadata only — it does not emit the child partition DDL. Run the child CREATE TABLE statements via Alembic op.execute() or a manual migration. Reflect the child tables as separate mapped classes if you need direct ORM access to individual partitions.

Step 2 — Verify Partition Pruning

The planner prunes list partitions when the WHERE clause contains an equality predicate or an IN list on the partition column with no implicit casting. Confirm pruning with EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT event_id, event_type, created_at
FROM tenant_events
WHERE tenant_id = 'acme'
  AND created_at > NOW() - INTERVAL '30 days';

Expected output fragment:

->  Bitmap Heap Scan on events_acme  (cost=...)
      Filter: (created_at > ...)

If the plan shows an Append node scanning all children, check for:

• Implicit type mismatch: the literal 'acme' must match the column’s declared type exactly. A tenant_id CHAR(32) column will not prune against a VARCHAR literal in older planner versions.
• Function wrapping: WHERE lower(tenant_id) = 'acme' defeats pruning because the planner cannot infer which partition owns lower(x) = 'acme' without evaluating every partition.
• enable_partition_pruning: confirm SHOW enable_partition_pruning; returns on.

Step 3 — Automate Partition Creation for New Values

Detect unmapped values before application writes land in the DEFAULT partition. Query pg_inherits to enumerate existing child partitions and compare against your authoritative tenant registry:

-- List values currently covered by named (non-DEFAULT) partitions
SELECT pg_get_expr(relpartbound, oid) AS bound
FROM pg_class
WHERE oid IN (
  SELECT inhrelid FROM pg_inherits
  WHERE inhparent = 'tenant_events'::regclass
)
AND relpartbound IS NOT NULL;

A Python provisioning script that runs before each deployment:

import psycopg2, re

KNOWN_TENANTS_QUERY = "SELECT tenant_slug FROM tenants WHERE active = true"
PARTITIONS_QUERY = """
    SELECT pg_get_expr(relpartbound, oid)
    FROM pg_class
    WHERE oid IN (SELECT inhrelid FROM pg_inherits
                  WHERE inhparent = 'tenant_events'::regclass)
      AND relpartbound IS NOT NULL
      AND relkind = 'r'
"""

def provision_missing_partitions(conn_str: str) -> list[str]:
    created = []
    with psycopg2.connect(conn_str) as conn, conn.cursor() as cur:
        cur.execute(KNOWN_TENANTS_QUERY)
        tenants = {row[0] for row in cur.fetchall()}

        cur.execute(PARTITIONS_QUERY)
        existing_bounds = " ".join(row[0] or "" for row in cur.fetchall())
        covered = set(re.findall(r"'([^']+)'", existing_bounds))

        for tenant in tenants - covered:
            safe = re.sub(r"[^a-z0-9_]", "_", tenant.lower())
            ddl = (
                f"CREATE TABLE IF NOT EXISTS events_{safe} "
                f"PARTITION OF tenant_events FOR VALUES IN ('{tenant}')"
            )
            cur.execute(ddl)
            cur.execute(f"CREATE INDEX CONCURRENTLY ON events_{safe} (created_at DESC)")
            cur.execute(f"ANALYZE events_{safe}")
            created.append(tenant)
        conn.commit()
    return created

Migration steps for backfilling existing data from the DEFAULT partition:

1. Create the new partition DDL (above).
2. Move rows in batches to avoid long-running transactions: INSERT INTO events_newco SELECT * FROM events_default WHERE tenant_id = 'newco' LIMIT 10000; — repeat until empty.
3. Delete migrated rows from DEFAULT: DELETE FROM events_default WHERE tenant_id = 'newco';
4. Run ANALYZE events_newco to refresh planner statistics before enabling traffic.

Step 4 — Detect and Mitigate Hot Partitions

High-volume tenants or event types create write bottlenecks. Identify skewed partitions through pg_stat_user_tables:

SELECT
  relname                                         AS partition_name,
  pg_size_pretty(pg_total_relation_size(oid))     AS total_size,
  n_live_tup                                      AS live_rows,
  n_dead_tup                                      AS dead_rows,
  last_autovacuum::date                           AS last_vacuum
FROM pg_stat_user_tables
WHERE relname LIKE 'events_%'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

When a single partition holds more than 3–5× the average size of its siblings, consider sub-partitioning by a secondary key. For a high-volume acme tenant, partition by month under the existing tenant partition:

-- Convert events_acme to a sub-partitioned table (requires recreation)
CREATE TABLE events_acme_2025_01
  PARTITION OF events_acme
  FOR VALUES IN ('acme')   -- inherit parent's list constraint
  -- then sub-partition by range:
;
-- Practical approach: create a new table partitioned by range and attach
CREATE TABLE events_acme (
  LIKE tenant_events INCLUDING ALL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_acme_2025_01 PARTITION OF events_acme
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

See Handling Hot Keys in List Partitioned Tables for detailed I/O splitting workflows including connection-pool sharding and write fan-out strategies.

Managing the Value Set Over Time

List partitioning binds the schema to a set of values, and that set is owned by the business rather than by engineering. Regions get added, product lines get renamed, tenants get merged — and each of those is a DDL change on a live table. The systems that stay healthy are the ones that decided in advance how the value set is allowed to change.

Adding a value. With a DEFAULT partition present, a new value needs no urgent action: rows land in DEFAULT and keep flowing. Creating the real partition afterwards is where the cost hides — PostgreSQL must scan DEFAULT to prove no row belongs to the new partition, taking an ACCESS EXCLUSIVE lock for the duration. On an empty DEFAULT that is instant; on one holding a month of unnoticed rows it is a table rewrite during business hours. This is the entire argument for alerting on DEFAULT row count rather than checking it during incident review.

Renaming a value. Never update the partition key in place. An UPDATE that changes a row’s list value is a delete-and-insert across partitions, holding both, and on a large table it produces bloat on the source and a write storm on the target. Prefer a mapping table from external name to stable internal code, and rename in the mapping — the partition key then never changes because it was never the human-facing string.

Retiring a value. Detach the partition rather than deleting rows, keep it for the retention window, then drop it. Detach is a catalog operation; the delete is hours of WAL and vacuum work that competes with live traffic for no benefit.

Merging two values. This is the expensive one, because rows must physically move between partitions. Treat it as a small migration with the same shape as any other: create the target, copy in throttled batches, verify with counts and checksums, then swap the mapping entry and drop the source. The mapping table is what makes the swap atomic — without it, the merge requires a deploy.

Guardrails worth adding

Two checks catch nearly every list-partitioning surprise before it becomes an incident. The first counts rows in DEFAULT and alerts above a small threshold. The second compares the set of partition values against the authoritative list in the mapping table and alerts on any value present in one and not the other:

-- values that exist in the directory but have no partition yet
SELECT d.region_code
FROM   region_directory d
LEFT   JOIN pg_partitioned_value pv ON pv.value = d.region_code
WHERE  pv.value IS NULL;

Both are cheap enough to run every few minutes, and between them they turn the two failure modes of list partitioning — an unrecognised value and a drifted value set — into alerts rather than archaeology.

Configuration Reference

Operational Contrast

List partitioning guarantees exact-match locality: a row for tenant acme will always land in events_acme, making compliance-scoped data deletion as simple as DROP TABLE. Range partitioning strategies are better suited when queries filter on continuous values like timestamps, because the planner can prune a single partition covering a date range rather than requiring an equality match. Hash routing algorithms outperform both for write-heavy workloads across many anonymous keys (such as user UUIDs) where even distribution matters more than value-based locality.

List partitioning fits multi-tenant isolation, event type segregation, geographic region boundaries, and compliance domains (GDPR per-region data). It breaks down when the value set grows beyond a few hundred distinct values dynamically or when values are not known at schema design time — at that scale, application-level sharding with a proxy routing architecture is a stronger fit.

Failure Modes

Common Mistakes

• Omitting the DEFAULT partition. Any INSERT for an unmapped value will fail with a runtime error. Make the DEFAULT partition mandatory in your DDL templates; treat its growth as a monitoring signal, not a safety valve.
• Using OR across partition values instead of IN. WHERE tenant_id = 'acme' OR tenant_id = 'globex' may not prune as reliably as WHERE tenant_id IN ('acme', 'globex'). Use IN consistently for multi-value predicates.
• Hardcoding partition names in application routing logic. Embedding names like events_acme in application code creates a tight coupling that silently breaks when partitions are renamed, merged, or re-partitioned. Let the database planner route through the parent table.
• Creating global indexes on the parent table. PostgreSQL does not support global (non-partitioned) indexes on declaratively partitioned tables. Every index must be created per child partition. Missing indexes on new partitions after automated provisioning are a common post-deploy performance regression.

FAQ

Related

• Partitioning Implementation Patterns & Routing — parent overview covering all implementation strategies
• Range Partitioning Strategies — continuous boundary partitioning for time-series and sequential workloads
• Hash Routing Algorithms — uniform distribution strategies using modulo and consistent hashing
• Handling Hot Keys in List Partitioned Tables — I/O splitting and sub-partitioning for high-volume categories
• List Partitioning for Multi-Tenant SaaS Schemas — tiered dedicated-plus-pooled tenant partition layouts with mapping-driven routing
• Composite List-Range Partitioning for Regional Time-Series Data — two-level region/month layouts for data residency and per-region retention
• Automated Partition Creation Workflows — event-driven DDL pipelines for zero-downtime partition provisioning