Big-Data Partitioning Strategies: Range, Hash & List

partitioning strategies are the physical-layout decision that determines whether a query against a billion-row table touches one gigabyte or one terabyte — and it is the single design choice a data engineer gets wrong most often, because a table that "works fine" at ten million rows silently becomes a full-scan disaster at ten billion. Every large table you own — the events stream, the orders ledger, the audit trail, the clickstream lake — has to be sliced into smaller physical chunks so that a query reads only the chunks it needs, so that old data can be retired without a giant DELETE, and so that many workers can scan different chunks at once. The engineering trade-off does not live in "should we partition" — every table past a certain size needs it — but in which key you partition on and how the query predicate lines up against that key.

This guide is the walkthrough you wished existed the first time an interviewer asked "you have a two-billion-row events table — how would you partition it, and prove that a date-ranged query prunes to one partition?" It opens the layout in three schemes: range partitioning (slice by an ordered key like a date, so time-series retention becomes a partition drop), hash partitioning and bucketing (spread rows evenly by hash(key) % N so no single chunk runs hot and co-partitioned joins skip the shuffle), and list / composite partitioning (map explicit values like region to partitions, then subpartition for two-axis pruning). Along the way it covers the mechanics that make or break every scheme — partition pruning (the predicate must match the key or you scan everything), data skew (the hot partition that eats a whole worker), and how partitioning differs from sharding across machines. Each 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. All examples are PostgreSQL declarative partitioning plus Spark/Hive bucketing, but the mental model carries to BigQuery, Snowflake, Hive, and Iceberg.

When you want hands-on reps immediately after reading, drill the database practice library →, sharpen the plan-reading axis on the optimization practice library →, and rehearse even-distribution joins on the bucketing practice library →.

On this page

• Why partition — pruning, parallelism, and retention
• Range partitioning — time-series and retention
• Hash partitioning and bucketing — even distribution
• List and composite partitioning
• Partition pruning, skew, and choosing a scheme
• Cheat sheet — partitioning recipes
• Frequently asked questions
• Practice on PipeCode

1. Why partition — pruning, parallelism, and retention

Three schemes, three payoffs — the key you pick binds every query and retention job for years

The one-sentence invariant: partitioning is the physical decomposition of one logical table into many smaller physical chunks along a chosen key, so that queries whose predicate matches the key read only the relevant chunks (pruning), so that independent chunks can be scanned concurrently (parallelism), and so that a whole slice of history can be retired by dropping a chunk instead of deleting rows — and the key you choose, together with the number of partitions, is a decision that every downstream query, index, and retention job hard-codes assumptions about. The scheme you pick in month one becomes the scheme you fight to migrate away from in year three, because a repartition of a billion-row table is a rewrite of the entire table, and every dashboard, every join, and every retention job was written expecting the old key.

The three payoffs that justify partitioning at all.

• Partition pruning — scan less. If the table is partitioned by event_day and the query says WHERE event_day = '2026-09-01', the planner reads exactly one partition and skips the rest. This is the headline win: query cost drops from O(whole table) to O(one partition). Pruning only fires when the predicate references the partition key — get the key wrong and every query scans everything.
• Parallelism — scan concurrently. Independent partitions live in separate files (or separate heaps), so a scan can dispatch one worker per partition. A hash-partitioned table with 64 buckets can feed 64 parallel readers; a monolithic table serialises on one heap. Partitioning is the precondition for partition-wise joins and parallel aggregation.
• Cheap retention — drop, don't delete. A range-partitioned table with one partition per day retires 90-day-old data with DROP TABLE events_20260601 — an O(1) catalog operation that reclaims the disk instantly. The alternative, DELETE FROM events WHERE event_day < ..., scans and marks millions of rows, bloats the table, and triggers a vacuum storm. Retention-by-drop is the reason time-series tables are almost always range-partitioned.

The axes that matter.

• The partition key. The single most important choice. It must be the column your hottest queries filter on, because pruning only fires on the key. Time-series → a date/timestamp. High-cardinality entity lookups → a hashed id. Known categorical splits → a region/tenant list. Pick the key from the query predicate, not from intuition.
• The number of partitions. Too few and each partition is still huge (no pruning benefit). Too many and the planner spends more time pruning the partition list than scanning data, and per-partition overhead (files, indexes, catalog rows) dominates. The sweet spot is usually hundreds to low thousands, with each partition sized in the low-GB range.
• The predicate alignment. Pruning is not automatic — it requires the query's WHERE to reference the partition key with a prunable operator (=, IN, range comparisons on the key). A query that filters on a non-key column reads every partition. Half of all "why is my partitioned table slow" incidents are predicate/key misalignment.

The 2026 reality — three schemes, one skew failure mode.

• Range is the default for anything time-ordered: events, logs, orders-by-date, metrics. One partition per hour/day/month; pruning on date ranges; retention by dropping old partitions. The risk is an unbounded "current" partition if the interval is too coarse.
• Hash is the default when there is no natural order but you need even spread across a high-cardinality key (user_id, order_id). hash(key) % N guarantees roughly equal partition sizes and enables shuffle-free co-partitioned joins. The risk is that you cannot prune a range query (there is no ordering).
• List is the default when the key is a small set of known categories: region IN ('US','EU','APAC'), tenant_id for a handful of big tenants, status. Explicit value→partition mapping; a DEFAULT partition catches the rest. The risk is skew when one category dwarfs the others.
• Data skew is the failure mode that stalks all three: one partition ends up far larger or far hotter than the others, so one worker does most of the work while the rest idle. Detecting and fixing skew (salting, sub-bucketing, splitting the hot range) is the senior differentiator.

What interviewers listen for.

• Do you say "pruning only fires when the predicate matches the partition key" unprompted? — required answer.
• Do you name retention-by-drop as the reason time-series tables are range-partitioned, rather than "it's faster"? — senior signal.
• Do you distinguish partitioning (within one table/engine) from sharding (across machines) cleanly? — senior signal.
• Do you raise data skew as the risk of any scheme, and name a fix (salting / sub-bucketing)? — senior signal.
• Do you size the partition count deliberately (hundreds–low-thousands, low-GB each) instead of "one per day forever"? — senior signal.

Worked example — the three-scheme comparison table

Detailed explanation. The single most useful artifact for a partitioning interview is a memorised comparison of the three schemes across the axes that decide the pick. Every senior partitioning discussion converges on this table; having it in your head separates a fluent answer from a stumbling one. Walk through building it for a hypothetical events table that must serve date-ranged analytics, per-user lookups, and per-region compliance queries.

• Source table. public.events (event_id, user_id, region, event_day, payload) — two billion rows on Postgres 16.
• Query mix. (a) "last 7 days of events" (date range), (b) "all events for user 42" (entity lookup), (c) "all EU events" (categorical).
• Retention. Drop data older than 90 days cheaply.
• Concern. No single partition should run hot.

Question. Build the three-scheme comparison for events and pick the scheme each query pattern favours.

Input.

Code.

-- Postgres: one parent table, three candidate partition schemes.

-- Scheme A — RANGE by day (time-series default)
CREATE TABLE events_range (
    event_id   BIGINT,
    user_id    BIGINT,
    region     TEXT,
    event_day  DATE        NOT NULL,
    payload    JSONB
) PARTITION BY RANGE (event_day);

-- Scheme B — HASH by user_id (even spread, entity lookups)
CREATE TABLE events_hash (
    event_id   BIGINT,
    user_id    BIGINT      NOT NULL,
    region     TEXT,
    event_day  DATE,
    payload    JSONB
) PARTITION BY HASH (user_id);

-- Scheme C — LIST by region (known categories)
CREATE TABLE events_list (
    event_id   BIGINT,
    user_id    BIGINT,
    region     TEXT        NOT NULL,
    event_day  DATE,
    payload    JSONB
) PARTITION BY LIST (region);

Step-by-step explanation.

1. The PARTITION BY RANGE (event_day) parent declares the strategy but holds no data itself — every row must land in a child partition whose bounds contain its event_day. This is why a range table needs partitions created ahead of time (or a default) or inserts fail.
2. The PARTITION BY HASH (user_id) parent spreads rows by an internal hash modulus. You create N child partitions with MODULUS N, REMAINDER i; Postgres routes each row to the child whose remainder matches hash(user_id) mod N. Sizes come out roughly equal for any high-cardinality key.
3. The PARTITION BY LIST (region) parent routes rows by explicit value membership. You declare FOR VALUES IN ('US'), FOR VALUES IN ('EU'), etc., plus optionally a DEFAULT partition for unlisted values.
4. The choice is query-driven. Query (a) "last 7 days" prunes only under RANGE. Query (b) "user 42" prunes under HASH (equality on the hash key). Query (c) "all EU" prunes under LIST. No single scheme prunes all three — which is exactly why composite partitioning (section 4) exists.
5. In practice most large tables pick the scheme that matches their dominant access pattern and accept full scans for the minority patterns (often served by a secondary index or a separately-partitioned copy).

Output.

Rule of thumb. Never pick a partition scheme by intuition. Pick it from the dominant query predicate: date-range → RANGE, entity-equality → HASH, known-category → LIST. Write the query mix down first; the scheme falls out of which predicate must prune.

Worked example — proving pruning actually fires

Detailed explanation. A partition scheme that does not prune is worse than no partitioning — you pay per-partition overhead and still scan everything. Every senior engineer proves pruning with EXPLAIN before declaring victory. Walk through a range-partitioned events table and confirm that a date predicate reads one partition while a non-key predicate reads all of them.

• Setup. events_range partitioned by day, with three daily partitions loaded.
• Good query. WHERE event_day = '2026-09-02' — references the key.
• Bad query. WHERE user_id = 42 — references a non-key column.

Question. Show the EXPLAIN output that proves the date query prunes to one partition and the user query scans all partitions.

Input.

Code.

-- Daily partitions
CREATE TABLE events_range_20260901 PARTITION OF events_range
  FOR VALUES FROM ('2026-09-01') TO ('2026-09-02');
CREATE TABLE events_range_20260902 PARTITION OF events_range
  FOR VALUES FROM ('2026-09-02') TO ('2026-09-03');
CREATE TABLE events_range_20260903 PARTITION OF events_range
  FOR VALUES FROM ('2026-09-03') TO ('2026-09-04');

-- Prunable query — predicate matches the partition key
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events_range
WHERE  event_day = '2026-09-02';
--                QUERY PLAN
-- Aggregate
--   ->  Seq Scan on events_range_20260902 events_range
--         Filter: (event_day = '2026-09-02')
-- (only ONE partition appears — the other two were pruned)

-- Non-prunable query — predicate on a non-key column
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events_range
WHERE  user_id = 42;
--                QUERY PLAN
-- Aggregate
--   ->  Append
--         ->  Seq Scan on events_range_20260901 ...
--         ->  Seq Scan on events_range_20260902 ...
--         ->  Seq Scan on events_range_20260903 ...
-- (ALL three partitions scanned — no pruning)

Step-by-step explanation.

1. The EXPLAIN (COSTS OFF) output is the ground truth for pruning. When the plan lists a single child partition under the scan node, the other partitions were pruned at plan time (static pruning). When it lists an Append over every child, nothing pruned.
2. The date query WHERE event_day = '2026-09-02' references the partition key with an equality operator, so the planner evaluates each partition's bound constraint (FROM '2026-09-02' TO '2026-09-03') and keeps only the matching child. Cost collapses to one partition.
3. The user query WHERE user_id = 42 references a column that is not the partition key. The planner cannot use partition bounds to exclude any child, so it produces an Append over all three and filters inside each. This is the classic "partitioned but not pruning" trap.
4. A range predicate (event_day >= '2026-09-01') prunes to the subset of partitions whose bounds overlap the range — here two of three. Range operators prune under RANGE partitioning; they do not prune under HASH (there is no ordering to compare against).
5. The fix for the user query, if per-user lookups are hot, is either a secondary index on user_id (Postgres can create a partitioned index that propagates to every child) or a second copy of the data hash-partitioned by user_id. You cannot make one key serve two unrelated predicates for free.

