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: | Whole Thread | Raw Message | 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
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 |