Everything you need to know about partitioning (Part 2)

Part 1

We continue to talk about partitioning. Last time, we looked at a simple task about a table divided into sections for each day - the so-called range partitioning. Having broken the table into sections, we faced a new problem - support for a partitioned table. We must create a new section in time, archive obsolete, modify accordingly the trigger controlling the insertion of new data.

I must say at that time I was a little gossip). There is no simple solution yet, and everyone has to reinvent their own bicycle. In the next versions of postgresql, the syntax for creating a partitioned table will appear (as far as I know, there is already such a patch), but for now, you will have to do everything yourself.

Today I want to share the solution that I use to simplify partitioning tasks. We will consider the same task as in the first part of the topic.
')

Automating Partitioning Tasks

In order to automate our task to a certain extent, it is necessary to create several auxiliary objects.

Section table

In this table we will store our sections. master_table is the name of the main table (along with the schema), partition_table is the name of the partition (along with the schema), range_check is a restriction for this section.

CREATE TABLE public .table_partitions
(
master_table text NOT NULL ,
partition_table text NOT NULL ,
range_check text NOT NULL ,
time_added TIMESTAMP DEFAULT now() NOT NULL ,
CONSTRAINT table_partitions_primary_key PRIMARY KEY (master_table, partition_table)
);

* This source code was highlighted with Source Code Highlighter .

Function to add a new section

With this feature we will be able to add a new section. The insertion conditions in the trigger will be automatically changed.

CREATE OR REPLACE FUNCTION public .pg_add_range_partition( IN p_master_table text,
IN p_partition_table text, IN p_range_check text, IN p_trigger_function text,
OUT status_code text)
RETURNS text AS
$$
DECLARE
v_table_ddl text := 'CREATE TABLE [PARTITION_TABLE] ( CHECK ( [RANGE_CHECK] ) ) INHERITS ([MASTER_TABLE]);' ;
v_trigger_ddl text := 'CREATE OR REPLACE FUNCTION [TRIGGER_FUNCTION]() RETURNS TRIGGER AS $body$ ' ||
'BEGIN [RANGE_CHECKS] ELSE RAISE EXCEPTION ' ||
'' 'Inserted data is out of range. Fix [TRIGGER_FUNCTION].' '; ' ||
'END IF; RETURN NULL; END; $body$ LANGUAGE plpgsql;' ;
v_range_checks text := '' ;
rec record;
BEGIN
IF EXISTS ( SELECT 1 FROM public .table_partitions
WHERE master_table = p_master_table
AND partition_table = p_partition_table) THEN
status_code := 'Partition ' || p_partition_table || ' already exists' ;
RETURN ;
END IF ;

v_table_ddl := replace(v_table_ddl, '[PARTITION_TABLE]' , p_partition_table);
v_table_ddl := replace(v_table_ddl, '[RANGE_CHECK]' , p_range_check);
v_table_ddl := replace(v_table_ddl, '[MASTER_TABLE]' , p_master_table);

FOR rec IN ( SELECT 'ELSIF (' || tp.range_check || ') THEN INSERT INTO ' ||
tp.partition_table || ' VALUES (NEW.*); ' AS range_check
FROM public .table_partitions tp
WHERE tp.master_table = p_master_table
ORDER BY tp.time_added DESC ) LOOP
v_range_checks := _pg_check_to_trigger(p_master_table, rec.range_check) || v_range_checks;
END LOOP;

v_range_checks := 'IF (' || _pg_check_to_trigger(p_master_table, p_range_check) ||
') THEN INSERT INTO ' || p_partition_table ||
' VALUES (NEW.*); ' || v_range_checks;

v_trigger_ddl := replace(v_trigger_ddl, '[TRIGGER_FUNCTION]' , p_trigger_function);
v_trigger_ddl := replace(v_trigger_ddl, '[RANGE_CHECKS]' , v_range_checks);

RAISE NOTICE 'Partition script: %' , v_table_ddl;
RAISE NOTICE 'Trigger script: %' , v_trigger_ddl;

EXECUTE v_table_ddl;
EXECUTE v_trigger_ddl;

INSERT INTO public .table_partitions (master_table, partition_table, range_check)
VALUES (p_master_table, p_partition_table, p_range_check);

status_code := 'OK' ;
RETURN ;
EXCEPTION
WHEN OTHERS THEN
status_code := 'Unexpected error: ' || SQLERRM;
END ;
$$ LANGUAGE 'plpgsql' ;

CREATE OR REPLACE FUNCTION public ._pg_check_to_trigger( IN master_table text, IN range_check text)
RETURNS text AS
$$
DECLARE
v_schema text := COALESCE ( SUBSTRING (master_table FROM E '(.*)\\.' ), 'public' );
v_tablename text := replace(master_table, v_schema || '.' , '' );
v_range_check text := range_check;
rec record;
BEGIN
RAISE NOTICE '%' , v_schema;
RAISE NOTICE '%' , v_tablename;

FOR rec IN ( SELECT column_name
FROM information_schema.columns
WHERE table_schema = v_schema
AND table_name = v_tablename) LOOP
v_range_check := replace(v_range_check, rec.column_name, 'NEW.' || rec.column_name);
END LOOP;

RETURN v_range_check;
END ;
$$ LANGUAGE 'plpgsql' ;

* This source code was highlighted with Source Code Highlighter .

Example

1. The first step is to create a master table and a trigger.

CREATE TABLE analytics.events
(
event_id BIGINT DEFAULT nextval( 'analytics.seq_events' ) PRIMARY KEY ,
user_id UUID NOT NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT now() NOT NULL ,
url VARCHAR (1024) NOT NULL ,
referrer VARCHAR (1024),
ip INET NOT NULL
);

CREATE OR REPLACE FUNCTION analytics.events_insert_trigger()
RETURNS TRIGGER AS
$body$
BEGIN
RETURN NULL ;
END ; $body$ LANGUAGE plpgsql;

CREATE TRIGGER events_before_insert
BEFORE INSERT ON analytics.events
FOR EACH ROW EXECUTE PROCEDURE analytics.events_insert_trigger();

* This source code was highlighted with Source Code Highlighter .

2. Add a section

SELECT *
FROM pg_add_range_partition( 'analytics.events' ,
'analytics.events_01012010' ,
'event_time >= TIMESTAMP ' '2010-01-01 00:00:00' ' AND event_time < TIMESTAMP ' '2010-01-02 00:00:00' '' ,
'analytics.events_insert_trigger' );

* This source code was highlighted with Source Code Highlighter .

3. We received a partitioned table, we can start working with it.

INSERT INTO analytics.events (user_id, event_type_id, event_time, url, referrer, ip)
VALUES ( '550e8400-e29b-41d4-a716-446655440000' ::UUID, 1, '2010-01-01 15:01:01' :: TIMESTAMP ,
'http://aymeshkov.habrahabr.ru' , 'http://habrahabr.ru' , '127.0.0.1' ::INET);

* This source code was highlighted with Source Code Highlighter .

Afterword

1. Of course, the solution that I propose is not suitable for everyone, but I think that it will not be so difficult to modify it for your specific task.
2. One small note that will make life easier for many - use generate_series () to understand which sections you need to create.

SELECT '2010-01-01' :: DATE + num * '1 day' :: INTERVAL AS day
FROM generate_series(0, 29) num

* This source code was highlighted with Source Code Highlighter .

The end of the second part

The topic came out voluminous, mainly due to the large amount of code. Again, not everything that I wanted to talk about fit in. On the third part are the following topics:
1. Section management through RULE - how it is done, and why it is bad.
2. Do you want to suffer every time with a trigger? And do not - use LIST PARTITIONING.