Postgres Table Partitioning

Table partitioning is a technique that divides the large database tables into smaller, more manageable parts called “partitions”. Partitioning can improve the query performance, facilitate the data maintenance, and enhance the data management in scenarios where the extensive data must be handled efficiently.

In this tutorial, we will explore the various table partition methods using the Pagila database as an example.

Prerequisites:

To follow this tutorial, you should have the following prerequisites in place:

A working installation of the PostgreSQL database management system
Installed and configured Pagila database in your PostgreSQL instance
Basic knowledge of SQL and PostgreSQL

Creating the Base Table

We begin by creating the base table that will be partitioned. In this case, we create a new table called payment_partition with the same structure as the original payment table.

CREATE TABLE payment_partition (

payment_id SERIAL PRIMARY KEY,

customer_id SMALLINT NOT NULL,

staff_id SMALLINT NOT NULL,

rental_id INTEGER NOT NULL,

amount NUMERIC(5,2) NOT NULL,

payment_date TIMESTAMP NOT NULL

);

Creating the Partitioned Tables

In this step, we create the individual partition tables that hold the data. We partition the payment_partition table based on the payment year.

-- Create partition tables for years 2005-2010

CREATE TABLE payment_partition_2005 PARTITION OF payment_partition

FOR VALUES FROM ('2005-01-01') TO ('2006-01-01');

CREATE TABLE payment_partition_2006 PARTITION OF payment_partition

FOR VALUES FROM ('2006-01-01') TO ('2007-01-01');

CREATE TABLE payment_partition_2007 PARTITION OF payment_partition

FOR VALUES FROM ('2007-01-01') TO ('2008-01-01');

Creating the Partition Function

To define the partitioning logic, we must create a partition function that determines which partition that each row should be placed in based on the payment date.

CREATE OR REPLACE FUNCTION payment_partition_function(payment_date TIMESTAMP)

RETURNS TABLE(payment_partition_name TEXT) AS $$

BEGIN

IF payment_date >= '2005-01-01' AND payment_date < '2006-01-01' THEN

RETURN QUERY VALUES ('payment_partition_2005');

ELSIF payment_date >= '2006-01-01' AND payment_date < '2007-01-01' THEN

RETURN QUERY VALUES ('payment_partition_2006');

ELSIF payment_date >= '2007-01-01' AND payment_date < '2008-01-01' THEN

RETURN QUERY VALUES ('payment_partition_2007');

ELSE

RAISE EXCEPTION 'Date out of range. Ensure partition is defined.';

END IF;

END;

$$ LANGUAGE plpgsql;

Creating the Partition Trigger

We must create a partition trigger to route the rows to the appropriate partition automatically.

CREATE TRIGGER payment_partition_trigger

BEFORE INSERT ON payment_partition

FOR EACH ROW

EXECUTE FUNCTION payment_partition_function(NEW.payment_date);

Verifying the Partitioned Tables

To verify that the partitioning setup works correctly, insert a few sample rows into the payment_partition table.

INSERT INTO payment_partition (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (1, 1, 1, 9.99, '2005-01-01'),
(2, 2, 2, 4.99, '2006-02-01'),
(3, 3, 3, 19.99, '2007-03-01');

Querying the Partitioned Tables

You can now query the partitioned tables just like any other table. The partitioning logic ensures that only the relevant partitions are scanned for each query.

SELECT * FROM payment_partition_2005;

SELECT * FROM payment_partition_2006;

SELECT * FROM payment_partition_2007;

After Actions

Partitioning can also simplify the data maintenance operations. For example, you can drop the corresponding partition if you want to drop all the data that are older than a certain year.

DROP TABLE payment_partition_2005;

Conclusion

You now learned how to work with table partition methods using the Pagila database as an example.