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.

Practice

1. What is the main purpose of creating partitioned tables in PostgreSQL?

easy

A. To split a large table into smaller, manageable parts based on a column

B. To create multiple copies of the same table for backup

C. To combine several tables into one large table

D. To encrypt the data in a table for security

Solution

1. Step 1: Understand partitioning concept

   Partitioned tables divide a big table into smaller parts based on a column value, improving management and query performance.

2. Step 2: Compare options

   Backup, merging several tables, and encryption are not related to partitioning.

3. Final Answer:

   To split a large table into smaller, manageable parts based on a column -> Option A

4. Quick Check:

   Partitioned tables split big tables = A [OK]

Hint: Partitioning means splitting big tables by column values [OK]

Common Mistakes:

• Confusing partitioning with backup or encryption
• Thinking partitioning merges tables instead of splitting
• Assuming partitioning duplicates data

2. Which of the following is the correct syntax to create a partitioned table by range on column created_date?

easy

A. CREATE TABLE orders PARTITION BY LIST (created_date);

B. CREATE TABLE orders PARTITION ON RANGE (created_date);

C. CREATE TABLE orders PARTITION BY HASH (created_date);

D. CREATE TABLE orders PARTITION BY RANGE (created_date);

Solution

1. Step 1: Recall partition syntax

   PostgreSQL uses PARTITION BY RANGE (column) to create range partitions.

2. Step 2: Check options

   CREATE TABLE orders PARTITION BY RANGE (created_date); uses correct syntax. CREATE TABLE orders PARTITION ON RANGE (created_date); uses wrong keyword 'PARTITION ON'. PARTITION BY LIST and PARTITION BY HASH use different partition types.

3. Final Answer:

   CREATE TABLE orders PARTITION BY RANGE (created_date); -> Option D

4. Quick Check:

   Correct syntax uses PARTITION BY RANGE [OK]

Hint: Use PARTITION BY RANGE (column) for range partitions [OK]

Common Mistakes:

• Using PARTITION ON instead of PARTITION BY
• Mixing partition types (LIST or HASH) when RANGE is needed
• Omitting parentheses around column name

3. Given the following commands, what will be the result of querying SELECT * FROM sales WHERE sale_year = 2023;?

CREATE TABLE sales (
  id INT,
  sale_year INT,
  amount NUMERIC
) PARTITION BY LIST (sale_year);

CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES IN (2022);
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES IN (2023);

INSERT INTO sales VALUES (1, 2022, 100), (2, 2023, 200), (3, 2023, 300);

medium

A. Rows with id 2 and 3 will be returned

B. Rows with id 1 and 2 will be returned

C. Only row with id 1 will be returned

D. No rows will be returned

Solution

1. Step 1: Understand partitioning by LIST on sale_year

   Table sales is partitioned by sale_year with partitions for 2022 and 2023.

2. Step 2: Analyze inserted data and query

   Rows with sale_year 2023 have ids 2 and 3. Query filters sale_year = 2023, so these rows are returned.

3. Final Answer:

   Rows with id 2 and 3 will be returned -> Option A

4. Quick Check:

   Query filters sale_year=2023, returns matching rows [OK]

Hint: Query returns rows matching partition values [OK]

Common Mistakes:

• Assuming all rows return regardless of partition
• Confusing partition column with other columns
• Forgetting to insert data into partitions

4. Identify the error in the following partition creation commands:

CREATE TABLE logs (
  id SERIAL,
  log_date DATE
) PARTITION BY RANGE (log_date);

CREATE TABLE logs_2023 PARTITION OF logs FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

medium

A. Partitioning by RANGE is not allowed on DATE columns

B. Missing PRIMARY KEY on the parent table

C. The TO value should be '2024-01-01' to include all 2023 dates

D. FOR VALUES clause should use LIST instead of RANGE

Solution

1. Step 1: Check RANGE partition boundaries

   RANGE partitions include values from FROM (inclusive) up to TO (exclusive). To cover all 2023 dates, TO must be '2024-01-01'.

2. Step 2: Analyze given TO value

   TO is '2023-12-31', which excludes that date and any after. This causes missing data for 2023-12-31.

3. Final Answer:

   The TO value should be '2024-01-01' to include all 2023 dates -> Option C

4. Quick Check:

   RANGE TO is exclusive, so use next day [OK]

Hint: RANGE TO value is exclusive; use next day after range end [OK]

Common Mistakes:

• Using inclusive TO value in RANGE partitions
• Thinking RANGE partitioning disallows DATE columns
• Confusing LIST and RANGE partition syntax

5. You want to create a partitioned table events partitioned by HASH on user_id with 4 partitions. Which set of commands correctly creates the table and its partitions?

hard

A. CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3);

B. CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);

C. CREATE TABLE events (id INT, user_id INT) PARTITION BY LIST (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3);

D. CREATE TABLE events (id INT, user_id INT) PARTITION BY RANGE (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES FROM (0) TO (1);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES FROM (1) TO (2);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES FROM (2) TO (3);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES FROM (3) TO (4);

Solution

1. Step 1: Understand HASH partition syntax

   HASH partitions require FOR VALUES WITH (MODULUS n, REMAINDER r) to define partitions.

2. Step 2: Check each option

   CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3); correctly uses HASH partitioning with modulus 4 and remainders 0 to 3. CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3); uses LIST syntax incorrectly. CREATE TABLE events (id INT, user_id INT) PARTITION BY LIST (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3); uses LIST partitioning, not HASH. CREATE TABLE events (id INT, user_id INT) PARTITION BY RANGE (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES FROM (0) TO (1); CREATE TABLE events_p1 PARTITION OF events FOR VALUES FROM (1) TO (2); CREATE TABLE events_p2 PARTITION OF events FOR VALUES FROM (2) TO (3); CREATE TABLE events_p3 PARTITION OF events FOR VALUES FROM (3) TO (4); uses RANGE partitioning, not HASH.

3. Final Answer:

   The commands using PARTITION BY HASH (user_id) with FOR VALUES WITH (MODULUS 4, REMAINDER 0-3) -> Option B

4. Quick Check:

   HASH partitions use MODULUS and REMAINDER [OK]

Hint: HASH partitions use MODULUS and REMAINDER in FOR VALUES WITH clause [OK]

Common Mistakes:

• Using FOR VALUES IN instead of FOR VALUES WITH for HASH
• Mixing partition types (LIST or RANGE) with HASH
• Omitting modulus or remainder values