Question about paritioning

From: Alex Samad <alex(at)samad(dot)com(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: Question about paritioning
Date: 2017-07-27 05:08:39
Message-ID: CAJ+Q1PUH9HR+YCNm=8hpwa7MabmW5WKZELjw+GbQhKXV6jOPAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I have a large table about 3B rows, that I would like to partition on a
column called _received which is of type timestamp

I followed this
https://medium.com/@StreamBright/creating-partitions-automatically-in-postgresql-7006d68c0fbb

and came up with
CREATE OR REPLACE FUNCTION create_partition_and_insert() RETURNS trigger AS
$BODY$
DECLARE
partition_date TEXT;
partition TEXT;
BEGIN
partition_date := to_char(NEW._received,'YYYYMM');
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 ' || partition || ' (check (
to_char(_received, '''YYYYMM''') = ''' || partition_date || ''')) INHERITS
(' || TG_TABLE_NAME || ');';
END IF;
EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_TABLE_NAME ||
' ' || quote_literal(NEW) || ').* RETURNING patent_id;';
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Which I believe generates something like this
CREATE TABLE feedintra_201707( check (to_char(_received,'YYYYMM') =
'201707' )) INHERITS (XXXXXX);

My concern is the cost of doing to_char on every row.

So
Is this going to work
Is this the right way to do it.

I was thinking if I could change the check to be something like

check ( _recieved >= YYYYMM 1 00:00 and _recieved <= YYYYMM <last day of
the month> 23:59:59.999 )

so I am not sure how to make up the above line based on a timestamp

Thanks
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2017-07-27 05:33:23 Re: Question about paritioning
Previous Message John R Pierce 2017-07-27 04:13:28 Re: ODBC driver issue