List partitioning by category/tenant: the multi-tenant SaaS case

Capsule description

Range covers 80% of cases (time-series). But there's a very frequent case in modern backend dev where range doesn't apply: multi-tenant SaaS. When almost all your queries have WHERE tenant_id = ? (because RLS forces it), partitioning by tenant_id with LIST gives you exactly what you need: each tenant in its own physical partition, per-tenant queries scanning only their partition, and dropping a tenant who cancels their subscription becomes an instant DROP PARTITION.

This capsule teaches you the PARTITION BY LIST syntax, the "one partition per tenant or grouped by tier?" decision, and the critical combination with Row-Level Security (RLS) from guide #13. You'll walk away with the missing piece to solve the ticket "queries from the big tenants are slow and we need data isolation for compliance".

By the end, you'll be able to take an existing multi-tenant table and propose the right partitioning strategy: per individual tenant if you have <50 stable tenants, grouped by tier (big vs small) if you have hundreds with uneven distribution, or LIST by a discrete category unrelated to tenants (region, status, country) when that's the case.

Mental model: list partitioning is a map of "this value → this partition"

If range is "this contiguous range goes to this partition", list is "these discrete values go to this partition". The partition key is a column with known values (a finite set of tenants, regions, statuses, countries), and you explicitly enumerate which values go to each child.

┌──────────────────────────────────────────────────────────────┐
│             INSERT INTO tenant_data (...)                    │
│             VALUES (..., tenant_id=42, ...)                  │
│                                                              │
│                            │                                 │
│                            ▼                                 │
│              ┌─────────────────────────┐                     │
│              │ tenant_data (parent)    │                     │
│              │ PARTITION BY LIST       │                     │
│              │   (tenant_id)           │                     │
│              └────────────┬────────────┘                     │
│                           │                                  │
│      ┌────────────────────┼────────────────────┐             │
│      ▼                    ▼                    ▼             │
│ ┌───────────┐      ┌───────────┐         ┌───────────┐       │
│ │tenant_42  │      │tenant_43  │   ...   │tenants_   │       │
│ │FOR VALUES │      │FOR VALUES │         │small      │       │
│ │IN (42)    │      │IN (43)    │         │FOR VALUES │       │
│ │           │      │           │         │IN (1, 2,  │       │
│ │(50M rows) │      │(45M rows) │         │ 3, ..., 99)│      │
│ └───────────┘      └───────────┘         └───────────┘       │
│                                                              │
│ Query: WHERE tenant_id = 42                                  │
│   → planner identifies: only tenant_42                       │
│   → scans only that partition                                │
└──────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. Each partition lists explicit values. There's no "range". FOR VALUES IN (1, 2, 3) covers exactly those three values. If you insert with tenant_id = 4 and no partition contains it, it goes to the default (or fails if there's no default).

2. A single partition can group several values. You're not forced into one partition per value. FOR VALUES IN (1, 2, 3, ..., 99) groups 99 tenants into a single partition. Useful when you have many small tenants.

3. The values must be known when you define the partition. Unlike range, where "next month" is predictable, list requires you to know the values. If a new tenant arrives, you have to create the partition beforehand (or have a default partition + a "promote from default" script).

This explains why list wins in multi-tenant SaaS (tenant_id is a mandatory column, queries always include it, dropping a tenant is a common operation) and loses on dynamic datasets (categories that change, statuses that get added).

The complete SQL: tenant_data table partitioned by tenant

You're going to build the setup for a SaaS app with 4 main tenants. Assume PostgreSQL 16.

Step 1: parent table with PARTITION BY LIST

CREATE TABLE tenant_data (
    id          BIGSERIAL,
    tenant_id   BIGINT NOT NULL,
    external_id TEXT NOT NULL,
    payload     JSONB NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, tenant_id),
    UNIQUE (tenant_id, external_id)
) PARTITION BY LIST (tenant_id);

Note:

• PARTITION BY LIST (tenant_id) declares that the partition column is tenant_id with discrete values.
• PRIMARY KEY (id, tenant_id) includes the partition key (same requirement as in range).
• UNIQUE (tenant_id, external_id) works because tenant_id is included. It means "external_id unique per tenant" — the typical SaaS pattern where two different tenants can each have external_id = 'ORDER-001' with no conflict.

Step 2: partitions per individual tenant

CREATE TABLE tenant_data_t1 PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_data_t2 PARTITION OF tenant_data FOR VALUES IN (2);
CREATE TABLE tenant_data_t3 PARTITION OF tenant_data FOR VALUES IN (3);
CREATE TABLE tenant_data_t4 PARTITION OF tenant_data FOR VALUES IN (4);

Each partition is a tenant's "physical home". Rows with tenant_id = 1 go only to tenant_data_t1.

