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 $$
declare
table_name varchar;
quarter int;
ts_begin date;
ts_end date;
query text;
rec RECORD;
t_year int;
n_year int;
begin
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
then
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;
end;
$$
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 |
Indexes:
"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.
Viswamitra's blog 