Skip to content

Start typing to search…

Navigation

Database Partitioning in PostgreSQL: A Practical Guide to Managing Large Tables

Understanding partition keys, partition pruning, automatic record routing, and partition lifecycle in PostgreSQL.

Understanding partition keys, partition pruning, automatic record routing, and the complete lifecycle of partitions.

Every application starts somewhere.

Initially, the database is small. Tables have a few thousand or maybe a few lakh records. Queries are reasonably fast, indexes are manageable, and database maintenance is not something the team worries about every day.

But applications grow. More users join, transactions increase, and tables that were once small start containing millions or even crores of records.

Let’s take an e-commerce application as an example.

Imagine we have an orders table that stores every order placed on the platform. As the business grows, this table keeps increasing in size. After a few years, it might contain hundreds of millions of records.

Now, some challenges start appearing:

  • Queries involving historical orders become expensive.
  • Indexes become larger and require more maintenance.
  • Deleting old records becomes a costly operation.
  • Backup, restore, and data archival become more complicated.
  • Routine database maintenance starts taking longer.

One approach to handling this growth is database partitioning.

Instead of keeping everything in one physical table, we divide the table into smaller pieces called partitions. Each partition stores a subset of the original table’s data, based on a rule that we define.

Let’s understand how this works in PostgreSQL and how we can implement it in a real application.

1. What is Database Partitioning?

Database partitioning is the process of dividing one large logical table into smaller physical tables, called partitions.

The important thing to understand is that these partitions still belong to the same logical table.

For example, suppose our application has an orders table. Instead of storing all the records in one physical structure, we can divide the data by month.

A simple view of monthly partitioning:

                 orders
              (Parent Table)
                    |
          PARTITION BY RANGE
              (created_at)
                    |
       +------------+------------+
       |            |            |
       v            v            v
  orders_2026_08 orders_2026_09 orders_2026_10
    (August)     (September)     (October)
                                   |
                                   v
                              orders_2026_11
                                (November)

Each partition contains only the records that belong to its defined date range.

From the application’s perspective, it is still working with a single table called orders.

We can execute a normal query:

SELECT *
FROM orders
WHERE created_at >= '2026-11-01'
  AND created_at < '2026-12-01';

PostgreSQL identifies which partition contains the required records and can avoid scanning irrelevant partitions.

This optimisation is known as partition pruning.

That is one of the biggest advantages of partitioning. When queries are designed around the partition key, PostgreSQL can reduce the amount of data it needs to examine.

However, partitioning does not automatically make every query faster. The benefit depends on the query patterns and whether PostgreSQL can eliminate unnecessary partitions.

2. How Do We Choose the Right Partition Key?

This is probably the most important decision when designing a partitioned table.

A partition key determines how records are divided across partitions.

Choosing the wrong key can make partitioning ineffective.

Let’s go back to our e-commerce application.

Most orders-related queries might look something like this:

SELECT *
FROM orders
WHERE created_at >= '2026-11-01'
  AND created_at < '2026-12-01';

We can see that the application frequently filters orders using created_at.

In this case, created_at is a good candidate for the partition key.

Why?

Because we can divide the orders table into monthly partitions. Whenever a query requests November orders, PostgreSQL can identify the November partition and avoid scanning unrelated monthly partitions.

When selecting a partition key, consider the following:

Understand your query patterns

Look at the most frequently executed queries. Which columns are commonly used in WHERE conditions?

A partition key should ideally align with how the application accesses its data.

Look for natural ranges

Some columns naturally divide data into ranges.

For example:

  • created_at for transaction dates.
  • order_date for orders.
  • Numeric IDs for specific ID ranges.

Consider data volume

The partitioning strategy should be based on how much data the application generates.

Monthly partitions might work well for one application, while another application generating millions of orders every day might need daily partitions.

Choose the right granularity

Avoid creating too many partitions unnecessarily.

For example, if monthly partitions contain a manageable amount of data, there is no reason to create daily partitions just for the sake of it.

The objective is to keep partitions manageable without introducing unnecessary database overhead.

3. Types of Partitioning in PostgreSQL

PostgreSQL supports three primary partitioning strategies.

Range Partitioning

Range partitioning divides records based on a range of values.

This is particularly useful for date and time columns.

For example, we can create monthly partitions for our orders table:

  • August 2026
  • September 2026
  • October 2026
  • November 2026

Each partition stores records within a defined range.

This is commonly used for orders, payments, transaction history, and application logs.

