Table Partitioning in PostgreSQL: What It Is and How It Works

Table partitioning in PostgreSQL is a way to split a large table into smaller, manageable pieces called partitions. Each partition holds a subset of the data based on a defined rule, making queries faster and maintenance easier.

How It Works

Imagine you have a huge filing cabinet full of papers, and you want to find a specific document quickly. Instead of searching the entire cabinet, you divide the papers into separate folders based on categories like year or topic. Table partitioning in PostgreSQL works similarly by dividing a big table into smaller parts called partitions.

Each partition holds rows that meet certain criteria, such as a range of dates or specific values. When you run a query, PostgreSQL can look only in the relevant partitions instead of scanning the whole table. This speeds up data retrieval and makes managing large datasets easier.

Partitions are still part of the main table logically, so you can query the table as a whole or just a specific partition. This helps keep your database organized and efficient.

Example

This example shows how to create a partitioned table in PostgreSQL by range on a date column. We create a main table and two partitions for different years.

sql

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_date DATE NOT NULL,
  amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

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

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

INSERT INTO sales (sale_date, amount) VALUES
  ('2023-05-10', 100),
  ('2024-03-15', 200);

SELECT * FROM sales WHERE sale_date >= '2024-01-01';

Output

 id | sale_date  | amount 
----+------------+--------
  2 | 2024-03-15 |    200
(1 row)

When to Use

Use table partitioning when you have very large tables that grow over time, such as logs, sales records, or sensor data. Partitioning helps improve query speed by limiting the data scanned and makes maintenance tasks like deleting old data faster and safer.

For example, a company storing millions of sales records can partition the data by year or month. This way, queries for recent sales only scan relevant partitions, and old data can be archived or removed easily without affecting current data.

Key Points

• Partitioning splits a large table into smaller, manageable pieces called partitions.
• Partitions are defined by rules like range or list of values.
• Queries can target specific partitions for faster performance.
• Maintenance tasks like data cleanup become easier with partitions.
• PostgreSQL supports declarative partitioning since version 10.

Key Takeaways

Table partitioning divides large tables into smaller parts to improve query speed and management.

Partitions hold subsets of data based on rules like ranges or lists of values.

Use partitioning for large, growing datasets such as logs or sales records.

Queries can scan only relevant partitions, reducing workload and speeding results.

PostgreSQL supports easy-to-use declarative partitioning starting from version 10.