Creating partitioned tables in PostgreSQL - Visual Walkthrough

Concept Flow - Creating partitioned tables

Start: Define main table

↓

Specify PARTITION BY clause

↓

Create child partitions

↓

Insert data into main table

↓

Data routed to correct partition

↓

Query main table returns combined data

You first create a main table with a partitioning rule, then create child tables as partitions. When you insert data, PostgreSQL routes it to the right partition automatically.

Execution Sample

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  region TEXT,
  amount INT
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales FOR VALUES IN ('North');
CREATE TABLE sales_south PARTITION OF sales FOR VALUES IN ('South');

This code creates a sales table partitioned by region, with two partitions for 'North' and 'South'.

Execution Table

Step	Action	Details	Result
1	Create main table	Define sales table with PARTITION BY LIST(region)	Main table 'sales' created with partitioning rule
2	Create partition sales_north	Partition for region='North'	Partition table 'sales_north' created
3	Create partition sales_south	Partition for region='South'	Partition table 'sales_south' created
4	Insert row (region='North')	Insert into sales (region, amount) values ('North', 100)	Row stored in 'sales_north' partition
5	Insert row (region='South')	Insert into sales (region, amount) values ('South', 200)	Row stored in 'sales_south' partition
6	Query sales	Select * from sales	Returns combined rows from both partitions
7	Insert row (region='East')	Insert into sales (region, amount) values ('East', 300)	Error: no partition for 'East' region

💡 Execution stops on error when inserting data for a region without a partition.

Variable Tracker

Variable	Start	After Step 4	After Step 5	After Step 7
sales_north rows	0	1 row (region='North', amount=100)	1 row	1 row
sales_south rows	0	0 rows	1 row (region='South', amount=200)	1 row
sales rows (main)	0	1 row	2 rows	Error on insert

Key Moments - 2 Insights

Why does inserting a row with region='East' cause an error?

Because there is no partition defined for 'East' region, PostgreSQL cannot route the data, causing an error as shown in step 7 of the execution_table.

Does data get stored in the main table directly?

No, data is stored in the child partitions. The main table acts as a parent and routes data to the correct partition, as seen in steps 4 and 5.

Visual Quiz - 3 Questions

Look at the execution_table, where is the row with region='North' stored after insertion?

A In the sales_north partition

B In the main sales table

C In the sales_south partition

D Nowhere, insertion fails

💡 Hint

Check step 4 in the execution_table where the row is routed to 'sales_north'.

At which step does the insertion fail due to missing partition?

A Step 4

B Step 7

C Step 5

D Step 6

💡 Hint

Look at the error described in step 7 of the execution_table.

If you add a partition for region='East', what changes in the variable_tracker after inserting an 'East' row?

A sales_north rows increase

B sales_south rows increase

C A new partition's row count increases

D Main sales table row count increases only

💡 Hint

Partitions store data separately; adding a new partition means its row count changes, not the existing ones.

Concept Snapshot

CREATE TABLE main_table (...) PARTITION BY LIST(column);
CREATE TABLE partition_name PARTITION OF main_table FOR VALUES IN ('value');
Data inserted into main_table is routed to correct partition.
Missing partition for a value causes insert error.
Querying main_table returns combined data from all partitions.

Full Transcript

Creating partitioned tables in PostgreSQL involves defining a main table with a partitioning rule, such as PARTITION BY LIST on a column. Then, child tables are created as partitions for specific values. When data is inserted into the main table, PostgreSQL automatically routes it to the correct partition based on the value. If no matching partition exists, insertion fails with an error. Querying the main table returns combined data from all partitions transparently.