Partitioning best practices in PostgreSQL - Mini Project: Build & Apply

Partitioning Best Practices in PostgreSQL

📖 Scenario: You are managing a large sales database for a retail company. The sales data grows rapidly every day, and queries on this data are becoming slower. To improve performance and manageability, you decide to use table partitioning in PostgreSQL.

🎯 Goal: Build a partitioned sales table in PostgreSQL using best practices. You will create the main partitioned table, define partitions by range on the sale date, and add a configuration setting to control partition size. Finally, you will implement the core logic to create partitions and complete the setup for efficient querying.

📋 What You'll Learn

Create a main partitioned table named sales with columns id (integer), sale_date (date), and amount (numeric).

Add a configuration variable partition_interval to define the number of days per partition.

Create partitions of the sales table by range on sale_date using the partition_interval.

Complete the partitioning setup by attaching partitions to the main table.

💡 Why This Matters

🌍 Real World

Partitioning large tables improves query speed and maintenance in databases with growing data, such as sales records, logs, or sensor data.

💼 Career

Database administrators and backend developers use partitioning to optimize performance and manage large datasets efficiently.

1

Create the main partitioned table

Create a table called sales with columns id (integer), sale_date (date), and amount (numeric). Make this table partitioned by RANGE on the sale_date column.

PostgreSQL

-- Your code here to create the partitioned table 'sales'

Need a hint?

Use PARTITION BY RANGE (sale_date) to enable range partitioning on the sale_date column.

2

Add a partition interval configuration

Create a variable called partition_interval and set it to 30 to represent the number of days each partition will cover.

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

-- Your code here to set partition_interval

Need a hint?

Use \set partition_interval 30 in psql to define the interval variable.

3

Create partitions using the partition interval

Create a partition named sales_2023_01 for sales from '2023-01-01' to '2023-01-31' using RANGE partitioning on sale_date. Use the partition_interval value to define the end date.

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

\set partition_interval 30

-- Your code here to create partition sales_2023_01

Need a hint?

Use CREATE TABLE <partition_name> PARTITION OF sales FOR VALUES FROM (<start_date>) TO (<end_date>).

4

Complete the partitioning setup

Create another partition named sales_2023_02 for sales from '2023-02-01' to '2023-03-03' and attach it to the sales table. This completes the partition setup for two months.

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    sale_date DATE,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

\set partition_interval 30

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

-- Your code here to create and attach sales_2023_02 partition

Need a hint?

Remember to create the partition as a PARTITION OF sales with the correct range values.