Partitioning a Table in PostgreSQL - Pros and Cons Explained

Partition is a very sensitive topic for some people. Like the 1947 India-Pak partition which lead to so many families getting devasted. But some partitions are not that bad. For e.g., partitioning your relational table. This blog talks about the good partitions and how you can use it to supercharge your database operations.

What Is Meant by Partitioning?

The idea behind partitioning a table is very simple - divide and conquer. Say you have a very big table containing information about all the orders placed in the last 5 years. Querying it to identify recent orders of a customer will involve writing a select query that utilizes an index built on the entire table’s data. Due to sheer size of the table, the index itself may be quite big with data running in gigabytes of storage space. Compare that to searching a table which only contains information about orders of the last three months. Because the number of records will be less, the search space is significantly smaller as compared to our other table with all the records. This is the core idea behind partitioning a table. Instead of 1 big table, there exists multiple small tables each containing a subset of the original data.

By definition, Partitioning is a physical data organization strategy where a single logical table is split into multiple child tables based on a key - the partition key.

So Many Tables! How Are Read & Writes Handled?

With so many small tables, how do you decide which data goes in which table during insertion and how do you search across multiple small tables? The short answer is - you don’t (decide). PSQL automatically routes your inserts to the correct partitioned table based on the partitioning key or column. Additionally the data fetching is dependent on your query. The idea is to identify the correct partitions your data may lie into and scanning those partitions. If the partitioned column is a part of your query, the query executor will not search partitions not containing your data. It will prune / exclude those partitions from search thus leading to a more selective and optimized search across the required partitions. In the worst case, the planner cannot prune extra partitions and behaves similar to a UNION ALL scan. In your context, nothing changes. You will still insert / fetch data in the same manner that you used to before a partitioned table.

Why To Partition (our table) ?

Speed. Efficiency. Storage optimization. These are the top 3 reasons I would consider partitioning my table. The queries including the partitioned key can execute significantly faster as we have discussed above because of partition pruning. Due to faster query execution, you get to save on average CPU and memory usage of your database. Additionally if needed, you can drop / archive old partitions easily. This helps you save on your database storage costs as well. This is better than identifying and deleting records selectively.

-- Query used to identify impact of partitioning
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE customer_id = 236 
AND order_date = '2026-01-17';

Notice how the query execution time reduces by more than 80% after partitioning our table.

One may argue that the same benefits can be realized by just archiving the old table and creating a new one in place of it. While it is true, this approach won’t work where you may frequently require reading data from your archived table to execute queries. For e.g., a table powering the customer support dashboard. The support agents may want to see past customer interactions to resolve their queries effectively.

When Should You Not Partition

If you have a write heavy table, where write performance matters and reads are rare, partitioning may not be helpful. Incase of a medium-sized table with a moderate number of records, you can instead focus on revisiting your current indexes to optimize your read performance than opting for partitioning your table. Partitioning comes with an added overhead of creating and maintaining partitions. You can refer to this article if you want to know more about how you unlock maximum performance from your table indexes.

Another hurdle while implementing partitioning is the tricky situation with the unique constraints. In PSQL, to ensure uniqueness at a table level, the constraint needs to be always be created in combination with the partition key. For e.g., In a payments table partitioned by creation date monthly where transaction ID is needed to be unique, a unique constraint on trx ID and creation date would only ensure that the trx ID is unique in each partition, i.e. in every month’s data. Technically it means, one can insert duplicate trx ID with two different dates. This may lead to serious validation issues incase any invalid data were to be captured in the database. This limitation is not by accident, but by choice while designing partitions in PSQL to support partition isolation and not hamper write performance. An industry accepted workaround is to create a separate table (not partitioned) recording such unique entries to ensure system’s integrity. Once the entry is created in this table, it can be safely inserted in our original partitioned table.

How To Partition?

While there are multiple ways to create and maintain partitions, these two are my current favorites:

1. Raw Dog Way

   1. Create a partitioned table.

   2.  CREATE TABLE orders (
           id           BIGSERIAL,
           order_date   DATE NOT NULL,
           customer_id  BIGINT,
           amount       NUMERIC(10,2),
           PRIMARY KEY (id, order_date)
       ) PARTITION BY RANGE (order_date);
      

   3. Run an automation job at regular intervals to check for existing partitions.

   4. Create new partitions and attach them to parent table.

   5.  CREATE TABLE orders_p20260116
       PARTITION OF orders
       FOR VALUES FROM ('2026-01-16') TO ('2026-01-17');
      

   6. If not needed, drop old partitions.

2. The Abstracted Extension Way

   1. Install pg_partman - a pSQL extension for partition management.

   2. Create a partitioned table. [same as shown in the above approach]

   3. Register your table with pg_partman and define partition intervals.

   4.  SELECT public.create_parent(
           p_parent_table := 'public.orders',
           p_control      := 'order_date',
           p_interval     := '1 month'
       );
      
       -- This will create parititions of orders table with 1 month intervals
      

   5. Use an automation job to run partman maintenance job at regular intervals.

   6. The maintenance job takes care of creating new and deleting old partitions on its own.

Final Remarks

Partitioning is a good strategy. It helps when your queries naturally filter by the partition key. However it comes with its own limitations regarding enforcing uniqueness at a table level and the overhead of maintaining partitions. Therefore, it is very important to identify your needs and setup before implementing it. Partitioning is a scalpel, not a hammer. Use it where it cuts deep, not everywhere it can.

If you liked this blog, you will surely love the upcoming blog where we discuss using pg_partman in detail with step-by-step instructions on how to install and configure it on your own. We will also cover the usage of pg_cron to simplify the overall process of partition management. Stay tuned as the blog drops early next month. Thanks for reading this article. Namaste!