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.

Practice

1. What is the main purpose of sub-partitioning in PostgreSQL?

easy

A. To encrypt data within partitions

B. To create backups of partitions automatically

C. To merge multiple partitions into one

D. To split data twice for better organization and faster queries

Solution

1. Step 1: Understand partitioning basics

   Partitioning divides a table into parts to improve management and performance.

2. Step 2: Recognize sub-partitioning role

   Sub-partitioning splits each partition further, organizing data more finely and speeding up queries.

3. Final Answer:

   To split data twice for better organization and faster queries -> Option D

4. Quick Check:

   Sub-partitioning = double data split [OK]

Hint: Sub-partitioning means splitting partitions again [OK]

Common Mistakes:

• Thinking sub-partitioning creates backups
• Confusing sub-partitioning with encryption
• Believing it merges partitions

2. Which of the following is the correct syntax to create a sub-partitioned table in PostgreSQL?

easy

A. CREATE TABLE sales (id INT, region TEXT, month INT) SUBPARTITION BY RANGE (region) PARTITION BY LIST (month);

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

C. CREATE TABLE sales (id INT, region TEXT, month INT) PARTITION BY RANGE (region) SUBPARTITION BY LIST (month);

D. CREATE TABLE sales (id INT, region TEXT, month INT) PARTITION BY LIST (region) SUBPARTITION BY HASH (month);

Solution

1. Step 1: Identify correct keywords for partitioning

   PostgreSQL uses PARTITION BY for main partition and SUBPARTITION BY for sub-partition.

2. Step 2: Check syntax order and clauses

   CREATE TABLE sales (id INT, region TEXT, month INT) PARTITION BY RANGE (region) SUBPARTITION BY LIST (month); correctly uses PARTITION BY RANGE then SUBPARTITION BY LIST, matching PostgreSQL syntax.

3. Final Answer:

   CREATE TABLE sales (id INT, region TEXT, month INT) PARTITION BY RANGE (region) SUBPARTITION BY LIST (month); -> Option C

4. Quick Check:

   Use PARTITION BY then SUBPARTITION BY [OK]

Hint: Use PARTITION BY first, then SUBPARTITION BY [OK]

Common Mistakes:

• Using PARTITION BY twice instead of SUBPARTITION BY
• Swapping PARTITION BY and SUBPARTITION BY keywords
• Using SUBPARTITION BY before PARTITION BY

3. Given the following table and partitions:

CREATE TABLE orders (id INT, country TEXT, year INT) PARTITION BY LIST (country) SUBPARTITION BY RANGE (year);
CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US') SUBPARTITION BY RANGE (year);
CREATE TABLE orders_us_2022 PARTITION OF orders_us FOR VALUES FROM (2022) TO (2023);

What will be the result of SELECT * FROM orders WHERE country = 'US' AND year = 2022; if there are rows with country 'US' and year 2022?

medium

A. Rows with country 'US' and year 2022 will be returned

B. No rows will be returned because subpartition is missing

C. Syntax error due to incorrect partitioning

D. Rows with any country but year 2022 will be returned

Solution

1. Step 1: Understand partition and subpartition setup

   The table is partitioned by country (LIST) and subpartitioned by year (RANGE). The 'US' partition and 2022 subpartition exist.

2. Step 2: Query filters match partition and subpartition

   The query filters country='US' and year=2022, matching the defined partitions, so matching rows will be found.

3. Final Answer:

   Rows with country 'US' and year 2022 will be returned -> Option A

4. Quick Check:

   Partition + subpartition match = rows returned [OK]

Hint: Query matches partition and subpartition filters [OK]

Common Mistakes:

• Assuming no rows because subpartition is complex
• Thinking query causes syntax error
• Ignoring subpartition filtering

4. You wrote this code:

CREATE TABLE logs (id INT, region TEXT, day DATE) PARTITION BY RANGE (region) SUBPARTITION BY LIST (day);

What is the error in this statement?

medium

A. RANGE partitioning cannot be done on a TEXT column

B. Partitioning by RANGE requires a numeric or date type, not TEXT

C. Syntax error: SUBPARTITION BY must come before PARTITION BY

D. SUBPARTITION BY LIST cannot be used with RANGE partitioning

Solution

1. Step 1: Check partition column data type

   Partitioning by RANGE requires a column with an orderable type like numeric or date, not TEXT.

2. Step 2: Identify the error cause

   Here, region is TEXT, so RANGE partitioning on it is invalid.

3. Final Answer:

   Partitioning by RANGE requires a numeric or date type, not TEXT -> Option B

4. Quick Check:

   RANGE needs numeric/date, not TEXT [OK]

Hint: RANGE partition needs numeric or date column [OK]

Common Mistakes:

• Thinking TEXT can be used for RANGE partitioning
• Confusing order of PARTITION BY and SUBPARTITION BY
• Assuming SUBPARTITION BY LIST is invalid with RANGE

5. You want to create a sales table partitioned by region (LIST) and subpartitioned by sale_date (RANGE). Which approach correctly handles the subpartitioning to optimize query performance for recent sales?

hard

A. Partition by LIST on region, then subpartition by RANGE on sale_date with recent years as separate subpartitions

B. Partition by RANGE on sale_date, then subpartition by LIST on region with all regions in one subpartition

C. Partition by HASH on region, no subpartitioning needed for sale_date

D. Partition by LIST on sale_date, then subpartition by RANGE on region

Solution

1. Step 1: Match partitioning to data and query needs

   Partitioning by region (LIST) groups data by location, then subpartitioning by sale_date (RANGE) organizes by time.

2. Step 2: Optimize recent sales queries

   Using RANGE subpartitions for recent years allows fast access to recent data, improving query speed.

3. Final Answer:

   Partition by LIST on region, then subpartition by RANGE on sale_date with recent years as separate subpartitions -> Option A

4. Quick Check:

   LIST then RANGE for region and date [OK]

Hint: Partition by region LIST, subpartition by date RANGE [OK]

Common Mistakes:

• Reversing partition and subpartition order
• Using HASH partitioning without subpartitioning
• Partitioning sale_date by LIST instead of RANGE