Partition types (range, list, hash) in PostgreSQL - Step-by-Step Execution

Concept Flow - Partition types (range, list, hash)

Start: Create Partitioned Table

↓

Choose Partition Type

↓

Range

↓

Define Ranges

↓

Create Partitions

↓

Insert Data Routed to Correct Partition

↓

Query Data Efficiently

You start by creating a partitioned table, then pick one partition type: range, list, or hash. Each type defines how data is split into partitions. Data inserts go to the right partition, making queries faster.

Execution Sample

PostgreSQL

CREATE TABLE sales (
  id INT,
  sale_date DATE,
  region TEXT
) PARTITION BY RANGE (sale_date);

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

This creates a sales table partitioned by date ranges, then creates a partition for all sales in 2023.

Execution Table

Step	Action	Partition Type	Partition Definition	Data Routing Example	Result
1	Create main table with partitioning	Range	PARTITION BY RANGE (sale_date)	N/A	Table 'sales' created as partitioned
2	Create partition for 2023	Range	FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')	N/A	Partition 'sales_2023' created
3	Insert row with sale_date = '2023-06-15'	Range	N/A	Falls into 2023 partition	Row stored in 'sales_2023'
4	Create main table with list partitioning	List	PARTITION BY LIST (region)	N/A	Table 'customers' created as partitioned
5	Create partition for region 'North'	List	FOR VALUES IN ('North')	N/A	Partition 'customers_north' created
6	Insert row with region = 'North'	List	N/A	Falls into 'North' partition	Row stored in 'customers_north'
7	Create main table with hash partitioning	Hash	PARTITION BY HASH (id) PARTITIONS 4	N/A	Table 'orders' created as partitioned
8	Create 4 hash partitions	Hash	FOR VALUES WITH (MODULUS 4, REMAINDER 0) FOR VALUES WITH (MODULUS 4, REMAINDER 1) FOR VALUES WITH (MODULUS 4, REMAINDER 2) FOR VALUES WITH (MODULUS 4, REMAINDER 3)	N/A	Partitions 'orders_0' to 'orders_3' created
9	Insert row with id = 7	Hash	N/A	Hash(id) mod 4 = 3	Row stored in 'orders_3'
10	Query data	All	N/A	Query optimizer targets relevant partitions	Faster query execution
11	End	N/A	N/A	N/A	Partitioning setup complete

💡 All partitions created and data routed correctly; queries can now use partitions for efficiency.

Variable Tracker

Variable	Start	After Step 2	After Step 5	After Step 8	Final
Partitioned Table	None	sales (Range)	sales, customers (Range, List)	sales, customers, orders (Range, List, Hash)	All 3 tables partitioned
Partitions Created	0	1 (sales_2023)	2 (sales_2023, customers_north)	6 (sales_2023, customers_north, orders_0..orders_3)	6 partitions total
Data Insert Example	N/A	Row with sale_date=2023-06-15 in sales_2023	Row with region='North' in customers_north	Row with id=7 in orders_3	Rows routed to correct partitions

Key Moments - 3 Insights

Why do we need to define ranges or lists when creating partitions?

Because PostgreSQL needs clear rules to decide which partition each row belongs to. See execution_table rows 2 and 5 where partitions are defined with ranges or lists.

How does hash partitioning decide where to put data?

It uses a hash function on the partition key and divides by the number of partitions. For example, in row 9, id=7 goes to partition 3 because hash(7) mod 4 = 3.

Can data be stored outside partitions in a partitioned table?

No, all data must fit into one of the defined partitions. If no partition matches, insert fails. This is why defining partitions correctly is important.

Visual Quiz - 3 Questions

Look at the execution_table row 3. Where is a row with sale_date = '2023-06-15' stored?

A In the partition 'sales_2023'

B In the main table 'sales'

C In a hash partition

D It is rejected because no partition matches

💡 Hint

Check the 'Data Routing Example' and 'Result' columns in row 3 of execution_table.

According to variable_tracker, how many partitions exist after step 8?

A 3

B 6

C 4

D 8

💡 Hint

Look at the 'Partitions Created' row after step 8 in variable_tracker.

If you insert a row with id=10 into the hash partitioned table with 4 partitions, which partition will it go to?

A orders_4

B orders_3

C orders_2

D orders_1

💡 Hint

Recall hash partitioning uses hash(id) mod number_of_partitions; see example in execution_table row 9.

Concept Snapshot

Partition types in PostgreSQL:
- RANGE: splits data by ranges of values (e.g., dates)
- LIST: splits data by specific values (e.g., regions)
- HASH: splits data by hashing a key (e.g., id)
Define partitions with FOR VALUES clause.
Data inserts go to matching partition.
Queries can target partitions for speed.

Full Transcript

Partitioning in PostgreSQL means splitting a big table into smaller pieces called partitions. You first create a main table and choose a partition type: range, list, or hash. Range partitions split data by value ranges, like dates. List partitions split by specific values, like regions. Hash partitions split by a hash function on a key, like an id. You create partitions with rules that tell PostgreSQL where to put data. When you insert data, PostgreSQL routes it to the right partition. This helps queries run faster because they only look at relevant partitions. The execution table shows steps creating tables and partitions, inserting data, and how data is routed. The variable tracker shows how many partitions exist and where data goes. Key moments explain why defining partitions is important and how hash partitioning works. The quiz tests understanding of data routing and partition counts. The snapshot summarizes the types and usage of partitions.