Table Partitioning

A 2-billion-row logs table full-scans on every date-filtered query, despite an index. Partitioning eliminates the irrelevant 99% before a single row is touched.

A B-tree index makes finding matching rows within a table faster, but it does not shrink the table itself — every index still ultimately points back into one large heap. Partitioning takes a structurally different approach: the table is split into several genuinely separate child tables, each holding a specific range (or list, or hash bucket) of rows, unified under one parent table name for querying. A query whose WHERE clause can be matched against the partitioning column lets the planner skip entire child partitions before touching a single row in them — not a faster scan of everything, but no scan at all of what clearly cannot match.\n\nThis lab builds a genuinely partitioned table by month, confirms real rows land in the correct child partition, and proves partition pruning directly in EXPLAIN: a query for one month's data touches only that partition, and a query spanning two months touches exactly those two while a third, irrelevant one is completely absent from the plan.

A Range-Partitioned Table, Three Months

CREATE TABLE sql_orders_part (id int, order_date date, amount numeric) PARTITION BY RANGE (order_date);
CREATE TABLE sql_orders_part_jan PARTITION OF sql_orders_part FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE sql_orders_part_feb PARTITION OF sql_orders_part FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE sql_orders_part_mar PARTITION OF sql_orders_part FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

Each child partition is a genuine, separate table with its own explicit range boundary — FOR VALUES FROM ... TO ... is a half-open interval, inclusive of the start date and exclusive of the end.

Real Rows, Routed to the Correct Partition

INSERT INTO sql_orders_part SELECT g, DATE '2025-01-01' + ((g%89)||' days')::interval, g FROM generate_series(1,90000) g;
SELECT count(*) FROM ONLY sql_orders_part_jan;  -- 31362
SELECT count(*) FROM ONLY sql_orders_part_feb;  -- 28308
SELECT count(*) FROM ONLY sql_orders_part_mar;  -- 30330

A single INSERT against the parent table sql_orders_part — PostgreSQL routes every row to its correct child partition automatically, based on the row's own order_date value. ONLY restricts the count to that specific child table, confirming the routing directly rather than trusting it blindly.

Partition Pruning, Proven in EXPLAIN

EXPLAIN SELECT * FROM sql_orders_part WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01';

Seq Scan on sql_orders_part_jan sql_orders_part  (cost=0.00..476.00 rows=102 width=40)

Only sql_orders_part_jan appears — _feb and _mar are not scanned, not even considered, because the planner proved from the WHERE clause alone that they cannot contain any matching rows.

EXPLAIN SELECT * FROM sql_orders_part WHERE order_date >= '2025-01-15' AND order_date < '2025-02-15';

Append  (cost=0.00..908.17 rows=194 width=40)
  ->  Seq Scan on sql_orders_part_jan sql_orders_part_1
  ->  Seq Scan on sql_orders_part_feb sql_orders_part_2

This range spans January and February — both appear under Append, and _mar is completely absent, correctly excluded even though it was never explicitly mentioned in the query.

Partition pruning

The planner's ability to prove, from a query's WHERE clause and each partition's declared range/list/hash bounds, that certain child partitions cannot possibly contain any matching rows — and to exclude them from the plan entirely, not merely scan them quickly. A pruned partition does not appear in EXPLAIN's output at all, which is the direct, checkable evidence that pruning genuinely happened rather than being assumed.

PARTITION BY RANGE / LIST / HASH

The three partitioning strategies PostgreSQL supports: RANGE assigns rows to a partition based on where their value falls within a declared interval (dates, sequential IDs); LIST assigns rows based on exact value membership in an explicit set (a country code, a status value); HASH assigns rows to a fixed number of partitions based on a hash of the partitioning column, used to spread load evenly when there is no natural range or list grouping.

🗂️ Build a Range-Partitioned Table by Month

Create a parent table partitioned by order_date, with 3 explicit monthly child partitions.

