PostgreSQL Partitioning Deep Dive: Range / List / Hash + Native Partition Pruning

postgres partitioning is the single biggest physical-design decision a Postgres team makes once a table crosses 100M rows — and the one most teams get wrong on the first try because they pick a partitioning scheme before they understand how the planner prunes partitions and how the executor parallelises scans across them. A partitioned table is not "one big table that is somehow faster"; it is a router on top of N child tables, each with its own statistics, its own indexes, and its own physical files. The planner decides which children to touch, the executor decides whether to touch them in parallel, and the maintenance layer (ATTACH PARTITION, DETACH PARTITION, pg_partman) decides how partitions are created and retired over time.

This guide is the senior-DE deep dive you wished existed the first time an interviewer asked "when do you pick partition by range postgres over list partitioning postgres or hash partitioning?" or "how does partition pruning actually work, and why is my prepared statement scanning every partition?" It walks through native declarative partitioning (the PG10+ replacement for inheritance + triggers), the three partition schemes and when each one wins, plan-time vs runtime pruning and the EXPLAIN (ANALYZE, VERBOSE) signals that prove a partition was pruned, attach partition / detach maintenance workflows and pg_partman for time-based automation, and partition-wise joins / partition-wise aggregates / parallel partition scans for performance on 100M+ row tables. Every section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on query optimisation problems →, sharpen the join axis with the JOIN practice library →, and stack the ETL practice library → for the data-movement patterns that pair with partition maintenance.

---

On this page

• Why native partitioning is now first-class in Postgres
• Three partition schemes — RANGE, LIST, HASH
• Partition pruning — plan-time + runtime
• ATTACH / DETACH / partition maintenance
• Performance — partition-wise join + parallel
• Cheat sheet — Postgres partitioning recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why native partitioning is now first-class in Postgres

postgres declarative partition replaced inheritance + triggers in PG10 — the planner, executor, and maintenance layer all became partition-aware

The one-sentence invariant: a Postgres partitioned table is a PARTITION BY parent that owns no rows itself, plus a set of child partitions that own all rows, and the planner uses partition bounds to skip children that cannot match the query. Every other consequence — pruning, partition-wise joins, parallel scans, pg_partman automation, FK limits — follows from that one structural fact. Once you internalise "parent is metadata, children are data, planner is partition-aware," the entire postgres partitioning interview surface collapses to a sequence of consequences from that compilation.

Three eras of Postgres partitioning.

• Pre-PG10 — inheritance + triggers. You created a parent table, then child tables that INHERITS from it, then a BEFORE INSERT trigger that routed rows to the correct child based on a CHECK constraint. Every layer was hand-rolled: routing, pruning, constraint exclusion. Fragile, slow on plan, and impossible to maintain at 100+ partitions. The planner used constraint_exclusion = partition to prune by CHECK constraints but the mechanism was brittle.
• PG10 — declarative partitioning lands. The PARTITION BY RANGE / LIST syntax landed. The planner became partition-aware by reading bounds directly from pg_partitioned_table rather than re-parsing CHECK constraints. Routing was native (no trigger). HASH partitioning followed in PG11.
• PG11–16 — production-grade. PG11 added HASH partitioning, default partitions, partition-wise joins (off by default), and FK on partitioned tables. PG12–13 added runtime partition pruning for prepared statements. PG14 added DETACH PARTITION ... CONCURRENTLY. PG15–16 added improved parallel partition-wise join planning and incremental sort across partitions. By 2026, every Postgres team using partitions starts with declarative.

The four "must-answer" axes interviewers actually probe.

• Scheme. RANGE for ordered ranges (time-series, numeric ranges). LIST for discrete categories (country code, tenant id). HASH for even distribution where no natural range or list exists (sharding by user_id). Sub-partitioning combines them: RANGE by month, then LIST by tenant, then HASH by user.
• Pruning. Plan-time pruning happens when the partition key appears as a constant in the WHERE clause; the planner emits an Append over only the matching children. Runtime pruning happens when the partition key is a parameter (prepared statement, generic plan, partition key in a join condition); the executor decides at run time which children to scan.
• Partition-wise operations. When two partitioned tables share the same partition bounds, the planner can run the join per-partition (smaller hash tables, parallel-friendly). Same for GROUP BY (partition-wise aggregate). Both controlled by enable_partitionwise_join / enable_partitionwise_aggregate.
• Maintenance. ATTACH PARTITION brings a pre-loaded table into the partition tree. DETACH PARTITION removes it without dropping (cold-tier exit). pg_partman automates time-based partition creation, premakes future partitions, and retention-drops old ones.

What changed in 2026 — PG16+ partitioning maturity.

• Runtime pruning is the default. Generic plans (prepared statements) prune at execute time without re-planning. The old "my prepared statement scans every partition" gotcha is largely gone for properly-parameterised queries.
• Partition-wise joins are pre-tuned for parallelism. PG16+ ships with better cost estimates for partition-wise hash joins; enable_partitionwise_join is increasingly safe to enable cluster-wide.
• DETACH CONCURRENTLY. No more long AccessExclusiveLock on the parent during detach. Production maintenance windows shortened from minutes to seconds.
• pg_partman 5.x. Native PG16 compatibility, better default templates, faster run_maintenance. The de-facto standard for time-based partition automation.
• Subscription support. Logical replication of partitioned tables is publish-by-root or publish-per-partition; production teams can replicate a multi-TB partitioned table without dragging every old partition.

What interviewers listen for.

• Do you say "the parent has no rows; children have all the rows" in the first sentence? — senior signal.
• Do you mention "plan-time pruning needs a constant; runtime pruning handles parameters" unprompted? — senior signal.
• Do you push back on "just partition by date" with "by date and by tenant where multi-tenant joins dominate"? — senior signal.
• Do you mention pg_partman instead of writing your own monthly cron job? — senior signal.

Worked example — same table, partitioned vs not

Detailed explanation. A common interview opener — "show me the schema and the query plan difference between an unpartitioned events table and a monthly-range-partitioned events table, both holding 36 months of data." The point is to see the Append node and the pruned children appear in EXPLAIN.

Question. Create a monthly-range-partitioned events table with 36 child partitions. Insert representative data. Run a WHERE created_at = '2026-06-12' query and compare its plan against an unpartitioned version of the same table.

Input.

table	rows	partitions	partition column
events_flat	360M	0	n/a
events_partitioned	360M	36 (one per month)	created_at (RANGE)

Code.

-- Parent table — no rows live here directly
CREATE TABLE events (
    event_id   BIGSERIAL,
    user_id    BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    payload    JSONB,
    PRIMARY KEY (event_id, created_at)
) PARTITION BY RANGE (created_at);

-- Create 36 monthly partitions (2024-01 .. 2026-12)
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... (34 more)
CREATE TABLE events_2026_12 PARTITION OF events
    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

-- Each child gets an index automatically inherited from the parent
CREATE INDEX ON events (user_id);
CREATE INDEX ON events (created_at);

-- Query — the planner prunes 35 of 36 partitions
EXPLAIN (ANALYZE, VERBOSE)
SELECT event_id, payload
FROM events
WHERE created_at = '2026-06-12 10:00:00+00';

Step-by-step explanation.

1. The PARTITION BY RANGE (created_at) clause on the parent tells Postgres how to route inserts and how the planner should prune reads. The parent table has no heap; its only job is metadata + routing.
2. Each CREATE TABLE ... PARTITION OF events FOR VALUES FROM ... TO ... creates a real child table with its own heap and indexes. Postgres validates that the bounds do not overlap with any existing partition.
3. The primary key is (event_id, created_at) — the partition key must be part of the primary key because a unique index on a partitioned table is enforced per-partition; Postgres cannot enforce global uniqueness across partitions efficiently.
4. The CREATE INDEX on the parent automatically propagates to every existing child and every future child (PG11+). You do not need to create the index on each child by hand.
5. When the query runs WHERE created_at = '2026-06-12 10:00:00+00', the planner reads pg_partitioned_table and walks the partition bounds. Only events_2026_06 matches; the other 35 partitions are pruned. The EXPLAIN output shows an Append node with a single child scan.

Output.

plan_node	scope	rows_scanned
Seq Scan on events_flat	full 360M-row table	360,000,000
Append → Index Scan on events_2026_06_created_at_idx	one partition, one index lookup	1
pruned_partitions (events_partitioned)	35 of 36	0
query_time_flat	(1.6s — full table scan-ish)	—
query_time_partitioned	(~ 1ms — single index lookup on one partition)	—

Rule of thumb. A partitioned table is a routing layer over many smaller tables; the planner uses partition bounds to skip the ones that cannot match. The win is not "faster scans" in some magical sense — it's "scan a tiny fraction of the rows."

Worked example — partition tree introspection

Detailed explanation. A senior interviewer often asks "given an existing partitioned table, how do you list every partition, its row count, and its bound expression?" The answer is pg_partition_tree, pg_partition_ancestors, and pg_class.relpages. Memorising the introspection catalog is a senior signal.

Question. Write a SQL query that lists every partition of the events table, its bound expression, and its approximate row count.

Input.

catalog_function	purpose
pg_partition_tree(parent_oid)	walks the partition hierarchy
pg_get_expr(relpartbound, oid)	renders the bound expression as text
pg_class.reltuples	approximate row count (set by ANALYZE)

Code.

SELECT
    c.relname                              AS partition,
    pg_get_expr(c.relpartbound, c.oid)     AS bound_expr,
    c.reltuples::BIGINT                    AS approx_rows,
    pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_partition_tree('events')          AS t
JOIN pg_class                              AS c ON c.oid = t.relid
WHERE t.isleaf                             -- skip the parent and any sub-parents
ORDER BY c.relname;

Step-by-step explanation.

1. pg_partition_tree('events') returns one row per node in the tree: the root, every sub-partitioned parent, and every leaf partition. isleaf filters to the real leaves that hold rows.
2. The join to pg_class resolves OIDs into table names, row counts, and on-disk sizes. reltuples is approximate — VACUUM and ANALYZE keep it within a few percent of the truth.
3. pg_get_expr(c.relpartbound, c.oid) renders the bound expression. For range partitions you see FOR VALUES FROM ('2026-06-01') TO ('2026-07-01'). For list, the list of values. For hash, the modulus + remainder.
4. The output is the catalog view senior DBAs check first when triaging "is partition X actually there and how big is it?" before running heavier diagnostics.

Output.

partition	bound_expr	approx_rows	size
events_2026_05	FOR VALUES FROM ('2026-05-01') TO ('2026-06-01')	10,000,000	1.4 GB
events_2026_06	FOR VALUES FROM ('2026-06-01') TO ('2026-07-01')	10,200,000	1.5 GB
events_2026_07	FOR VALUES FROM ('2026-07-01') TO ('2026-08-01')	0	16 kB

Rule of thumb. Memorise the pg_partition_tree query — it is the first thing you run on an unfamiliar partitioned table. It tells you whether the partitions are healthy, balanced, and correctly bounded.

Worked example — the inheritance-vs-declarative mental model

Detailed explanation. Some legacy Postgres systems still use pre-PG10 inheritance partitioning. Interviewers sometimes ask "what is the difference and why did declarative win?" — the answer hinges on planner integration, executor parallelism, and maintenance ergonomics.

