HardDSA13 min read

What is table partitioning?

Q: What is table partitioning?

**Target Audience:** Developers whose biggest table keeps growing and who keep hearing "just partition it", and candidates who need to explain what partitioning does and does not solve. **Difficulty:** Hard **Engines:** PostgreSQL 16.15 (declarative partitioning; every plan and error here was produced on it) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable example uses; it has no partitioning, so the example builds it by hand). MySQL facts are from its manual and marked as not run here. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result, plan and error shown was produced by running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First An accountant keeps every receipt the company ever received in one enormous box. To find March's receipts she digs through ten years of paper. To throw away

44 views

82 of 82

The problem

Question presented to candidate: "Our events table has a few billion rows, and most queries only look at the last few days. Someone suggested partitioning it. What is table partitioning, how does it work in PostgreSQL, and what does it actually buy us — and cost us?"

What a strong answer should cover:

Definition: one logical table stored as several physical tables (partitions), each holding the rows for one range, list of values or hash bucket of a partition key; queries and inserts still target the parent
The three methods (RANGE, LIST, HASH) and when each fits; a DEFAULT partition for rows that match no partition
The real benefits: partition pruning (scanning only the partitions the WHERE clause can match), cheap retention by detaching or dropping a whole partition instead of a huge DELETE, smaller per-partition indexes and maintenance
The costs and rules: primary keys and unique constraints must include the partition key, queries without the key scan every partition, too many partitions slow planning, partitions must be created ahead of the data
How it differs from sharding (partitions live on one server) and from indexing (an index does not make old data cheap to drop)
Engine facts: PostgreSQL declarative partitioning since version 10 (HASH and DEFAULT since 11); MySQL partitioned InnoDB tables cannot have foreign keys; SQLite has no partitioning

Clarifying questions expected:

Which column do almost all queries filter on, and how is old data removed today?
How many rows per day, and how long must data be kept?

Code / implementation expected: Optional — a PARTITION BY RANGE table with monthly partitions, a pruned plan, and a detach-and-drop for retention.

Schema used by the examples:

partitioning

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers whose biggest table keeps growing and who keep hearing "just partition it", and candidates who need to explain what partitioning does and does not solve. Difficulty: Hard Engines: PostgreSQL 16.15 (declarative partitioning; every plan and error here was produced on it) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable example uses; it has no partitioning, so the example builds it by hand). MySQL facts are from its manual and marked as not run here.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result, plan and error shown was produced by running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

An accountant keeps every receipt the company ever recei

The solution is hidden so you can try first

13 min read · 13 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSQLite: partitioning by hand — one table per month, a UNION ALL view, and a cheap DROP for retention

PostgreSQL: a RANGE-partitioned table routes each row to its month (and an April row to DEFAULT)

CREATE TABLE events_part (
  id int NOT NULL,
  event_date date NOT NULL,
  user_id int NOT NULL,
  kind text NOT NULL,
  PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);
CREATE TABLE events_part_2024_01 PARTITION OF events_part
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_part_2024_02 PARTITION OF events_part
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_part_2024_03 PARTITION OF events_part
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE events_part_default PARTITION OF events_part DEFAULT;

INSERT INTO events_part SELECT id, event_date::date, user_id, kind FROM events;
INSERT INTO events_part VALUES (11, '2024-04-02', 2, 'login');

SELECT tableoid::regclass AS stored_in, COUNT(*) AS row_count,
       MIN(event_date) AS first_day, MAX(event_date) AS last_day
FROM events_part
GROUP BY tableoid
ORDER BY first_day;

PostgreSQL: partition pruning — a February filter scans one partition, a filter without the key scans all

CREATE TABLE events_part (
  id int NOT NULL,
  event_date date NOT NULL,
  user_id int NOT NULL,
  kind text NOT NULL,
  PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);
CREATE TABLE events_part_2024_01 PARTITION OF events_part
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_part_2024_02 PARTITION OF events_part
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_part_2024_03 PARTITION OF events_part
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE events_part_default PARTITION OF events_part DEFAULT;
INSERT INTO events_part SELECT id, event_date::date, user_id, kind FROM events;
ANALYZE events_part;
CREATE FUNCTION plan(q text) RETURNS SETOF text LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
    RETURN NEXT r."QUERY PLAN";
  END LOOP;
END $$;
CREATE TABLE steps (n serial, query text, plan_line text);

INSERT INTO steps (query, plan_line)
SELECT 'February only', plan('SELECT COUNT(*) FROM events_part WHERE event_date >= ''2024-02-01'' AND event_date < ''2024-03-01''');

INSERT INTO steps (query, plan_line)
SELECT 'no date filter', plan('SELECT COUNT(*) FROM events_part WHERE user_id = 2');

SELECT query, plan_line FROM steps ORDER BY n;

PostgreSQL: retention and growth — DETACH and DROP January, CREATE April, list the partitions

CREATE TABLE events_part (
  id int NOT NULL,
  event_date date NOT NULL,
  user_id int NOT NULL,
  kind text NOT NULL,
  PRIMARY KEY (id, event_date)
) PARTITION BY RANGE (event_date);
CREATE TABLE events_part_2024_01 PARTITION OF events_part
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_part_2024_02 PARTITION OF events_part
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_part_2024_03 PARTITION OF events_part
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE events_part_default PARTITION OF events_part DEFAULT;
INSERT INTO events_part SELECT id, event_date::date, user_id, kind FROM events;

-- Retention: remove January without a DELETE
ALTER TABLE events_part DETACH PARTITION events_part_2024_01;
DROP TABLE events_part_2024_01;

-- Next month: create the partition before its rows arrive
CREATE TABLE events_part_2024_04 PARTITION OF events_part
  FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

SELECT c.relname AS partition_name,
       pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'events_part'::regclass
ORDER BY c.relname;

Discussion

Other approaches, follow-ups and how it went in real interviews.

No comments yet. Share how you would answer it.

Question 82 of 82 in SQL

Keep your momentum going.

Mark this question solved to track your progress, then take the next one while it is fresh.

All SQL questions

End of the topic

That was the last SQL question.

Pick another topic