Best Practices for PostgreSQL Table Partition Managing
Tags: postgres, partition
Published: 2023-10-04 11:52:24

## What is PostgreSQL Table Partition

In PostgreSQL 10, table partitioning was introduced as a feature that allows you to divide a large table into smaller, more manageable pieces called partitions. Each partition is essentially a separate table that stores a subset of the data from the original table. This can be useful for improving query performance, as well as for managing large datasets more efficiently.

In PostgreSQL 11, several improvements to table partitioning were added, of which the key improvements are the ability to create default partitions, do automatic index creation, support foreign keys and unique indexes, and the ability to create hash partitions. "Interval partitioning" allows you to define a partitioning scheme that automatically creates new partitions as needed based on the values in the partition key. Hash partitioning allows you to divide your data into partitions based on a hash function applied to the partition key, it can be useful for evenly distributing data across partitions, which can help to improve query performance.

And PostgreSQL 12 brought more improvements to table partitioning, make it an even more powerful feature for managing large datasets and improving query performance. Here are some of the key improvements:

1. Partition elimination: allows to eliminate partitions that don't need to be scanned for a given query. This can significantly improve query performance for tables with many partitions.
2. Performance for partitioned tables with indexes: make queries on partitioned tables with indexes faster than it was in previous versions.
3. More partitioning methods: list partitioning and range partitioning with sub-partitions, giving you more flexibility in how you partition your data.
4. Partitioning foreign keys: make it easier to manage partitioning foreign keys than it was in previous versions.
5. Partitioning DML statements: make it easier to perform data manipulation language (DML) statements on partitioned tables, including support for cascading updates and deletes across partitions, support for RETURNING clauses in INSERT/UPDATE/DELETE statements.

## How to Use PostgreSQL Table Partition

To use table partitioning in PostgreSQL, you first need to define a partitioning scheme for your table. This involves specifying the partition key, which is the column or set of columns that will be used to determine which partition each row belongs to. You can then create the partitions themselves, either manually or using a script.

Here are some examples of real-world schemes for PostgreSQL partition tables, partitioned by RANGE, LIST, and HASH respectively:

1. Partitioned by RANGE: Suppose you have a table that stores sales data for a large retail chain. You could partition the table by date, using a RANGE partitioning scheme. For example, you could create partitions for each quarter of the year, like this:

    ```sql
    CREATE TABLE sales (
        id BIGINT GENERATED ALWAYS AS IDENTITY,
        date DATE NOT NULL,
        store_id INTEGER NOT NULL,
        amount NUMERIC NOT NULL
    )
    PARTITION BY RANGE (date);

    CREATE TABLE sales_q1 PARTITION OF sales
        FOR VALUES FROM ('2022-01-01') TO ('2022-04-01');

    CREATE TABLE sales_q2 PARTITION OF sales
        FOR VALUES FROM ('2022-04-01') TO ('2022-07-01');

    CREATE TABLE sales_q3 PARTITION OF sales
        FOR VALUES FROM ('2022-07-01') TO ('2022-10-01');

    CREATE TABLE sales_q4 PARTITION OF sales
        FOR VALUES FROM ('2022-10-01') TO ('2023-01-01');
    ```

2. Partitioned by LIST: Suppose you have a table that stores customer data for an e-commerce site. You could partition the table by country, using a LIST partitioning scheme. For example, you could create partitions for each country where you have customers, like this:

    ```sql
    CREATE TABLE customers (
        id BIGINT GENERATED ALWAYS AS IDENTITY,
        name TEXT NOT NULL,
        email TEXT NOT NULL,
        country TEXT NOT NULL
    )
    PARTITION BY LIST (country);

    CREATE TABLE customers_usa PARTITION OF customers
        FOR VALUES IN ('USA');

    CREATE TABLE customers_canada PARTITION OF customers
        FOR VALUES IN ('Canada');

    CREATE TABLE customers_uk PARTITION OF customers
        FOR VALUES IN ('UK');

    CREATE TABLE customers_germany PARTITION OF customers
        FOR VALUES IN ('Germany');
    ```

