Help & Documentation>实践教程>TencentDB for PostgreSQL>Automatically Creating Partition in PostgreSQL

Automatically Creating Partition in PostgreSQL

Last updated: 2024-04-30 16:00:12

On this page:

In older versions of PostgreSQL, table partitioning can be supported through inheritance, such as creating a partition for each month based on time and storing data records in corresponding partitions. Starting from PostgreSQL version 10, declarative partitioning is also supported. This article will discuss how to create partitions in advance or create partitions in real-time based on the data being written.
The following are several common schemes for PostgreSQL to automatically create partitioned tables.

Scenes

In practical use, partitioned tables generally use a time field as the partition key. For example, if the partition field type is timestamp and the partitioning method is List of values. The table structure is as follows:
CREATE TABLE tab
(
    id   bigint GENERATED ALWAYS AS IDENTITY,
    ts   timestamp NOT NULL,
    data text
) PARTITION BY LIST ((ts::date));
CREATE TABLE tab_def PARTITION OF tab DEFAULT;
Partition creation generally falls into the following two scenarios:

Scheduled partition creation

You can create partitions in advance with the help of a task scheduling tool. Common tools and partition creation methods are as follows:

Using system schedulers such as Crontab (Linux, Unix, etc.) and Task Scheduler (Windows)

Taking Linux as an example, create a partitioned table at 14:00 every day for the next day:
cat > /tmp/create_part.sh <<EOF
dateStr=\$(date -d '+1 days' +%Y%m%d); 
psql -c "CREATE TABLE tab_\$dateStr (LIKE tab INCLUDING INDEXES); ALTER TABLE tab ATTACH PARTITION tab_\$dateStr FOR VALUES IN ('\$dateStr')";
EOF
(crontab -l 2>/dev/null; echo "0 14   * bash /tmp/create_part.sh ") | crontab -

Using built-in schedulers such as pg_cron and pg_timetable

Taking pg_cron as an example, create a partitioned table at 14:00 every day for the next day:
CREATE OR REPLACE FUNCTION create_tab_part() RETURNS integer
    LANGUAGE plpgsql AS
$$
DECLARE
    dateStr varchar;
BEGIN
    SELECT to_char(DATE 'tomorrow', 'YYYYMMDD') INTO dateStr;
    EXECUTE
        format('CREATE TABLE tab_%s (LIKE tab INCLUDING INDEXES)', dateStr);
    EXECUTE
        format('ALTER TABLE tab ATTACH PARTITION tab_%s FOR VALUES IN (%L)', dateStr, dateStr);
    RETURN 1;
END;
$$;

CREATE EXTENSION pg_cron;

SELECT cron.schedule('0 14   *', $$SELECT create_tab_part();$$);

Using dedicated partition management extensions such as pg_partman

Taking pg_partman as an example, create a partitioned table every day for the next day:
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(p_parent_table => 'public.tab',
                             p_control => 'ts',
                             p_type => 'native',
                             p_interval=> 'daily',
                             p_premake => 1);

On-demand real-time partition creation

If you want to create partitions according to the need of data insertion, so you can determine whether there is data in a time range based on whether a partition exists, this generally can be implemented with triggers.
Note that this method has the following two issues::
Only PostgreSQL 13 and later provide BEFORE/FOR EACH ROW triggers for partitioned tables.
ERROR:  "tab" is a partitioned table
DETAIL:  Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.
When data is inserted, the partitioned table definition cannot be modified due to the table lock; that is, child tables cannot be attached. Therefore, another connection must be used to perform the ATTACH operation. Here, the LISTEN/NOTIFY mechanism can be used to ask another connection to modify the partition definition.
ERROR:  cannot CREATE TABLE .. PARTITION OF "tab"
      because it is being used by active queries in this session
OR
ERROR:  cannot ALTER TABLE "tab"
      because it is being used by active queries in this session
Triggers (Implementing sub-table creation and NOTIFY)
CREATE FUNCTION part_trig() RETURNS trigger
    LANGUAGE plpgsql AS
$$
BEGIN
    BEGIN
        / try to create a table for the new partition /
        EXECUTE
            format('CREATE TABLE %I (LIKE tab INCLUDING INDEXES)', 'tab_' || to_char(NEW.ts, 'YYYYMMDD'));

        /*
         * tell listener to attach the partition
         * (only if a new table was created)
         */
        EXECUTE
            format('NOTIFY tab, %L', to_char(NEW.ts, 'YYYYMMDD'));
    EXCEPTION
        WHEN duplicate_table THEN
            NULL; -- ignore
    END;

    / insert into the new partition /
    EXECUTE
        format('INSERT INTO %I VALUES ($1.*)', 'tab_' || to_char(NEW.ts, 'YYYYMMDD'))
        USING NEW;

    / skip insert into the partitioned table /
    RETURN NULL;
END;
$$;

CREATE TRIGGER part_trig
    BEFORE INSERT
    ON TAB
    FOR EACH ROW
    WHEN (pg_trigger_depth() < 1)
EXECUTE FUNCTION part_trig();
Code (implementing LISTEN and ATTACH for child tables)
#!/usr/bin/env python3.9
# encoding:utf8
import asyncio

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

conn = psycopg2.connect('application_name=listener')
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
cursor.execute(f'LISTEN tab;')


def attach_partition(table, date):
    with conn.cursor() as cs:
        cs.execute('ALTER TABLE "%s" ATTACH PARTITION "%s_%s" FOR VALUES IN (\'%s\')' % (table, table, date, date))


def handle_notify():
    conn.poll()
    for notify in conn.notifies:
        print(notify.payload)
        attach_partition(notify.channel, notify.payload)
    conn.notifies.clear()

loop = asyncio.get_event_loop()
loop.add_reader(conn, handle_notify)
loop.run_forever()

Summary

This document describes two schemes for automatic partition creation as summarized below:
Scheduled pre-creation of partitions solutions in this scenario are straightforward but rely on the system or plugin's scheduling mechanism, resulting in additional management costs during maintenance and migration.
Real-time partition creation on demand scenario can reduce the unnecessary number of partitions according to the actual data pattern, but it also requires a higher version (≥13) and additional connections to complete, with a relatively high complexity.
You can choose an appropriate automatic partition creation method based on your business conditions.
Scenes
Version
Implementation Difficulty
Whether a system scheduler or plugin tool is required
Is an additional connection mechanism required?
Cost effectiveness
Scheduled Pre-creation of Partitions
PostgreSQL 10
Simpler
Supported
Not required
Relatively higher
Create partitions in real-time as needed
Greater than or equal to PostgreSQL 13
More complex
Not required
Supported
Relatively lower

中国站 - English