List Partitioning

List partitioning divides records based on specific values.

For example, suppose we have a customer table and want to partition records based on city.

We could create partitions for:

  • Chennai
  • Bengaluru
  • Mumbai

Whenever a record is inserted, PostgreSQL checks the partition key and routes it to the matching partition.

This approach is useful when the partition key contains a known set of distinct values.

Hash Partitioning

Sometimes, there is no natural range or fixed list that makes sense.

In such cases, hash partitioning can be useful.

PostgreSQL applies a hash function to the partition key and distributes records across a predefined number of partitions.

For example, we can use store_id to distribute records across four partitions.

This helps distribute records without manually defining individual ranges.

The choice depends on the nature of the data and how the application queries it.

4. How Does Partition Pruning Work?

Let’s understand this with a simple example.

Suppose our orders table has six monthly partitions.

                  orders
               (Parent Table)
                     |
        PARTITION BY RANGE (created_at)
                     |
       +------+------+------+------+------+------+
       |      |      |      |      |      |
       v      v      v      v      v      v
      Aug    Sep    Oct    Nov    Dec    Jan
     2026   2026   2026   2026   2026   2027

Now, suppose we execute the following query:

SELECT *
FROM orders
WHERE created_at >= '2026-11-01'
  AND created_at < '2026-12-01';

PostgreSQL checks the partition boundaries and determines that only the November partition is relevant.

Conceptually, the execution looks like this:

                +------------------+
                |     SQL Query    |
                +------------------+
                         |
                         v
                +------------------+
                | Partition Pruning|
                +------------------+
                         |
                         v
                Check partition key
                   (created_at)
                         |
                         v
              Evaluate all partition
                     ranges
                         |
       +--------+--------+--------+--------+--------+
       |        |        |        |        |        |
       v        v        v        v        v        v
    +------+ +------+ +------+ +------+ +------+ +------+
    | Aug  | | Sep  | | Oct  | | Nov  | | Dec  | | Jan  |
    +------+ +------+ +------+ +------+ +------+ +------+
    | SKIP | | SKIP | | SKIP | | SCAN | | SKIP | | SKIP |
    +------+ +------+ +------+ +------+ +------+ +------+
                                  |
                                  v
                         +----------------+
                         |  Return Rows   |
                         +----------------+

Instead of scanning every partition, PostgreSQL can skip the partitions that cannot contain the requested data.

This is partition pruning.

It is important to understand that PostgreSQL does this based on the partition boundaries and the query conditions. We do not need to manually mention the physical partition name in our SQL query.

For production verification, we can use EXPLAIN or EXPLAIN ANALYZE to see which partitions PostgreSQL actually scans.

5. How Does PostgreSQL Route Records to the Correct Partition?

One of the interesting things about PostgreSQL declarative partitioning is that the application does not need to decide which partition should receive a record.

PostgreSQL handles this automatically.

Let’s understand the flow.

Imagine our Spring Boot application inserts an order with the following value:

created_at = '2026-11-14'

Insert routing flow:

+------------------+
|   Spring Boot    |
|   Application    |
+------------------+
         |
         | INSERT INTO orders
         | created_at = 2026-11-14
         v
+------------------+
| PostgreSQL       |
| Query Executor   |
+------------------+
         |
         | Check partition key
         | (created_at)
         v
+------------------+
|     Partition    |
|     Routing      |
+------------------+
         |
         | Match date range
         v
+------------------+
| orders_2026_11   |
| November         |
+------------------+
         |
         v
    Record Inserted

The process is straightforward:

  1. The Spring Boot application executes an INSERT statement on the orders table.
  2. The query reaches the PostgreSQL executor.
  3. PostgreSQL checks the partition key, which is created_at.
  4. It evaluates the partition boundaries.
  5. The record is routed to the matching November partition.
  6. The record is inserted into that partition.

The application continues interacting with the parent table. It does not need to know the physical partition where the record is stored.

This is called partition routing.

It keeps the application code simple because the database handles the partition selection internally.

The same principle applies when reading data. PostgreSQL’s query planner can use partition pruning to eliminate partitions that cannot contain the requested records.

6. Implementing Monthly Partitioning in PostgreSQL

Now let’s implement this in our orders table.

We will use range partitioning based on created_at.

Step 1: Create the parent table

First, we create the main table.