3. Partitioned by HASH: Suppose you have a table that stores sensor data for a large industrial plant. You could partition the table by sensor ID, using a HASH partitioning scheme. For example, you could create partitions for each sensor, like this:

    ```sql
    CREATE TABLE sensor_data (
        id BIGINT GENERATED ALWAYS AS IDENTITY,
        sensor_id INTEGER NOT NULL,
        timestamp TIMESTAMP NOT NULL,
        value NUMERIC NOT NULL
    )
    PARTITION BY HASH (sensor_id);

    CREATE TABLE sensor_data_1 PARTITION OF sensor_data
        FOR VALUES WITH (MODULUS 4, REMAINDER 0);

    CREATE TABLE sensor_data_2 PARTITION OF sensor_data
        FOR VALUES WITH (MODULUS 4, REMAINDER 1);

    CREATE TABLE sensor_data_3 PARTITION OF sensor_data
        FOR VALUES WITH (MODULUS 4, REMAINDER 2);

    CREATE TABLE sensor_data_4 PARTITION OF sensor_data
        FOR VALUES WITH (MODULUS 4, REMAINDER 3);
    ```

## Automatic Partition Management

As your data grows and changes over time, your might find manual partition creation and deletion can be complex, time-consuming and error-prone. So let's talk about automatic partition management.

Automatic partition creation and deletion can be very useful when using PostgreSQL partition tables for several reasons:

1. Simplifies management: With automatic partition creation and deletion, you don't need to manually create and manage partitions as your data grows and changes. This can simplify management and reduce the risk of errors.
2. Improves performance: Automatic partition creation and deletion can help to improve query performance by ensuring that data is stored in the appropriate partition. This can reduce the amount of data that needs to be scanned for a given query, which can improve query performance.
3. Reduces storage costs: Automatic partition deletion can help to reduce storage costs by allowing you to easily delete old data that is no longer needed. This can help to free up storage space and reduce costs.
4. Supports data retention policies: Automatic partition deletion can also help to support data retention policies by allowing you to easily delete data that is no longer needed after a certain period of time.

There are several ways to achieve automatic partition management in PostgreSQL.

### Partition pre-creation and deferred removal

Partition pre-creation is the process of creating partitions in advance of when they are needed. It can help to improve performance by reducing the overhead of creating partitions dynamically as data is inserted into a partitioned table. When you pre-create partitions, you create a set of empty partitions in advance, based on a predefined partitioning scheme. These partitions are typically created with no data, and are ready to receive data as it is inserted into the partitioned table.

Deferred removal involves delaying the removal of partitions until a later time. This can be useful in situations where you want to keep data for a certain period of time, but don't want to keep it indefinitely. By deferring the removal of partitions, you can ensure that you have enough time to analyze and process the data before it is deleted.

Here are several ways to implement these two techniques.

#### *nix cron task

Here's an example of how you could use a *nix cron task to achieve partition pre-creation and deferred removal in PostgreSQL:

1. Create a partitioned table, called "sales" that is partitioned by range on the "date" column.

    ```sql
    CREATE TABLE sales (
        id BIGINT GENERATED ALWAYS AS IDENTITY,
        date DATE NOT NULL,
        store_id INTEGER NOT NULL,
        amount NUMERIC NOT NULL
    )
    PARTITION BY RANGE (date);
    ```

2. Create a partitioning function, called "sales_partition_function" that returns the name of the partition that should be used for a given date. The function takes a date as input and returns the name of the partition based on the year and month of the date.

    ```sql
    CREATE OR REPLACE FUNCTION sales_partition_function(date)
    RETURNS TEXT AS $$
    DECLARE
        partition_name TEXT;
    BEGIN
        partition_name := 'sales_' || to_char($1, 'YYYY_MM');
        RETURN partition_name;
    END;
    $$ LANGUAGE plpgsql;
    ```