Output.

Rule of thumb. Always confirm pruning with EXPLAIN before shipping a partition scheme. If the plan shows an Append over every child for your hottest query, the key does not match the predicate — repartition or add a secondary index. A partitioned table that never prunes is pure overhead.

Worked example — partitioning vs sharding

Detailed explanation. Interviewers love to probe whether you conflate partitioning with sharding, because the words are used loosely. Partitioning splits one table into chunks within one database or engine; sharding splits data across independent machines that do not share a query planner. Both use the same keys (range, hash, list) but solve different problems. Walk through the distinction with a concrete orders example.

• Partitioning. orders split into 64 hash partitions inside one Postgres. One planner, one connection, pruning across local children.
• Sharding. orders split across 8 Postgres servers by hash(customer_id) % 8. Eight independent databases; a routing layer picks the shard; no cross-shard planner.

Question. Contrast partitioning and sharding for a growing orders table and state when each is the right escalation.

Input.

Code.

# Sharding router — pick the shard for a customer, then talk to that DB.
# (Partitioning needs no such router; the single engine routes internally.)
import hashlib

SHARDS = {
    0: "postgres://orders-shard-0.internal/orders",
    1: "postgres://orders-shard-1.internal/orders",
    2: "postgres://orders-shard-2.internal/orders",
    3: "postgres://orders-shard-3.internal/orders",
    4: "postgres://orders-shard-4.internal/orders",
    5: "postgres://orders-shard-5.internal/orders",
    6: "postgres://orders-shard-6.internal/orders",
    7: "postgres://orders-shard-7.internal/orders",
}

def shard_for(customer_id: int) -> str:
    """Route a customer to one of 8 physical shards by hash."""
    h = int(hashlib.sha256(str(customer_id).encode()).hexdigest(), 16)
    return SHARDS[h % len(SHARDS)]

# A single-customer query hits exactly one shard (like pruning to one partition).
dsn = shard_for(42)          # e.g. orders-shard-2

# A cross-customer aggregate must scatter-gather across ALL shards
# and re-aggregate in the application — there is no shared planner.
def total_revenue_all_customers():
    subtotals = [query_shard(dsn) for dsn in SHARDS.values()]
    return sum(subtotals)

Step-by-step explanation.

1. Partitioning keeps one logical table and one query engine. The engine's planner prunes across local partitions and can do partition-wise joins — everything stays inside one transaction boundary and one connection. You scale scan parallelism and storage, but all data still lives on machines that share a planner.
2. Sharding removes the shared planner entirely. Data lives on independent servers; an application-level router (or a proxy like Vitess/Citus) computes the shard from the key and opens a connection to just that server. This scales write throughput and total capacity beyond a single machine — the thing partitioning alone cannot do.
3. Both use the same key math. hash(customer_id) % 8 picks a shard exactly like hash(customer_id) mod 64 picks a partition. The difference is where the split lands (across boxes vs within one), not the arithmetic.
4. The cost of sharding is cross-shard queries. A single-customer lookup hits one shard (great). A "total revenue across all customers" query must scatter to every shard and re-aggregate in the app — there is no engine to join for you. Choosing the shard key so that the hot queries stay single-shard is the whole game.
5. The escalation ladder: start with one table, add partitioning when scans/retention hurt, add sharding only when a single machine cannot hold the write volume or storage. Sharding is strictly more operational overhead; reach for it last.

Output.

Rule of thumb. Partitioning is "one table, many chunks, one planner"; sharding is "many machines, no shared planner". Use the same key math for both, keep hot queries single-partition and single-shard, and escalate from partitioning to sharding only when one machine genuinely runs out of write or storage headroom.

Data engineering interview question on choosing a partition scheme

A senior interviewer often opens with: "You own a two-billion-row events table on Postgres that today is a single unpartitioned heap. Queries filter mostly by event_day for the last 7–30 days, retention is 90 days, and nightly the analytics team also runs per-region rollups. Walk me through the partition scheme you'd choose, prove that the hot queries prune, and explain how retention and skew are handled."

Solution Using range-by-day partitioning with a region-aware secondary index and drop-based retention

-- 1. Parent — RANGE by event_day (matches the dominant date predicate)
CREATE TABLE events (
    event_id   BIGINT       NOT NULL,
    user_id    BIGINT       NOT NULL,
    region     TEXT         NOT NULL,
    event_day  DATE         NOT NULL,
    payload    JSONB,
    PRIMARY KEY (event_id, event_day)      -- key must include the partition column
) PARTITION BY RANGE (event_day);

-- 2. Daily partitions (create ahead of time; automate with pg_partman)
CREATE TABLE events_20260901 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-09-02');
CREATE TABLE events_20260902 PARTITION OF events
  FOR VALUES FROM ('2026-09-02') TO ('2026-09-03');
-- ... one per day, ~90 live at any time ...

-- 3. A DEFAULT partition so an out-of-range insert never fails the pipeline
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- 4. Partitioned secondary index for the per-region rollup predicate.
--    Declared once on the parent; Postgres creates it on every child.
CREATE INDEX idx_events_region_day ON events (region, event_day);

-- 5. Hot query prunes to the last 7 days (7 partitions, not 2B rows)
EXPLAIN (COSTS OFF)
SELECT region, count(*)
FROM   events
WHERE  event_day >= CURRENT_DATE - INTERVAL '7 days'
GROUP  BY region;

-- 6. Retention — drop the oldest day in O(1) once per night
DROP TABLE IF EXISTS events_20260601;   -- 90 days ago

Step-by-step trace.

After the migration, the 7-day analytics query prunes to 7 daily partitions (~150M rows scanned instead of 2B), the nightly region rollup uses the (region, event_day) index within those pruned partitions, retention runs as a single DROP TABLE per day, and a mis-dated event lands in the DEFAULT partition instead of erroring the ingest.

Output:

Why this works — concept by concept:

• Range key = query predicate — the partition key event_day is exactly the column the hot queries filter on, so static pruning fires and the 7-day query reads 7 partitions instead of the whole heap. Aligning key to predicate is the entire source of the speedup.
• Daily interval sizing — one partition per day yields ~90 live partitions at 90-day retention, each in the low-GB range. Coarser (monthly) would leave the current partition huge; finer (hourly) would explode the partition count and planning time.
• Retention by DROP — dropping a whole partition is a catalog operation that reclaims disk instantly, avoiding the DELETE scan, the dead-tuple bloat, and the vacuum pressure that make time-series retention painful on an unpartitioned table.
• Partitioned secondary index — declaring (region, event_day) on the parent propagates the index to every child, so the minority per-region predicate is served without a second copy of the data. Pruning narrows to the day, the index narrows to the region.
• Cost — 90 partitions + one propagated index + a nightly DROP/CREATE. Scan cost drops from O(2B) to O(days-in-range × rows-per-day); retention drops from O(rows-deleted) to O(1). The one overhead is pre-creating partitions (automated by pg_partman) so inserts never hit the DEFAULT.

SQL
Topic — database
Database partitioning and pruning problems

Practice →

Practice →

2. Range partitioning — time-series and retention

PARTITION BY RANGE (date) slices an ordered key into intervals — the default for time-series, and the reason retention is a DROP, not a DELETE

The mental model in one line: range partitioning splits a table by an ordered key — almost always a date or timestamp — into one partition per interval, so that a query filtering on a date range prunes to the overlapping partitions, so that old data is retired by dropping whole partitions, and so that ingestion always appends to the newest partition — it is the correct default for any append-mostly, time-ordered dataset, and its one hazard is an oversized "current" partition when the interval is too coarse. Every senior data engineer has built one; range partitioning is the workhorse of the analytics warehouse.

The axes for range partitioning.

• Key. An ordered column — event_day DATE, created_at TIMESTAMPTZ, or occasionally a monotonic id. The key must be ordered so that range comparisons (>=, <, BETWEEN) can prune. Ordering is what distinguishes range from hash.
• Interval sizing. Hourly, daily, weekly, monthly. Pick the interval so each partition sits in the low-GB range and the live partition count stays in the hundreds. Daily is the most common default; hourly for very high volume; monthly for modest volume with long retention.
• Pruning. A predicate on the key with =, IN, or a range operator prunes to the overlapping partitions. WHERE created_at >= '2026-09-01' AND created_at < '2026-09-08' prunes to seven daily partitions.
• Retention. DROP TABLE events_20260601 removes a whole interval in O(1). This is range partitioning's signature advantage and the single biggest reason to choose it for time-series.

The boundary rules every range table must get right.

• Bounds are half-open. FROM ('2026-09-01') TO ('2026-09-02') includes Sep-01 00:00 and excludes Sep-02 00:00. Adjacent partitions must chain exactly (TO of one equals FROM of the next) or you leave a gap that rejects inserts.
• The DEFAULT partition. A DEFAULT catch-all absorbs rows whose key falls outside every declared range. Without it, an out-of-range insert errors — which will eventually take down an ingestion pipeline when someone backfills a stray date.
• Pre-create ahead of time. Partitions must exist before the interval starts, or the first insert of the new day fails (or lands in DEFAULT). Automate creation with pg_partman (Postgres), or the engine's native auto-partitioning (BigQuery/Snowflake create date partitions implicitly).
• The PK must include the key. Postgres requires the partition column to be part of any unique constraint. PRIMARY KEY (event_id, event_day) — the composite is mandatory because uniqueness can only be enforced per-partition.

The current-partition hazard.

• The problem. If the interval is monthly and today is the 28th, the current partition already holds 28 days of data and every "today" query scans all of it. The newest partition is always the hottest and, with a coarse interval, the biggest.
• The fix. Size the interval to the query granularity. If most queries ask for "today" or "last 24h", use daily (or hourly) partitions so "today" is one small partition, not a slice of a giant month.
• Sub-partitioning. For extreme volume, composite-partition the current range by a second key (section 4) so even the hot interval is split.

Common interview probes on range partitioning.

• "How does range partitioning make retention cheap?" — required answer: DROP PARTITION is O(1) vs DELETE scanning rows.
• "What happens to an insert whose date is outside every partition?" — errors, unless a DEFAULT partition exists.
• "Why must the partition key be in the primary key?" — uniqueness is enforced per-partition; the engine needs the key to route.
• "When is range the wrong choice?" — when queries filter by a non-ordered key (entity id) — hash or list fits better.

Worked example — daily range partitions on an events table

Detailed explanation. The canonical range setup: an events parent partitioned by event_day, daily children, a DEFAULT catch-all, and a pruning query that touches only the requested days. Build the whole thing and confirm pruning.

• Parent. PARTITION BY RANGE (event_day).
• Children. One per day; half-open bounds that chain exactly.
• Query. A 3-day range that prunes to 3 partitions.

Question. Create a daily range-partitioned events table with a DEFAULT partition and show a query pruning to three days.

Input.

Code.

-- Parent
CREATE TABLE events (
    event_id   BIGINT      NOT NULL,
    user_id    BIGINT      NOT NULL,
    event_day  DATE        NOT NULL,
    payload    JSONB,
    PRIMARY KEY (event_id, event_day)
) PARTITION BY RANGE (event_day);

