Partition an Existing Table in Postgres

In PostgreSQL, table partitioning refers to a feature that allows us to divide the large tables into smaller and more manageable tables called “table partitions”. Each partition contains a subset but separate data based on a specified partitioning strategy.

Partitioning is a handy feature that can significantly improve the database and query performance and simplify the data management.

PostgreSQL Table Partitioning Strategies

PostgreSQL supports the following partitioning strategies:

Range Partitioning

The data is partitioned based on a specified range of values in a chosen column. For example, you can partition a table based on a date column where each partition holds the data for a specific date range.

List Partitioning

The data is partitioned based on a predefined list of values in a chosen column. Each partition contains the data that matches one of the specified values.

Hash Partitioning

The data is distributed across partitions based on a Hash function that is applied to one or more columns. This strategy ensures a more balanced distribution of data across partitions.

Composite Partitioning

This approach combines multiple partitioning strategies. For example, you can partition the data using the range partitioning first and then further partition each range using the Hash partitioning.

Partition the Existing Table

Let us discuss the steps that we can use to partition an existing table. For this, we use the Pagila database for demonstration purposes. However, feel free to use any database for your convenience.

Step 1: Create a Partitioned Table

The first step is creating a new table with the desired partitioning strategy. This acts as the parent for all the table partitions.

CREATE TABLE partitioned_film (
film_id SERIAL,
title VARCHAR(255),
description TEXT,
release_year INTEGER,
language_id SMALLINT,
rental_duration SMALLINT,
rental_rate NUMERIC(4,2),
length SMALLINT,
replacement_cost NUMERIC(5,2),
rating VARCHAR(10),
special_features TEXT,
last_update TIMESTAMP,
PRIMARY KEY (film_id, release_year)
) PARTITION BY RANGE (release_year);

In the given query, we create the partitioned_film table as the parent table for the partitions.

Step 2: Create the Table Partition

The next step is to create the individual child tables that hold the data for each partition. Each child table should have unique names and should contain similar columns to the parent table.

CREATE TABLE partitioned_film_2000s PARTITION OF partitioned_film
FOR VALUES FROM (2000) TO (2010);
CREATE TABLE partitioned_film_2010s PARTITION OF partitioned_film
FOR VALUES FROM (2010) TO (2023);

We create two partitions for the 2000s and 2010s decades in the example queries.

Step 3: Move the Data into the Partitions

Lastly, we can move the existing data from the original film table to the appropriate partitioned tables.

We can use the INSERT INTO … SELECT statement to achieve this:

INSERT INTO partitioned_film_2000s
SELECT film_id, title, description, release_year, language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update
FROM film
WHERE release_year >= 2000 AND release_year < 2010;

Feel free to adjust the partition ranges and conditions according to your specific partitioning criteria.

Step 4: Verify the Data Move

Once completed, we can verify that the data has been moved successfully using the select statement.

select * from partitioned_film_2000s;

Output:

Conclusion

This comprehensive tutorial explored about table partitioning, the various partition strategies, and how to partition an existing table in PostgreSQL.