3. Create a shell script to pre-create partitions. This is a shell script that pre-creates partitions for the current month and removes partitions that are older than one year. The script uses the "psql" command to execute SQL statements in PostgreSQL.

    ```shell
    #!/bin/bash

    # Get the next year and month
    YEAR_MONTH=$(date +%Y_%m -d "$(date) +1 month")

    # Get range begin and end with date of next month
    ONE_MONTH_LATER=$(date +%Y-%m-%d -d "$(date) +1 month")
    TWO_MONTHS_LATER=$(date +%Y-%m-%d -d "$(date) +2 month")

    # Create the partition for the next month
    psql -c "CREATE TABLE IF NOT EXISTS sales_${YEAR_MONTH} PARTITION OF sales FOR VALUES FROM ('$ONE_MONTH_LATER') TO ('$TWO_MONTHS_LATER');"

    # Remove partitions that are older than a year
    YEAR=$(date +%Y)
    tbArray=$(psql -tc "\dt sales_$(($YEAR - 1))_*" | awk -F'|' '{print $2}')
    for i in $(echo ${tbArray[@]}); do 
	    psql -c "DROP TABLE IF EXISTS $i"; 
	done
    ```

4. Add the shell script to a *nix cron task, that runs on the first day of every month. This will ensure that partitions are pre-created and removed on a regular basis.

    ```shell
    0 0 1 * * /path/to/script.sh
    ```

#### The pg_cron extension

The pg_cron extension is a PostgreSQL extension that allows you to schedule and run cron-like jobs directly from within the database. It provides a simple and convenient way to schedule tasks such as backups, data exports, and other maintenance tasks.

With pg_cron, you can schedule jobs using a familiar cron-like syntax, and the jobs will be executed directly within the database. This eliminates the need for external scheduling tools and simplifies the management of scheduled tasks.

Here's an example of how you could use pg_cron to schedule the creation and removal of partition tables:

```sql
CREATE TABLE sales (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    date DATE NOT NULL,
    store_id INTEGER NOT NULL,
    amount NUMERIC NOT NULL
)
PARTITION BY RANGE (date);

-- create function to manage partitions
CREATE OR REPLACE
FUNCTION manage_sales_partition() RETURNS integer
    LANGUAGE plpgsql AS
$$
DECLARE
   regPatternTableName varchar;
   targetTableName varchar;
   oneMonthLater varchar;
   twoMonthLater varchar;
   lastValidTableName varchar;
   _tb_name information_schema.tables.table_name%TYPE;
BEGIN
	SELECT 'sales_\d{4}_\d{2}' INTO regPatternTableName;
    -- Get the next year and month
	SELECT to_char(now() + INTERVAL '1 month', 'sales_YYYY_MM') INTO targetTableName;

    -- Get range begin and end with date of next month
    SELECT to_char(now() + INTERVAL '1 month', 'YYYY-MM-DD') INTO oneMonthLater;
    SELECT to_char(now() + INTERVAL '2 month', 'YYYY-MM-DD') INTO twoMonthLater;
    SELECT to_char(now() - INTERVAL '12 month', 'sales_YYYY_MM') INTO lastValidTableName;

    -- Create the partition for the next month
	EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF sales FOR VALUES FROM (%L) TO (%L);', targetTableName, oneMonthLater, twoMonthLater);

    -- Remove partitions that are older than a year
	FOR _tb_name IN
		SELECT table_name
		FROM information_schema.TABLES
		WHERE table_name ~ regPatternTableName
		AND table_name <= lastValidTableName
	LOOP
		EXECUTE format('DROP TABLE IF EXISTS %I', _tb_name);
	END LOOP;
	RETURN 1;
END;
$$;

-- create extension pg_cron
CREATE EXTENSION pg_cron;

-- create a pg_cron task and schedule monthly
SELECT cron.schedule('0 0 1 * *', $$SELECT manage_sales_partition();$$);
```