-- Daily children — half-open bounds chain exactly
CREATE TABLE events_20260901 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-09-02');
CREATE TABLE events_20260902 PARTITION OF events
  FOR VALUES FROM ('2026-09-02') TO ('2026-09-03');
CREATE TABLE events_20260903 PARTITION OF events
  FOR VALUES FROM ('2026-09-03') TO ('2026-09-04');
CREATE TABLE events_20260904 PARTITION OF events
  FOR VALUES FROM ('2026-09-04') TO ('2026-09-05');

-- Catch-all so a stray date never errors the pipeline
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Routing is automatic — the engine picks the child by event_day
INSERT INTO events (event_id, user_id, event_day, payload) VALUES
  (1, 42, '2026-09-01', '{"t":"click"}'),
  (2, 42, '2026-09-03', '{"t":"view"}'),
  (3, 99, '2027-01-01', '{"t":"click"}');   -- lands in events_default

-- Pruning query — 3-day range touches exactly 3 partitions
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE  event_day >= '2026-09-01' AND event_day < '2026-09-04';

Step-by-step explanation.

1. The parent events holds no rows; it is a routing shell. Every insert is dispatched to the child whose [FROM, TO) bound contains its event_day. Row 1 (Sep-01) goes to events_20260901; row 3 (2027-01-01) matches no declared range and lands in events_default.
2. The half-open bounds (FROM '2026-09-01' TO '2026-09-02') mean Sep-01 belongs to the first child and Sep-02 00:00 belongs to the second. Chaining TO of one to FROM of the next leaves no gap; a gap would reject an insert for the missing day.
3. The DEFAULT partition is the safety valve. Without it, row 3 would raise no partition of relation "events" found for row. In an ingestion pipeline that single error can stall a whole batch, so DEFAULT is non-negotiable for anything fed by upstream data you do not fully control.
4. The range query event_day >= '2026-09-01' AND event_day < '2026-09-04' references the partition key with range operators. The planner keeps the three children whose bounds overlap [Sep-01, Sep-04) and prunes Sep-04 and DEFAULT. The plan is an Append over exactly three children.
5. The composite PRIMARY KEY (event_id, event_day) is required because Postgres enforces uniqueness per-partition and needs the partition key inside every unique constraint. A bare PRIMARY KEY (event_id) is rejected on a partitioned table.

Output.

Rule of thumb. For any range table: chain half-open bounds with no gaps, always add a DEFAULT partition so stray dates never error ingestion, put the partition key in the primary key, and pre-create partitions before their interval starts. These four rules remove entire classes of range-partitioning incidents.

Worked example — retention by dropping partitions

Detailed explanation. The reason time-series tables are range-partitioned is retention. Deleting 90-day-old rows from a giant heap scans and marks millions of tuples, bloats the table, and forces a vacuum. Dropping a whole partition is O(1). Walk through a 90-day retention job that drops yesterday-minus-90 each night, and contrast it with the DELETE it replaces.

• Policy. Keep 90 days; drop the day that just aged out.
• Drop. DROP TABLE events_YYYYMMDD — instant, reclaims disk.
• Contrast. DELETE ... WHERE event_day < ... — scans, bloats, vacuums.

Question. Write the nightly retention job (drop-based) and quantify why it beats the equivalent DELETE.

Input.

Code.

-- Nightly retention: detach then drop the day that aged past 90 days.
-- DETACH first so a long-running query holding the old partition
-- doesn't block; drop after it finishes.
DO $$
DECLARE
    old_day  DATE := CURRENT_DATE - INTERVAL '90 days';
    part     TEXT := format('events_%s', to_char(old_day, 'YYYYMMDD'));
BEGIN
    IF EXISTS (SELECT 1 FROM pg_class WHERE relname = part) THEN
        EXECUTE format('ALTER TABLE events DETACH PARTITION %I CONCURRENTLY', part);
        EXECUTE format('DROP TABLE %I', part);
        RAISE NOTICE 'dropped partition %', part;
    END IF;
END$$;

-- The anti-pattern this replaces (do NOT do this on a big heap):
-- DELETE FROM events WHERE event_day < CURRENT_DATE - INTERVAL '90 days';
-- -> scans/marks millions of rows, bloats the heap, triggers autovacuum,
--    and holds locks far longer than a metadata DROP.

-- Pair the drop with creating tomorrow's partition (rolling window)
CREATE TABLE IF NOT EXISTS events_20260906 PARTITION OF events
  FOR VALUES FROM ('2026-09-06') TO ('2026-09-07');

Step-by-step explanation.

1. ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY first removes the old partition from the parent without a heavy lock, so any in-flight query still reading it can finish. Detaching turns the partition back into a standalone table.
2. DROP TABLE events_YYYYMMDD then removes that standalone table. This is a catalog operation plus a file unlink — O(1) regardless of how many rows the partition held. Disk is reclaimed immediately, no vacuum required.
3. The DELETE alternative must locate every row older than the cutoff (a scan), write a dead-tuple marker for each (WAL + heap writes), and leave the space occupied until autovacuum reclaims it. On a billion-row heap this is minutes of work, a WAL spike, and lingering bloat.
4. The retention job is paired with partition creation: as the window rolls forward, drop the oldest day and create the newest. Running both in the same nightly job keeps exactly 90 live partitions. pg_partman automates both halves.
5. The one subtlety: a query that started before the DETACH and is still scanning the old partition. DETACH ... CONCURRENTLY waits for such readers; only after they drain is the DROP safe. This is why detach-then-drop is preferred over a bare DROP on a busy table.

Output.

Rule of thumb. Never retire time-series data with DELETE on a partitioned table — DETACH CONCURRENTLY then DROP the whole partition. Pair every nightly drop with a create so the live-partition count stays fixed. Retention-by-drop is the single biggest operational payoff of range partitioning.

Worked example — the coarse-interval hot-partition trap

Detailed explanation. A team partitions metrics by month to keep the partition count low. Two weeks into the month, every "today" and "last 24h" dashboard query scans the entire month-to-date partition — hundreds of millions of rows — because the current partition is coarse. The fix is to size the interval to the query granularity. Walk through the diagnosis and the repartition to daily.

• Symptom. "Last 24h" query latency grows linearly through the month, worst on the 28th.
• Root cause. Monthly partition means "today" is a slice of a 28-day partition; no finer pruning is possible.
• Fix. Daily partitions so "today" is one small partition.

Question. Show why monthly partitioning fails the 24h query and how daily partitioning fixes it, with the pruning contrast.

Input.

Code.

-- BEFORE — monthly partitions; "today" scans the whole month-to-date
CREATE TABLE metrics_2026_09 PARTITION OF metrics
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

EXPLAIN (COSTS OFF)
SELECT avg(value) FROM metrics
WHERE  ts >= now() - INTERVAL '24 hours';
-- ->  Seq Scan on metrics_2026_09     (28 days of rows to answer a 1-day question)

-- AFTER — daily partitions; "today" prunes to one small partition
CREATE TABLE metrics_20260928 PARTITION OF metrics
  FOR VALUES FROM ('2026-09-28') TO ('2026-09-29');
CREATE TABLE metrics_20260929 PARTITION OF metrics
  FOR VALUES FROM ('2026-09-29') TO ('2026-09-30');

