Skip site navigation (1) Skip section navigation (2)

Partitioning attempts

From: "Mikael Carneholm" <Mikael(dot)Carneholm(at)WirelessCar(dot)com>
To: "'Pgsql-General (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: Partitioning attempts
Date: 2005-10-31 20:02:03
Message-ID: 7F10D26ECFA1FB458B89C5B4B0D72C2B0881FB@sesrv12.wirelesscar.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi,

I did a partially successful attempt at creating a partitioned table + two subpartitions in Pg8.1b inspired by the method used by Bizgres (see http://www.bizgres.org/assets/docs/html/tblpartn.htm)

I.e:

CREATE TABLE parttest.mastertab
(
  id serial not null,
  datecol date not null,
  CONSTRAINT pk_mastertab PRIMARY KEY (id)
);

CREATE TABLE parttest.mastertab_jan05
(
  CONSTRAINT mastertab_jan05_datecol_check CHECK (datecol between '20050101' AND '20050131')
) 
INHERITS (parttest.mastertab);

CREATE TABLE parttest.mastertab_feb05
(
  CONSTRAINT mastertab_jan05_datecol_check CHECK (datecol between '20050201' AND '20050228')
) 
INHERITS (parttest.mastertab);


I then added INSERT rules to the mastertab:

create or replace rule "mastertab_insert_0501" as 
on insert to mastertab where (NEW.datecol between '20050101' and '20050131')
do instead insert into mastertab_jan05 (id, datecol) values (NEW.id, NEW.datecol);

create or replace rule "mastertab_insert_0502" as 
on insert to mastertab where (NEW.datecol between '20050201' and '20050228')
do instead insert into mastertab_feb05 (id, datecol) values (NEW.id, NEW.datecol);


Testing the INSERT rules:

insert into parttest.mastertab (id, datecol)
values (nextval('mastertab_id_seq'), '20050101');

insert into parttest.mastertab (id, datecol)
values (nextval('mastertab_id_seq'), '20050201');

-- where did it go?
select p.relname, m.id, m.datecol
from mastertab m, pg_class p
where m.tableoid = p.oid;

relname                                                    id         datecol 
-----------------------------------------------------------------------------
mastertab_jan05                                             1      2005-01-01 
mastertab_feb05                                             2      2005-02-01 


So far so good. Now, the part where it fails to be useful (performance wise) is when you want to make sure a select query only scans the relevant partition(s), as it's not possible to create a conditional SELECT rule using an INSTEAD select ... from ONLY ..., i.e:

create or replace rule "mastertab_select_0501" as
on select to mastertab where (datecol between '20050101' and '20050131')
do instead select * from only mastertab_jan05;

Also, we would need UPDATE and DELETE rules, but these are tricky as an update may involve both inserting and deleting rows (if the row belongs to another child table after the update).

The steps carried out above could be wrapped by a function to ease the process, something like:

partition_add('mastertable', 'jan05', 'datecol between 20050101 and 20050131', 'tbsp1')

..could create the child table "mastertable_jan05" in tablespace tbsp1 together with the needed rules, but new DDL syntax would of course be better still.


Ideas/comments?


*******************************************************************
Mikael Carneholm
Systems Engineer
Service Delivery
WirelessCar
Address Kajskjul 107, Frihamnen, SE-417 07 Göteborg, Sweden
Phone + 46 31 65 19 57
Mobile + 46 703 22 29 57
Fax + 46 31 65 19 99
E-mail to_lowercase(my_first_name.my_last_name)(at)wirelesscar.com
Website www.wirelesscar.com
Telematics made easy
 

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2005-10-31 20:29:52
Subject: Re: vacuuming strangeness
Previous:From: Wes WilliamsDate: 2005-10-31 19:51:39
Subject: Re: Oracle 10g Express - any danger for Postgres?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group