Dynamic partitions in PostgreSQL

Up to PostgreSQL 9.6, to partition a table we use trigger functions and inheritance to create partitions. In range partitioning(on date or a sequence number) we need to create partitions manually in advance and change the trigger function every time.

We have a similar situation where we have a transactions table which is partitioned on quarter basis and we need to create partitions manually and change trigger function when the limit is reached.

To overcome this I wrote a function which will create partition if it not exists, also it will create the indexes same as the master table. Below is the table and function. We can use the same function for month wise partitions by doing some changes.

Table Definition

Below is my table definition. We need to partition this table based on transaction_date column. It is quarter wise partition at any time we need to store only last two quarters data and remaining partitions we need to drop.

create table client_transactions(
    id bigserial primary key,
    client_id varchar,
    transaction_date date not null,
    ISIN varchar,
    price decimal,
    description text

Trigger Function

To partition the table we need to create a trigger function on the master table, which will handle the creation of child table, creation of indexes on child table same as in the master table.

create or replace function client_transactions_function()
returns trigger as $$
    table_name varchar;
    quarter int;
    ts_begin date;
    ts_end date;
	query text;
	rec RECORD;
	t_year int;
	n_year int;
    select EXTRACT(QUARTER FROM new.transaction_date) into quarter;
	select EXTRACT(year from new.transaction_date) into t_year;
    table_name := format('client_transactions_%s_Q%s', t_year, quarter);
    perform 1 from pg_class where lower(relname) = lower(table_name) limit 1;
    if not found
        CASE quarter
          WHEN 1 THEN select t_year||'-01-01' into ts_begin; select t_year||'-04-01' into ts_end;
		  WHEN 2 THEN select t_year||'-04-01' into ts_begin; select t_year||'-07-01' into ts_end;
          WHEN 3 THEN select t_year||'-07-01' into ts_begin; select t_year||'-10-01' into ts_end;
          WHEN 4 THEN select t_year||'-10-01' into ts_begin; n_year = t_year+1; select n_year||'-01-01' into ts_end;
          ELSE RAISE EXCEPTION 'Wrong date range';
        END CASE; 		  
        execute format('create table %s (like client_transactions including all)', table_name);
        execute format('alter table %s inherit client_transactions, add check (transaction_date >= ''%s'' and transaction_date < ''%s'')',table_name, ts_begin, ts_end);
    end if;
    execute 'insert into ' || table_name || ' values ( ($1).* )' using new;
    return null;
language plpgsql;

In this function first we verify the existence of table “client_transactions_<year>_<quarter>” , if it exists then we will insert data into the table. Else we will create the table, indexes and insert the data into that table.

Trigger creation

Now we will proceed and create the trigger.

create trigger insert_client_transactions_trigger before insert on client_transactions for each row execute procedure client_transactions_function();

This will create the trigger on the client_transactions table and execute the function before data inserted into the client_transactions table.

Verify the trigger function

Now the trigger creation is completed, we will verify by inserting data into the table.

--- inserting some random data into the table
insert into client_transactions(client_id,transaction_date,ISIN,price,description) values ('EI1W34',now()::date,'INE001A01036',986282.1022,'HDFC BANK');

insert into client_transactions(client_id,transaction_date,ISIN,price,description) values ('EI1W34',now()::date,'INE001A01036',986282.1022,'HDFC BANK');

insert into client_transactions(client_id,transaction_date,ISIN,price,description) values ('EI1W34','2020-01-01'::date,'INE001A01036',986282.1022,'HDFC BANK');
--- checking new child tables created or not
partitioning=# \dt
                    List of relations
 Schema |            Name             | Type  |  Owner
 public | client_transactions         | table | postgres
 public | client_transactions_2020_q1 | table | postgres
 public | client_transactions_2020_q2 | table | postgres
(3 rows)
--- checking table definition
partitioning=# \d client_transactions_2020_q2
                               Table "public.client_transactions_2020_q2"
      Column      |       Type        |                            Modifiers
 id               | bigint            | not null default nextval('client_transactions_id_seq'::regclass)
 client_id        | character varying |
 transaction_date | date              | not null
 isin             | character varying |
 price            | numeric           |
 description      | text              |
    "client_transactions_2020_q2_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "client_transactions_2020_q2_transaction_date_check" CHECK (transaction_date >= '2020-04-01'::date AND transaction_date < '2020-07-01'::date)
Inherits: client_transactions

As we can see two tables for two different quarters got created. Also the table got all indexes including constraints (primary key, not null etc).

Now we will execute a query and verify the partition scanning.

partitioning=# explain analyze select * from client_transactions where transaction_date >= '2020-04-19';
                                                           QUERY PLAN
 Append  (cost=0.00..16.00 rows=161 width=140) (actual time=0.008..0.009 rows=2 loops=1)
   ->  Seq Scan on client_transactions  (cost=0.00..0.00 rows=1 width=140) (actual time=0.003..0.003 rows=0 loops=1)
         Filter: (transaction_date >= '2020-04-19'::date)
   ->  Seq Scan on client_transactions_2020_q2  (cost=0.00..16.00 rows=160 width=140) (actual time=0.004..0.005 rows=2 loops=1)
         Filter: (transaction_date >= '2020-04-19'::date)
 Planning time: 0.214 ms
 Execution time: 0.029 ms
(7 rows)

As we can see only the child table “client_transactions_2020_q2” got scanned.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s