Why partitioning is needed in PostgreSQL - See It in Action

Why Partitioning is Needed in PostgreSQL

📖 Scenario: You are managing a large database for an online store. The sales data is growing every day, and queries on this data are becoming slower. You want to organize the data better to improve performance and manageability.

🎯 Goal: Build a simple example to understand why partitioning a table in PostgreSQL helps manage large datasets efficiently.

📋 What You'll Learn

Create a main sales table with sample columns

Create a partition key variable for dividing data

Write SQL to create partitions based on the partition key

Add a final command to attach partitions to the main table

💡 Why This Matters

🌍 Real World

Large databases like sales records grow fast and become slow to query. Partitioning helps keep data manageable and queries fast.

💼 Career

Database administrators and developers use partitioning to optimize performance and maintainability of big data tables.

1

Create the main sales table

Create a table called sales with columns id (integer), sale_date (date), and amount (numeric). This will hold all sales records.

PostgreSQL

-- Create the sales table with id, sale_date, and amount columns
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and data types.

2

Define the partition key

Add a column called sale_year of type integer to the sales table. This will be used as the partition key to divide data by year.

PostgreSQL

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount NUMERIC
);
-- Add sale_year column of type integer
-- Your code here

Need a hint?

Add the sale_year column inside the table definition.

3

Create partitions by year

Write SQL commands to create two partitions of the sales table: sales_2023 for sale_year = 2023 and sales_2024 for sale_year = 2024. Use PARTITION OF sales syntax.

PostgreSQL

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount NUMERIC,
    sale_year INT
);
-- Create sales_2023 partition for sale_year = 2023
-- Create sales_2024 partition for sale_year = 2024
-- Your code here

Need a hint?

Use PARTITION BY LIST (sale_year) in the main table and create partitions with FOR VALUES IN (...).

4

Attach partitions to the main table

Add the final command to attach an existing table sales_archive as a partition of sales for sale_year = 2022. Use ATTACH PARTITION syntax.

PostgreSQL

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

CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES IN (2023);
CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES IN (2024);
-- Attach sales_archive as partition for sale_year = 2022
-- Your code here

Need a hint?

Use ALTER TABLE sales ATTACH PARTITION sales_archive FOR VALUES IN (2022); to attach the partition.