Partitions in Postgres: Automatically creating partitions based on an attribute

A long time ago... I worked on importing ~ half a billion log records into Postgres. To achieve a low query response time, I used a partitioner that would shard records monthly. I documented it in the Postgres docs

Here it is:

	----------------------------------------
	-- function that inserts records.
	-- if the partition for the month of
	-- the record isn't found, a new partition
	-- is created.
	----------------------------------------
	CREATE OR REPLACE FUNCTION logs_insert_func()
	RETURNS TRIGGER AS $$
	DECLARE
	ourTable varchar;
	ourTableExists integer;
	ourFirstOfMonth date;
	ourFirstOfNextMonth date;
	ourInsertSTMT TEXT;
	ourCreateSTMT TEXT;
	ourMasterTable TEXT;
	BEGIN
	-- The table we'll inherit from
	ourMasterTable := 'logs';
	-- Get the partition table names ~ master_year_month
	SELECT ourMasterTable || '_' ||
	EXTRACT(ISOYEAR FROM NEW.log_time) || '_' ||
	EXTRACT(MONTH FROM NEW.log_time)
	into ourTable;
	-- Create our insert statement
	ourInsertSTMT := 'INSERT INTO '|| ourTable || ' (status,log_time,svc_time,ip_addr,query) VALUES (';
	ourInsertSTMT := ourInsertSTMT|| NEW.status ||',';
	ourInsertSTMT := ourInsertSTMT|| quote_nullable(NEW.log_time) ||',';
	ourInsertSTMT := ourInsertSTMT|| NEW.svc_time ||',';
	ourInsertSTMT := ourInsertSTMT|| quote_nullable(NEW.ip_addr) ||',';
	ourInsertSTMT := ourInsertSTMT|| quote_nullable(NEW.query);
	ourInsertSTMT := ourInsertSTMT || ')';
	--
	--Try execute it
	EXECUTE ourInsertSTMT;
	-- Phew! We didn't throw an exception. Insert worked which means that the
	-- partition for this month exists.
	RETURN NULL;
	EXCEPTION WHEN OTHERS THEN
	-- Insert failed. Let's check whether the table exists
	SELECT count(*) into ourTableExists
	FROM pg_catalog.pg_class c
	WHERE c.relname = ourTable;
	-- If it doesn't exist, try create it
	IF ourTableExists = 0 THEN
	-- First of this month and next month
	SELECT date_trunc('month', NEW.log_time) into ourFirstOfMonth;
	SELECT (ourFirstOfMonth + interval '1 month')::date into ourFirstOfNextMonth;
	-- Create partition with range
	ourCreateSTMT := 'CREATE TABLE '|| ourTable || '(';
	ourCreateSTMT := ourCreateSTMT ||' CHECK ( log_time >=' || quote_nullable(ourFirstOfMonth) ;
	ourCreateSTMT := ourCreateSTMT ||' AND log_time < DATE '|| quote_nullable(ourFirstOfNextMonth) || ')';
	ourCreateSTMT := ourCreateSTMT ||') INHERITS (' || ourMasterTable || ')';
	RAISE NOTICE 'Attempting to create a new table with STMT %',ourCreateSTMT;
	EXECUTE ourCreateSTMT;
	-- Retry to insert row
	EXECUTE ourInsertSTMT;
	IF NOT found THEN
	RAISE NOTICE 'Error inserting into created partition % for %',ourTable,ourInsertSTMT;
	END IF;
	ELSE
	RAISE NOTICE 'Error inserting into existing partition % for %',ourTable,ourInsertSTMT;
	END IF;
	RETURN NULL;
	END;
	$$
	LANGUAGE plpgsql;
	------------------------------
	-- Example master table:
	------------------------------
	CREATE TABLE logs
	(
	id bigserial NOT NULL,
	status smallint,
	log_time timestamp without time zone,
	svc_time real,
	ip_addr inet,
	query character varying(2048),
	CONSTRAINT logs_id PRIMARY KEY (id)
	)
	WITH ( OIDS=FALSE);
	------------------------------
	-- Insert trigger:
	------------------------------
	CREATE TRIGGER
	logs_insert_trigger
	BEFORE INSERT ON logs
	FOR EACH ROW EXECUTE PROCEDURE logs_insert_func();
	------------------------------
	-- Testing time baby!
	-- Inserts from an existing
	-- large table into the new
	-- partitioned tables
	------------------------------
	=# insert into logs (status,log_time,svc_time,ip_addr,query) (select status,log_time,svc_time,ip_addr,query from whois_logs);
	NOTICE: Attempting to create a new table with STMT CREATE TABLE logs_2009_1( CHECK ( log_time >='2010-01-01' AND log_time < DATE '2010-02-01')) INHERITS (logs)
	NOTICE: Attempting to create a new table with STMT CREATE TABLE logs_2009_5( CHECK ( log_time >='2009-05-01' AND log_time < DATE '2009-06-01')) INHERITS (logs)
	NOTICE: Attempting to create a new table with STMT CREATE TABLE logs_2006_9( CHECK ( log_time >='2006-09-01' AND log_time < DATE '2006-10-01')) INHERITS (logs)
	----

view raw temporal_partition_creator_postgres.sql hosted with ❤ by GitHub