CREATE TABLE orders (
    order_id BIGINT,
    store_id BIGINT,
    created_at TIMESTAMPTZ NOT NULL,
    status TEXT,
    PRIMARY KEY (order_id, created_at)
)
PARTITION BY RANGE (created_at);

There are two important things to notice here.

First, we are using created_at as our partition key.

Second, our primary key contains both order_id and created_at.

PostgreSQL requires a primary key or unique constraint on a partitioned table to include all the partition key columns.

Step 2: Create a default partition

We can also create a default partition.

CREATE TABLE orders_default
PARTITION OF orders DEFAULT;

This partition stores records that do not match any of the explicitly defined partitions.

For example, if an order arrives for a month for which we have not created a partition, PostgreSQL can route it to the default partition.

This provides a fallback mechanism, but we should not depend on it as a replacement for creating partitions ahead of time.

A large default partition can create operational challenges when we later need to create the missing monthly partition.

Step 3: Create partitions ahead of time

Since we have chosen monthly range partitioning, we should create future partitions before they are required.

For example, let’s create the December 2026 partition.

CREATE TABLE orders_2026_12
PARTITION OF orders
FOR VALUES FROM ('2026-12-01')
TO ('2027-01-01');

Here:

  • FROM is the inclusive lower boundary.
  • TO is the exclusive upper boundary.

So this partition stores orders created from December 1, 2026, up to but not including January 1, 2027.

We should ideally maintain the next two or three months’ partitions in advance.

This can be automated using a scheduled job or a tool such as pg_partman.

Step 4: Create indexes

Indexes are also important in partitioned tables.

For example, suppose our application frequently searches orders using store_id and created_at.

We can create an index on the parent table:

CREATE INDEX idx_orders_store_created
ON orders (store_id, created_at);

PostgreSQL creates corresponding indexes on the existing partitions and manages index creation for partitions added later.

Remember that partitioning and indexing solve different problems.

Partitioning helps PostgreSQL eliminate irrelevant partitions, while indexes help locate records efficiently within the partitions that need to be scanned.

Both can work together.

Step 5: Insert records normally

Now comes the interesting part.

We do not need to write separate INSERT statements for every month.

Our application can simply execute:

INSERT INTO orders (
    order_id,
    store_id,
    created_at,
    status
)
VALUES (
    101,
    501,
    '2026-11-14',
    'PLACED'
);

PostgreSQL checks the value of created_at and automatically routes the record to the matching November partition.

The application continues using the same logical table throughout.

7. Understanding the Complete Partition Lifecycle

Creating partitions is only the beginning.

In a production application, we need to think about what happens to partitions as they become older.

For our orders table, the lifecycle might look like this:

       +----------------+
       |   SETUP        |
       | Create parent  |
       | and partitions |
       +----------------+
                |
                v
       +----------------+
       |   CREATE       |
       | Future monthly |
       | partitions     |
       +----------------+
                |
                v
       +----------------+
       |   FILL         |
       | Insert orders  |
       | into partitions|
       +----------------+
                |
                v
       +----------------+
       |   AGE          |
       | Older data     |
       | becomes inactive|
       +----------------+
                |
                v
       +----------------+
       |   ARCHIVE      |
       | Detach and     |
       | preserve data  |
       +----------------+
                |
                v
       +----------------+
       |   DROP         |
       | Remove expired |
       | partition      |
       +----------------+

Let’s understand each stage.

Stage 1: Setup

This is a one-time activity.

We create the parent table, define the partitioning strategy, create the initial partitions, and configure the required indexes.

Once this is done, the application can start writing data.

Stage 2: Create partitions ahead of time

We should not wait for the current partition to become full or for a new month to begin.

For monthly partitioning, a scheduled job can create the next two or three months’ partitions in advance.

For example:

CREATE TABLE orders_2026_12
PARTITION OF orders
FOR VALUES FROM ('2026-12-01')
TO ('2027-01-01');

This avoids situations where new records arrive but the required partition does not exist.

Stage 3: Active partitions

These are the partitions receiving most of the new records.

For example, the November partition is actively receiving orders throughout November.

Recent partitions are generally more frequently accessed, so they may need more attention from the maintenance perspective.

Stage 4: Age out old partitions

After 60–90 days, some partitions may become less active.

For example, our August partition may now contain mostly historical orders.

At this point, we can review its access patterns and maintenance requirements.

We can run:

VACUUM ANALYZE orders_2026_08;

This performs vacuuming and updates statistics.

PostgreSQL’s autovacuum can also handle routine maintenance, so manually running this command should depend on the actual maintenance requirements.