Step 3: default partition for new tenants

CREATE TABLE tenant_data_default PARTITION OF tenant_data DEFAULT;

When a new tenant arrives (signup registration), their first insert goes to the default partition (because their tenant_id isn't in any list). The correct process is:

1. Create the new tenant's specific partition before accepting inserts.
2. If for some reason data lands in the default, move it afterwards with a "promotion" script (see gotchas).

Step 4: indexes that propagate

-- Index on the JSONB payload (covered in module 1)
CREATE INDEX tenant_data_payload_gin_idx ON tenant_data USING GIN (payload);

-- Index on created_at for time-based per-tenant queries
CREATE INDEX tenant_data_created_at_idx ON tenant_data (created_at DESC);

Just like in range, indexes on the parent propagate to all existing and future partitions.

One partition per tenant vs grouping by tier

This is the most important design decision in multi-tenant list partitioning. There are two main patterns.

Pattern A: one partition per tenant (1:1)

When to apply:

• You have <50 stable tenants (new ones don't show up every week).
• Each tenant has significant volume (>1M rows) that justifies its own partition.
• The tenants are "VIP" — enterprise clients paying for physical isolation.

Advantages:

• Total physical isolation: one tenant's queries never touch another's data.
• Instant DROP PARTITION when a tenant cancels.
• Per-tenant backup/restore is simple (pg_dump of a single table).
• Independent VACUUM and ANALYZE — a write-heavy tenant doesn't affect the others.

Disadvantages:

• Onboarding a new tenant requires CREATE TABLE (operational DDL every time).
• Partition count grows with every tenant — more planner overhead if you reach the hundreds.

-- Example: 4 enterprise tenants
CREATE TABLE tenant_data_acme PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_data_globex PARTITION OF tenant_data FOR VALUES IN (2);
CREATE TABLE tenant_data_initech PARTITION OF tenant_data FOR VALUES IN (3);
CREATE TABLE tenant_data_umbrella PARTITION OF tenant_data FOR VALUES IN (4);

Pattern B: group by tier (N:1)

When to apply:

• You have hundreds or thousands of tenants.
• Very uneven volume distribution: 5 tenants hold 80% of the data, the other 95% share the remaining 20%.
• Constant onboarding (frequent signups).

Strategy: the big tenants get a dedicated partition; the small ones share a "small" partition (or several grouped by hash).

Advantages:

• Big tenants keep physical isolation and dedicated performance.
• New tenants don't require DDL — but only if you build the small tier the way I show you below. Watch out for this one, it's where almost everyone gets it wrong.
• Total partition count stays manageable.

Disadvantages:

• Promoting a small tenant that grows (from "small" to a dedicated partition) requires manual migration.
• Queries against the "small" partition scan data from several tenants (RLS still filters, but it's more work).

The trap: enumerating the small tenants does NOT give you DDL-free onboarding

The intuitive attempt is to list the small tenants' IDs and sub-partition by hash:

-- ⚠️ THIS DOES NOT DO WHAT IT LOOKS LIKE
CREATE TABLE tenant_data_small PARTITION OF tenant_data
    FOR VALUES IN (6, 7, 8, /* ... */ 500)
    PARTITION BY HASH (tenant_id);

The problem: the bounds of a LIST partition are a closed list. A new tenant with tenant_id = 501 is not in the list, so it doesn't go into tenant_data_small or its hash sub-partitions: it goes to the default. See it for yourself by inserting a tenant outside the list and checking where the row landed:

SELECT tableoid::regclass AS landed_in, tenant_id FROM tenant_data;

     landed_in       | tenant_id
---------------------+-----------
 tenant_data_small_h3|         7   ← listed tenant: goes into the hash
 tenant_data_default |       501   ← NEW tenant: falls to the default, not the hash

And you can't "add" a value to the list on the fly: there's no ALTER TABLE ... ADD VALUE. To get 501 in there you'd have to DETACH tenant_data_small, recreate it with the new bounds and ATTACH it back (moving the data). In other words: heavy DDL for every new tenant, exactly what you were trying to avoid.

The correct pattern: sub-partition the DEFAULT

The DEFAULT partition can itself be sub-partitioned by hash. Since the default is, by definition, "everything that didn't land in another partition", any new tenant gets in without touching the schema — and the hash spreads them evenly across the sub-partitions:

-- Big tenants: dedicated partition
CREATE TABLE tenant_data_acme PARTITION OF tenant_data FOR VALUES IN (1);
CREATE TABLE tenant_data_globex PARTITION OF tenant_data FOR VALUES IN (2);
CREATE TABLE tenant_data_initech PARTITION OF tenant_data FOR VALUES IN (3);
CREATE TABLE tenant_data_umbrella PARTITION OF tenant_data FOR VALUES IN (4);
CREATE TABLE tenant_data_stark PARTITION OF tenant_data FOR VALUES IN (5);

-- Everyone else (small tenants + the ones that don't exist yet): DEFAULT split by hash
CREATE TABLE tenant_data_rest PARTITION OF tenant_data
    DEFAULT PARTITION BY HASH (tenant_id);

CREATE TABLE tenant_data_rest_h0 PARTITION OF tenant_data_rest
    FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE tenant_data_rest_h1 PARTITION OF tenant_data_rest
    FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE tenant_data_rest_h2 PARTITION OF tenant_data_rest
    FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE tenant_data_rest_h3 PARTITION OF tenant_data_rest
    FOR VALUES WITH (modulus 4, remainder 3);

Now a new tenant (tenant_id = 9999, never mentioned in the DDL) gets in on its own, and pruning still finds it:

EXPLAIN (COSTS OFF) SELECT * FROM tenant_data WHERE tenant_id = 9999;

 Seq Scan on tenant_data_rest_h0 tenant_data
   Filter: (tenant_id = 9999)

A single sub-partition scanned, zero DDL at signup. That is the promise of pattern B, and it only holds with the sub-partitioned default.

This is sub-partitioning (nested LIST + HASH). Advanced but powerful. Hash partitioning is covered in capsule 05 — this is a preview of how they combine.

The critical combination: partitioning + RLS

This is the most important section of the capsule. The question "RLS or partitioning for multi-tenant?" is a false dichotomy. The correct answer is "both, they do different things":

Without RLS, application code has to always remember WHERE tenant_id = ? in every query — one slip = data leak. RLS enforces it at the DB level. But RLS without partitioning on a 200M-row table with 1000 tenants still scans (or uses a giant index over) the whole table.

Combined: RLS applies the filter, partition pruning scans only the corresponding partition. Security guarantee and performance guarantee.

Complete setup: partitioning + RLS

Assume you already have tenant_data partitioned as shown above. You add RLS on top:

-- Enable RLS on the parent table (it applies to the children)
ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;

-- Helper function that the app sets per session
-- (you learned this in guide #13, module 5)
CREATE OR REPLACE FUNCTION current_tenant_id()
RETURNS BIGINT AS $$
    SELECT NULLIF(current_setting('app.current_tenant_id', true), '')::BIGINT;
$$ LANGUAGE SQL STABLE;

-- Policy: every query only sees rows from the current tenant
CREATE POLICY tenant_isolation ON tenant_data
    FOR ALL
    USING (tenant_id = current_tenant_id());

The NULLIF isn't decorative — without it, the app crashes in production. The second argument true of current_setting (missing_ok) only avoids the error when the GUC was never defined in that session. But as soon as you set it once with SET LOCAL, closing the transaction doesn't return the value to "nonexistent": it returns it to the empty string. And ''::BIGINT blows up:

ERROR:  invalid input syntax for type bigint: ""

With a connection pool — that is, with any real app — that connection gets recycled and handed to the next request. The first query touching the table outside a transaction explodes. NULLIF(..., '') turns the empty string into NULL, the policy matches no rows, and the result is zero rows instead of an exception: it fails closed, which is exactly what you want from a security mechanism.

How the app uses it from SQLAlchemy 2.0 async

# middleware/tenant_context.py
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

async def set_tenant_context(session: AsyncSession, tenant_id: int) -> None:
    """Sets the tenant for this session. Call at the start of every request."""
    await session.execute(
        text("SET LOCAL app.current_tenant_id = :tid"),
        {"tid": tenant_id},
    )

# In your FastAPI middleware/dependency:
async def get_tenant_session(
    tenant_id: int,  # typically comes from the JWT or the subdomain
    session: AsyncSession = Depends(get_session),
) -> AsyncSession:
    await set_tenant_context(session, tenant_id)
    return session

Querying from the ORM

# services/tenant_data_service.py
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

async def list_recent_data(session: AsyncSession) -> list[TenantData]:
    """Lists recent data. RLS filters by tenant; partition pruning touches only
    the tenant's partition. Without passing tenant_id explicitly."""
    stmt = (
        select(TenantData)
        .order_by(TenantData.created_at.desc())
        .limit(100)
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

What happens internally:

1. SQLAlchemy generates: SELECT * FROM tenant_data ORDER BY created_at DESC LIMIT 100.
2. PostgreSQL applies RLS: the effective query is SELECT * FROM tenant_data WHERE tenant_id = 42 ORDER BY created_at DESC LIMIT 100 (assuming tenant_id=42 is set).
3. Partition pruning: the planner sees tenant_id = 42, identifies that only tenant_data_t42 applies, scans only that partition.

Result: a fast query (touches 1 of N partitions) and a safe one (impossible to read another tenant's data even if the app has a bug).

Verifying with EXPLAIN

SET LOCAL only exists inside a transaction. If you run it loose, PostgreSQL warns you and the value doesn't get set:

WARNING:  SET LOCAL can only be used in transaction blocks

…and the next query either fails or sees nothing. So wrap everything in an explicit block:

BEGIN;
SET LOCAL app.current_tenant_id = '1';

EXPLAIN ANALYZE
SELECT * FROM tenant_data ORDER BY created_at DESC LIMIT 100;

ROLLBACK;

Real plan (PostgreSQL 17, with the partitions and indexes from above):

Limit (actual rows=100 loops=1)
  ->  Merge Append (actual rows=100 loops=1)
        Sort Key: tenant_data.created_at DESC
        Subplans Removed: 2
        ->  Index Scan using tenant_data_t1_created_at_idx on tenant_data_t1 tenant_data_1
              Filter: (tenant_id = (current_setting('app.current_tenant_id'::text, true))::bigint)

There are three things to read in that plan, and none of them are obvious:

1. Subplans Removed: 2 — that's what pruning looks like here. Notice it's runtime pruning, not plan-time pruning: current_tenant_id() is STABLE, not IMMUTABLE, so the planner does not know the tenant_id when it builds the plan. It prepares all three partitions and discards two at execution time. That's the difference from a literal WHERE tenant_id = 1, where the planner prunes earlier and the other partitions don't even appear in the plan.

2. Of the three partitions, only tenant_data_t1 gets scanned. Which is exactly what we were after: tenant 1's partition.

3. current_tenant_id() doesn't appear by name — PostgreSQL inlines the SQL function and the filter shows current_setting(...) directly. Don't panic: it's the same function, expanded.

And an Index Scan shows up, not a Seq Scan, because the tenant_data_created_at_idx index you created on the parent propagated to every partition and serves the ORDER BY created_at DESC without sorting anything.

Another list case: discrete statuses unrelated to tenants

List partitioning isn't just for tenants. Any column with known discrete values can be used. Typical cases:

Case: jobs table partitioned by status

A background-jobs table with four states: pending, running, completed, failed. Volume: very uneven. pending and running are small (hundreds of rows, high churn). completed and failed are huge (millions of rows, almost never queried).

CREATE TABLE jobs (
    id          BIGSERIAL,
    job_type    TEXT NOT NULL,
    status      TEXT NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, status)
) PARTITION BY LIST (status);

-- Active partitions (small, write-heavy)
CREATE TABLE jobs_pending PARTITION OF jobs FOR VALUES IN ('pending');
CREATE TABLE jobs_running PARTITION OF jobs FOR VALUES IN ('running');

-- Archive partitions (large, almost read-only)
CREATE TABLE jobs_completed PARTITION OF jobs FOR VALUES IN ('completed');
CREATE TABLE jobs_failed PARTITION OF jobs FOR VALUES IN ('failed');

Why list applies: the dominant queries are WHERE status = 'pending' ORDER BY created_at LIMIT 10 (a worker looking for the next job). Pruning means you scan only jobs_pending (hundreds of rows) instead of the whole table.

Bonus: when a job moves from pending to running, PostgreSQL physically moves the row between partitions (because the partition key changed). This has been atomic since PG 11+, but it generates I/O. For tables where inserts change partition key frequently, consider whether list is the right choice versus another design.

Case: sales table partitioned by region

A global app with 4 regions (AMERICAS, EMEA, APAC, GLOBAL). Queries almost always have WHERE region = ? (because each region has its own dashboard).

CREATE TABLE sales (
    id         BIGSERIAL,
    region     TEXT NOT NULL,
    customer_id BIGINT NOT NULL,
    amount     NUMERIC(10, 2) NOT NULL,
    sold_at    TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE sales_americas PARTITION OF sales FOR VALUES IN ('AMERICAS');
CREATE TABLE sales_emea PARTITION OF sales FOR VALUES IN ('EMEA');
CREATE TABLE sales_apac PARTITION OF sales FOR VALUES IN ('APAC');
CREATE TABLE sales_global PARTITION OF sales FOR VALUES IN ('GLOBAL');

Operational bonus: if a region needs special compliance (e.g. EMEA with GDPR), you can move sales_emea to a separate tablespace on a specific disk, or restrict backups by region without touching the other partitions.

Why does this matter in real work?

1. Multi-tenant SaaS is most of modern B2B backend. Any app serving multiple organizations (CRM, project management, invoicing, analytics, monitoring) faces the question "how do I isolate and scale per tenant?". List partitioning + RLS is the most-used combination in production for that case. Knowing how to apply it is necessary, not optional.

2. The "RLS or partitioning" conversation happens in every SaaS team. You will be in that meeting. Your role is to explain that they're not alternatives and show how they combine. This capsule gives you the vocabulary and the examples to defend that position with authority.

3. Tenant onboarding and offboarding is a daily operation. When a client signs, you need to create their partition. When they cancel, you need to drop it cleanly (compliance, costs). If your schema isn't partitioned by tenant, that cycle is painful: onboarding requires no DDL but offboarding is a massive blocking DELETE. Partitioned, both are fast declarative operations.

4. The technique generalizes beyond tenants. Once you internalize list partitioning, you apply it to jobs.status, users.country, events.event_category, any discrete column. It's the tool for discrete columns, not just tenant_id.

5. Compliance frequently demands physical isolation, not just logical. Some enterprise contracts ask for "my company's data is separated from other companies' data". RLS alone is logical separation. Partitioning + RLS is physical + logical separation — and you can show it in an audit with \d+ tenant_data displaying the partitions.

Traps and common mistakes

Mistake 1 (conceptual): assuming RLS replaces partitioning (or vice versa)

Symptom: a dev on the team says "we already have RLS, we don't need partitioning". Another says "we already partition by tenant_id, we don't need RLS". Both are wrong.

Why it happens: they confuse the problems each one solves. RLS solves security (impossibility of a leak from an app-code bug). Partitioning solves performance (queries only scan their partition). They're different problems with different solutions that coexist.

How to tell them apart:

• If your fear is "what happens if a dev forgets WHERE tenant_id = ?" → you need RLS.
• If your fear is "queries from big tenants are slowing down" → you need partitioning.
• If both worry you (which is normal in serious SaaS) → you need both together.

How to fix it: in the design doc, explicitly separate "security strategy" (RLS) from "scale strategy" (partitioning). Document that they're orthogonal.

Mistake 2 (practical): partition count explodes with new tenants

Symptom: you started with 10 tenants and 10 partitions. A year later you have 800 tenants and 800 partitions. Queries start showing high planning time. EXPLAIN takes longer to run than the query itself.

Why it happens: every query, even with pruning, requires the planner to consider all partitions to decide which to discard. With hundreds of partitions, planning cost grows. PostgreSQL handles up to ~100-200 partitions well; beyond that, there's noticeable degradation.

How to prevent it: pattern B (group by tier). Keep 5-10 dedicated partitions for big tenants and group the small ones into 4-8 hash partitions. The total stays at 15-20, manageable.

Migrating to tier: if you already have 800 individual partitions and need to consolidate, it's a zero-downtime migration (technique from #13 + capsule 08).

Mistake 3 (conceptual): an UPDATE that changes the partition key moves the row

Symptom: in the jobs table partitioned by status, you run UPDATE jobs SET status = 'running' WHERE id = 42. You're surprised it's slower than expected.

Why it happens: when the UPDATE changes the partition key's value, PostgreSQL moves the row from one partition to another. Internally it's a DELETE in the old partition + an INSERT in the new one. It generates extra I/O and, in high-concurrency scenarios, can cause contention.

How to tell them apart:

-- This does NOT move the row (doesn't change the partition key)
UPDATE jobs SET payload = '{...}' WHERE id = 42;

-- This DOES move the row (changes status)
UPDATE jobs SET status = 'running' WHERE id = 42 AND status = 'pending';

How to prevent it: if your workload has many partition-key changes (frequent status transitions), evaluate whether list by status is the right choice. Alternatives:

• Partition by created_at (range) and keep status as an indexed column.
• A separate non-partitioned jobs_active table (small) + jobs_archive partitioned by date.
• Accept the cost if the pruning benefit outweighs it.

Mistake 4 (practical): a new tenant lands in the default and you don't notice

Symptom: a client signs. Their signup goes to tenant_data_default because nobody created the specific partition. Days pass. Later it's discovered that their data is in the default mixed with other edge cases. Migrating is manual work.

Why it happens: you didn't integrate "create partition" into the onboarding flow. The partition doesn't create itself.

How to prevent it: automate partition creation in the signup endpoint:

# services/tenant_service.py
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

async def onboard_tenant(session: AsyncSession, tenant_id: int, name: str) -> None:
    # 1. Create the tenant's row
    await session.execute(
        text("INSERT INTO tenants (id, name) VALUES (:tid, :name)"),
        {"tid": tenant_id, "name": name},
    )

    # 2. Create the partition BEFORE accepting inserts
    partition_name = f"tenant_data_t{tenant_id}"
    await session.execute(
        text(f"""
            CREATE TABLE {partition_name}
            PARTITION OF tenant_data
            FOR VALUES IN ({tenant_id})
        """)
    )

    await session.commit()

Important — and this one works in your favor: in PostgreSQL, DDL is transactional. The CREATE TABLE ... PARTITION OF lives inside the same transaction as the INSERT, so if something fails, both get rolled back: you're left with neither a half-created tenant nor an orphaned partition. Check it:

BEGIN;
CREATE TABLE tenant_data_t77 PARTITION OF tenant_data FOR VALUES IN (77);
ROLLBACK;

SELECT count(*) FROM pg_class WHERE relname = 'tenant_data_t77';
--  count
-- -------
--      0        ← the partition vanished with the ROLLBACK

This is not true in MySQL or Oracle (where DDL does an implicit commit). If you're coming from those, it's one of those things PostgreSQL gets right and that you can lean on without fear: the onboard_tenant function above is genuinely atomic.

The only real precaution is the lock: CREATE TABLE ... PARTITION OF takes ACCESS EXCLUSIVE on the parent table. While that transaction is open, every query against tenant_data waits. Keep the onboarding transaction short — don't put HTTP calls, email sends, or anything slow inside it.

Better long-term option: a "rows in default partition" monitor + alert. If you have rows there, something in onboarding failed.

Mistake 5 (conceptual): list partitioning for high-cardinality columns

Symptom: you try to partition by user_id with LIST because you have 500k users. PostgreSQL doesn't complain when creating the partitions, but the system becomes unmanageable.

Why it happens: list partitioning is designed for columns with discrete and known values in a manageable quantity (dozens, at most low hundreds). 500k partitions is absurd: astronomical planning time, unmanageable autovacuum, schema migrations taking days.

How to tell: if your candidate column's cardinality is >100 unique values, list is probably not the tool. Consider:

• Hash partitioning (capsule 05) if you want to distribute uniformly.
• Range if the column has a natural order (e.g. user_id by ID ranges).
• Tier-based list (a few dedicated partitions + grouped by hash inside "small").

Exercises

Exercise 1: design a multi-tenant schema for B2B SaaS

Your SaaS app has a documents table with this profile:

• 5 enterprise tenants (each >10M documents).
• 200 medium tenants (each between 100k and 1M documents).
• 1500 small tenants (each <100k documents).
• Growth: 50 new tenants/month.
• Queries: always WHERE tenant_id = ? (RLS in production).
• Compliance: enterprise tenants demand explicit physical isolation.

Design the schema (complete DDL) with the right partitioning strategy.

-- Parent table
CREATE TABLE documents (
    id          BIGSERIAL,
    tenant_id   BIGINT NOT NULL,
    title       TEXT NOT NULL,
    content     TEXT,
    metadata    JSONB DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, tenant_id),
    UNIQUE (tenant_id, title)  -- Title unique per tenant
) PARTITION BY LIST (tenant_id);

-- Dedicated partitions for the 5 enterprise tenants (compliance + scale)
CREATE TABLE documents_t1 PARTITION OF documents FOR VALUES IN (1);
CREATE TABLE documents_t2 PARTITION OF documents FOR VALUES IN (2);
CREATE TABLE documents_t3 PARTITION OF documents FOR VALUES IN (3);
CREATE TABLE documents_t4 PARTITION OF documents FOR VALUES IN (4);
CREATE TABLE documents_t5 PARTITION OF documents FOR VALUES IN (5);

-- Medium tenants: group into 4 partitions by hash of tenant_id
-- (closed list: these are 200 known, stable IDs)
CREATE TABLE documents_medium PARTITION OF documents
    FOR VALUES IN (6, 7, 8, /* ...the 200 medium IDs... */ 205)
    PARTITION BY HASH (tenant_id);

CREATE TABLE documents_medium_h0 PARTITION OF documents_medium
    FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE documents_medium_h1 PARTITION OF documents_medium
    FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE documents_medium_h2 PARTITION OF documents_medium
    FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE documents_medium_h3 PARTITION OF documents_medium
    FOR VALUES WITH (modulus 4, remainder 3);

-- Small tenants AND all the ones that don't exist yet: the DEFAULT, hash-subpartitioned.
-- It's DEFAULT (not a list of IDs) precisely to absorb the 50 signups/month with no DDL.
CREATE TABLE documents_rest PARTITION OF documents
    DEFAULT PARTITION BY HASH (tenant_id);

CREATE TABLE documents_rest_h0 PARTITION OF documents_rest
    FOR VALUES WITH (modulus 8, remainder 0);
CREATE TABLE documents_rest_h1 PARTITION OF documents_rest
    FOR VALUES WITH (modulus 8, remainder 1);
-- ... h2 through h7

-- Indexes that propagate
CREATE INDEX documents_created_at_idx ON documents (created_at DESC);
CREATE INDEX documents_metadata_gin_idx ON documents USING GIN (metadata);

-- RLS for security
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON documents
    FOR ALL
    USING (tenant_id = current_tenant_id());

Exercise 2: detect the "new tenant in default partition" bug

Your monitoring alerts: tenant_data_default has 5,234 rows. Investigate: what could have happened and how do you fix it without losing data?

-- See which tenant_ids are in the default
SELECT tenant_id,
       count(*)        AS row_count,
       min(created_at) AS first_seen,
       max(created_at) AS last_seen
FROM tenant_data_default
GROUP BY tenant_id
ORDER BY row_count DESC;

 tenant_id | row_count |       first_seen       |       last_seen
-----------+-----------+------------------------+------------------------
        47 |      4892 | 2026-04-28 14:24:24+00 | 2026-05-02 16:13:36+00
        48 |       342 | 2026-05-01 10:45:54+00 | 2026-05-02 08:55:48+00
(2 rows)

CREATE TABLE tenant_data_t47 PARTITION OF tenant_data FOR VALUES IN (47);

ERROR:  updated partition constraint for default partition "tenant_data_default"
        would be violated by some row

BEGIN;

-- Step 1: pull the conflicting rows out of the default and stash them
CREATE TEMP TABLE rows_to_move ON COMMIT DROP AS
    SELECT * FROM tenant_data_default WHERE tenant_id IN (47, 48);

DELETE FROM tenant_data_default WHERE tenant_id IN (47, 48);

-- Step 2: now the default is clean and the partitions can be created
CREATE TABLE tenant_data_t47 PARTITION OF tenant_data FOR VALUES IN (47);
CREATE TABLE tenant_data_t48 PARTITION OF tenant_data FOR VALUES IN (48);

-- Step 3: reinsert through the parent — now they route to the right partitions
INSERT INTO tenant_data SELECT * FROM rows_to_move;

COMMIT;

SELECT tableoid::regclass AS partition, count(*) AS row_count
FROM tenant_data WHERE tenant_id IN (47, 48) GROUP BY 1 ORDER BY 1;

    partition    | row_count
-----------------+-----------
 tenant_data_t47 |      4892
 tenant_data_t48 |       342
(2 rows)

Exercise 3: combine RLS with partitioning in SQLAlchemy

Write the complete code (model + middleware + service) for a FastAPI app with SQLAlchemy 2.0 async where:

• The documents table is partitioned by tenant_id.
• RLS is active, with a policy based on current_tenant_id().
• The tenant comes from a JWT in the header.
• The service lists the current tenant's documents.

# models/document.py
from datetime import datetime
from sqlalchemy import BigInteger, String, Text
from sqlalchemy.dialects.postgresql import JSONB, TIMESTAMP
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class Document(Base):
    __tablename__ = "documents"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    tenant_id: Mapped[int] = mapped_column(BigInteger, primary_key=True, nullable=False)
    title: Mapped[str] = mapped_column(String, nullable=False)
    content: Mapped[str | None] = mapped_column(Text)
    # CAREFUL: the attribute CANNOT be named `metadata` — it's a name reserved by
    # SQLAlchemy's Declarative API. We name it differently in Python and map it
    # explicitly to the table's "metadata" column.
    doc_metadata: Mapped[dict] = mapped_column("metadata", JSONB, default=dict)
    created_at: Mapped[datetime] = mapped_column(
        TIMESTAMP(timezone=True), nullable=False
    )

# middleware/tenant.py
from fastapi import Depends, Header, HTTPException
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.core.db import get_session
from app.core.auth import decode_jwt

async def get_tenant_session(
    authorization: str = Header(...),
    session: AsyncSession = Depends(get_session),
) -> AsyncSession:
    """Decodes the JWT, extracts tenant_id, sets the RLS context."""
    try:
        token = authorization.replace("Bearer ", "")
        payload = decode_jwt(token)
        tenant_id = int(payload["tenant_id"])
    except Exception:
        raise HTTPException(status_code=401, detail="Invalid token")

    # Set the tenant in the PostgreSQL session so RLS can use it
    await session.execute(
        text("SET LOCAL app.current_tenant_id = :tid"),
        {"tid": tenant_id},
    )
    return session

# services/document_service.py
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.document import Document

async def list_documents(
    session: AsyncSession,
    limit: int = 50,
    offset: int = 0,
) -> list[Document]:
    """Lists the current tenant's documents.

    We don't pass tenant_id explicitly: RLS applies it automatically from
    the session context, and partition pruning touches only the partition
    corresponding to the tenant.
    """
    stmt = (
        select(Document)
        .order_by(Document.created_at.desc())
        .limit(limit)
        .offset(offset)
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

# routers/documents.py
from fastapi import APIRouter, Depends
from sqlalchemy.ext.asyncio import AsyncSession

from app.middleware.tenant import get_tenant_session
from app.services.document_service import list_documents

router = APIRouter(prefix="/documents", tags=["documents"])

@router.get("")
async def get_documents(
    session: AsyncSession = Depends(get_tenant_session),
):
    docs = await list_documents(session)
    return {"items": [{"id": d.id, "title": d.title} for d in docs]}

InvalidRequestError: Attribute name 'metadata' is reserved when using the Declarative API.

Exercise 4: identify a case where list is NOT the tool

Your team proposes partitioning the users table (10M rows) by country_code with LIST (200 countries). Is it a good idea? Argue.

Exercise 5: drop a tenant that cancels

A tenant cancels their subscription and compliance requires deleting all their data. The documents table is partitioned by tenant_id (1:1 for enterprise). How do you do it correctly?

-- Step 1: confirm the partition exists and holds the tenant's data
SELECT count(*) FROM documents_t42;
-- 12,453,201 rows

-- Step 2: drop the partition (instant, frees disk)
DROP TABLE documents_t42;

-- Step 3: clean up references in other tables if applicable
DELETE FROM tenants WHERE id = 42;
-- (assuming tenants is a normal, non-partitioned table)

-- Without partitioning, this would be:
DELETE FROM documents WHERE tenant_id = 42;
-- 40 minutes, SHARE UPDATE lock, massive bloat, requires VACUUM FULL afterwards.

-- Detach (still a table, but outside the parent)
ALTER TABLE documents DETACH PARTITION documents_t42;

-- Back up to a file
-- pg_dump --table=documents_t42 my_db > tenant_42_archive.sql

-- Drop after confirming the backup
DROP TABLE documents_t42;

Summary and next step

In this capsule you completed the multi-tenant SaaS repertoire with list partitioning:

• PARTITION BY LIST (column) for columns with known discrete values (tenant_id, status, region).
• Pattern A (1:1) vs Pattern B (tier-based): one partition per tenant if you have <50 stable tenants; tier-based with nested hash if you have hundreds with uneven distribution.
• In tier-based, the catch-all goes as DEFAULT ... PARTITION BY HASH, not as a list of IDs. A list is closed: the new tenant falls to the default anyway, and "adding it to the list" doesn't exist (it's DETACH + recreate + ATTACH). Only the sub-partitioned default gives you genuinely DDL-free onboarding.
• The critical RLS + partitioning combination for serious multi-tenant SaaS: RLS for security isolation, partitioning for performance scale. They are not alternatives.
• Details you only learn by breaking them: SET LOCAL only works inside a transaction; current_setting(...) returns an empty string (not NULL) on a recycled connection, so the policy needs NULLIF; and you can't create a partition while the DEFAULT holds rows that would belong to it.
• When list is NOT the tool: very high cardinality (>100 values), dominant queries don't use the column, frequent UPDATEs that change the partition key.
• Other list use cases: jobs.status, sales.region, any discrete column where queries always filter by that value.

Before moving on, you should be able to:

• Decide between the 1:1 pattern and tier-based depending on tenant count and distribution.
• Write the complete DDL for a table partitioned by tenant_id with a default partition.
• Combine list partitioning with RLS and verify both work in production.
• Identify when list partitioning is NOT the right choice (high cardinality, columns with frequent UPDATEs).

Next capsule — Hash partitioning for uniform distribution. Range covers time-series. List covers discrete categories. But what do you do when there's no natural criterion and you just need to spread rows evenly across N partitions (typically to parallelize I/O or reduce contention)? Capsule 05 teaches you hash partitioning, the rarest use case of the three but the one that shows up when nothing else applies. You'll also see how the three types combine in nested partitioning (a preview of which you already saw with tier-based).

Resources

1. PostgreSQL 16 — Partition by List — the official reference for list partitioning.
2. PostgreSQL 16 — Row Security Policies — RLS reference. Reread if you need a refresher from guide #13.
3. Citus Data — Schema-Based vs Row-Based Multi-Tenancy — a comparison of multi-tenant strategies. This capsule is row-based; Citus extends to schema-based for extreme cases.
4. Crunchy Data — Multi-Tenant Patterns in Postgres — patterns combining RLS and partitioning.
5. AWS Database Blog — Sharding multi-tenant SaaS apps with Aurora PostgreSQL — a cloud perspective with enterprise cases.
6. Supabase docs — Row Level Security — a practical RLS tutorial with direct examples.
7. PostgreSQL Wiki — Table Partitioning — history and trade-offs documented by the community.

Module 4 — Advanced PostgreSQL for Backend Guide

Next capsule: Hash partitioning for uniform distribution — the third type, the least common but useful when there's no natural criterion.