Question. Compare the legacy inheritance + trigger pattern with declarative PARTITION BY. Identify the three planner integration points where the declarative form wins.

Input.

Aspect	Inheritance + triggers (pre-PG10)	Declarative PARTITION BY (PG10+)
Parent ownership	parent owns rows + children inherit	parent owns no rows, children own all
Routing	INSERT trigger picks the child	native router uses partition bounds
Pruning	constraint_exclusion reads CHECK	planner reads pg_partitioned_table

Code.

-- LEGACY — inheritance + trigger
CREATE TABLE events_legacy (
    event_id BIGINT,
    created_at TIMESTAMPTZ,
    payload JSONB
);

CREATE TABLE events_legacy_2026_06 (
    CHECK (created_at >= '2026-06-01' AND created_at < '2026-07-01')
) INHERITS (events_legacy);

CREATE OR REPLACE FUNCTION events_legacy_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.created_at >= '2026-06-01' AND NEW.created_at < '2026-07-01' THEN
        INSERT INTO events_legacy_2026_06 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'No partition for %', NEW.created_at;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- ... and a trigger, and per-month CHECK constraints, and constraint_exclusion=partition

-- MODERN — declarative
CREATE TABLE events_modern (
    event_id BIGINT,
    created_at TIMESTAMPTZ,
    payload JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_modern_2026_06 PARTITION OF events_modern
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Step-by-step explanation.

1. With inheritance, the parent table owns its own heap. Rows can be inserted into the parent directly and stay there forever; only the trigger redirects them. Mistakes (a trigger gap, a missed month) silently leave rows in the parent — invisible to per-child queries unless you also scan the parent.
2. With declarative, the parent is metadata only. An insert that matches no partition raises an error (PG10) or routes to the DEFAULT partition (PG11+) — never silently lost.
3. Pruning under inheritance reads CHECK constraints via constraint_exclusion = partition. Pruning under declarative reads the partition bounds directly from pg_partitioned_table — faster, more reliable, and supports list and hash schemes which constraint_exclusion never did well.
4. Maintenance under inheritance requires per-month DDL: a new child table, a new CHECK constraint, a trigger update. Under declarative, pg_partman automates it; ATTACH PARTITION and DETACH PARTITION are single statements.
5. Parallel and partition-wise features (partition-wise join, partition-wise aggregate) only work under declarative. Inheritance partitioning is a dead end for performance work in 2026.

Output.

feature	inheritance	declarative
Native routing	no (trigger)	yes
Plan-time pruning	constraint_exclusion (slow)	partition bound walk (fast)
Runtime pruning	no	yes (PG12+)
Partition-wise join	no	yes (PG11+)
Default partition	no	yes (PG11+)
Parallel append	partial	yes
FK on parent	no	yes (PG12+)

Rule of thumb. Migrate every legacy inheritance-partitioned table to declarative when you upgrade past PG12. The migration cost is real but the planner, executor, and maintenance wins compound. Inheritance partitioning is legacy.

Senior interview question on first-principles partitioning

A senior interviewer often opens with: "Walk me through the trade-offs of partitioning a 100M-row Postgres table. When does partitioning not help, and what are the operational costs you have to budget for?"

Solution Using the "scan cost vs router cost" trade-off

First-principles partitioning trade-off
=======================================

Wins
----
1. Plan-time pruning cuts the data scanned per query by Nx where N is the
   number of partitions that can be ruled out. A monthly partition over
   36 months can prune to 1/36 of the table for a single-month query.
2. Per-partition indexes are smaller → cheaper to maintain, faster to scan.
3. DROP PARTITION on cold data is O(1) — no DELETE + VACUUM cycle.
4. Per-partition ANALYZE / VACUUM is faster and runs more often.
5. Partition-wise join + partition-wise aggregate run smaller hash tables.

Costs
-----
1. Every query pays a small "partition routing" cost on the planner — bigger
   trees (1000+ partitions) start to show planning latency.
2. UNIQUE constraints across partitions are limited — the partition key
   must be part of the unique index, or you cannot enforce uniqueness.
3. FK from a non-partitioned table TO a partitioned table works (PG12+);
   FK FROM a partitioned table TO another partitioned table is limited.
4. Cross-partition UPDATE moves the row (PG11+ route updates) — extra cost.
5. Maintenance burden — pg_partman or hand-rolled cron is mandatory for
   time-series; without retention, partition count grows unbounded.

Not-helpful cases
-----------------
- Tables under ~10M rows where index-only scans already serve every query.
- Queries that never touch the partition key — pruning is useless and the
  query scans every partition every time.
- High-cardinality LIST partitioning (millions of values) — the planner
  walks the list linearly; HASH is better.

Step-by-step trace.

Workload	Rows	Partitions	Wins	Notes
Time-series append-only events	360M	36 monthly (RANGE)	pruning + DROP PARTITION for retention	classic win
Multi-tenant SaaS	200M	1024 by tenant_id (HASH)	per-tenant pruning + isolation	hash works
Country-keyed analytics	50M	200 (LIST)	per-country pruning	LIST works
OLTP table, mostly point reads	8M	0	none — partitioning hurts	skip
Reporting table queried without partition key	50M	12 (RANGE)	none — every query scans 12 partitions	redesign the key

After the trade-off pass, the choice is usually unambiguous. The remaining 5% — where partitioning is borderline — defaults to "leave the table flat until access patterns prove a partition key."

Output:

Outcome	When it wins
Partition by RANGE	time-series, append-only, retention-driven
Partition by LIST	bounded discrete categories (country, tenant tier)
Partition by HASH	high-cardinality even distribution (user_id, tenant_id)
Don't partition	< 10M rows, point reads, no clear key in WHERE

Why this works — concept by concept:

• Pruning is the entire point — partitioning helps if-and-only-if the planner can prune partitions for the queries that matter. Pick the partition key so it appears in the WHERE clause of the queries you care about, every time.
• Per-partition indexes shrink — a 360M-row table with one index is one big B-tree; the same table with 36 monthly partitions is 36 small B-trees. The per-month index fits in cache; the global index does not.
• DROP PARTITION beats DELETE — for time-series retention, dropping a partition is an instant catalogue change; deleting the equivalent rows from a flat table triggers VACUUM, table bloat, and index bloat for hours.
• Maintenance is a real ops cost — without pg_partman or a cron job, partition count grows forever. Old partitions accumulate. New months are missed. Plan for it on day one.
• Cost — partitioning is amortised over query count. For tables read 1000 times per day, the planner overhead is a rounding error; for a table read once per hour, partitioning may not pay.

SQL
Topic — sql
Postgres partitioning SQL problems

Practice →

SQL Topic — optimization Query optimisation problems

Practice →

---

2. Three partition schemes — RANGE, LIST, HASH

partition by range postgres, LIST, and HASH each solve a different access pattern — picking the wrong one wastes the partition tree

The mental model in one line: RANGE partitions order-able columns into contiguous bounded slices, LIST partitions discrete category columns into explicit value buckets, and HASH partitions arbitrary columns into modulus-based even buckets — the three schemes target three completely different access patterns, and a wrong choice silently disables partition pruning. Once you say "RANGE for time, LIST for tenant, HASH for user_id," every list partitioning postgres interview probe becomes a deduction from the access pattern.

RANGE — the time-series default.

• Syntax. PARTITION BY RANGE (created_at) + per-child FOR VALUES FROM (lo) TO (hi) (lo inclusive, hi exclusive).
• When it wins. Time-series append-only data; numeric ranges (price tiers, account-id ranges); any ordered key with predictable partition boundaries.
• Pruning behaviour. WHERE created_at = X prunes to one partition; WHERE created_at BETWEEN A AND B prunes to the partitions whose ranges intersect [A, B].
• Gotchas. Inclusive lo / exclusive hi — a row at exactly the boundary goes to the next partition. Off-by-one errors here are subtle.

LIST — the discrete-category scheme.

• Syntax. PARTITION BY LIST (country) + per-child FOR VALUES IN ('US', 'CA', 'MX').
• When it wins. Bounded, well-known discrete value set: country codes, tenant tiers, event types. The list must be exhaustive at design time — use a DEFAULT partition for fallback.
• Pruning behaviour. WHERE country = 'US' prunes to the partition containing 'US'. WHERE country IN ('US', 'CA') prunes to the matching partitions.
• Gotchas. Linear-scan-of-bounds cost — with 1000+ list values across many partitions, the planner walks them linearly. Above ~200 values, HASH typically wins.

HASH — the even-distribution scheme.

• Syntax. PARTITION BY HASH (user_id) + per-child FOR VALUES WITH (MODULUS 16, REMAINDER 0..15).
• When it wins. High-cardinality keys with no natural range or list: user_id, tenant_id (millions), session_id. The hash partitioner distributes rows roughly evenly across all partitions.
• Pruning behaviour. WHERE user_id = 42 prunes to one partition. WHERE user_id IN (1, 2, 3) may prune to fewer than 3 (some may hash to the same bucket).
• Gotchas. No range pruning — WHERE user_id BETWEEN 100 AND 200 scans every partition. HASH is for equality, not range queries.

Sub-partitioning — combine two schemes.

• A child partition can itself be PARTITION BY something else. The classic pattern: events PARTITION BY RANGE (created_at) → events_2026_06 PARTITION BY LIST (tenant_tier).
• Two levels deep is common in production; three levels deep is rare and usually a design smell.
• Pruning works through both levels: a WHERE created_at = X AND tenant_tier = 'enterprise' prunes month-then-tier.

The pruning matrix — what each scheme prunes on.

• RANGE prunes on =, <, >, BETWEEN, IN (constants) — all ordered predicates.
• LIST prunes on =, IN (constants), IS NULL — equality only.
• HASH prunes on = and IN — never on range predicates.
• Mixed predicates — if any condition can prune to one partition, the others are evaluated only against that partition.

Common interview probes on schemes.

• "Why not partition by user_id with RANGE?" — because user_id BETWEEN 1000 AND 2000 would prune nicely, but WHERE user_id = 42 would only prune to one partition out of N anyway; the access pattern is equality, which HASH serves with better distribution.
• "When does LIST beat HASH for tenant?" — when you have <200 tenants and want per-tenant DDL isolation (e.g. one tenant's partition tablespaced separately).
• "When do you sub-partition?" — when two access patterns share top priority (time AND tenant); never just "to be safe."
• "What's the DEFAULT partition for?" — a catch-all for values that don't match any explicit list/range; PG11+ feature, never required but useful for LIST schemes during onboarding.

Worked example — monthly RANGE events table

Detailed explanation. The canonical partition by range postgres example — an events table partitioned by month. Time-series data, append-only, retention via DROP PARTITION. Every query has created_at in the WHERE, so pruning works on every read.

Question. Create a monthly RANGE-partitioned events table with proper indexes and explain how the partition bounds prune a single-day query.

Input.

Column	Type	Notes
event_id	BIGSERIAL	primary key (must include partition key)
user_id	BIGINT	indexed
created_at	TIMESTAMPTZ	partition key
payload	JSONB	hot read column

Code.

CREATE TABLE events (
    event_id   BIGSERIAL,
    user_id    BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    payload    JSONB NOT NULL,
    PRIMARY KEY (event_id, created_at)
) PARTITION BY RANGE (created_at);

-- 2026 partitions (12 child tables)
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- ...
CREATE TABLE events_2026_12 PARTITION OF events
    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

-- Indexes propagate to every child automatically (PG11+)
CREATE INDEX events_user_id_idx     ON events (user_id);
CREATE INDEX events_created_at_idx  ON events (created_at);

-- A DEFAULT partition catches any out-of-range insert
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Pruned query — touches one partition
EXPLAIN (ANALYZE, VERBOSE)
SELECT count(*)
FROM events
WHERE created_at >= '2026-06-12' AND created_at < '2026-06-13';

Step-by-step explanation.

1. The parent declares PARTITION BY RANGE (created_at). The parent itself has no rows; every insert routes to the matching child based on created_at.
2. Each monthly child uses FOR VALUES FROM (lo) TO (hi) — lo is inclusive, hi is exclusive. A row with created_at = '2026-02-01 00:00:00' lands in events_2026_02, not events_2026_01.
3. The primary key is (event_id, created_at) because partition tables require the partition key to be part of any unique index. A bare PRIMARY KEY (event_id) would error out.
4. Indexes created on the parent are auto-propagated to every child and to every future partition. This is a major usability win versus inheritance partitioning.
5. The query WHERE created_at >= '2026-06-12' AND created_at < '2026-06-13' overlaps only events_2026_06. The planner walks the bounds, picks one child, emits an Append with a single index scan.
6. The DEFAULT partition is a safety net for out-of-range inserts. A 2027 insert before the 2027 partitions exist lands in events_default rather than erroring.

Output (EXPLAIN output, abbreviated).

node	partition_touched	rows
Aggregate	—	1
Append	events_2026_06	(one child)
Index Scan using events_2026_06_created_at_idx	events_2026_06	850,000
pruned_partitions	11 of 12	0

Rule of thumb. For time-series, partition by month unless you write more than ~50M rows/month (then partition by week) or less than ~1M rows/month (then partition by quarter or year). Match partition size to a few GB each — small enough that the index fits in cache, large enough to keep partition count under ~200.

Worked example — LIST partitioning by country

Detailed explanation. A customer_events table is queried almost exclusively by country in regulatory and analytical reports. country has ~200 possible values. LIST partitioning gives each country its own partition; queries with WHERE country = 'X' prune to exactly one partition. The DEFAULT partition catches new countries before someone adds them explicitly.

Question. Create a LIST-partitioned customer_events table keyed by country, with one explicit partition per major market and a DEFAULT for everything else. Show how the planner prunes a per-country query.

Input.

country	annual events	partition strategy
US	400M	own partition
CA	80M	own partition
MX	40M	own partition
(~200 others)	200M total	one DEFAULT partition

Code.

CREATE TABLE customer_events (
    event_id   BIGSERIAL,
    user_id    BIGINT NOT NULL,
    country    TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    payload    JSONB NOT NULL,
    PRIMARY KEY (event_id, country)
) PARTITION BY LIST (country);

CREATE TABLE customer_events_us PARTITION OF customer_events
    FOR VALUES IN ('US');

CREATE TABLE customer_events_ca PARTITION OF customer_events
    FOR VALUES IN ('CA');

CREATE TABLE customer_events_mx PARTITION OF customer_events
    FOR VALUES IN ('MX');

-- Group of small countries can share a single LIST partition
CREATE TABLE customer_events_emea PARTITION OF customer_events
    FOR VALUES IN ('FR', 'DE', 'IT', 'ES', 'NL', 'BE', 'PL', 'SE');

-- Everything else lands in DEFAULT
CREATE TABLE customer_events_other PARTITION OF customer_events DEFAULT;

-- Pruned query — touches one partition
EXPLAIN (ANALYZE, VERBOSE)
SELECT count(*) FROM customer_events
WHERE country = 'US' AND created_at >= '2026-06-01';

Step-by-step explanation.

1. The parent declares PARTITION BY LIST (country). The country column must be NOT NULL (or the partition IS NULL clause must be used explicitly).
2. Each FOR VALUES IN (...) lists the discrete values that route to this partition. A single partition can hold many values — useful for grouping low-volume countries together.
3. The primary key is (event_id, country) because country is the partition key and must be part of any unique index. Without country, a unique constraint would not be enforceable across partitions.
4. The DEFAULT partition is the catch-all for values not in any explicit IN clause. When a new country shows up, you can either: leave it in _other and let traffic grow, or run a one-time migration to ATTACH PARTITION a new dedicated partition for it.
5. The planner sees country = 'US' and prunes to customer_events_us. The created_at predicate then narrows the index scan within that partition. With LIST partitioning, the partition key is the primary prune; secondary indexes inside the partition handle the rest.

Output.

partition	rows	pruned
customer_events_us	400M	scanned
customer_events_ca	80M	pruned
customer_events_mx	40M	pruned
customer_events_emea	varied	pruned
customer_events_other	varied	pruned

Rule of thumb. LIST partitioning wins when (a) the value set is bounded and small (<200 values), (b) queries equality-filter on the partition key, and (c) you want per-partition operational isolation (tablespace, retention, DROP PARTITION). Above 200 values, HASH usually wins on planner cost.

Worked example — HASH partitioning for user_id

Detailed explanation. A user_sessions table is keyed by user_id with 200M+ rows and zero natural range or list. Equality lookups dominate (WHERE user_id = ...); range queries on user_id are nonsensical. HASH partitioning gives even distribution and per-user prune.

Question. Create a HASH-partitioned user_sessions table with 16 buckets keyed by user_id. Show how an equality query prunes and a range query does not.

Input.

query	partition_key	predicate	pruning expected
Q1	user_id	= 12345	one partition
Q2	user_id	BETWEEN 100 AND 200	all 16 partitions
Q3	user_id	IN (1, 5, 9)	up to 3 partitions

Code.

CREATE TABLE user_sessions (
    session_id  BIGSERIAL,
    user_id     BIGINT NOT NULL,
    started_at  TIMESTAMPTZ NOT NULL,
    payload     JSONB,
    PRIMARY KEY (session_id, user_id)
) PARTITION BY HASH (user_id);

-- 16 HASH buckets (MODULUS 16, REMAINDER 0..15)
CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 16, REMAINDER 0);
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 16, REMAINDER 1);
-- ...
CREATE TABLE user_sessions_15 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 16, REMAINDER 15);

