Table Partitioning in PostgreSQL (with Example)

In this article, I will explain what table partitioning is and how to implement it in PostgreSQL with an example.

What is Partitioning and Why is it Necessary?

Some tables in a database may contain significantly more rows than an average table (for example, tables that keep logs). This increases the index value when trying to retrieve data from the table, making the search results slower than it used to be. Partitioning can help us in this situation. By dividing tables into specific parts according to a defined criterion, this process will be much more efficient than before.

In short, partitioning refers to dividing a table into organized parts based on a specific feature that we decide. This division aims to:

• Improve query performance
• Reduce index size
• Optimize memory and CPU usage
• Simplify maintenance operations

NOTE: If partitioning is not necessary or the partitioning criteria are not appropriately chosen, this choice may backfire and lead to system/performance problems.

Partitioning Types

There are 3 types of partitioning recognized in PostgreSQL:

1. Range Partitioning

This is the most common type of partitioning. Data is divided according to value ranges.

Examples:

• Age ranges (0–20, 20–60, 60+)
• ID ranges (1–100, 100–200,200–300…)
• Date ranges (January, February, March…)

Range partitioning is especially useful for time-series data.

2. List partitioning

This refers to partitioning based on a predefined value. For example:

• Product color: red, black, white
• Country: TR, DE, US

3. Hash partitioning

Each partition will contain the rows for which the given remainder is obtained by dividing the partition key’s hash value by the designated modulus. This method is useful for evenly distributing data across partitions.

Table Partitioning in PostgreSQL

Now let’s examine how table partitioning is done with an example. In this example, we will use PostgreSQL 17.

NOTE: If you are using a version lower than PostgreSQL 11, you will not be able to implement it because it does not have partitioning support.

Scenario

Let’s assume we are looking at the database of a movie website, and our tables are:
- movies
- subscriptions
- users

In addition to this schema, let’s create a log table called watched_history that keeps track of the movies that users have watched. We can say that this table tends to grow much more than other data. As the number of users of the application increases, the number of movies they log will also increase with a similar rate.

Get Ceren Çağlayan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

We will partition this table by time using watched_at(i.e., range partitioning):

1: Create the Partitioned Table (Parent Table)

CREATE TABLE watch_history (
    id BIGSERIAL,
    user_id BIGINT NOT NULL REFERENCES users(id),
    movie_id BIGINT NOT NULL REFERENCES movies(id),
    watched_at TIMESTAMP NOT NULL,
    watch_duration_minutes INT,
    PRIMARY KEY (id, watched_at)
) PARTITION BY RANGE (watched_at);

• Here, we have specified partition key = watched_at.
• The table itself does not store data directly.
• It only routes rows to appropriate partitions.

Why Is the Primary Key Different?

In a non-partitioned table, we could simply define PRIMARY KEY (id). This works because all rows live in a single table.

However, in a partitioned table, the data is physically split across multiple partition tables. Because of this, PostgreSQL requires:

Any PRIMARY KEY or UNIQUE constraint must include the partition key.

Since our partition key is watched_at, we define the primary key as:

PRIMARY KEY (id, watched_at)

This ensures that each row is uniquely identifiable within the structure.

2: Create Partition Tables and Add Data

After creating our main table, we also need to create our partition tables.
Now, we can create partitions for specific time ranges.

January 2026

CREATE TABLE watch_history_2026_01
PARTITION OF watch_history
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

February 2026

CREATE TABLE watch_history_2026_02
PARTITION OF watch_history
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Each partition physically stores only its own time range. Now, to see how this will look, let’s add data to the table:

INSERT INTO watch_history (user_id, movie_id, watched_at, watch_duration_minutes)
VALUES
(1, 10, '2026-01-10 20:00:00', 120),
(2, 15, '2026-02-05 21:30:00', 95);

ADDITIONAL NOTE: In production systems, it is a good practice to define a default partition to prevent insert errors when a row does not match any defined range.

Even though we insert into the parent table, PostgreSQL automatically routes the rows to:

• watch_history_2026_01
• watch_history_2026_02

Our schema is now:

Final version of our schema

In addition, when we look at partition tables in the Tables/Partition section, we can find our data divided over time:

watch_history_2026_01 Partition Table

watch_history_2026_02 Partition Table

When we query the data, PostgreSQL performs partition pruning. It scans onlywatch_history_2026_01:

SELECT *
FROM watch_history
WHERE watched_at >= '2026-01-01'
  AND watched_at < '2026-02-01';

watch_history_2026_01 Partition Table

Moreover, maintenance is an advantage with partitioning. Suppose we want to remove old data (in this example, old logs that are not necessary anymore) . With partitioning, we can instantly remove by using DROP TABLE:

Without partitioning:

DELETE FROM watch_history
WHERE watched_at < '2025-01-01';

With partitioning:

DROP TABLE watch_history_2025_01;

References

• PostgreSQL Partitioning Tutorial — YouTube
• PostgreSQL: Documentation: 17: 5.12. Table Partitioning