PostgreSQL – Automatic Date wise Table Partitioning with Index
MiniBytes: In this article we are going to see, how to automatically partition tables with triggers including index. This tutorial will be helpful for you to create the table partition in the PostgreSQL server including index, which you can use in your Web applications. What i am narrating here is the code which was taken from the live projects which i am currently working on.
Table partitioning is one of the most useful feature which was introduced in PostgreSQL server. If you are handling huge amount of data in the database, then you surely need this code to automatically partition your data table daily.
In this article we are going to see how to partition the PostgreSQL database table automatically with the help of the trigger. I have also included a feature to add automatic indexing if you need one. If you are going to use millions of rows in your table, then you surely need indexing, which i have included in this trigger.
CREATE OR REPLACE FUNCTION public.create_partition_and_insert() RETURNS trigger AS $BODY$ DECLARE partition_date TEXT; partition TEXT; BEGIN partition_date := to_char(NEW.timestamp,'YYYY_MM_DD'); partition := TG_RELNAME || '_' || partition_date; IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN RAISE NOTICE 'A partition has been created %',partition; EXECUTE 'CREATE TABLE ' || partition || ' (check (date(timestamp) = ''' || date(NEW.timestamp) || ''')) INHERITS (' || TG_RELNAME || ');'; RAISE NOTICE 'Index has been created'; execute 'create index ' || partition || '_timestamp_idx on ' || partition || '( date(timestamp))'; END IF; EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING id;'; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION public.create_partition_and_insert() OWNER TO postgres;
In the above code, you can see the function or stored procedure which can be used with the trigger to create table automatically each and every day. But before using the table, you at least need one column which consists of timestamp data. Then only this trigger will work.
Here “timestamp” is a column name which i have used, you can replace with whatever name you need, or you can replace with the column name too.
If you have any doubt, please leave the comments…:-)..