I’ve been working on a project that needs a time series database and we are building in Postgresql so I needed to figure out how to partition data. Having done this a number of times on Oracle, I was curious about the comparison and after an hour or poking around the internet, I settled on an implementation that was fairly elegant.
First the time series table.
CREATE TABLE main.meter_usage_ts ( production_meter_ts_id bigserial PRIMARY KEY, meter_id bigint NOT NULL references main.meter(meter_id), start_timestamp timestamp NOT NULL, end_timestamp timestamp NOT NULL, timezone text, value numeric(10, 4), uom text ) WITH ( OIDS = FALSE );
We will want to create partitions for this data by the month to help with query performance. From here, there are two choices: create all of your partitions from the start and run a maintenance job that ensures there are partitions available to hold data. Or you can write a stored proc that checks for the presence of the partition before inserting and creates it if needed. I chose the second way.
CREATE OR REPLACE FUNCTION main.create_production_meter_ts_partition_and_insert() RETURNS TRIGGER AS $BODY$ DECLARE partition_date TEXT; partition TEXT; BEGIN partition_date := to_char(NEW.start_timestamp, 'YYYY_MM'); partition := TG_TABLE_NAME || '_' || 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 ' || TG_TABLE_SCHEMA || '.' || partition || ' () INHERITS (' || TG_TABLE_SCHEMA || '.' || TG_RELNAME || ');'; END IF; EXECUTE 'INSERT INTO ' || TG_TABLE_SCHEMA || '.' || partition || ' select $1.* ' USING new; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER create_production_meter_ts_partition_and_insert_trigger BEFORE INSERT ON main.production_meter_ts FOR EACH ROW EXECUTE PROCEDURE main.create_production_meter_ts_partition_and_insert();
The cool thing about this is that there are no maintenance jobs to run (and to forget to run) to create the partitions. Bad thing is the IF check runs on every insert.
Extra bonus – this is fairly generic if you are partitioning by date.