psql -U postgres -d beer_db -c "CREATE TABLE sql_orders_part (id int, order_date date, amount numeric) PARTITION BY RANGE (order_date);" -c "CREATE TABLE sql_orders_part_jan PARTITION OF sql_orders_part FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');" -c "CREATE TABLE sql_orders_part_feb PARTITION OF sql_orders_part FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');" -c "CREATE TABLE sql_orders_part_mar PARTITION OF sql_orders_part FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_orders_part (id int, order_date date, amount numeric) PARTITION BY RANGE (order_date);" -c "CREATE TABLE sql_orders_part_jan PARTITION OF sql_orders_part FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');" -c "CREATE TABLE sql_orders_part_feb PARTITION OF sql_orders_part FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');" -c "CREATE TABLE sql_orders_part_mar PARTITION OF sql_orders_part FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');" SET CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE

📬 Insert Data and Verify Correct Routing

Insert 90,000 rows into the parent table, then confirm directly how many landed in each specific child partition.

psql -U postgres -d beer_db -c "INSERT INTO sql_orders_part SELECT g, DATE '2025-01-01' + ((g%89)||' days')::interval, g FROM generate_series(1,90000) g;"

psql -U postgres -d beer_db -c "SELECT count(*) FROM ONLY sql_orders_part_jan;" -c "SELECT count(*) FROM ONLY sql_orders_part_feb;" -c "SELECT count(*) FROM ONLY sql_orders_part_mar;"

student@lab:~$ psql -U postgres -d beer_db -c "INSERT INTO sql_orders_part SELECT g, DATE '2025-01-01' + ((g%89)||' days')::interval, g FROM generate_series(1,90000) g;" SET INSERT 0 90000 student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM ONLY sql_orders_part_jan;" -c "SELECT count(*) FROM ONLY sql_orders_part_feb;" -c "SELECT count(*) FROM ONLY sql_orders_part_mar;" SET count ------- 31362 (1 row) count ------- 28308 (1 row) count ------- 30330 (1 row)

✂️ Confirm Partition Pruning for a Single Month

Run EXPLAIN on a query filtered to exactly one month, and confirm only that one partition appears in the plan.

psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM sql_orders_part WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01';"

student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM sql_orders_part WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01';" SET QUERY PLAN ------------------------------------------------------------------------------------------ Seq Scan on sql_orders_part_jan sql_orders_part (cost=0.00..476.00 rows=102 width=40) Filter: ((order_date >= '2025-01-01'::date) AND (order_date < '2025-02-01'::date)) (2 rows)

🔀 Confirm Pruning Correctly Excludes a Third, Irrelevant Partition

Run a query spanning exactly two months, and confirm the plan touches only those two, correctly excluding the third.

psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM sql_orders_part WHERE order_date >= '2025-01-15' AND order_date < '2025-02-15';"

student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM sql_orders_part WHERE order_date >= '2025-01-15' AND order_date < '2025-02-15';" SET QUERY PLAN ------------------------------------------------------------------------------------------------ Append (cost=0.00..908.17 rows=194 width=40) -> Seq Scan on sql_orders_part_jan sql_orders_part_1 (cost=0.00..476.00 rows=102 width=40) Filter: ((order_date >= '2025-01-15'::date) AND (order_date < '2025-02-15'::date)) -> Seq Scan on sql_orders_part_feb sql_orders_part_2 (cost=0.00..431.20 rows=92 width=40) Filter: ((order_date >= '2025-01-15'::date) AND (order_date < '2025-02-15'::date)) (5 rows)

🔍 Check What pg_partman Would Automate — and Whether It Exists Here

Understand pg_partman's role in automating partition creation and retention, then check directly whether it can actually be installed on this server.

psql -U postgres -d beer_db -c "SELECT * FROM pg_available_extensions WHERE name = 'pg_partman';"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM pg_available_extensions WHERE name = 'pg_partman';" SET name | default_version | installed_version | comment ------+-----------------+-------------------+--------- (0 rows)

Lab 3.4.1 complete. Table partitioning, proven with real routing and real pruning:\n\n\n Range-partitioned table, 3 months : ✅ built, real child tables\n 90,000 rows, correctly routed : ✅ 31,362 + 28,308 + 30,330\n Single-month pruning : ✅ only 1 partition in the plan\n 2-month pruning, 3rd excluded : ✅ Append of exactly 2, proven\n pg_partman checked, not assumed : ✅ confirmed absent, substitute understood\n

Enable JavaScript to run the live terminal and track your progress.