EXPLAIN (COSTS OFF)
SELECT avg(value) FROM metrics
WHERE  ts >= now() - INTERVAL '24 hours';
-- ->  Append
--       ->  Seq Scan on metrics_20260928   (yesterday's tail)
--       ->  Seq Scan on metrics_20260929   (today)
--     (2 small partitions, not one giant month)

Step-by-step explanation.

1. Under monthly partitioning, the ts >= now() - INTERVAL '24 hours' predicate still references the key, so pruning "works" — but the finest granularity available is the month. On the 28th, the current partition already holds 28 days, so a 1-day question scans 28 days of rows.
2. The latency grows through the month because the current partition grows through the month. On the 1st it is small; by the 28th it is at its largest. This "sawtooth" latency pattern (resets each month, climbs within it) is the fingerprint of a too-coarse interval.
3. Switching to daily partitions makes "today" a single small partition. The 24h query prunes to today plus a sliver of yesterday — two low-tens-of-millions partitions instead of one 600M partition. Scan cost becomes flat across the month.
4. The trade-off is partition count: daily over a year is 365 partitions vs 12 monthly. That is well within the healthy hundreds-to-low-thousands range, so daily is the right call whenever queries ask for sub-week windows.
5. The general rule: the partition interval should be no coarser than the smallest common query window. If dashboards ask for 24h, do not partition by month. If they ask for last-quarter, monthly is fine.

Output.

Rule of thumb. Size the range interval to the finest common query window, not to minimise partition count. If the hot query asks for "today" or "last 24h", partition daily (or hourly) so the current partition stays small. A coarse interval turns pruning into a lie — the predicate matches the key but still scans weeks of data.

Data engineering interview question on range partitioning

A senior interviewer might ask: "Design a range-partitioned orders table on Postgres 16 that keeps 24 months of history, serves month-ranged analytics, retires the oldest month cheaply, and never fails an insert for an unexpected date. Include the interval choice, the index strategy, the retention job, and how you'd prove a 3-month query prunes."

Solution Using monthly range partitions with a partitioned index, a DEFAULT catch-all, and detach-then-drop retention

-- 1. Parent — RANGE by order_month (a date truncated to month start)
CREATE TABLE orders (
    order_id     BIGINT       NOT NULL,
    customer_id  BIGINT       NOT NULL,
    total_cents  BIGINT       NOT NULL,
    status       TEXT         NOT NULL,
    order_month  DATE         NOT NULL,     -- e.g. 2026-09-01 for Sep 2026
    PRIMARY KEY (order_id, order_month)
) PARTITION BY RANGE (order_month);

-- 2. Monthly children (24 live; automate creation with pg_partman)
CREATE TABLE orders_2026_09 PARTITION OF orders
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE orders_2026_10 PARTITION OF orders
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
-- ... one per month ...

-- 3. DEFAULT catch-all so a stray/mis-truncated date never errors ingest
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- 4. Partitioned index for customer lookups within a month window
CREATE INDEX idx_orders_customer_month ON orders (customer_id, order_month);

-- 5. Prove a 3-month analytics query prunes to 3 partitions
EXPLAIN (COSTS OFF)
SELECT status, sum(total_cents)
FROM   orders
WHERE  order_month >= '2026-07-01' AND order_month < '2026-10-01'
GROUP  BY status;
-- -> Append over orders_2026_07, orders_2026_08, orders_2026_09 (3 of 24)

-- 6. Retention — detach then drop the month aged past 24
ALTER TABLE orders DETACH PARTITION orders_2024_09 CONCURRENTLY;
DROP TABLE orders_2024_09;

Step-by-step trace.

After deployment, month-ranged analytics prune to the overlapping monthly partitions (3 of 24 for a quarter query), per-customer lookups use the propagated (customer_id, order_month) index within the pruned months, retiring the 25th-oldest month is a detach-then-drop, and a row with a bad order_month lands in DEFAULT instead of failing the batch.

Output:

Why this works — concept by concept:

• Monthly interval matches the query window — analytics ask for month ranges, so a monthly key prunes cleanly to the overlapping partitions. Interval granularity is chosen to match the predicate, not to minimise partition count.
• DEFAULT partition — the catch-all makes ingestion robust: a mis-truncated or out-of-range order_month lands in orders_default instead of raising "no partition found" and stalling the batch. You reconcile the DEFAULT partition separately.
• Partitioned secondary index — (customer_id, order_month) declared on the parent propagates to every child, so the per-customer predicate is served without a second hash-partitioned copy. Pruning narrows to the month; the index narrows to the customer.
• Detach-then-drop retention — DETACH ... CONCURRENTLY releases the old month without blocking live readers, then DROP reclaims the disk in O(1). This is the cheap-retention payoff that made range the right scheme.
• Cost — 24 partitions + one propagated index + a monthly detach/drop/create. Scan cost is O(months-in-range × rows-per-month); retention is O(1). The overhead is pre-creating next month's partition, which pg_partman handles.

SQL
Topic — database
Database range-partitioning and retention problems

Practice →

Practice →

3. Hash partitioning and bucketing — even distribution

PARTITION BY HASH (key) % N spreads rows evenly across N buckets — no ordering, no pruning on ranges, but shuffle-free joins and no hot partition

The mental model in one line: hash partitioning routes each row to one of N partitions by hash(key) mod N, producing partitions of roughly equal size for any high-cardinality key — it gives up range-pruning (there is no ordering to compare against) in exchange for guaranteed even distribution, so no single partition runs hot, and it enables the killer optimisation of the data-lake world: two tables **bucketed on the same key and bucket count join without a shuffle.** Every senior data engineer reaches for hash when the key is a high-cardinality entity id and the workload is entity-equality lookups or large joins rather than range scans.

The axes for hash partitioning.

• Key. A high-cardinality column — user_id, order_id, session_id. Cardinality is what makes the hash spread evenly; a low-cardinality key (a boolean, a 3-value status) would pile all rows into a few buckets.
• Bucket count N. Fixed at table-creation time. Changing N later re-hashes every row (a full rewrite), so N is a commitment. Pick N so each bucket is a comfortable size and N aligns with your parallelism (often a power of two, or the number of executor cores for Spark bucketing).
• Pruning. Only equality (WHERE user_id = 42) prunes — the engine hashes the literal and reads the one matching bucket. Range predicates on the hash key do not prune, because hashing destroys ordering.
• Distribution. Even by construction. Skew appears only when the key itself is skewed (one user_id with 40% of the rows) — the hash cannot spread a single value across buckets.

Bucketing — hash partitioning for files.

• What it is. In Spark/Hive/Iceberg, bucketing writes rows into a fixed number of files per partition by hash(key) % num_buckets. It is hash partitioning applied at the file layout level rather than the table-catalog level.
• The payoff — shuffle-free joins. If two tables are bucketed on the same key with the same bucket count, matching buckets contain matching keys, so a join can pair bucket-i with bucket-i locally — no shuffle, no network exchange. On large joins this is the difference between minutes and hours.
• The payoff — pre-aggregation. A GROUP BY key on a bucketed-by-key table needs no shuffle either; each bucket already holds all rows for its keys.
• The constraint. Both sides must share the same bucket count and key type, and the writer must actually enforce bucketing (CLUSTERED BY (key) INTO N BUCKETS in Hive; bucketBy(N, "key") in Spark).

Choosing N — the sizing rule.

• Too small. Each bucket is huge; parallelism is capped at N; a single bucket may not fit a worker's memory for a hash join.
• Too large. Millions of tiny files (the "small-files problem"); metadata overhead dominates; the shuffle-free join advantage is eaten by file-open cost.
• The heuristic. Target ~128 MB–1 GB per bucket file. N ≈ total_size / target_bucket_size, rounded to a power of two and to at least the executor-core count so every core has a bucket to chew.

Common interview probes on hash partitioning.

• "Why can't hash partitioning prune a range query?" — required answer: hashing destroys ordering; only equality prunes.
• "What is bucketing and why does it speed up joins?" — co-bucketed tables join per-bucket with no shuffle.
• "How do you choose the bucket count?" — target ~128 MB–1 GB per bucket; align to parallelism; power of two.
• "What if one key value dominates?" — hash can't split a single value; you need salting (section 5).

Worked example — hash-partitioned orders for even spread

Detailed explanation. The canonical hash setup: an orders table hash-partitioned by customer_id into 8 buckets so no partition runs hot, with equality lookups pruning to one bucket. Build it and show the routing plus the pruning contrast against a range query.

• Parent. PARTITION BY HASH (customer_id).
• Children. 8 buckets via MODULUS 8, REMAINDER 0..7.
• Prunes. customer_id = 42 → one bucket; customer_id > 100 → all buckets.

Question. Create an 8-way hash-partitioned orders table and show that equality prunes to one bucket while a range predicate scans all eight.

Input.

Code.

-- Parent — hash by a high-cardinality key
CREATE TABLE orders (
    order_id     BIGINT   NOT NULL,
    customer_id  BIGINT   NOT NULL,
    total_cents  BIGINT   NOT NULL,
    status       TEXT     NOT NULL,
    PRIMARY KEY (order_id, customer_id)
) PARTITION BY HASH (customer_id);

-- 8 buckets — each takes one remainder of hash(customer_id) mod 8
CREATE TABLE orders_h0 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE orders_h1 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE orders_h2 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE orders_h3 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE orders_h4 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE orders_h5 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE orders_h6 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE orders_h7 PARTITION OF orders FOR VALUES WITH (MODULUS 8, REMAINDER 7);

-- Equality prunes to exactly one bucket
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE customer_id = 42;
-- ->  Seq Scan on orders_h5 orders   (only the matching remainder bucket)

-- Range does NOT prune — hashing destroys ordering
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE customer_id > 100;
-- ->  Append over orders_h0 .. orders_h7   (all 8 buckets)

Step-by-step explanation.

1. PARTITION BY HASH (customer_id) with 8 children declared as MODULUS 8, REMAINDER i tells Postgres to route each row to the child whose remainder equals hash(customer_id) mod 8. Because customer_id is high-cardinality, the 8 buckets fill to within a few percent of each other — even distribution by construction.
2. The equality query WHERE customer_id = 42 lets the planner compute hash(42) mod 8 at plan time and read only that one bucket (here orders_h5). This is hash pruning: equality on the hash key prunes to a single partition.
3. The range query WHERE customer_id > 100 cannot prune. Hashing scrambles order, so customer_id values 101, 102, 103 land in unpredictable, different buckets. The planner has no way to exclude any bucket and must Append over all eight. This is the fundamental limitation: hash trades range-pruning for even spread.
4. The composite PRIMARY KEY (order_id, customer_id) again includes the partition key, as Postgres requires for unique constraints on partitioned tables.
5. Because buckets are even, a full scan parallelises perfectly: 8 workers each take one bucket of equal size. Compare to a skewed list partition where one worker gets the giant partition and the rest finish early and idle.

Output.

Rule of thumb. Reach for hash partitioning when the key is high-cardinality and the workload is equality lookups or large joins, not range scans. Equality prunes to one bucket; ranges scan everything. Fix N at creation — changing the bucket count re-hashes every row.

Worked example — a shuffle-free bucketed join in Spark

Detailed explanation. The signature payoff of hash/bucketing shows up in Spark: two large tables bucketed on the same key with the same bucket count join without a shuffle, because matching keys already sit in matching bucket files. Walk through writing two bucketed tables and the join that skips the exchange.

• Tables. orders and order_items, both bucketed by order_id into 64 buckets.
• Join. orders JOIN order_items ON order_id — bucket-i pairs with bucket-i.
• Result. No shuffle stage; each task joins one matching bucket pair.

Question. Write two co-bucketed tables and show the join plan is shuffle-free.

Input.

Code.

# Write both tables bucketed by order_id into the SAME bucket count.
(orders_df.write
    .format("parquet")
    .mode("overwrite")
    .bucketBy(64, "order_id")
    .sortBy("order_id")
    .saveAsTable("orders"))

(items_df.write
    .format("parquet")
    .mode("overwrite")
    .bucketBy(64, "order_id")
    .sortBy("order_id")
    .saveAsTable("order_items"))

# The join reads bucket-i of orders with bucket-i of order_items — no shuffle.
spark.sql("""
    SELECT o.order_id, o.customer_id, i.sku, i.qty
    FROM   orders o
    JOIN   order_items i ON o.order_id = i.order_id
""").explain()
# == Physical Plan ==
# *(3) SortMergeJoin [order_id], [order_id], Inner
#   :- *(1) FileScan parquet orders   ... (64 buckets)
#   +- *(2) FileScan parquet order_items ... (64 buckets)
# NOTE: no Exchange (shuffle) node between the scans and the join —
#       the matching bucket files are already co-located by key.

-- Hive equivalent — CLUSTERED BY enforces the same bucketing on write
CREATE TABLE orders (order_id BIGINT, customer_id BIGINT, total_cents BIGINT)
CLUSTERED BY (order_id) INTO 64 BUCKETS STORED AS PARQUET;

CREATE TABLE order_items (order_id BIGINT, sku STRING, qty INT)
CLUSTERED BY (order_id) INTO 64 BUCKETS STORED AS PARQUET;
-- SET hive.optimize.bucketmapjoin = true;  -- enables the shuffle-free join

Step-by-step explanation.

1. bucketBy(64, "order_id") on both writes hashes order_id into 64 files per table. Because both tables use the same key and the same count, order_id = X lands in the same bucket number in both — the files are co-located by key.
2. When Spark plans orders JOIN order_items ON order_id, it recognises both inputs are bucketed identically on the join key and produces a SortMergeJoin with no Exchange (shuffle) node. Each task reads bucket-i from both tables and joins locally.
3. The sortBy("order_id") inside each bucket makes the local join a cheap merge (both sides already sorted) rather than a hash-build. Bucketed + sorted is the ideal layout for a merge join.
4. The shuffle that would otherwise dominate — repartitioning both tables across the network by order_id — is eliminated. On a terabyte join that shuffle can be the entire runtime, so skipping it is the headline win of bucketing.
5. The requirement is symmetry: same bucket count, same key, same type on both sides. Bucket orders by 64 and order_items by 32 and the optimisation does not apply — the buckets no longer line up, and Spark falls back to a full shuffle.

Output.

Rule of thumb. To make a big join shuffle-free, bucket both sides by the join key into the same bucket count and sort within buckets. Mismatched counts or keys silently reintroduce the shuffle. Bucketing is hash partitioning aimed squarely at the join.

Worked example — sizing the bucket count

Detailed explanation. The bucket count N is a permanent commitment (changing it re-hashes everything), so sizing it right matters. Too few buckets caps parallelism and overflows worker memory; too many creates the small-files problem. Walk through sizing N for a 512 GB table.

• Total size. 512 GB.
• Target bucket size. ~256 MB per bucket file (fits a task comfortably).
• Parallelism floor. 256 executor cores available.

Question. Compute an appropriate bucket count for a 512 GB table and justify the rounding.

Input.

Code.

GB = 1024                      # MB
total_mb   = 512 * GB          # 524288 MB
target_mb  = 256               # per-bucket target
cores      = 256               # parallelism floor

raw_n = total_mb / target_mb   # 2048 buckets by size

# Round to a power of two (clean modulus) and stay >= core count
import math
def next_pow2(x): return 1 << math.ceil(math.log2(x))

n = max(next_pow2(int(raw_n)), next_pow2(cores))
print(f"raw={int(raw_n)}  chosen N={n}  bucket_size={total_mb/n:.0f} MB")
# raw=2048  chosen N=2048  bucket_size=256 MB

# Anti-patterns:
#   N = 8      -> each bucket 64 GB: won't fit a task, caps parallelism at 8
#   N = 100000 -> each bucket ~5 MB: small-files problem, metadata overhead

Step-by-step explanation.

1. Start from size: total / target = 524288 MB / 256 MB = 2048 buckets. This makes each bucket file a task-friendly ~256 MB — big enough to amortise file-open cost, small enough to fit a task's memory for a hash/merge join.
2. Check the parallelism floor: with 256 cores you want at least 256 buckets so every core has work. 2048 comfortably exceeds 256, so the size-driven number wins.
3. Round to a power of two (2048 is already one). Powers of two give a clean modulus and let engines split/coalesce buckets predictably. It also makes the count easy to reason about when you later add executors.
4. The too-few anti-pattern (N=8) makes each bucket 64 GB — it will not fit a task's memory, and the whole job's parallelism is capped at 8 regardless of how many cores you have. The too-many anti-pattern (N=100000) yields ~5 MB files: the small-files problem, where metadata and file-open time dominate real work.
5. Because N is fixed at write time, size for the table's mature volume, not today's. Re-bucketing a 512 GB table to change N is a full rewrite — pick N with a year of growth in mind.

Output.

Rule of thumb. Size the bucket count as total_size / ~256 MB, floor it at your core count, round to a power of two, and pick for mature volume because N is fixed at write time. Aim for 128 MB–1 GB per bucket: below that you hit small files, above that you cap parallelism and risk OOM on joins.

Data engineering interview question on hash partitioning and bucketing

A senior interviewer might ask: "You have a 1 TB clickstream fact table and a 300 GB sessions dimension, joined nightly on session_id, and the job spends 70% of its time in shuffle. The session_id key is high-cardinality with no natural ordering. Design a layout that removes the shuffle, justify the bucket count, and explain what happens if one session_id is abnormally hot."

Solution Using symmetric bucketing on session_id with a sized bucket count and a salting note for the hot key

# 1. Bucket BOTH tables on the join key into the SAME count.
#    1 TB / 256 MB ~= 4096 buckets; floor at core count; power of two.
N = 4096

(clickstream_df.write
    .format("parquet").mode("overwrite")
    .bucketBy(N, "session_id").sortBy("session_id")
    .saveAsTable("clickstream"))          # 1 TB -> 4096 x ~256 MB buckets

(sessions_df.write
    .format("parquet").mode("overwrite")
    .bucketBy(N, "session_id").sortBy("session_id")
    .saveAsTable("sessions"))             # 300 GB -> 4096 x ~75 MB buckets

# 2. The nightly join is now shuffle-free: bucket-i joins bucket-i.
plan = spark.sql("""
    SELECT c.session_id, c.url, s.user_id, s.device
    FROM   clickstream c
    JOIN   sessions s ON c.session_id = s.session_id
""")
plan.explain()   # SortMergeJoin, NO Exchange node between scans and join

# 3. Hot-key defense — if one session_id holds a huge share of rows,
#    its single bucket runs hot (hash can't split ONE value across buckets).
#    Salt only the hot keys so they spread across sub-buckets.
from pyspark.sql import functions as F

HOT = {"sess_ffff"}          # detected from a key-frequency scan
salted = clickstream_df.withColumn(
    "join_key",
    F.when(F.col("session_id").isin(HOT),
           F.concat_ws("#", F.col("session_id"),
                       (F.rand() * 16).cast("int")))    # 16-way salt
     .otherwise(F.col("session_id"))
)
# The sessions side is exploded 16x for the hot keys so every salt matches.

Step-by-step trace.

After the rewrite, the nightly join reads matching bucket pairs with no network shuffle — the 70% shuffle time disappears — and each of the 4096 tasks handles ~256 MB. The one residual risk, a single session_id holding a disproportionate share of rows, is handled by salting only the detected hot keys into 16 sub-buckets so no single task is overwhelmed.

Output:

Why this works — concept by concept:

• Symmetric bucketing — both tables hashed on session_id into the same 4096 buckets means session_id = X sits in bucket-i on both sides, so the join pairs bucket-i with bucket-i and no exchange is needed. Symmetry (same key, same count) is the precondition; break it and the shuffle returns.
• Bucket sizing — 1 TB / 256 MB ≈ 4096 makes each bucket a task-friendly size and floors parallelism above the core count. Powers of two keep the modulus clean and let engines coalesce buckets later.
• Sort within buckets — sortBy makes the per-bucket join a merge over two already-sorted streams, cheaper and lower-memory than building a hash table per bucket.
• Salting the hot key — hashing cannot split a single value across buckets, so one dominant session_id still lands in one bucket. Appending a random salt (only to detected hot keys) spreads that one value across 16 sub-buckets; the dimension side is exploded 16× so the salted keys still match.
• Cost — a one-time rewrite to bucket both tables (O(data) once), then every nightly join is O(data) with no shuffle instead of O(data) plus a full network exchange. The salt adds a 16× fan-out only on the handful of hot keys, not the whole table.

Spark
Topic — bucketing
Bucketing and shuffle-free join problems

Practice →

Practice →

4. List and composite partitioning

PARTITION BY LIST (region) maps explicit values to partitions, and composite partitioning nests a second key — two-axis pruning for multi-tenant and multi-region tables

The mental model in one line: list partitioning routes rows to partitions by explicit value membership — region = 'US' to one partition, 'EU' to another, everything else to a DEFAULT — and composite partitioning nests a second scheme inside each list partition (list by region, then range by month) so a query can prune on both axes at once; list fits low-cardinality categorical keys with known values (region, tenant, status), and composite fits tables that must slice by category and by time. Every senior data engineer uses list for multi-region or multi-tenant tables where each category is queried and retired independently.

The axes for list partitioning.

• Key. A low-cardinality categorical column with a known, small set of values — region, tenant_id (for a handful of big tenants), status, country. The values must be enumerable; if there are thousands of distinct values, hash fits better.
• Explicit mapping. Each partition declares FOR VALUES IN ('US', 'CA') — one partition can hold several values. This is list's superpower: you group related values (e.g. all North-American countries) into one partition.
• The DEFAULT partition. Catches any value not explicitly listed — critical, because a new region added upstream would otherwise error every insert. DEFAULT is the safety net for the open-world problem.
• Pruning and retention. WHERE region = 'EU' prunes to the EU partition; retiring a decommissioned region is a DROP TABLE of its partition. Per-category isolation is the win.

Composite (sub)partitioning — nesting a second key.

• What it is. Each list partition is itself partitioned by a second scheme. PARTITION BY LIST (region) at the top, and each region partition is PARTITION BY RANGE (order_month) underneath. Postgres implements this by making the list child a partitioned table in turn.
• Two-axis pruning. A query WHERE region = 'EU' AND order_month >= '2026-07-01' prunes first to the EU partition, then to the overlapping monthly subpartitions inside it. Both predicates prune — the scan touches only EU's recent months.
• Independent retention. You can drop old months within one region without touching another, or drop an entire region wholesale. The two axes retire independently.
• The cost. Partition count multiplies: 3 regions × 24 months = 72 leaf partitions. Keep the product within the healthy hundreds-to-low-thousands range.

When list is the wrong choice.

• High-cardinality key. Thousands of tenants → thousands of list partitions to declare and maintain. Use hash (sub)partitioning instead so tenants spread automatically.
• Skewed categories. If one region holds 80% of the rows, its partition is a giant and the others are tiny — list gives you isolation but not balance. Composite-subpartition the big region by time or hash to split it.
• Unknown future values. A rapidly-growing value set means constant ALTER TABLE ... ADD PARTITION. DEFAULT absorbs them, but a bloated DEFAULT prunes poorly.

Common interview probes on list/composite partitioning.

• "When do you pick list over hash?" — required answer: low-cardinality, known, independently-queried categories.
• "What does the DEFAULT partition protect against?" — an unlisted/new value erroring inserts.
• "What is composite partitioning good for?" — slicing by category and time with two-axis pruning.
• "What if one list value dominates?" — subpartition that value by time or hash to fix skew.

Worked example — list partitions by region with a DEFAULT catch-all

Detailed explanation. The canonical list setup: a sales table partitioned by region, with one partition per region group and a DEFAULT for anything unlisted. Build it, show grouped values in one partition, and confirm pruning plus the DEFAULT safety net.

• Parent. PARTITION BY LIST (region).
• Children. p_na for US/CA, p_eu for EU countries, p_apac, plus DEFAULT.
• Prunes. region = 'US' → p_na; a new region → DEFAULT.

Question. Create a region list-partitioned sales table (grouping countries), show routing, and confirm a region predicate prunes.

Input.

Code.

-- Parent — LIST by region
CREATE TABLE sales (
    sale_id   BIGINT   NOT NULL,
    region    TEXT     NOT NULL,
    amount    BIGINT   NOT NULL,
    PRIMARY KEY (sale_id, region)
) PARTITION BY LIST (region);

-- One partition can hold SEVERAL related values
CREATE TABLE sales_na   PARTITION OF sales FOR VALUES IN ('US', 'CA');
CREATE TABLE sales_eu   PARTITION OF sales FOR VALUES IN ('DE', 'FR', 'GB');
CREATE TABLE sales_apac PARTITION OF sales FOR VALUES IN ('JP', 'IN', 'AU');

-- DEFAULT catches any value NOT listed above (new markets, typos)
CREATE TABLE sales_default PARTITION OF sales DEFAULT;

INSERT INTO sales (sale_id, region, amount) VALUES
  (1, 'US', 100),        -- -> sales_na
  (2, 'FR', 200),        -- -> sales_eu
  (3, 'BR', 300);        -- -> sales_default (Brazil not listed)

-- Region predicate prunes to one partition
EXPLAIN (COSTS OFF)
SELECT sum(amount) FROM sales WHERE region = 'US';
-- ->  Aggregate -> Seq Scan on sales_na   (only the NA partition)

-- Multi-value predicate prunes to the partitions holding those values
EXPLAIN (COSTS OFF)
SELECT sum(amount) FROM sales WHERE region IN ('DE', 'JP');
-- ->  Append over sales_eu, sales_apac   (2 of 4)

Step-by-step explanation.

1. PARTITION BY LIST (region) routes each row by exact value membership. FOR VALUES IN ('US', 'CA') lets one partition (sales_na) hold multiple related values — grouping North America into a single physical chunk. This value-grouping flexibility is unique to list.
2. Row 3 ('BR') matches none of the declared value sets and lands in sales_default. Without DEFAULT, that insert would raise "no partition found" — and since regions are open-world (a new market can appear any day), DEFAULT is mandatory.
3. WHERE region = 'US' prunes to sales_na because the planner knows only that partition can hold 'US'. The scan reads one partition; the other three (EU, APAC, DEFAULT) are excluded.
4. WHERE region IN ('DE', 'JP') prunes to the two partitions that can hold those values — sales_eu and sales_apac. List pruning handles IN lists by mapping each value to its partition and unioning the set.
5. The DEFAULT partition should be monitored: rows accumulating there mean a new value appeared that deserves its own partition. You periodically ALTER TABLE ... ADD PARTITION for the new region and move its rows out of DEFAULT, keeping DEFAULT small so it does not become a scan bottleneck.

Output.

Rule of thumb. Use list partitioning for low-cardinality, known categorical keys, group related values into one partition with FOR VALUES IN (...), always add a DEFAULT for the open-world case, and monitor DEFAULT — rows piling up there signal a new value that needs its own partition.

Worked example — composite list-then-range for region × month

Detailed explanation. A multi-region audit table must slice by region (compliance, per-region retention) and by month (time-range queries, retention). Composite partitioning — list by region at the top, range by month underneath — gives two-axis pruning. Build the nested scheme and show a query pruning on both axes.

• Top. PARTITION BY LIST (region).
• Nested. Each region partition is PARTITION BY RANGE (audit_month).
• Prunes. region = 'EU' AND audit_month >= '2026-08-01' → EU's recent months only.

Question. Build a composite region×month audit table and show two-axis pruning.

Input.

Code.

-- Top level — LIST by region; each region child is itself partitioned
CREATE TABLE audit (
    audit_id     BIGINT   NOT NULL,
    region       TEXT     NOT NULL,
    audit_month  DATE     NOT NULL,
    detail       JSONB,
    PRIMARY KEY (audit_id, region, audit_month)
) PARTITION BY LIST (region);

-- EU region partition — subpartitioned by month
CREATE TABLE audit_eu PARTITION OF audit
  FOR VALUES IN ('DE', 'FR', 'GB')
  PARTITION BY RANGE (audit_month);

CREATE TABLE audit_eu_2026_08 PARTITION OF audit_eu
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE audit_eu_2026_09 PARTITION OF audit_eu
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- APAC region partition — also subpartitioned by month
CREATE TABLE audit_apac PARTITION OF audit
  FOR VALUES IN ('JP', 'IN', 'AU')
  PARTITION BY RANGE (audit_month);

CREATE TABLE audit_apac_2026_09 PARTITION OF audit_apac
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- Two-axis pruning: region THEN month
EXPLAIN (COSTS OFF)
SELECT count(*) FROM audit
WHERE  region = 'DE' AND audit_month >= '2026-09-01';
-- ->  Seq Scan on audit_eu_2026_09
--     (pruned to EU by region, then to Sep by month — ONE leaf)

Step-by-step explanation.

1. The top-level PARTITION BY LIST (region) creates region partitions, but audit_eu is declared PARTITION BY RANGE (audit_month) — making it a partitioned table in turn. Its own children are the monthly leaves. This nesting is how Postgres expresses composite partitioning.
2. The primary key must include both partition keys across the hierarchy: (audit_id, region, audit_month). Every level's key participates in uniqueness enforcement.
3. A query with predicates on both axes prunes twice. region = 'DE' selects the audit_eu subtree (DE is in EU's value list); audit_month >= '2026-09-01' then prunes within that subtree to audit_eu_2026_09. The scan touches a single leaf out of the whole hierarchy.
4. Retention is now two-dimensional. You can drop old months within one region (DROP TABLE audit_eu_2026_08) without touching APAC, or drop an entire region wholesale (DROP TABLE audit_eu cascades its months). The axes retire independently, which is exactly what per-region compliance rules demand.
5. The cost is the multiplied partition count: regions × months. Three region groups × 24 months = 72 leaves — comfortably within budget. But 50 regions × daily × 2 years would be tens of thousands of leaves; keep the product bounded or the planner's pruning list itself becomes the bottleneck.

Output.

Rule of thumb. Use composite (list-then-range) partitioning when a table must be sliced by category and by time with independent retention on each axis. Include every level's key in the primary key, keep the region × interval product within the low thousands, and enjoy two-axis pruning — the query narrows to a single leaf.

Worked example — fixing a dominant list value with a nested subpartition

Detailed explanation. A tenant_events table is list-partitioned by tenant_id for a dozen big tenants. One whale tenant holds 75% of all rows, so its partition is a giant while the others are small — list gave isolation but not balance, and any full scan of the whale partition serialises on one worker. The fix: subpartition just the whale by hash so its rows spread across buckets. Walk through it.

• Symptom. The whale tenant's partition is 10× the others; scans of it dominate.
• Root cause. List isolates values but cannot balance a dominant one.
• Fix. Make the whale partition PARTITION BY HASH (event_id) into 16 buckets.

Question. Subpartition the dominant tenant's list partition by hash to restore balance, leaving small tenants as plain list partitions.

Input.

Code.

-- Parent — LIST by tenant_id
CREATE TABLE tenant_events (
    event_id   BIGINT   NOT NULL,
    tenant_id  BIGINT   NOT NULL,
    payload    JSONB,
    PRIMARY KEY (event_id, tenant_id)
) PARTITION BY LIST (tenant_id);

-- Small tenants: one plain list partition each
CREATE TABLE tenant_events_t2 PARTITION OF tenant_events FOR VALUES IN (2);
CREATE TABLE tenant_events_t3 PARTITION OF tenant_events FOR VALUES IN (3);
-- ... t4..t12 ...

-- WHALE tenant: subpartition by HASH so its 75% spreads across 16 buckets
CREATE TABLE tenant_events_t1 PARTITION OF tenant_events
  FOR VALUES IN (1)
  PARTITION BY HASH (event_id);

CREATE TABLE tenant_events_t1_h0 PARTITION OF tenant_events_t1
  FOR VALUES WITH (MODULUS 16, REMAINDER 0);
CREATE TABLE tenant_events_t1_h1 PARTITION OF tenant_events_t1
  FOR VALUES WITH (MODULUS 16, REMAINDER 1);
-- ... h2..h15 ...

-- A scan of the whale now parallelises across 16 balanced buckets;
-- small tenants remain single, cheap list partitions.
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenant_events WHERE tenant_id = 1;
-- ->  Append over tenant_events_t1_h0 .. _h15   (16 balanced buckets)

Step-by-step explanation.

1. Plain list partitioning by tenant_id isolates each tenant but cannot balance them: the whale's single partition holds 75% of the data. A full scan or aggregation of that partition runs on one worker while the 11 small partitions finish instantly — classic skew.
2. The fix leaves the small tenants as ordinary list partitions (they are already small and cheap) and turns only the whale's partition into a hash-subpartitioned table: tenant_events_t1 ... PARTITION BY HASH (event_id) with 16 buckets.
3. event_id is high-cardinality, so hashing it spreads the whale's rows evenly across 16 buckets. A scan of tenant_id = 1 now Appends over 16 balanced buckets and parallelises 16-way instead of running on one giant partition.
4. Queries filtering tenant_id = 1 still prune to the whale subtree first (list pruning), then fan out across its 16 buckets. Small-tenant queries (tenant_id = 5) prune to a single plain list partition, untouched by the change.
5. This "list on top, hash under the hot value" is the standard remedy for a dominant categorical value: keep list's per-tenant isolation and retention, add hash's balance exactly where one value would otherwise create a hot partition.

Output.

Rule of thumb. When one list value dominates, subpartition only that value by hash on a high-cardinality column. Keep the small values as plain list partitions. This preserves list's per-category isolation and retention while restoring the even distribution that hash gives — the best of both schemes exactly where you need it.

Data engineering interview question on list and composite partitioning

A senior interviewer might ask: "Design the physical layout for a multi-region transactions table: queries filter by region for compliance, by month for reporting, and retention differs per region (EU keeps 7 years, US keeps 3). One region carries 60% of the volume. Choose a scheme, justify the nesting, handle the skew, and show a compliance query pruning to a single leaf."

Solution Using composite list(region)-then-range(month) with a hash sub-split for the dominant region

-- 1. Top — LIST by region (compliance + per-region retention)
CREATE TABLE transactions (
    txn_id      BIGINT   NOT NULL,
    region      TEXT     NOT NULL,
    txn_month   DATE     NOT NULL,
    amount      BIGINT   NOT NULL,
    PRIMARY KEY (txn_id, region, txn_month)
) PARTITION BY LIST (region);

-- 2. EU region -> range by month (7-year retention lives here)
CREATE TABLE txn_eu PARTITION OF transactions
  FOR VALUES IN ('DE','FR','GB')
  PARTITION BY RANGE (txn_month);
CREATE TABLE txn_eu_2026_09 PARTITION OF txn_eu
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- 3. US region is the 60% whale -> range by month, and each month
--    hash-subpartitioned so the dominant region stays balanced.
CREATE TABLE txn_us PARTITION OF transactions
  FOR VALUES IN ('US')
  PARTITION BY RANGE (txn_month);
CREATE TABLE txn_us_2026_09 PARTITION OF txn_us
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01')
  PARTITION BY HASH (txn_id);
CREATE TABLE txn_us_2026_09_h0 PARTITION OF txn_us_2026_09
  FOR VALUES WITH (MODULUS 8, REMAINDER 0);
-- ... h1..h7 ...

-- 4. DEFAULT for any unlisted region
CREATE TABLE txn_default PARTITION OF transactions DEFAULT;

-- 5. Compliance query prunes region THEN month -> one EU leaf
EXPLAIN (COSTS OFF)
SELECT count(*) FROM transactions
WHERE  region = 'DE' AND txn_month >= '2026-09-01' AND txn_month < '2026-10-01';
-- ->  Seq Scan on txn_eu_2026_09   (single leaf)

-- 6. Per-region retention differs, and drops independently
DROP TABLE txn_us_2023_09;    -- US: 3-year cutoff
-- EU months are kept 7 years and dropped on their own schedule

Step-by-step trace.

After deployment, a compliance query for one EU country in one month prunes to a single leaf partition; per-region retention runs on independent schedules (US drops months at 3 years, EU at 7) via DROP TABLE on the relevant subtree; and the 60%-volume US region is hash-subpartitioned within each month so no single leaf runs hot.

Output:

Why this works — concept by concept:

• List on region — the top level isolates each region into its own subtree, so compliance queries prune to a region and per-region retention runs independently. Region is low-cardinality and known, which is exactly what list is for.
• Range under region — nesting range-by-month inside each region gives the second pruning axis and makes monthly retention a DROP within the region subtree. Two schemes, two axes, two independent retention clocks.
• Hash under the whale's months — the US region carries 60% of volume, so each US month is further hash-subpartitioned on txn_id; the dominant region's data spreads across balanced leaves instead of piling into one hot partition. Small regions skip this extra level.
• DEFAULT partition — an unlisted region lands in DEFAULT rather than erroring ingest, preserving the open-world safety net at the top of the hierarchy.
• Cost — a three-level hierarchy (region × month × optional hash) whose leaf count stays bounded because only the whale region adds the hash level. Compliance queries hit one leaf (O(one leaf)); retention is O(1) drops per region; the whale stays balanced. The overhead is a deeper hierarchy to maintain, justified by the two independent retention policies and the skew fix.

SQL
Topic — database
Database list and composite partitioning problems

Practice →

Practice →

5. Partition pruning, skew, and choosing a scheme

Pruning pays only when the predicate matches the key, skew is the failure mode of every scheme, and the right choice falls out of the access pattern

The mental model in one line: partition pruning is the optimiser eliminating partitions that cannot match a query's predicate — but it fires only when the predicate references the partition key with a prunable operator, so a mismatched key silently scans everything; data skew is the failure mode where one partition holds far more (or hotter) data than the others, so one worker does most of the work; and choosing a scheme is a short decision tree over access pattern, cardinality, and retention need. This section ties the three schemes together: how to guarantee pruning fires, how to detect and fix skew, and how to pick range vs hash vs list under pressure.

Static vs dynamic pruning.

• Static pruning. The predicate is a constant known at plan time (WHERE region = 'EU'). The planner eliminates partitions during planning; the plan lists only survivors. This is the common, reliable case.
• Dynamic pruning. The pruning value is not known until runtime — it comes from a join or a subquery (WHERE event_day IN (SELECT day FROM active_days)). The engine prunes at execution once the values materialise. Spark's dynamic partition pruning and Postgres's runtime partition pruning both do this; verify with EXPLAIN ANALYZE because it does not show at plan time.
• The alignment rule. Both forms require the predicate to reference the partition key. A predicate on a non-key column never prunes, static or dynamic. The number-one pruning bug is filtering on a column that is not the key.

Data skew — the failure mode of every scheme.

• Range skew. The current/newest partition is always the hottest; a burst (a viral event, a Black-Friday day) makes one day's partition dwarf the rest.
• Hash skew. Even distribution breaks only when a single key value dominates — hash cannot split one value across buckets, so a whale user_id piles into one bucket.
• List skew. A dominant category (one region with 80% of rows) makes its partition a giant.
• The detection. Compare per-partition row counts / sizes; a partition >2–3× the median is skewed. In Spark, watch for one task running far longer than the rest in a stage.

Fixing skew.

• Salting. Append a small random suffix to the hot key so its rows spread across sub-buckets; explode the other join side to match. Applies to hash/bucketing skew from a dominant value.
• Sub-partitioning the hot slice. Split the hot range/value by a second key (hash or finer range) so even the hot partition parallelises — the "list-then-hash" and "range-then-hash" remedies from section 4.
• Isolating the whale. Give the dominant value its own dedicated partition (and its own parallelism budget) so it does not drag the shared ones.
• Finer intervals. For range skew from a coarse interval, shrink the interval (monthly → daily → hourly) so the hot slice is smaller.

Choosing a scheme — the decision tree.

• Q1 — Do queries filter by an ordered key (date/time)? → yes: range (and get cheap retention for free).
• Q2 — Is the key high-cardinality with equality/JOIN access and no ordering? → yes: hash / bucketing (even spread, shuffle-free joins).
• Q3 — Is the key a small set of known categories queried/retired independently? → yes: list.
• Q4 — Do you need two of the above at once (category + time)? → composite (list-then-range, or range-then-hash).
• Q5 — Does one value/slice dominate? → add a hash sub-split or salt on the hot slice regardless of the top scheme.

Common interview probes on pruning and skew.

• "Why isn't my partitioned query pruning?" — required answer: the predicate doesn't reference the partition key.
• "How do you detect skew?" — per-partition size/row-count; one Spark task far slower than the rest.
• "How do you fix a hot key?" — salt it / sub-partition it; isolate the whale.
• "Static vs dynamic pruning?" — constant at plan time vs value from a join at runtime.

Worked example — making a stubborn query prune

Detailed explanation. A range-partitioned events table (by event_day) has a dashboard query that scans every partition despite a date filter. The culprit: the filter wraps the key in a function (date(created_at)), which the planner cannot match to the partition bounds. Walk through the diagnosis and the rewrite that restores pruning.

• Symptom. EXPLAIN shows an Append over all partitions.
• Root cause. WHERE date(created_at) = '2026-09-02' — the function on the key blocks pruning.
• Fix. Filter the bare key with a range: created_at >= '2026-09-02' AND created_at < '2026-09-03'.

Question. Show why the function-wrapped predicate fails to prune and rewrite it to prune to one partition.

Input.

Code.

-- Table partitioned by RANGE (created_at)
-- BAD — a function on the partition key defeats pruning
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE  date(created_at) = '2026-09-02';
-- ->  Append
--       ->  Seq Scan on events_20260901 ...
--       ->  Seq Scan on events_20260902 ...
--       ->  Seq Scan on events_20260903 ...
-- (planner can't map date(created_at) onto the raw-timestamp bounds)

-- GOOD — filter the BARE key with a half-open range
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE  created_at >= '2026-09-02' AND created_at < '2026-09-03';
-- ->  Aggregate -> Seq Scan on events_20260902   (one partition)

-- Also GOOD — sargable range on the key, driven by a parameter
PREPARE q(timestamptz) AS
SELECT count(*) FROM events
WHERE  created_at >= $1 AND created_at < $1 + INTERVAL '1 day';

Step-by-step explanation.

1. Partition bounds are defined on the raw key created_at. When the predicate wraps the key in date(created_at), the planner sees a computed expression, not the partition column, and cannot prove which partitions the result can come from — so it keeps them all.
2. The rewrite filters the bare key with a half-open range: created_at >= '2026-09-02' AND created_at < '2026-09-03'. This is sargable against the partition bounds; the planner prunes to events_20260902 alone.
3. The general rule is "keep the partition key naked on one side of the comparison." Any function, cast, or arithmetic on the key (date(k), k::date, k + interval, extract(... from k)) can block static pruning. Move the transformation to the literal side instead.
4. The parameterised PREPARE version keeps the key bare and computes the upper bound from the parameter, so runtime (generic) plans still prune. This matters for prepared statements and ORM-generated queries that reuse plans.
5. The same trap appears with implicit casts: comparing a timestamptz key to a date literal can force a cast on the key. Match the literal's type to the key's type so the comparison stays on the bare column.

Output.

Rule of thumb. Keep the partition key bare on one side of the predicate — never wrap it in a function or cast. Filter time-series tables with half-open ranges on the raw timestamp, not date(key) =. If EXPLAIN shows an Append over every partition, look first for a function or implicit cast on the key.

Worked example — detecting and quantifying skew

Detailed explanation. Before you can fix skew you must measure it. Walk through a per-partition size query on Postgres and a per-key frequency scan that finds the dominant value driving a hot partition, then read the numbers to decide the fix.

• Partition-size scan. Compare row counts / bytes across partitions; flag any >2–3× the median.
• Key-frequency scan. Find the top keys by row count; a single value with a huge share is the skew source.
• Decision. Coarse-interval skew → finer interval; dominant-value skew → salt / sub-partition.

Question. Write the queries that quantify partition skew and identify the dominant key, and interpret a skewed result.

Input.

Code.

-- 1. Per-partition row counts + sizes (Postgres)
SELECT
    child.relname                                   AS partition,
    pg_size_pretty(pg_relation_size(child.oid))     AS size,
    child.reltuples::bigint                          AS approx_rows
FROM   pg_inherits
JOIN   pg_class parent ON parent.oid = pg_inherits.inhparent
JOIN   pg_class child  ON child.oid  = pg_inherits.inhrelid
WHERE  parent.relname = 'events'
ORDER  BY pg_relation_size(child.oid) DESC;
--   partition          | size    | approx_rows
--   events_20260902    | 41 GB   | 512000000     <- 12x the median: SKEW
--   events_20260901    |  3 GB   |  38000000
--   events_20260903    |  3 GB   |  37000000

-- 2. Find the dominant key inside the hot partition
SELECT user_id, count(*) AS n
FROM   events_20260902
GROUP  BY user_id
ORDER  BY n DESC
LIMIT  5;
--   user_id | n
--   999999  | 470000000     <- one user = 92% of the hot partition
--   1234    |     51000

# 3. Spark-side skew signal — one task dwarfs the stage
# In the Spark UI, a stage where max task time >> median task time
# (e.g. 45 min vs 40 s) is the fingerprint of a skewed partition/key.
# Programmatic check on a DataFrame's key distribution:
from pyspark.sql import functions as F
(df.groupBy("user_id").count()
   .orderBy(F.desc("count"))
   .show(5))
# user_id=999999 count=470000000   <- dominant key -> salt this one

Step-by-step explanation.

1. The partition-size query joins pg_inherits (parent↔child map) with pg_class to list each partition's on-disk size and approximate row count. Sorting by size surfaces the outlier immediately: events_20260902 at 41 GB is ~12× the ~3 GB median — unambiguous skew.
2. Having found the hot partition, the key-frequency scan finds the hot value inside it. GROUP BY user_id ORDER BY count DESC shows user_id = 999999 owns 470M of the partition's 512M rows — 92%. The skew is one dominant key, not a broad imbalance.
3. This distinction drives the fix. A single dominant key → salt that key (or isolate it) so its rows spread; a broadly heavier partition with no single culprit → a finer interval or more buckets. Measuring tells you which.
4. On the Spark side, the fingerprint is a stage where one task's duration dwarfs the median (45 min vs 40 s). The groupBy(key).count() scan confirms the dominant key so you know exactly which value to salt.
5. Quantify before fixing: knowing the hot key holds 92% tells you a 16-way salt turns one 470M-row task into sixteen ~29M-row tasks — enough to rebalance. If it were only 3× the median, a lighter touch (a couple of extra buckets) would do.

Output.

Rule of thumb. Measure skew before fixing it: a per-partition size scan finds the hot partition, a per-key frequency scan finds the dominant value inside it, and one slow Spark task confirms it. A single dominant key calls for salting or isolation; a broadly heavy partition calls for a finer interval or more buckets.

Worked example — salting a hot key to rebalance

Detailed explanation. With the dominant key identified (user_id = 999999 at 92% of a partition), the fix is salting: append a small random bucket to the hot key so its rows spread across sub-groups, and explode the other side of any join so the salted keys still match. Walk through an aggregation and a join under salting.

• Salt. For the hot key, key || '#' || (rand()*16)::int → 16 sub-keys.
• Aggregate. Group by the salted key, then re-aggregate to fold the 16 sub-groups.
• Join. Explode the dimension side 16× for the hot key so every salt has a match.

Question. Salt the dominant key for a GROUP BY aggregation and for a join, restoring balance.

Input.

Code.

from pyspark.sql import functions as F

SALT = 16
HOT  = 999999

# 1. Salted aggregation — spread the hot key, then fold back
salted = df.withColumn(
    "salt",
    F.when(F.col("user_id") == HOT, (F.rand() * SALT).cast("int"))
     .otherwise(F.lit(0))
)
partial = (salted.groupBy("user_id", "salt")
                 .agg(F.sum("amount").alias("part")))     # 16 sub-groups for HOT
final = (partial.groupBy("user_id")
                .agg(F.sum("part").alias("total")))       # fold 16 -> 1

# 2. Salted join — explode the dimension side for the hot key so salts match
salts = spark.range(SALT).select(F.col("id").alias("salt"))   # 0..15

fact = df.withColumn(
    "salt",
    F.when(F.col("user_id") == HOT, (F.rand() * SALT).cast("int")).otherwise(F.lit(0))
)
# dim rows for the hot key are replicated across all 16 salts; others get salt=0
dim_salted = (dim.join(F.broadcast(salts),
                       F.col("user_id") == HOT, "left")
                 .withColumn("salt", F.coalesce(F.col("salt"), F.lit(0))))

joined = fact.join(dim_salted, ["user_id", "salt"])           # balanced 16-way

Step-by-step explanation.

1. Salting adds a random bucket 0–15 only to the hot key (user_id = 999999); every other key keeps salt 0. The hot key's 470M rows now split across 16 (user_id, salt) groups of ~29M each — balanced tasks.
2. For the aggregation, grouping by (user_id, salt) computes 16 partial sums for the hot key, then a second groupBy(user_id) folds those 16 partials into the final total. The two-stage aggregate is the price of splitting the hot key, and it is cheap (16 rows to fold).
3. For the join, the fact side is salted the same way. But the dimension row for the hot key must match all 16 salts, so it is replicated across salts 0–15 (a 16× explode of a single dimension row — negligible). Non-hot keys keep salt 0 on both sides and match normally.
4. After salting, the previously 470M-row task becomes 16 tasks of ~29M rows; the stage's max task time drops from 45 min toward the ~40 s median. The skew is gone without touching the non-hot keys.
5. Salt only the keys you must. Salting every key would 16× the whole dataset and the fold cost; salting just the detected hot key(s) keeps the fan-out tiny. This is why the detection step (previous example) is a prerequisite — you salt surgically.

Output.

Rule of thumb. Salt only the detected hot key(s): add a random 0..(S-1) bucket to spread them, re-aggregate to fold the sub-groups, and explode the other join side across the salts so matches still land. Surgical salting fixes the skew without inflating the rest of the dataset.

Data engineering interview question on pruning and skew

A senior interviewer might ask: "A range-partitioned (by day) events table on Spark/Iceberg has a nightly job that (a) scans every partition despite a date filter and (b) has one task that runs 40× longer than the rest. Diagnose both problems, fix the pruning, fix the skew, and prove each fix worked."

Solution Using a sargable date rewrite for pruning plus detection-then-salting for the skew

from pyspark.sql import functions as F

# --- PROBLEM A: no pruning. Root cause = function on the partition key. ---
# BAD: to_date() on the key blocks partition pruning in the scan.
bad = spark.sql("""
    SELECT * FROM events
    WHERE  to_date(event_ts) = '2026-09-02'
""")
# GOOD: half-open range on the BARE partition column prunes to one day.
good = spark.sql("""
    SELECT * FROM events
    WHERE  event_ts >= '2026-09-02' AND event_ts < '2026-09-03'
""")
good.explain()   # scan reports 1 partition read (PartitionFilters present)

# --- PROBLEM B: skew. Detect the dominant key, then salt it. ---
top = (good.groupBy("user_id").count().orderBy(F.desc("count")))
top.show(3)      # user_id=999999 count=470000000  -> the hot key

SALT, HOT = 16, 999999
salted = good.withColumn(
    "salt",
    F.when(F.col("user_id") == HOT, (F.rand() * SALT).cast("int")).otherwise(F.lit(0))
)
agg = (salted.groupBy("user_id", "salt").agg(F.sum("amount").alias("p"))
             .groupBy("user_id").agg(F.sum("p").alias("total")))

-- Prove pruning with Iceberg metadata (files scanned, not whole table)
-- SELECT * FROM events.files  -- inspect data_file partition + record counts
-- After the range rewrite, the query planner reports partitions=1.
-- After salting, the skewed stage's max task time falls to ~ the median.

Step-by-step trace.

After both fixes, the nightly job prunes to the single requested day (the scan reports one partition, not the whole table) and the previously 40×-slow task is gone because the hot user_id is spread across 16 balanced sub-groups that fold back into one total. Pruning is proven by the plan's partition filter; the skew fix is proven by the stage's max task time collapsing to the median.

Output:

Why this works — concept by concept:

• Sargable key predicate — pruning requires the partition key bare on one side; wrapping it in to_date() hides it from the partition filter. A half-open range on the raw event_ts restores static pruning to the single requested day.
• Detection before fixing — the groupBy(key).count() top-N scan identifies the one dominant value (92% of a day) so the salt is applied surgically to that key, not the whole dataset.
• Salting the hot key — a random 0..15 bucket on only the hot key splits its 470M rows across 16 balanced sub-groups; the other keys are untouched, so the fan-out stays tiny.
• Two-stage fold — grouping by (key, salt) then re-grouping by key folds the 16 partial sums into one correct total, the necessary complement to splitting the key.
• Cost — the pruning fix is free (a predicate rewrite) and cuts scan cost from O(all partitions) to O(one). The skew fix adds a 16× fan-out on a single key plus a cheap fold, turning one O(470M) task into sixteen O(29M) tasks. Both fixes are surgical: rewrite the predicate, salt only the whale.

SQL
Topic — optimization
Optimization problems on pruning and skew

Practice →

Spark
Topic — bucketing
Bucketing and skew-handling problems

Practice →

Cheat sheet — partitioning recipes

• Which scheme when. Range for an ordered key (date/time) — get cheap DROP-based retention for free. Hash for a high-cardinality key with equality/JOIN access and no ordering — even spread + shuffle-free bucketed joins. List for a small set of known categories queried/retired independently. Composite (list-then-range or range-then-hash) when you need two axes at once. Add a hash sub-split or salt on any dominant value regardless of the top scheme.
• Range DDL + retention. CREATE TABLE t (..., d DATE NOT NULL, PRIMARY KEY (id, d)) PARTITION BY RANGE (d); with half-open, gap-free children FOR VALUES FROM ('2026-09-01') TO ('2026-09-02'), a DEFAULT catch-all, and pre-created future partitions (pg_partman). Retire with ALTER TABLE t DETACH PARTITION p CONCURRENTLY; DROP TABLE p; — O(1), never DELETE.
• Hash DDL + bucket sizing. PARTITION BY HASH (key) with N children FOR VALUES WITH (MODULUS N, REMAINDER i). Size N ≈ total_size / ~256 MB, floor at the executor-core count, round to a power of two, and pick for mature volume — N is fixed at write time (changing it re-hashes everything). Equality prunes to one bucket; ranges do not prune.
• Bucketing for shuffle-free joins. Bucket both join sides on the same key into the same count and sort within buckets: Spark df.write.bucketBy(N, "k").sortBy("k").saveAsTable(...); Hive CLUSTERED BY (k) INTO N BUCKETS. Matching counts + key ⇒ SortMergeJoin with no Exchange. Mismatched counts silently reintroduce the shuffle.
• List + DEFAULT. PARTITION BY LIST (region) with FOR VALUES IN ('US','CA') (group related values), always a DEFAULT for the open-world case, and monitor DEFAULT — accumulating rows mean a new value needs its own partition.
• Composite / subpartition. Make a list/range child itself PARTITION BY RANGE/HASH; include every level's key in the PRIMARY KEY. Two-axis pruning narrows to a single leaf; retention drops independently per axis. Keep the leaf count (category × interval) in the low thousands.
• Prove pruning. EXPLAIN (COSTS OFF) should show one child (or a small Append), never an Append over all children for your hot query. Keep the partition key bare on one side of the predicate — no date(key), no cast, no arithmetic on the key. Half-open ranges on the raw timestamp, not date(key) =.
• Static vs dynamic pruning. Static = constant predicate pruned at plan time (shows in EXPLAIN). Dynamic = pruning value from a join/subquery, pruned at runtime (shows only in EXPLAIN ANALYZE / the engine's runtime filter). Both need the predicate on the partition key.
• Detect skew. Per-partition size scan (pg_inherits ⋈ pg_class by pg_relation_size); flag any partition >2–3× the median. Per-key frequency (GROUP BY key ORDER BY count DESC) finds the dominant value. In Spark, one task ≫ median task time in a stage is the fingerprint.
• Fix skew. Salt only the detected hot key(s): random 0..S-1 bucket, re-aggregate to fold, explode the other join side across salts. Or sub-partition the hot slice by hash/finer range, or isolate the whale in its own partition. Never salt the whole dataset — surgical only.
• Partition-count sizing. Aim for hundreds-to-low-thousands of partitions, each in the low-GB range (or 128 MB–1 GB per bucket file). Too few = no pruning benefit; too many = planning time and small-files overhead dominate. Size for mature volume.
• Partitioning vs sharding. Partitioning = one table, many chunks, one query planner (prunes, partition-wise joins). Sharding = many machines, no shared planner (a router picks the shard; cross-shard queries scatter-gather). Same key math; escalate to sharding only when one machine runs out of write/storage headroom.

Frequently asked questions

What are partitioning strategies in one sentence?

Partitioning strategies are the ways you physically decompose one large logical table into many smaller chunks along a chosen key — range (an ordered key like a date), hash (hash(key) % N for even spread), or list (explicit categorical values) — so that queries whose predicate matches the key read only the relevant chunks (partition pruning), so that independent chunks can be scanned in parallel, and so that old data can be retired by dropping a whole chunk instead of deleting rows. The key you pick, the number of partitions, and how well the query predicate aligns with the key together decide whether a partitioned table is dramatically faster or just carries extra overhead. It is one of the most-probed senior data-engineering topics because it is the load-bearing layout decision for every large warehouse or lake table.

Range vs hash vs list partitioning — when do I pick each?

Pick range partitioning when the key is ordered and queries filter by ranges of it — almost always a date or timestamp for time-series data; you also get cheap retention because retiring old data is a DROP PARTITION instead of a DELETE. Pick hash partitioning when the key is high-cardinality with no natural ordering and the workload is equality lookups or large joins — hash(key) % N spreads rows evenly so no partition runs hot, and co-bucketed tables join without a shuffle; the cost is that range predicates no longer prune. Pick list partitioning when the key is a small set of known categories (region, tenant, status) that are queried and retired independently — you map explicit values to partitions and add a DEFAULT for unlisted ones. When you need two of these at once (category and time), nest them with composite partitioning.

What is partition pruning and why isn't my query pruning?

Partition pruning is the optimiser eliminating partitions that cannot contain rows matching a query's predicate, so the scan reads only the surviving partitions instead of the whole table. It fires only when the query's WHERE references the partition key with a prunable operator (=, IN, or a range comparison for range partitioning). The number-one reason a partitioned query does not prune is that the predicate references a non-key column — then every partition is scanned. The number-two reason is wrapping the key in a function or cast (date(created_at) = ...), which hides the key from the planner; the fix is to keep the key bare and use a half-open range (created_at >= '...' AND created_at < '...'). Always confirm with EXPLAIN — a plan that shows an Append over every partition for your hot query means pruning is not happening.

What causes data skew and how do I fix it?

Data skew is when one partition holds far more (or far hotter) data than the others, so one worker does most of the work while the rest idle. It has three common causes: a coarse range interval (the current partition grows huge), a single dominant key value under hash partitioning (hashing cannot split one value across buckets), or a dominant category under list partitioning (one region with most of the rows). Detect it with a per-partition size scan (flag any partition >2–3× the median) and a per-key frequency scan (find the value with a disproportionate share); in Spark, one task running far longer than the rest of its stage is the fingerprint. Fix it surgically: salt only the hot key (append a small random bucket so its rows spread, then re-aggregate to fold and explode the other join side to match), sub-partition the hot slice by hash or a finer range, or isolate the whale in its own dedicated partition. Never salt the whole dataset — that just multiplies the work everywhere.

Is bucketing the same as hash partitioning?

Bucketing is hash partitioning applied at the file-layout level rather than the table-catalog level. In Spark, Hive, and Iceberg, bucketing writes rows into a fixed number of files per partition by hash(key) % num_buckets — the same modulus math as PARTITION BY HASH in a relational database. The reason bucketing gets its own name is its signature payoff: if two tables are bucketed on the same key into the same number of buckets, matching keys sit in matching bucket files, so a join can pair bucket-i with bucket-i locally and skip the shuffle entirely — often the single biggest speedup on a large join. A GROUP BY on the bucket key is likewise shuffle-free. The constraints are that the bucket count and key must match on both join sides, the count is fixed at write time, and you must size it (roughly total_size / 256 MB, a power of two, at least the core count) to avoid both giant buckets and the small-files problem.

Partitioning vs sharding — what's the difference?

Partitioning splits one logical table into many chunks within a single database or engine that shares one query planner: the planner prunes across local partitions, does partition-wise joins, and everything stays inside one connection and transaction boundary. Sharding splits data across independent machines that do not share a planner: an application-level router (or a proxy like Citus or Vitess) computes the shard from the key and talks to just that server, and a cross-shard query must scatter-gather and re-aggregate in the application. Both use the same key math — hash(customer_id) % 8 picks a shard exactly as it picks a partition — but they solve different problems: partitioning scales scan parallelism, pruning, and cheap retention; sharding scales write throughput and total storage beyond one machine. The right escalation is to partition first and only shard when a single machine genuinely runs out of write or storage headroom, keeping the hot queries single-partition and single-shard.

Practice on PipeCode

• Drill the database practice library → for the partitioning, pruning, retention, and schema-design problems senior interviewers love.
• Sharpen the plan-reading axis on the optimization practice library → for pruning verification, predicate sargability, and cost-based scan problems.
• Rehearse even-distribution joins on the bucketing practice library → for shuffle-free bucketed joins, bucket-count sizing, and skew-handling scenarios.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the range-vs-hash-vs-list decision tree against real graded inputs.

Lock in partitioning muscle memory

Docs explain the schemes. PipeCode drills explain the decision — when range earns cheap retention, when hash and bucketing skip the shuffle, when list isolates a tenant, when a dominant key demands salting, and when a query silently scans every partition. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs data engineers actually face.

Practice database problems →
Practice bucketing problems →