Postgres Partitions

partition.md

Sample function to add monthly partitions

Create partitioned table

CREATE TABLE IF NOT EXISTS order
(
    id varchar(36) NOT NULL,
    created_at     timestamp(3) with time zone NOT NULL,
    updated_at     timestamp(3) with time zone NOT NULL,
    delivered_at   timestamp(3) with time zone NOT NULL,
    status         int
    primary key (id, created_at)
)
PARTITION BY RANGE (created_at);

Function to add monthly partition

CREATE OR REPLACE FUNCTION create_monthly_partition(
    yyyymm_start int,
    yyyymm_end   int,
    table_name   text,
    hash_key     text
)
    RETURNS text
    LANGUAGE 'plpgsql'
AS $$
DECLARE
    partition_name varchar(50);
    approximate_row_count bigint;
    modulus int;
    sqltext text;
BEGIN
    IF NOT EXISTS
        (SELECT 1
         FROM   information_schema.tables 
         WHERE  table_name = table_name)
    THEN
        RETURN '';
    END IF;

    -- get partition name to be created
    partition_name = table_name || '_' || yyyymm_start;

    -- check if it has already been created
    IF NOT EXISTS
        (SELECT 1
         FROM   information_schema.tables 
         WHERE  table_name = partition_name)
    THEN
        -- modulus can be changed with the increase of data
        modulus = 2;

        -- partitioned by month
        sqltext = 'CREATE TABLE '  || table_name || '_' || yyyymm_start || 
            ' PARTITION OF ' || table_name || ' FOR VALUES 
              FROM (' || '''' || yyyymm_start || '01' || '''' || ') 
              TO   (' || '''' || yyyymm_end   || '01' || '''' || ') 
              PARTITION BY HASH (' || hash_key || ');';

        EXECUTE sqltext;

        -- partitioned by modulus
        FOR counter IN 0..(modulus - 1) BY 1 LOOP

            sqltext = 'CREATE TABLE ' || table_name || '_' || yyyymm_start || '_' || counter || 
                      ' PARTITION OF ' || table_name || '_' || yyyymm_start || 
                      ' FOR VALUES WITH (MODULUS ' || modulus || ', REMAINDER ' || counter || ');';
            EXECUTE sqltext;
        END LOOP;
    END IF;
    RETURN '';
END
$$;

Sample to execute function

EXECUTE create_monthly_partition(
  cast(to_char(current_timestamp, 'yyyyMM') as int),
  cast(to_char(current_timestamp + interval '1 month', 'yyyyMM') as int),
  'order',
  'id'
);

• schemas skipped in all SQLs above