#### The pg\_partman extension

The pg\_partman extension is a PostgreSQL extension that provides advanced partitioning capabilities for large tables. It allows you to manage partitions automatically based on time intervals or other criteria, and provides a set of tools for managing partitioned tables.

With pg\_partman, you can create and manage partitioned tables using a variety of partitioning schemes, including range, list, and hash partitioning. You can also use pg\_partman to manage partition maintenance tasks such as creating and dropping partitions, and to automate the process of moving data between partitions.

Here are some key features of the pg\_partman extension:

* Automatic partitioning: pg\_partman provides automatic partitioning capabilities, allowing you to create and manage partitions based on time intervals or other criteria. This can help to simplify the management of large tables and improve performance.

* Partitioning schemes: pg\_partman supports a variety of partitioning schemes, including range, list, and hash partitioning. This allows you to choose the partitioning scheme that best fits your data and query patterns.

* Partition maintenance: pg\_partman provides a set of tools for managing partition maintenance tasks such as creating and dropping partitions, and for automating the process of moving data between partitions.

* Query routing: pg\_partman supports query routing, allowing you to route queries to the appropriate partition based on the partitioning scheme. This can help to improve query performance and reduce the overhead of managing large tables.

* Compatibility: pg\_partman is compatible with PostgreSQL 9.4 and later, and is available as an open-source extension.

To use pg\_partman, you'll need to install the extension and create a partitioned table using the "partman.create_parent()" function. Here's an example of how you could use pg\_partman to create a partitioned table based on time intervals:

```sql
CREATE TABLE sales (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    date DATE NOT NULL,
    store_id INTEGER NOT NULL,
    amount NUMERIC NOT NULL
)
PARTITION BY RANGE (date);

-- Install the pg_partman extension
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

-- Create a partitioned table based on time intervals
SELECT partman.create_parent('public.sales', 'date', 'native', 'monthly', p_premake := 2);
-- Set retention policy
UPDATE partman.part_config  SET retention = '12 month', retention_keep_table=false WHERE parent_table='public.sales';

-- auto trigger maintenance(partition creation and retention) using pg_cron
CREATE EXTENSION pg_cron;
SELECT cron.schedule('0 0 1 * *', $$SELECT partman.run_maintenance();$$);
```

#### The pg\_pathman extension

The pg\_pathman is a Postgres Pro extension that provides an optimized partitioning solution for large and distributed databases. Using pg\_pathman, you can:

* Partition large databases without downtime.
* Speed up query execution for partitioned tables.
* Manage existing partitions and add new partitions on the fly.
* Add foreign tables as partitions.
* Join partitioned tables for read and write operations.
* The extension is compatible with Postgres Pro 9.5 or higher.

It is worth noting that, starting from Postgres Pro 12, using pg_pathman is not recommended. And it has been deprecated since 2019.

### Partition dynamical creation

Dynamic partition creation can help to simplify management by allowing you to create partitions as needed, without having to pre-create a large number of partitions in advance. This can help to reduce storage costs, as you only create partitions when they are needed, and can help to improve performance by reducing the overhead of managing a large number of partitions.

However, it's important to note that dynamic partition creation can also have some downsides. It can add overhead to the insert process, as PostgreSQL must perform additional work to create the partition and update the partitioning metadata. This overhead can be significant if you are inserting a large amount of data. Additionally, dynamic partition creation can make it more difficult to manage your partitions, as you may need to monitor the partitioning metadata to ensure that you are not creating too many partitions or running out of disk space. So dynamic on-demand creation is not recommended in mass production.

## Conclusion

In this article we introduced PostgreSQL partition tables comprehensively, including its definition, common usage accompanied by example codes. I believe you in front of the screen have fully understood how to use it. If there are any questions, please leave a comment in the comment section, and I will do my best to answer them when I see them.