Sub-partitioning in PostgreSQL - Step-by-Step Execution

Concept Flow - Sub-partitioning

Create main partitioned table

↓

Define first-level partitions

↓

Define sub-partitions inside each partition

↓

Insert data

↓

Data routed to correct sub-partition

↓

Query data from main table

↓

PostgreSQL fetches from relevant sub-partition

Sub-partitioning splits data first by main partitions, then further divides each partition into sub-partitions for better organization and query performance.

Execution Sample

PostgreSQL

CREATE TABLE sales (
  id INT,
  region TEXT,
  month INT
) PARTITION BY LIST (region);

CREATE TABLE sales_us PARTITION OF sales FOR VALUES IN ('US') PARTITION BY RANGE (month);

CREATE TABLE sales_us_jan PARTITION OF sales_us FOR VALUES FROM (1) TO (2);

This code creates a main table partitioned by region, then sub-partitions the US region by month ranges.

Execution Table

Step	Action	Input/Condition	Result/Output
1	Create main table 'sales' partitioned by region	N/A	Table 'sales' created with LIST partitioning on 'region'
2	Create partition 'sales_us' for region 'US'	FOR VALUES IN ('US')	Partition 'sales_us' created, sub-partitioned by RANGE on 'month'
3	Create sub-partition 'sales_us_jan' for January	FOR VALUES FROM (1) TO (2)	Sub-partition 'sales_us_jan' created for month=1
4	Insert row (1, 'US', 1)	region='US', month=1	Row routed to 'sales_us_jan' sub-partition
5	Insert row (2, 'US', 3)	region='US', month=3	Row routed to 'sales_us' partition but no matching sub-partition, error or rejection
6	Query SELECT * FROM sales WHERE region='US' AND month=1	region='US', month=1	Data fetched from 'sales_us_jan' sub-partition
7	Query SELECT * FROM sales WHERE region='EU'	region='EU'	No matching partition, returns empty or error

💡 Execution stops after all partitions and sub-partitions are created and data is routed accordingly.

Variable Tracker

Variable	Start	After Step 4	After Step 5	Final
Table 'sales'	Empty	Contains 1 row in 'sales_us_jan'	Error or no row for month=3	Contains 1 row in 'sales_us_jan'
Partition 'sales_us_jan'	Empty	Contains 1 row	No change	Contains 1 row
Partition 'sales_us'	Empty	Contains 1 row in sub-partition	No matching sub-partition for month=3	Contains 1 row in sub-partition

Key Moments - 2 Insights

Why does the row with month=3 cause an error or no insertion in step 5?

Because there is no sub-partition defined for month=3 inside 'sales_us', so PostgreSQL cannot route the row to a sub-partition, causing an error or rejection as shown in execution_table step 5.

How does PostgreSQL decide which sub-partition to put a row in?

PostgreSQL first checks the main partition key (region), then within that partition it uses the sub-partition key (month) to route the row to the correct sub-partition, as seen in step 4 routing to 'sales_us_jan'.

Visual Quiz - 3 Questions

Look at the execution_table at step 4. Where is the row with region='US' and month=1 stored?

A In the main table 'sales' directly

B In the sub-partition 'sales_us_jan'

C In the partition 'sales_us' but not in any sub-partition

D It causes an error and is not stored

💡 Hint

Refer to execution_table row 4 showing routing to 'sales_us_jan' sub-partition

At which step does PostgreSQL create the sub-partition for January?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Check execution_table row 3 where 'sales_us_jan' is created for month=1

If you insert a row with region='US' and month=5, what will happen according to the execution flow?

A It will be stored in 'sales_us_jan' sub-partition

B It will be stored in 'sales_us' partition without sub-partition

C It will cause an error due to missing sub-partition

D It will be stored in the main 'sales' table

💡 Hint

Refer to execution_table step 5 where missing sub-partition causes error

Concept Snapshot

Sub-partitioning splits a main partitioned table into smaller parts.
Syntax: PARTITION BY ... then PARTITION BY ... inside partitions.
Data is routed first by main partition key, then by sub-partition key.
Missing sub-partitions cause insert errors.
Improves query speed by narrowing data access.

Full Transcript

Sub-partitioning in PostgreSQL means dividing a partitioned table further into smaller partitions called sub-partitions. First, you create a main table partitioned by one column, like region. Then, inside each partition, you define sub-partitions by another column, like month. When inserting data, PostgreSQL routes rows first to the main partition based on the first key, then to the correct sub-partition based on the second key. If a sub-partition does not exist for a value, insertion fails. Queries on the main table fetch data from the relevant sub-partitions, improving performance by scanning less data.