The important idea is to understand that older partitions often have a different access pattern from current partitions.

Stage 5: Archive old partitions

Eventually, the business may decide that older orders do not need to remain in the active table.

Instead of deleting millions of records individually, we can detach the entire partition.

For example:

ALTER TABLE orders
DETACH PARTITION orders_2026_08;

Concurrent detachment

A regular DETACH PARTITION requires an ACCESS EXCLUSIVE lock on the parent table. This blocks other sessions from accessing the table while the operation is in progress.

PostgreSQL 14 introduced DETACH PARTITION CONCURRENTLY to reduce the impact on other sessions.

ALTER TABLE orders
DETACH PARTITION orders_2026_08 CONCURRENTLY;

PostgreSQL uses two transactions to complete this operation:

  1. In the first transaction, it marks the partition as pending detachment and commits the change.
  2. It waits for existing transactions using the parent table to complete.
  3. In the second transaction, it acquires the required locks and completes the detachment.

Limitations of Concurrent Detachment

  • PostgreSQL 14 or later: Not supported in earlier versions.
  • Transaction block: Cannot run inside an explicit transaction. Run as a standalone statement with autocommit enabled.
  • Default partition: Not allowed if the parent table has a default partition, even when it is empty.
  • Pending detach: Only one pending detach is allowed per parent. Use DETACH PARTITION ... FINALIZE to complete an interrupted operation.

Stage 6: Drop old partitions

After verifying that the archived data is safely available and the retention policy allows deletion, we can drop the old table.

DROP TABLE orders_2026_08;

This is one of the biggest benefits of partitioning.

Think about deleting millions of individual records using a DELETE statement. That can generate a significant amount of database work, including dead tuples and subsequent vacuum requirements.

Dropping an entire partition can be a much simpler and more efficient way to remove old data.

Of course, we should always ensure that the required backups and archives are available before dropping anything.

8. Automating Partition Management

In a real production application, manually creating partitions every month is not a good idea.

Someone eventually forgets to create the next partition, and the application starts facing insertion failures when no matching partition exists.

We should automate the complete lifecycle.

Tools such as pg_partman can help manage recurring partition creation and maintenance.

Alternatively, we can implement scheduled jobs using cron or another job scheduler.

For our orders table, the automation could handle:

  • Creating partitions for the next two or three months.
  • Checking whether expected partitions exist.
  • Monitoring the size and growth of individual partitions.
  • Identifying partitions that are older than the retention period.
  • Archiving and detaching old partitions.
  • Dropping partitions after the retention period expires.

The objective is to make partition management a predictable database operation rather than a manual activity.

9. When Does Partitioning Actually Make Sense?

Partitioning is useful, but it is not something we should introduce into every database table.

Before implementing it, we should understand the actual problem.

For example, if our application has a customer table containing only a few lakh records, partitioning might not provide any meaningful benefit.

But an orders table containing hundreds of millions of records, with frequent date-based queries and a need for historical data retention, could be a good candidate.

Partitioning is particularly useful when:

  • A table contains a very large volume of data.
  • Queries frequently filter on a particular column.
  • Historical data needs to be archived or removed regularly.
  • Maintenance operations on the complete table are becoming expensive.
  • The workload naturally separates into ranges or categories.

There are also a few things to keep in mind.

Partitioning does not automatically improve every query. Queries that do not use the partition key may still need to scan multiple partitions.

Similarly, creating too many partitions can increase planning and maintenance overhead.

So, the right approach is to start with the application’s query patterns, understand the data growth, and then choose an appropriate partitioning strategy.

Final Thoughts

Database partitioning is more than just dividing a large table into smaller tables.

It is about designing our data in a way that makes it easier to query, maintain, and manage as the application grows.

The most important decision is choosing the right partition key. A good partition key should align with the application’s query patterns and the natural growth of its data.

But the job does not end after creating partitions. We also need to think about the complete lifecycle: creating future partitions, routing new records, maintaining older partitions, archiving historical data, and eventually dropping partitions.

For me, the biggest takeaway is this:

A good partitioning strategy is not just about how we store today’s data. It is also about how we plan to manage tomorrow’s data and the data that our application no longer needs in its active tables.

If you found this article helpful, do leave a clap 👏 and follow for more insights on GenAI, Python, and backend engineering!

Discussion

Have a question, a correction, or a different approach? Sign in with GitHub to join the conversation.

100%