Complete Guide to PostgreSQL Table Partitioning
PostgreSQL is a powerful, open-source relational database that offers a wide range of features for performance tuning and scalability. One of the most effective techniques for managing large datasets in PostgreSQL is table partitioning. In this guide, we’ll explore what table partitioning is, why it’s useful, the different partitioning strategies supported by PostgreSQL, and how to implement them effectively — all while taking reference from best practices and real-world examples.
What is Table Partitioning?
Table partitioning is the process of splitting a large table into smaller, more manageable pieces called partitions, while maintaining the illusion of a single logical table to the end user.
Instead of storing all rows in a single table, PostgreSQL stores them in child tables (partitions), and routes incoming data based on a defined strategy — such as range or list.
Why Use Partitioning?
Partitioning helps in:
Improving query performance: Queries targeting a subset of data can be faster due to partition pruning.
Managing large datasets: Partitioning enables better data organization, backup, and maintenance (e.g., dropping old partitions).
Avoiding performance degradation: Very large tables can slow down vacuuming and indexing. Partitioning mitigates this.
Parallel processing: PostgreSQL can parallelize operations across partitions.
Types of Partitioning in PostgreSQL
PostgreSQL supports three primary partitioning strategies:
1. Range Partitioning
Data is partitioned based on a range of values.
Example: Partition a logs table by month:
1CREATE TABLE logs (
2 id SERIAL,
3 message TEXT,
4 created_at DATE
5) PARTITION BY RANGE (created_at);Then define monthly partitions:
1CREATE TABLE logs_2025_01 PARTITION OF logs
2 FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
3CREATE TABLE logs_2025_02 PARTITION OF logs
4 FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');2. List Partitioning
Data is split by a list of discrete values.
Example: Partition a users table by region:
1CREATE TABLE users (
2 id SERIAL,
3 name TEXT,
4 region TEXT
5) PARTITION BY LIST (region);
6
7CREATE TABLE users_apac PARTITION OF users FOR VALUES IN ('APAC');
8CREATE TABLE users_emea PARTITION OF users FOR VALUES IN ('EMEA');3. Hash Partitioning
Distributes data across partitions using a hash function.
Example:
1CREATE TABLE orders (
2 id SERIAL,
3 customer_id INT
4) PARTITION BY HASH (customer_id);
5
6CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
7CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);Default Partition
A default partition handles values that don’t match any defined partition.
1CREATE TABLE logs_default PARTITION OF logs DEFAULT;This is useful for catching unexpected or uncategorized data during insertion.
Insert Data into Partitions
Now, we can insert data into the table, and PostgreSQL will automatically route the data to the appropriate partition based on the partition column:
1INSERT INTO logs (message, created_at)
2VALUES ('log entry', '2023-01-15');
3
4INSERT INTO logs (message, created_at)
5VALUES ('log entry', '2023-02-20');
6
7INSERT INTO logs (message, created_at)
8VALUES ('log entry', '2023-03-10');
Partition Pruning
PostgreSQL automatically excludes partitions that are irrelevant to a query — this is called partition pruning.
1SELECT * FROM logs WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';Only the logs_2025_01 partition is scanned in this case, improving performance significantly.
Best Practices
Index partitions individually: Each partition should have its own indexes.
Avoid too many partitions: Keep the number of partitions reasonable to avoid planner overhead.
Use declarative partitioning: PostgreSQL 10+ supports native declarative partitioning — prefer it over triggers.
Automate partition creation: Use scripts or scheduled jobs to create new partitions as needed.
Use CHECK constraints cautiously: Avoid manually adding conflicting constraints on partitioned tables.
Monitor performance: Use EXPLAIN ANALYZE to ensure pruning and query performance is working as expected.
Common Use Cases
Time-series data (logs, metrics, events)
Multi-tenant SaaS data (by customer ID)
Geographic or regional datasets
Archival systems with retention policies
Conclusion
PostgreSQL table partitioning is a robust tool to manage and optimize large datasets. Whether you're dealing with time-series logs, customer data, or regional metrics, the right partitioning strategy can significantly boost performance and scalability. Start small, monitor your queries, and evolve your partitioning logic as your data grows.