CREATE INDEX ON user_sessions (user_id, started_at);

-- Q1 — equality — prunes to 1 partition
EXPLAIN (ANALYZE) SELECT * FROM user_sessions WHERE user_id = 12345;

-- Q2 — range — scans every partition
EXPLAIN (ANALYZE) SELECT count(*) FROM user_sessions WHERE user_id BETWEEN 100 AND 200;

Step-by-step explanation.

1. The parent declares PARTITION BY HASH (user_id). The HASH partitioner uses Postgres' internal hash function on user_id modulo 16, producing a bucket number 0..15.
2. Each child specifies FOR VALUES WITH (MODULUS 16, REMAINDER N). All 16 children together cover every possible hash. There is no notion of "list of values" — just the modulus + remainder.
3. Q1 (user_id = 12345) — the planner computes hash(12345) mod 16, picks the one partition that matches, prunes the other 15. Single index scan, single partition.
4. Q2 (user_id BETWEEN 100 AND 200) — HASH cannot prune range predicates because adjacent integers hash to scattered buckets. The planner scans every partition, performing 16 index scans and merging the results.
5. Q3 (user_id IN (1, 5, 9)) — the planner computes the hash bucket of each constant; some may collide on the same bucket. The query touches up to 3 partitions (sometimes fewer).
6. HASH partitioning is the right pick when (a) equality lookups dominate, (b) the key is high-cardinality, and (c) you want even row distribution across partitions for parallel work.

Output.

query	partitions_touched	rows_scanned
Q1 — equality	1 of 16	12 (one user's sessions)
Q2 — range	16 of 16	200M total scan
Q3 — IN (3 values)	up to 3 of 16	~3x equality cost

Rule of thumb. HASH partitioning is for equality lookups on a high-cardinality key. If your access pattern is mixed equality + range, either pick RANGE (better range pruning, worse hash distribution) or denormalise so each access pattern has its own table layout.

Senior interview question on scheme selection

A senior interviewer might frame this as: "You have an orders table that's growing 100M rows a year. Walk me through how you'd pick between RANGE, LIST, and HASH partitioning, and what additional information you'd ask for before deciding."

Solution Using the access-pattern + cardinality decision tree

-- Decision tree (executed as a thought process, not SQL)
--
-- 1. What is in the WHERE clause 80%+ of the time?
--    - created_at, processed_at, etc.   → RANGE by that column
--    - country, tenant_tier, event_type → LIST by that column
--    - user_id, account_id, session_id  → HASH by that column
--    - Nothing predictable              → partitioning won't help
--
-- 2. Is the column orderable AND queried with range predicates?
--    - YES → RANGE
--    - NO  → LIST (if cardinality < 200) or HASH (if higher)
--
-- 3. Is retention policy time-based?
--    - YES → RANGE by time is mandatory (DROP PARTITION beats DELETE)
--
-- 4. Do you need per-partition operational isolation
--    (different tablespaces, different VACUUM schedules)?
--    - YES → LIST or RANGE (HASH gives you no semantic handle)
--    - NO  → HASH is fine
--
-- 5. Will you need partition-wise joins to other partitioned tables?
--    - YES → the partition scheme must align with the join partner

-- Applied to `orders`:
CREATE TABLE orders (
    order_id     BIGSERIAL,
    customer_id  BIGINT NOT NULL,
    placed_at    TIMESTAMPTZ NOT NULL,
    amount       NUMERIC(12,2) NOT NULL,
    status       TEXT NOT NULL,
    PRIMARY KEY (order_id, placed_at)
) PARTITION BY RANGE (placed_at);

Step-by-step trace.

Decision step	Answer for orders	Pushes to
Q1 — common WHERE?	placed_at in 90% of queries	RANGE by placed_at
Q2 — orderable + range predicates?	yes (BETWEEN, > , <)	RANGE confirmed
Q3 — time-based retention?	yes (drop orders >5 years)	RANGE mandatory
Q4 — per-partition isolation?	not strictly	doesn't change choice
Q5 — partition-wise join?	yes — join with order_lines	both must align by placed_at

The decision tree confirms RANGE by placed_at. The 10% of queries that filter by customer_id alone scan every partition unless we also add a sub-partition by hash on customer_id — usually not worth it for a minority pattern; a covering index handles those.

Output:

Scheme	Used when	Example	Pruning
RANGE	time + numeric ranges	RANGE (placed_at)	=, <, >, BETWEEN, IN
LIST	bounded discrete values	LIST (country)	=, IN
HASH	high-cardinality equality	HASH (user_id)	=, IN

Why this works — concept by concept:

• Access pattern dominates — the partition key must appear in the WHERE of the queries that matter. Pick the partition scheme by reading the slow-query log and the access-pattern doc, not by intuition.
• Orderable vs categorical vs cardinality — RANGE serves ordered columns with range predicates; LIST serves bounded discrete sets; HASH serves high-cardinality equality. The three schemes are not interchangeable.
• Retention is a force multiplier — if you DROP old data on a schedule, RANGE by time is a near-mandate. DROP PARTITION is an O(1) catalog change; DELETE FROM ... WHERE created_at < ... is hours of writes and VACUUM.
• Sub-partitioning is a refinement — combine schemes only when two access patterns share top priority. Three levels deep is almost always over-engineering.
• Cost — a wrong scheme is a year-long migration to fix. A right scheme amortises over millions of queries. The decision is worth two hours of design review and a written ADR.

SQL
Topic — sql
Partition-scheme design problems

Practice →

SQL Topic — optimization Partition-key optimisation drills

Practice →

---

3. Partition pruning — plan-time + runtime

partition pruning is the whole point of partitioning — plan-time prunes constants, runtime prunes parameters, and a missing partition key in WHERE silently disables it

The mental model in one line: plan-time partition pruning happens during planning when the partition key is a constant in WHERE; runtime partition pruning happens during execution when the partition key is a parameter, a nested-loop join key, or a sub-query result — and if the partition key is not in the WHERE clause at all, no pruning happens and every partition is scanned. Once you say "plan-time wants constants; runtime wants parameters; both want the key in WHERE," every partition pruning interview probe collapses to "where does the value come from?"

Plan-time pruning — what it is.

• During planning, the planner walks pg_partitioned_table and applies the WHERE clause to the partition bounds. Children that cannot match are removed from the Append node.
• Only works when the partition key compares to a constant or a constant-folded expression (= '2026-06-12', BETWEEN '2026-01-01' AND '2026-12-31', IN ('US', 'CA')).
• Visible in EXPLAIN as a reduced child list under the Append node. The pruned children are simply absent.
• Cost: a few microseconds at plan time, regardless of partition count. Highly cached after the first plan.

Runtime pruning — what it is.

• During execution, the executor re-evaluates the partition predicate against the actual parameter value. Children that cannot match are skipped at execute time.
• Triggers for runtime pruning: prepared statements with $1 parameters, generic plans, nested-loop joins where the outer side provides the partition key, sub-query results, set-returning functions.
• Visible in EXPLAIN (ANALYZE) as Subplans Removed: N underneath the Append node. Without ANALYZE, the plan shows every potential child even if most are pruned at run time.
• Cost: a few microseconds per execution. Essentially free.

The "no partition key in WHERE" trap.

• If no condition references the partition key, Postgres has nothing to prune by. Every partition is scanned. The query touches the entire table.
• This is the single most common partition-pruning bug. Symptom: EXPLAIN shows N child scans and no Subplans Removed.
• Fix: either rewrite the query to include the partition key (preferred), or add an index that does not rely on partition pruning (a multi-partition index, e.g. on user_id, which the planner can use via Append of per-partition index scans).

Function call gotcha — partition pruning and IMMUTABLE.

• WHERE created_at = '2026-06-12' prunes — the comparison value is a constant.
• WHERE created_at = now() may prune at runtime — now() is STABLE not IMMUTABLE, so the planner cannot fold it at plan time but the executor can evaluate it.
• WHERE created_at = my_function() where my_function is VOLATILE — does NOT prune (the executor cannot trust the value to be stable).
• Mark helper functions IMMUTABLE or STABLE if they should participate in partition pruning.

EXPLAIN signals to look for.

• Append node with N children → partitioned table.
• Subplans Removed: K → runtime pruning kicked in; K of N children were skipped.
• Index Scan on events_2026_06_created_at_idx (only one child name) → plan-time pruning hit; only matched children remain.
• Buffers: shared hit=... per child → confirms which partitions were actually touched.

Common interview probes on pruning.

• "Why is my prepared statement scanning every partition?" — either the partition key isn't in WHERE, or generic-plan estimation went wrong (force a custom plan once or rewrite). PG12+ runtime pruning fixes this.
• "How do I verify pruning happened?" — EXPLAIN (ANALYZE) + look for Subplans Removed: N or for a single-child Append.
• "Why doesn't WHERE created_at = now() prune at plan time?" — now() is STABLE, not IMMUTABLE; folded at execute time, not plan time.
• "Does pruning work across joins?" — yes if the partition key is in the join condition; the executor uses outer-side values for runtime pruning of the inner side.

Worked example — plan-time pruning with a constant

Detailed explanation. The simplest case — a constant in WHERE prunes at plan time. The query reads only one of 36 monthly partitions. EXPLAIN shows a single child scan and a Buffers: shared hit=... count that matches one partition's index.

Question. Run a single-day query against the events table. Show the EXPLAIN output and identify the plan-time pruning signals.

Input.

query	partition key in WHERE	pruning type
WHERE created_at >= '2026-06-12' AND created_at < '2026-06-13'	yes, with constants	plan-time

Code.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT count(*)
FROM events
WHERE created_at >= '2026-06-12'
  AND created_at <  '2026-06-13';

Step-by-step explanation.

1. The planner walks every child's partition bound. events_2026_06's bound is FROM ('2026-06-01') TO ('2026-07-01'). The query's range [2026-06-12, 2026-06-13) falls entirely inside that bound. Every other child's bound does not overlap the query range.
2. The planner emits an Append node with exactly one child — events_2026_06. The other 35 children are gone from the plan; they consume zero plan space at execute time.
3. Inside the matched partition, the planner picks an index scan on events_2026_06_created_at_idx (auto-created from the parent index). The index range [2026-06-12, 2026-06-13) is the inner work.
4. The BUFFERS clause shows shared hit= counts only for the matched partition's index and heap pages — concrete proof of plan-time pruning.
5. The query time is bounded by the cost of one partition's index scan, not 36 partitions' worth.

Output (EXPLAIN abbreviated).

node	partition	rows	buffers
Aggregate	—	1	hit=240
Append	events_2026_06	(one child)	hit=240
Index Scan using events_2026_06_created_at_idx	events_2026_06	850,000	hit=240
(no other partitions listed)	—	—	—

Rule of thumb. Always run EXPLAIN (ANALYZE, BUFFERS) on a partitioned-table query before shipping it. A single-child Append confirms plan-time pruning; a multi-child Append with no Subplans Removed is the bug.

Worked example — runtime pruning with a parameter

Detailed explanation. Application code typically uses prepared statements with parameters ($1, $2). At plan time, the parameter value is unknown, so the planner cannot prune. PG12+ added runtime pruning: at execute time, the executor evaluates the parameter and prunes children before scanning. Look for Subplans Removed: N in EXPLAIN (ANALYZE).

Question. Show a prepared statement against the events table. EXPLAIN the generic plan and identify the runtime-pruning signal.

Input.

step	command
PREPARE	builds a generic plan for parameterised query
EXECUTE	binds the parameter and triggers runtime pruning

Code.

PREPARE q_events_day (date) AS
SELECT count(*) FROM events
WHERE created_at >= $1::timestamptz
  AND created_at <  $1::timestamptz + interval '1 day';

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
EXECUTE q_events_day ('2026-06-12');

Step-by-step explanation.

1. PREPARE builds the plan once and caches it. At first, the planner uses custom plans — re-planning with the bound parameter — for the first 5 executions, then switches to a generic plan that does not re-plan.
2. The generic plan cannot fold $1 at plan time, so it keeps every partition in the Append. Without runtime pruning, every execution would scan every partition.
3. PG12+'s runtime pruning kicks in at execute time. The executor evaluates $1, computes the partition range, and removes the children that cannot match before scanning.
4. EXPLAIN (ANALYZE) reports Subplans Removed: 11 (or 35) underneath the Append node — concrete proof that runtime pruning eliminated those children.
5. Buffer counts confirm only the matched partition was actually read.

Output.

node	observed
Append	all 12 (or 36) children listed
Subplans Removed	11 (one matched, others pruned at runtime)
Index Scan on events_2026_06_created_at_idx	only this one ran
total query time	comparable to plan-time-pruned version

Rule of thumb. For ORM / application code, prefer prepared statements with parameters. Runtime pruning gives you almost the same performance as plan-time pruning, with the bonus of one cached plan per query shape. The old "PREPARE breaks pruning" advice is obsolete in PG12+.

Worked example — the no-partition-key-in-WHERE trap

Detailed explanation. A subtle bug — a query is fast in dev but slow in prod. EXPLAIN shows every partition being scanned because the partition key is not in the WHERE clause. The fix is either to add the partition key constraint (the natural fix) or to accept a multi-partition index scan via a non-partition-key index.

Question. Show a query that fails to prune because the partition key is missing from WHERE. Demonstrate both the broken state and the fix.

Input.

query	partition key in WHERE	pruning
Q1 — WHERE user_id = 42 (no created_at)	no	NONE
Q2 — WHERE user_id = 42 AND created_at > now() - interval '7 days'	yes	runtime

Code.

-- Q1 — broken: no partition key in WHERE
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE user_id = 42;
-- Append over ALL 36 partitions; "Subplans Removed: 0"
-- Each partition does an index scan on user_id; 36 index scans total.

-- Q2 — fixed: add the partition key range
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE user_id = 42
  AND created_at > now() - interval '7 days';
-- Append over ~1-2 partitions; "Subplans Removed: 34"

Step-by-step explanation.

1. Q1 has no constraint on created_at (the partition key). The planner cannot rule out any partition; every monthly child is in the Append and gets an index scan on user_id.
2. The result is technically correct (the answer is the same), but the query touches 36 indexes instead of 1-2. On large data this is the difference between 50ms and 5s.
3. Q2 adds a partition-key constraint. The planner (or executor, depending on whether now() is folded as STABLE) prunes to 1-2 partitions. Only those partitions perform the user_id index scan.
4. The trap is subtle because Q1 looks "complete" — it does answer the question. But it answers the question by force, scanning every partition. The fix is recognising that any query against a partitioned table should include the partition key in WHERE whenever possible.
5. When the partition key cannot reasonably appear (e.g. "all-time user count"), the workaround is to accept the multi-partition scan and ensure the per-partition index is efficient; sometimes a materialised view or per-month aggregates make more sense.

Output.

query	partitions_touched	wall time
Q1 (no partition key)	36 of 36	~5s
Q2 (partition key added)	1-2 of 36	~50ms

Rule of thumb. Every partitioned-table query should ideally include the partition key in WHERE. If a real business query cannot, that is a signal you've partitioned by the wrong column or that this query should hit a different table (a per-period rollup, a materialised view, or a denormalised lookup).

Senior interview question on debugging pruning

A senior interviewer might ask: "A user reports that a Postgres query against a partitioned table is scanning all 36 partitions instead of one. Walk me through the diagnostic steps you take to identify why pruning isn't happening, and the three or four common root causes."

Solution Using EXPLAIN ANALYZE + the four common root causes

-- Step 1 — run EXPLAIN ANALYZE to see what actually happened
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ... FROM partitioned_table WHERE ...;

-- Step 2 — look for these signals in the output
--
-- Signal A — Append over every partition + "Subplans Removed: 0"
--   → no pruning at all
--
-- Signal B — Append over every partition + "Subplans Removed: N"
--   → runtime pruning worked, no problem
--
-- Signal C — Append over one child
--   → plan-time pruning worked, no problem

-- Step 3 — if Signal A, walk the four root causes:

-- Cause 1: partition key missing from WHERE
-- Fix: add the partition key constraint (or accept the scan)

-- Cause 2: predicate uses a VOLATILE function
SELECT pg_get_functiondef('my_func()'::regprocedure);
-- Fix: mark function IMMUTABLE or STABLE if safe

-- Cause 3: type mismatch on the partition key
-- e.g. partition key is TIMESTAMPTZ but WHERE uses TEXT comparison
-- Fix: cast the literal to the partition-key type

-- Cause 4: generic plan estimation hurts pruning
SHOW plan_cache_mode;
SET plan_cache_mode = 'force_custom_plan';
-- Fix: prefer custom plans for selective queries, or upgrade past PG12

Step-by-step trace.

Diagnostic	Outcome	Root cause	Fix
EXPLAIN ANALYZE shows 36 child scans, "Subplans Removed: 0"	query is unpruned	varies	continue
Inspect WHERE clause for partition key	missing	Cause 1	add the partition key
Inspect WHERE clause functions	one is VOLATILE	Cause 2	mark IMMUTABLE/STABLE
Check predicate column types	TEXT vs TIMESTAMPTZ mismatch	Cause 3	cast literal
Generic vs custom plan	generic, with poor estimates	Cause 4	force custom or upgrade

After the diagnostic walk, the root cause is usually obvious. The fix is local; you do not need to redesign the partition tree.

Output:

Root cause	Diagnostic signal	Frequency
Partition key not in WHERE	no key column in predicate	most common (≈70%)
VOLATILE function in predicate	function definition says VOLATILE	≈15%
Type mismatch on literal	predicate has implicit cast	≈10%
Generic plan vs runtime pruning	EXPLAIN shows no Subplans Removed	≈5%

Why this works — concept by concept:

• EXPLAIN ANALYZE is the source of truth — never debug pruning by reading the SQL; always check the plan. Plan-time pruning shows a smaller Append; runtime pruning shows Subplans Removed. No-pruning shows the full Append with no removals.
• Partition key in WHERE is non-negotiable — without the partition key, the planner has no axis to prune by. Adding the key to WHERE (even a wide range) lets the planner prune everything outside the range.
• Function volatility matters — IMMUTABLE folds at plan time, STABLE folds at execute time, VOLATILE never folds. Application helper functions used in WHERE should be IMMUTABLE if possible.
• Type matching matters — implicit casts can prevent the planner from using partition bounds. Cast literals explicitly to the partition-key column type when in doubt.
• Cost — pruning makes queries Nx faster on N-partition tables. Diagnosing a no-prune bug pays back the first time it surfaces. The diagnostic walk takes 5-10 minutes; the bug, if missed, costs hours per day in production scan time.

SQL
Topic — optimization
EXPLAIN ANALYZE drills

Practice →

SQL Topic — sql Partition pruning problems

Practice →

---

4. ATTACH / DETACH / partition maintenance

attach partition brings a pre-loaded table into the tree; pg_partman automates time-based partition creation; DETACH archives without DROP

The mental model in one line: ATTACH PARTITION plugs an existing table into a partitioned tree as a new child, DETACH PARTITION removes a child without dropping its data, and pg_partman runs as a maintenance job to premake future partitions and retention-drop old ones — together they are the operational layer that keeps a partitioned table alive over years. Once you say "ATTACH plugs, DETACH unplugs, pg_partman schedules," every pg_partman interview question collapses to a deduction from those three primitives.

ATTACH PARTITION — plugging an external table in.

• Syntax: ALTER TABLE parent ATTACH PARTITION child FOR VALUES FROM (lo) TO (hi);
• The child must already exist, must have a compatible schema (same columns + types), and must not overlap any existing partition bound.
• During ATTACH, Postgres validates that every row in the child satisfies the bound expression. To skip the validation, add a matching CHECK constraint on the child before ATTACH — the planner trusts the CHECK and skips the scan.
• ATTACH takes an AccessExclusiveLock on the parent briefly; rest of the tree is online.

DETACH PARTITION — removing without dropping.

• Syntax: ALTER TABLE parent DETACH PARTITION child; — child becomes a standalone table.
• Use cases: cold-tier exit (detach an old monthly partition, archive to S3, drop), DDL migration (detach, ALTER child schema, re-attach), zero-data-loss schema evolution.
• PG14+ supports DETACH PARTITION ... CONCURRENTLY — no AccessExclusiveLock on the parent; uses a two-phase protocol. Safe for live production.
• After DETACH, the child still has its data and indexes; nothing is dropped automatically.

pg_partman — automated time-based partitioning.

• An extension that wraps ATTACH + DETACH + CREATE into time-based automation. The de-facto standard.
• Config: register a parent with partman.create_parent('events', 'created_at', 'native', 'monthly', p_premake => 3, p_retention => '12 months').
• partman.run_maintenance() (run on a cron) creates the next N premake partitions and detach/drop old partitions per the retention policy.
• Native mode (PG10+) uses declarative PARTITION BY; legacy "trigger" mode uses inheritance — almost no new deployments should use trigger mode in 2026.

The DETACH-and-archive pattern for cold storage.

• Step 1 — DETACH PARTITION events_2024_01 CONCURRENTLY; — partition becomes a standalone table.
• Step 2 — Run COPY events_2024_01 TO PROGRAM 'aws s3 cp - s3://archive/events_2024_01.csv.zst'; or use pg_dump to export.
• Step 3 — Verify the archive integrity (size, row count match).
• Step 4 — DROP TABLE events_2024_01; — reclaim the disk.
• The partitioned table never had downtime; the parent was online throughout. This is the standard cold-tier exit pattern.

FK on partitioned tables — limitations and workarounds.

• FK from a non-partitioned table TO a partitioned table — works in PG12+. The FK targets the root partitioned table; Postgres enforces the constraint across all partitions.
• FK from a partitioned table TO another non-partitioned table — works since PG11.
• FK between two partitioned tables — supported in PG12+; both sides must have compatible partitioning so the constraint can be enforced per-partition.
• Cascading deletes across partitions — work, but a DELETE that cascades into multiple partitions can be slow; consider per-partition deletes.
• UNIQUE constraints across partitions — the unique index must include the partition key; otherwise Postgres can only enforce uniqueness per-partition.

Common interview probes on maintenance.

• "How do you handle retention on a time-partitioned table?" — pg_partman with p_retention and p_retention_keep_table=false, or hand-rolled DROP PARTITION.
• "How do you bulk-load a year of historical data into a partitioned table?" — load into a standalone table, then ATTACH PARTITION with a matching CHECK to skip validation.
• "What's the difference between DETACH and DROP?" — DETACH removes from the partition tree but keeps the data; DROP destroys the data.
• "Why does DETACH CONCURRENTLY matter?" — it removes the AccessExclusiveLock on the parent, so production writes don't block during the operation.

Worked example — pg_partman monthly automation

Detailed explanation. A real production setup: an events table partitioned monthly, retained for 12 months, with 3 future months premade. pg_partman handles it all via one config call + a periodic run_maintenance() invocation from cron.

Question. Configure pg_partman to monthly-partition the events table, premake 3 months of future partitions, and drop partitions older than 12 months. Show the config call and the maintenance schedule.

Input.

setting	value
parent	events
partition column	created_at
interval	monthly
premake	3 (months ahead)
retention	12 months

Code.

-- One-time setup
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

-- Convert an existing flat events table into a partitioned table
-- (or start fresh with the parent already declared PARTITION BY RANGE)

-- Register with pg_partman
SELECT partman.create_parent(
    p_parent_table         => 'public.events',
    p_control              => 'created_at',
    p_type                 => 'native',          -- declarative partitioning
    p_interval             => 'monthly',
    p_premake              => 3,                  -- always 3 future months
    p_start_partition      => '2024-01-01',
    p_default_table        => true                -- create a DEFAULT partition
);

-- Set retention: drop partitions older than 12 months
UPDATE partman.part_config
SET retention             = '12 months',
    retention_keep_table  = false,                -- DROP, not DETACH
    retention_keep_index  = false
WHERE parent_table = 'public.events';

-- Run maintenance: creates next premake partitions, drops old ones
SELECT partman.run_maintenance(p_parent_table => 'public.events');

# In production, schedule run_maintenance via pg_cron or system cron
# 0 3 * * * psql -d mydb -c "SELECT partman.run_maintenance_proc();"

Step-by-step explanation.

1. CREATE EXTENSION pg_partman installs the extension in a dedicated partman schema. Best practice: keep it out of public.
2. partman.create_parent registers the parent table with pg_partman. The native type uses declarative PARTITION BY — the modern path. The interval monthly instructs pg_partman to create one partition per calendar month.
3. p_premake=3 means at any time, the next 3 months exist as empty partitions. On 2026-06-15, partitions for 2026-06, 2026-07, 2026-08, 2026-09 all exist; inserts for future dates never fall through to the DEFAULT.
4. p_default_table=true creates a DEFAULT partition. Any row that ends up there is a bug indicator — it means the premake schedule lagged or someone inserted very-far-future data. Monitor the DEFAULT row count and alert if it climbs.
5. retention='12 months' plus retention_keep_table=false causes run_maintenance to DROP partitions older than 12 months (counted from the partition's start date). This is the retention enforcement.
6. run_maintenance is idempotent — running it multiple times in the same minute does nothing extra. Cron it once a day; pg_partman handles the rest.

Output (catalog after first run_maintenance).

partition	bound	status
events_p2025_07	FROM ('2025-07-01') TO ('2025-08-01')	active (retention edge)
events_p2026_06	FROM ('2026-06-01') TO ('2026-07-01')	active (current month)
events_p2026_09	FROM ('2026-09-01') TO ('2026-10-01')	premake (3 months ahead)
events_p2025_06	(dropped — older than 12 months)	dropped
events_default	DEFAULT	catch-all (empty, alerted on rows>0)

Rule of thumb. Always use pg_partman for time-based partitioning automation. Hand-rolled cron jobs work but drift: a missed run, a leap-year edge case, a daylight-saving boundary, and you have a missing partition that quietly routes rows to DEFAULT. pg_partman is battle-tested.

Worked example — ATTACH PARTITION with skip-validation

Detailed explanation. Bulk-loading historical data into a partitioned table. The naive approach is INSERT INTO partitioned_table SELECT ... — slow because every row is routed through the parent. The fast approach: load into a standalone table with a matching CHECK constraint, then ATTACH PARTITION with the matching bound. Postgres trusts the CHECK and skips the validation scan.

Question. Load 100M rows of June 2025 historical events into the partitioned events table without taking a long parent lock. Show the ATTACH-with-CHECK pattern.

Input.

step	command
1	CREATE TABLE events_loader_2025_06 (same schema)
2	COPY 100M rows into events_loader_2025_06
3	Add a CHECK constraint matching the partition bound
4	ATTACH PARTITION — Postgres trusts the CHECK

Code.

-- Step 1: create a standalone loader table with the same schema
CREATE TABLE events_loader_2025_06 (LIKE events INCLUDING ALL);

-- Step 2: bulk-load (COPY is much faster than INSERT)
COPY events_loader_2025_06 (event_id, user_id, created_at, payload)
FROM '/data/events_2025_06.csv' WITH (FORMAT csv);

-- Step 3: build indexes on the loader (faster off the partition tree)
CREATE INDEX ON events_loader_2025_06 (created_at);
CREATE INDEX ON events_loader_2025_06 (user_id);

-- Step 4: add the matching CHECK so ATTACH can skip validation
ALTER TABLE events_loader_2025_06
ADD CONSTRAINT events_loader_2025_06_bound
CHECK (created_at >= '2025-06-01' AND created_at < '2025-07-01') NOT VALID;

ALTER TABLE events_loader_2025_06 VALIDATE CONSTRAINT events_loader_2025_06_bound;

-- Step 5: ATTACH — Postgres reads the CHECK and skips its own scan
ALTER TABLE events
ATTACH PARTITION events_loader_2025_06
FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

-- Drop the now-redundant CHECK (the partition bound replaces it)
ALTER TABLE events_loader_2025_06
DROP CONSTRAINT events_loader_2025_06_bound;

Step-by-step explanation.

1. CREATE TABLE ... LIKE events INCLUDING ALL clones the schema, indexes, constraints, and defaults. The loader is structurally identical to a partition.
2. COPY is 10-50x faster than INSERT for bulk loads. Loading into a standalone table avoids the partition-routing overhead.
3. Indexes are built after the load — index-then-load is faster than load-on-an-indexed-table because the index avoids per-row updates.
4. The CHECK constraint matches the partition bound exactly: >= lo AND < hi. The NOT VALID + VALIDATE pattern lets you add the constraint without taking a long lock; VALIDATE is a separate, online-only scan.
5. ATTACH PARTITION sees the matching CHECK and skips its own bound-validation scan. Without the CHECK, ATTACH scans every row to verify the bound — slow on 100M rows.
6. After ATTACH, the partition bound replaces the CHECK semantically. Dropping the redundant CHECK is optional but keeps the catalog clean.

Output.

step	duration	notes
Load (COPY 100M rows)	4 minutes	bulk-load throughput
Build indexes	6 minutes	off the partition tree
ADD + VALIDATE CHECK	30 seconds	online
ATTACH PARTITION	< 1 second	trusts the CHECK, no scan
Total	~11 minutes	vs ~3 hours for naive INSERT

Rule of thumb. Always ATTACH historical data via the loader-then-attach pattern with a matching CHECK. The skip-validation optimisation is the difference between a maintenance window and an outage on a 100M-row load.

Worked example — DETACH CONCURRENTLY for cold-tier exit

Detailed explanation. A 13-month-old partition needs to be archived to S3 and dropped. The legacy DETACH PARTITION takes an AccessExclusiveLock on the parent — blocks all writes for the duration. PG14+'s DETACH PARTITION ... CONCURRENTLY uses a two-phase protocol that does not block the parent.

Question. Detach events_2025_05, archive it to S3, and drop it — all without blocking writes to the rest of the events table.

Input.

step	command
1	DETACH PARTITION ... CONCURRENTLY
2	Verify partition is now a standalone table
3	Archive to S3
4	DROP the standalone table

Code.

-- Phase 1: DETACH CONCURRENTLY (no AccessExclusiveLock on parent)
ALTER TABLE events DETACH PARTITION events_2025_05 CONCURRENTLY;

-- The detach is now in "pending" state until all in-flight transactions
-- referencing the parent finish. Once they do, the partition is fully
-- standalone.

-- Phase 2: confirm it is fully detached
SELECT relname
FROM pg_partition_tree('events')
WHERE relname = 'events_2025_05';
-- (zero rows = detached)

-- Phase 3: archive to S3 via COPY
COPY events_2025_05 TO PROGRAM
    'zstd | aws s3 cp - s3://archive/events/2025_05.csv.zst'
WITH (FORMAT csv);

-- Phase 4: verify the archive (size + checksum) before dropping
-- ... (out-of-band verification step)

-- Phase 5: drop the standalone table
DROP TABLE events_2025_05;

Step-by-step explanation.

1. DETACH PARTITION ... CONCURRENTLY begins the detach but does not block the parent's writes. Behind the scenes, Postgres uses a two-phase protocol: phase A marks the partition as "detach pending" in a transaction that takes a brief lock; phase B waits for old snapshots to age out, then finalises the detach.
2. During the pending window, the partition behaves like a normal partition (queries against the parent still hit it). Once finalised, it becomes a standalone table — no longer in the partition tree.
3. The pg_partition_tree check confirms detach completion. If it still appears, wait for old snapshots to finish (long-running transactions).
4. COPY ... TO PROGRAM streams the partition data through zstd and uploads to S3 in one pass. Out-of-band, run a verification job that compares row counts and checksums.
5. Only after archive verification do you DROP TABLE to reclaim disk. The detach + archive + drop sequence is the standard cold-tier exit; production uptime is preserved.

Output.

step	parent lock	duration
DETACH CONCURRENTLY phase A	brief (~10 ms)	seconds
DETACH CONCURRENTLY phase B (wait for snapshots)	none	up to seconds
Archive to S3	none	minutes (depends on data)
DROP TABLE standalone	brief on the standalone, none on parent	seconds

Rule of thumb. Always use DETACH CONCURRENTLY on production partitioned tables (PG14+). The legacy DETACH is fine on dev or on tables with no concurrent writes; in production, it's a hidden outage waiting to happen.

Senior interview question on retention automation

A senior interviewer might ask: "Design a retention scheme for an events table that holds 36 months of data, with monthly partitions, where partitions older than 12 months should be archived to cold storage and dropped. What do you automate, what do you monitor, and what failure modes do you plan for?"

Solution Using pg_partman + DETACH CONCURRENTLY + S3 archive pipeline

-- Architecture
-- ============
-- Hot tier (PG): months 0..11 (most recent 12 months)
-- Cold tier (S3 / Glacier): months 12..35 (next 24 months)
-- Deleted: months 36+

-- Step 1: register with pg_partman, but DETACH instead of DROP at retention
SELECT partman.create_parent(
    p_parent_table   => 'public.events',
    p_control        => 'created_at',
    p_type           => 'native',
    p_interval       => 'monthly',
    p_premake        => 3,
    p_default_table  => true
);

UPDATE partman.part_config
SET retention             = '12 months',
    retention_keep_table  = true,                  -- DETACH, do not DROP
    retention_schema      = 'archive_pending'      -- move detached partitions here
WHERE parent_table = 'public.events';

-- Step 2: nightly archive job (custom, outside pg_partman) reads each
-- table in archive_pending, COPYs to S3, drops the local table.

-- Step 3: monitor and alert
-- - rows in events_default > 0  (pg_partman lag or far-future inserts)
-- - tables in archive_pending older than 7 days (archive job stuck)
-- - missing premake partition (next 3 months not all present)

Step-by-step trace.

Day	Action	Affected partitions
Day 1 of each month	pg_partman run_maintenance creates next premake	months ahead
Day 1 of each month	pg_partman DETACHes month-N-12 into archive_pending	oldest in-PG month
Nightly	Archive job COPYs each archive_pending table to S3	newly-detached
Nightly	Archive job verifies and drops the local table	newly-detached
Monitoring	Alert on events_default rows > 0	DEFAULT partition
Monitoring	Alert on archive_pending tables > 7 days old	archive_pending
Monitoring	Alert on missing premake partitions	parent tree

The retention scheme is layered: pg_partman handles the partition lifecycle inside Postgres; a custom archive job handles the S3 export; monitors catch failures in either layer.

Output:

Layer	Responsibility	Tooling
Partition creation	premake next N months	pg_partman.run_maintenance
Partition rollover	DETACH old partitions into archive_pending	pg_partman.run_maintenance
Cold archive	COPY archive_pending → S3, then DROP	custom nightly job
Monitoring	DEFAULT row count, stuck archive, missing premake	Prometheus/Grafana alerts
Failure recovery	re-attach archived partition if needed	manual + restore from S3

Why this works — concept by concept:

• pg_partman owns the lifecycle — partition creation, retention, premake are all in one tool. Hand-rolled cron is fragile; pg_partman is battle-tested and idempotent.
• DETACH first, then archive — never DROP a partition without verifying the archive. DETACH-to-archive_pending is the safety net: if the archive job fails, the data is still there to retry.
• Monitor every layer — the DEFAULT partition row count is the canary for missed premake or wrong inserts; archive_pending age is the canary for stuck archive jobs; missing premake is the canary for pg_partman failure.
• Reversibility — keeping detached partitions in archive_pending for 7 days before drop gives an "undo" window. If a query reveals a data error, you can re-attach within the window.
• Cost — automation is upfront work; the alternative is missed months, lost retention, and panic-driven cleanups. pg_partman + an archive job is 1-2 days of engineering effort that pays back over years.

SQL
Topic — etl
ETL retention pipeline problems

Practice →

SQL Topic — sql Partition maintenance problems

Practice →

---

5. Performance — partition-wise join + parallel

partition wise join runs a join per-partition when both sides align — combined with parallel append, a 30s join can drop to 3s

The mental model in one line: when two partitioned tables share the same partition bounds, Postgres can run the join per-partition (smaller hash tables, parallel workers per partition pair), and partition-wise aggregate does the same for GROUP BY — together with parallel append, a properly-aligned partition design converts a single-thread cross-partition join into N parallel per-partition joins. Once you say "alignment + partition-wise + parallel append," every senior performance probe collapses to "does your join key match the partition key on both sides?"

Partition-wise join — what it is.

• When two partitioned tables share identical partition bounds on the join key, Postgres can join partition-by-partition: t1.part_06 ⋈ t2.part_06, t1.part_07 ⋈ t2.part_07, etc., then Append the results.
• Each per-partition join has a smaller hash table (the partition's rows only) → fits in work_mem more often → fewer spills to disk.
• Per-partition joins are independently parallelisable — N workers can run N partition joins in parallel.
• Controlled by enable_partitionwise_join = on (off by default in many older versions, increasingly on by default in PG16+).

Partition-wise aggregate — the GROUP BY equivalent.

• A GROUP BY over a partitioned table can run per-partition and combine results — same shape, smaller hash tables.
• Especially valuable when the GROUP BY key is the partition key (the result is already per-partition).
• Controlled by enable_partitionwise_aggregate = on.

Alignment requirements — the strict rules.

• Both partitioned tables must use the same partition scheme (both RANGE, or both LIST, or both HASH).
• Both must have the same partition bound expressions — events PARTITION BY RANGE (created_at) joined with event_payloads PARTITION BY RANGE (created_at) works if their bound dates match exactly.
• The join condition must include the partition key — ON events.created_at = event_payloads.created_at AND events.event_id = event_payloads.event_id works; ON events.event_id = event_payloads.event_id alone does not.
• For HASH partitioning: both sides must use the same modulus and the same hash function (Postgres' internal hash).

Parallel partition scan — partitions as a parallelism axis.

• Parallel Append (PG11+) runs multiple partition scans in parallel workers. Independent of partition-wise join.
• Controlled by max_parallel_workers_per_gather (per-query) and max_parallel_workers (cluster-wide).
• The planner picks parallel scan automatically when the cost model favours it; you rarely need to force it.

Settings cheat sheet.

• enable_partitionwise_join = on — enable partition-wise joins (default off in older PG, default on PG16+).
• enable_partitionwise_aggregate = on — enable partition-wise aggregates (default off).
• max_parallel_workers_per_gather = 4 — typical setting for 4 parallel workers per query.
• parallel_setup_cost, parallel_tuple_cost — cost tuning; usually fine at defaults.
• work_mem — per-operation memory budget; partition-wise joins have smaller hash tables, so work_mem is exercised differently than on flat tables.

The "alignment broken" failure mode.

• A team partitions orders by month and order_lines by order_id (HASH). The join orders.id = order_lines.order_id cannot use partition-wise — the partition keys are different.
• Fix options: re-partition order_lines by month (to align), or accept the cross-partition join cost.
• This is the most common production gotcha — designing two partitioned tables independently and discovering at JOIN time that they don't align.

Common interview probes on performance.

• "What is partition-wise join?" — a per-partition join across two partition-aligned tables; smaller hash tables, parallelisable.
• "Why is it off by default?" — historical planner cost-model concerns on small tables; pre-PG16 it could over-estimate. Modern Postgres (16+) is increasingly safe to default on.
• "What's the difference between partition-wise join and parallel append?" — partition-wise is the join algorithm choice (per-partition vs single big hash); parallel append is the executor parallelism (multiple workers scanning partitions). They compose.
• "How do you know partition-wise kicked in?" — EXPLAIN shows Append over per-partition Hash Join nodes instead of one big Hash Join over Appends.

Worked example — partition-wise join on aligned monthly tables

Detailed explanation. A common production join — orders partitioned by placed_at joined with order_lines also partitioned by placed_at (both monthly). With partition-wise join enabled, the planner joins each month's orders to the same month's order_lines — smaller hash tables, parallelisable.

Question. Set up two monthly-partitioned tables that join on placed_at and order_id. Show the EXPLAIN difference with and without partition-wise join.

Input.

table	partition scheme	join column
orders	RANGE (placed_at) monthly	(placed_at, order_id)
order_lines	RANGE (placed_at) monthly	(placed_at, order_id)

Code.

CREATE TABLE orders (
    order_id    BIGINT NOT NULL,
    placed_at   TIMESTAMPTZ NOT NULL,
    customer_id BIGINT NOT NULL,
    PRIMARY KEY (order_id, placed_at)
) PARTITION BY RANGE (placed_at);

CREATE TABLE order_lines (
    line_id     BIGINT,
    order_id    BIGINT NOT NULL,
    placed_at   TIMESTAMPTZ NOT NULL,
    sku         TEXT NOT NULL,
    qty         INT NOT NULL,
    PRIMARY KEY (line_id, placed_at),
    FOREIGN KEY (order_id, placed_at) REFERENCES orders (order_id, placed_at)
) PARTITION BY RANGE (placed_at);

-- Create monthly partitions on BOTH tables with identical bounds
DO $$
DECLARE m DATE := '2026-01-01';
BEGIN
    WHILE m < '2027-01-01' LOOP
        EXECUTE format(
            'CREATE TABLE orders_%s PARTITION OF orders
             FOR VALUES FROM (%L) TO (%L)',
            to_char(m, 'YYYY_MM'), m, m + interval '1 month');
        EXECUTE format(
            'CREATE TABLE order_lines_%s PARTITION OF order_lines
             FOR VALUES FROM (%L) TO (%L)',
            to_char(m, 'YYYY_MM'), m, m + interval '1 month');
        m := m + interval '1 month';
    END LOOP;
END $$;

-- Enable partition-wise join
SET enable_partitionwise_join = on;
SET max_parallel_workers_per_gather = 4;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.customer_id, sum(l.qty)
FROM orders o
JOIN order_lines l
  ON o.order_id = l.order_id
 AND o.placed_at = l.placed_at
WHERE o.placed_at >= '2026-01-01' AND o.placed_at < '2027-01-01'
GROUP BY o.customer_id;

Step-by-step explanation.

1. Both orders and order_lines are partitioned by placed_at with identical monthly bounds. The bounds match exactly: [2026-01-01, 2026-02-01), etc. This is the alignment requirement.
2. The FK on order_lines references (order_id, placed_at) — the partition key is part of the FK so cross-partition FK enforcement is per-partition, not whole-table.
3. With enable_partitionwise_join = on, the planner sees the alignment and generates a plan with N parallel per-partition joins instead of one big cross-partition hash join.
4. Each per-partition hash join builds a hash table from one month's orders and probes it with one month's order_lines. The hash table is ~12x smaller than a full-year hash table.
5. max_parallel_workers_per_gather = 4 lets 4 worker processes pick up partition pairs in parallel. With 12 months and 4 workers, each worker handles ~3 months.
6. The final GROUP BY customer_id aggregates across all partitions — since customer_id is not the partition key, the partition-wise aggregate does NOT kick in for this final step; a single final aggregator combines the per-partition partial aggregates.

Output.

Plan style	join_shape	work_mem usage	wall_time
without partition-wise	1 big Hash Join over Appends	high per join, spills likely	~30s
with partition-wise (1 worker)	Append of 12 small Hash Joins	low per join, no spills	~12s
with partition-wise + 4 parallel workers	parallel Append of 12 small Hash Joins	low per join, no spills	~3s

Rule of thumb. Partition-wise join wins big when (a) both sides have aligned partition bounds and (b) the join condition includes the partition key. If either is missing, you fall back to the single big hash join, which scales worse.

Worked example — partition-wise aggregate on the partition key

Detailed explanation. A GROUP BY on the partition key (or a subset that includes it) is the cleanest partition-wise aggregate case. Each partition's aggregate is final — no cross-partition merge needed.

Question. Compute a monthly count of orders per customer. The GROUP BY includes the partition key (placed_at truncated to month) and the customer. Show that partition-wise aggregate eliminates the final merge step.

Input.

table	partition scheme
orders	RANGE (placed_at) monthly

Code.

SET enable_partitionwise_aggregate = on;
SET max_parallel_workers_per_gather = 4;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT date_trunc('month', placed_at) AS month,
       customer_id,
       count(*) AS order_count
FROM orders
WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01'
GROUP BY date_trunc('month', placed_at), customer_id;

Step-by-step explanation.

1. The GROUP BY includes date_trunc('month', placed_at) — a function of the partition key. Each partition's rows all belong to one truncated month, so each per-partition aggregate is complete (no further merging needed).
2. With enable_partitionwise_aggregate = on, the planner pushes the GROUP BY into each per-partition scan. Each partition emits final (month, customer_id, count) tuples.
3. The top-level Append simply concatenates the per-partition outputs — no merge aggregator, no final hash table.
4. Parallel workers split partitions and run their per-partition aggregates in parallel. With 12 monthly partitions and 4 workers, each worker handles ~3 partitions.
5. The plan shape is: Append over Partial Aggregate per partition, with no Finalize Aggregate step. Compare with the non-partition-wise plan, which has Finalize Aggregate → Gather → Partial Aggregate → Append — extra steps.

Output.

Plan style	extra_step_count	wall_time
without partition-wise aggregate	Final Aggregate + cross-partition merge	~8s
with partition-wise aggregate	per-partition Final Aggregate, no merge	~2s

Rule of thumb. When the GROUP BY key includes the partition key (or a function of it), partition-wise aggregate is a near-instant win. When it doesn't, the partition-wise aggregate is partial only; a final merge step still runs but each partial aggregate is smaller.

Worked example — broken alignment kills partition-wise

Detailed explanation. A team partitioned orders by placed_at (monthly) but partitioned order_lines by HASH(order_id). The join orders.id = order_lines.order_id cannot use partition-wise — the partition keys don't match. Even with enable_partitionwise_join = on, the planner falls back to a single big hash join.

Question. Show the EXPLAIN output when partition alignment is broken, identify the symptom, and walk through the two fix options.

Input.

table	partition scheme
orders	RANGE (placed_at) monthly
order_lines	HASH (order_id) 16 buckets

Code.

CREATE TABLE order_lines_broken (
    line_id  BIGINT,
    order_id BIGINT NOT NULL,
    sku      TEXT NOT NULL,
    qty      INT NOT NULL,
    PRIMARY KEY (line_id, order_id)
) PARTITION BY HASH (order_id);

-- Create 16 HASH partitions
DO $$
BEGIN
    FOR i IN 0..15 LOOP
        EXECUTE format(
            'CREATE TABLE order_lines_broken_%s PARTITION OF order_lines_broken
             FOR VALUES WITH (MODULUS 16, REMAINDER %s)',
            i, i);
    END LOOP;
END $$;

SET enable_partitionwise_join = on;

EXPLAIN (ANALYZE)
SELECT o.customer_id, sum(l.qty)
FROM orders o
JOIN order_lines_broken l ON o.order_id = l.order_id
WHERE o.placed_at >= '2026-01-01'
GROUP BY o.customer_id;
-- Plan: one big Hash Join — partition-wise did NOT kick in

Step-by-step explanation.

1. orders is partitioned by RANGE on placed_at; order_lines_broken is partitioned by HASH on order_id. The partition keys differ — no alignment.
2. Even with enable_partitionwise_join = on, the planner cannot use partition-wise. The two tables' partition bounds do not correspond; per-partition join would require shuffling rows across partition boundaries, defeating the optimisation.
3. The EXPLAIN shows a single big Hash Join over an Append from each side. The hash table is the size of the entire orders slice; spills to disk are likely on large data.
4. Fix option A — re-partition order_lines by placed_at to align. This requires schema redesign + bulk re-load via the loader-and-attach pattern.
5. Fix option B — accept the cross-partition join cost and tune work_mem to fit the hash table in memory. Works for moderate data; fails on multi-TB joins.

Output.

Setup	partition-wise kicked in?	wall_time
aligned monthly on both sides	yes	~3s
broken: monthly + hash	no	~25s

Rule of thumb. Partition-wise join requires that both tables share the same partition scheme and the same bound expressions on the join column. Design partitioned tables together — never independently — when they participate in joins.

Senior interview question on performance tuning

A senior interviewer might frame this as: "A partitioned orders table joined to a partitioned order_lines table runs for 30 seconds. Walk me through every knob you'd try to bring it under 5 seconds, and how you'd verify each change."

Solution Using partition-wise join + parallel append + work_mem tuning

-- Step 0: baseline — measure with EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ... FROM orders o JOIN order_lines l ON ... WHERE ...;
-- Wall time: 30s

-- Step 1: confirm partition alignment
SELECT pg_get_expr(relpartbound, oid)
FROM pg_class
WHERE oid = ANY (SELECT relid FROM pg_partition_tree('orders') WHERE isleaf);
-- Must match the same SELECT for order_lines

-- Step 2: enable partition-wise join + aggregate
SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;
-- Re-run EXPLAIN ANALYZE — wall time drops to ~12s with 1 worker

-- Step 3: enable parallel append + more workers
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 100;
-- Re-run — drops to ~4s with 4 parallel workers

-- Step 4: tune work_mem so each per-partition hash join fits in memory
-- Without spilling
SET work_mem = '128MB';   -- per operation
-- Re-run — drops to ~3s

-- Step 5: confirm via EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ... FROM orders o JOIN order_lines l ON ... WHERE ...;
-- Look for:
--   Parallel Append
--   per-partition Hash Join nodes
--   no "external merge" hash spills in BUFFERS

Step-by-step trace.

Step	Setting	wall_time	Note
baseline	defaults	30s	one big hash join, no parallel
confirm alignment	pg_partition_tree matches on both sides	—	precondition
partition-wise on	enable_partitionwise_join=on	12s	per-partition joins
parallel append	max_parallel_workers_per_gather=4	4s	4 parallel workers
work_mem tuned	work_mem=128MB	3s	no hash spills
partition-wise agg	enable_partitionwise_aggregate=on	2.5s	per-partition GROUP BY

After the tuning pass, the query runs ~12x faster than the baseline. Every step is verifiable via EXPLAIN.

Output:

Knob	Effect	EXPLAIN signal
enable_partitionwise_join=on	per-partition Hash Join	Append over Hash Join, not Hash Join over Append
enable_partitionwise_aggregate=on	per-partition GROUP BY	per-partition Final Aggregate
max_parallel_workers_per_gather	parallel workers per query	Parallel Append, Workers Launched: N
work_mem	avoid spill to disk	no "external merge" in Sort or "batches" > 1 in Hash

Why this works — concept by concept:

• Partition-wise gives N smaller joins — each per-partition hash table is 1/N the size of the cross-partition one. Smaller hash tables fit in work_mem, avoid spill, and run faster individually.
• Parallel append composes with partition-wise — once the join is N small joins, N parallel workers can run them simultaneously. Linear speedup until you hit CPU or I/O ceilings.
• work_mem matters per-operation — Postgres applies work_mem per hash-join, per sort, per per-partition agg. Setting it too low triggers disk spills; too high and concurrent queries OOM the server.
• Alignment is the precondition — without identical partition bounds and a join condition on the partition key, partition-wise does not engage. Design partitioned tables together when they will be joined.
• Cost — tuning is a one-time win on a long-lived query. The diagnostic + EXPLAIN walk takes 30-60 minutes; the query speedup pays back every time the query runs.

SQL
Topic — joins
Partition-wise join problems

Practice →

SQL
Topic — optimization
Performance tuning drills

Practice →

---

Cheat sheet — Postgres partitioning recipes

• RANGE template (time-series). CREATE TABLE events (... created_at TIMESTAMPTZ NOT NULL, PRIMARY KEY (event_id, created_at)) PARTITION BY RANGE (created_at); then one child per month with FOR VALUES FROM (lo) TO (hi). Lo inclusive, hi exclusive — the most common off-by-one trap.
• LIST template (discrete categories). PARTITION BY LIST (country) + FOR VALUES IN ('US', 'CA', ...) + a DEFAULT partition for safety. Up to ~200 values; above that, switch to HASH.
• HASH template (high-cardinality equality). PARTITION BY HASH (user_id) + N children with FOR VALUES WITH (MODULUS N, REMAINDER 0..N-1). Equality prunes; range does not.
• pg_partman monthly automation. partman.create_parent(..., p_interval => 'monthly', p_premake => 3) + nightly run_maintenance via cron. Sets retention for auto-drop or retention_keep_table=true for archive-then-drop.
• ATTACH skip-validation. Load into a standalone table, add a matching CHECK constraint, ATTACH PARTITION — Postgres trusts the CHECK and skips the bound-validation scan. 10x+ faster than ATTACH-then-validate.
• DETACH CONCURRENTLY (PG14+). ALTER TABLE parent DETACH PARTITION child CONCURRENTLY; — no AccessExclusiveLock on the parent. Required for online operation on production tables.
• Pruning sanity-check. EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ... — look for single-child Append (plan-time) or Subplans Removed: N (runtime). Multi-child Append with no Removed is the bug.
• Function volatility for pruning. Predicate functions must be IMMUTABLE (folded at plan time) or STABLE (folded at execute time). VOLATILE functions break pruning silently.
• Partition-wise join settings. SET enable_partitionwise_join = on; SET enable_partitionwise_aggregate = on; SET max_parallel_workers_per_gather = 4; — usually safe at session level; consider cluster-wide once monitored.
• Alignment requirements. For partition-wise join: both tables RANGE (or both LIST, or both HASH); identical bound expressions; join condition includes the partition key. Any one missing and partition-wise does not engage.
• FK with partition key. Use FOREIGN KEY (order_id, placed_at) REFERENCES orders (order_id, placed_at) so the FK includes the partition key — required for per-partition enforcement and partition-wise joins.
• UNIQUE with partition key. Unique constraints must include the partition key column — Postgres cannot efficiently enforce uniqueness across partitions without it. Workaround: use a BIGSERIAL event_id + the partition key in the PK.
• DEFAULT partition canary. Always create a DEFAULT partition for LIST and RANGE schemes. Monitor events_default.row_count == 0 as a missed-partition canary.
• pg_partition_tree introspection. SELECT relname, pg_get_expr(relpartbound, oid), reltuples FROM pg_partition_tree(parent) JOIN pg_class ON ... — the catalog query every senior DBA runs first on a new partitioned table.
• work_mem tuning per query. Partition-wise joins exercise work_mem per per-partition hash. Set work_mem = '64MB' to '256MB' per session for heavy analytics queries; never globally to large values.

Frequently asked questions

When should I partition a Postgres table by RANGE vs LIST vs HASH?

Pick RANGE when the partition key is orderable and queries use range predicates (BETWEEN, <, >) — the canonical case is time-series (created_at), and the strongest signal is "we drop old data on a schedule" (RANGE + DROP PARTITION beats DELETE every time). Pick LIST when the partition key has a bounded discrete value set (country codes, tenant tiers, event types) and queries equality-filter on it; the practical ceiling is around 200 values before the planner's linear-scan-of-bounds cost makes HASH cheaper. Pick HASH when the key is high-cardinality (user_id, tenant_id, session_id), equality lookups dominate, and you want even row distribution across partitions for parallel work — HASH never prunes on range predicates, only on equality. Sub-partition only when two access patterns share top priority (time AND tenant), and never deeper than two levels. The postgres partitioning interview question almost always reduces to "what is the WHERE clause of the queries you care about?"

Why isn't my partition pruning happening?

Four root causes account for 95% of "no pruning" bugs. First, the partition key is missing from WHERE — the planner has nothing to prune by. Second, a VOLATILE function is in the predicate; mark it STABLE or IMMUTABLE if it's deterministic. Third, a type mismatch on the partition-key column (e.g. TEXT vs TIMESTAMPTZ) forces an implicit cast that confuses bound-matching; cast the literal explicitly. Fourth, a generic plan estimates poorly — PG12+'s runtime partition pruning fixes most of these by re-evaluating parameters at execute time, but the cluster must be on a recent enough version. Diagnose with EXPLAIN (ANALYZE, BUFFERS, VERBOSE): a single-child Append confirms plan-time pruning; Subplans Removed: N confirms runtime pruning; a multi-child Append with Subplans Removed: 0 is the bug.

pg_partman vs hand-rolled cron — which one should I use?

Use pg_partman for every time-based partitioning automation. The extension handles premake, retention, default partition creation, and detach-or-drop semantics in one configuration call. Hand-rolled cron jobs work in theory but drift in practice: a missed run during a leap year, a daylight-saving boundary, a schema migration that didn't update the cron script, and you have a missing partition that quietly routes rows to DEFAULT. pg_partman is idempotent, battle-tested at scale, and supports both monthly/weekly/daily intervals and custom intervals; its run_maintenance proc is the only function you have to schedule. Hand-roll only if you have a partition pattern pg_partman doesn't model (e.g. fiscal-quarter boundaries that don't align with calendar months), and even then most teams write a tiny pg_partman wrapper rather than a full custom system.

What works and what doesn't with foreign keys on partitioned tables?

FK FROM a non-partitioned table TO a partitioned table works fully in PG12+; the constraint targets the root partitioned table and Postgres enforces it across all partitions transparently. FK FROM a partitioned table TO a non-partitioned table has worked since PG11. FK between two partitioned tables works in PG12+, but both sides must include the partition key in the FK so per-partition enforcement is possible (e.g. FOREIGN KEY (order_id, placed_at) REFERENCES orders (order_id, placed_at)). Cascading deletes across partitions work but can be slow on multi-partition cascades; consider per-partition delete jobs for retention-driven cleanup. UNIQUE across partitions is the hardest limit — a unique index must include the partition key column, so you cannot enforce global uniqueness on email if email is not the partition key. The workaround is a unique index on (email, partition_key) plus an application-level dedup check, or denormalising email lookups into a separate small table.

How many partitions can Postgres handle before planner overhead becomes a problem?

Modern Postgres (PG14+) comfortably handles a few thousand partitions in a single tree. The planner cost is O(partitions) per query for the bound walk, so 100 partitions add microseconds; 10,000 partitions add milliseconds; 100,000 partitions starts to hurt for short queries. The practical ceiling for time-series at monthly intervals is around 240 (20 years of data) — comfortably inside the comfort zone. If you find yourself approaching 5,000+ partitions, the design smell is usually too-fine partitioning: daily partitioning for low-volume data (switch to monthly), or sub-partitioning beyond two levels (collapse one level). A second factor is max_locks_per_transaction — a query that touches every partition acquires N locks; if N approaches max_locks_per_transaction, the query errors. Tune max_locks_per_transaction upward (default 64) if you intentionally run cross-partition queries. The postgres native partitioning ceiling has risen significantly through PG13/14/15/16; numbers from 2020 advice (200 partitions max) no longer apply.

What are the requirements for partition-wise join to actually kick in?

Three alignment requirements must all hold. First, both partitioned tables must use the same partition scheme — both RANGE, or both LIST, or both HASH; mixed schemes never work. Second, the partition bounds must be identical on both sides — for RANGE, the same FOR VALUES FROM (lo) TO (hi) on every matching child; for HASH, the same MODULUS and REMAINDER. Third, the join condition must include the partition key — ON t1.placed_at = t2.placed_at AND t1.order_id = t2.order_id works; ON t1.order_id = t2.order_id alone does not. Plus the session settings: enable_partitionwise_join = on and enable_partitionwise_aggregate = on for the GROUP BY case (both off by default in older PG, increasingly default on in PG16+). Confirm via EXPLAIN (ANALYZE) — look for an Append over per-partition Hash Join nodes instead of one big Hash Join over Appends. With max_parallel_workers_per_gather = 4, the partition-wise plan also runs in parallel: a 30-second cross-partition join routinely drops to 3 seconds.

Practice on PipeCode

• Drill the SQL practice library → for the partition-key design, schema, and EXPLAIN family of probes.
• Sharpen on query optimisation problems → when the interviewer wants pruning and tuning depth.
• Stack the JOIN practice library → for the partition-wise join family of probes and alignment design.
• Layer the ETL practice library → for the retention pipeline + cold-tier archive patterns that pair with partition maintenance.

Lock in Postgres partitioning muscle memory

Postgres docs explain declarative partitioning. PipeCode drills explain the decision — when RANGE beats LIST, when pruning fails silently, when partition-wise join saves a 30s query. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice JOIN problems →