X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3AECF9DCA89 for ; Mon, 13 Feb 2006 10:50:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28721-09 for ; Mon, 13 Feb 2006 10:50:19 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.194]) by postgresql.org (Postfix) with ESMTP id 1C8F49DC848 for ; Mon, 13 Feb 2006 10:50:12 -0400 (AST) Received: by xproxy.gmail.com with SMTP id t12so679333wxc for ; Mon, 13 Feb 2006 06:50:18 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=k/4+dw4E5T9ni+2SpmTlTsjAA6S9YdJsxOSmsDw2UJ7SRIAMTCVK7BTJrul0HnLHjywwMP5d98JdKuJfWJE2bCU7pNU4FwzN0+x6OPRLubPG4XdaU+IXTikdcDuV6FjDrD0ZNt4cXrRDuDmOG0YGw2Pq1TG146Z9cfqIFbIO+Xc= Received: by 10.70.48.3 with SMTP id v3mr2254262wxv; Mon, 13 Feb 2006 06:50:10 -0800 (PST) Received: by 10.70.80.20 with HTTP; Mon, 13 Feb 2006 06:50:10 -0800 (PST) Message-ID: <1d219a6f0602130650l690a255amf4bb68ceab914e3b@mail.gmail.com> Date: Mon, 13 Feb 2006 09:50:10 -0500 From: Chris Hoover To: "pgsql-admin@postgresql.org" Subject: constraint_exclusion on OLTP tables MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1809_12075737.1139842210854" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.459 required=5 tests=[AWL=-0.184, HTML_00_10=0.642, HTML_MESSAGE=0.001] X-Spam-Score: 0.459 X-Spam-Level: X-Archive-Number: 200602/158 X-Sequence-Number: 20579 ------=_Part_1809_12075737.1139842210854 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Has anyone got a working system using the new 8.1 contraint_exclusion partitioning on OLTP tables? Here is my situation. The main system I administer is required by law to keep 7 years of data. Management wants to keep all 7 years online so the user community can acces= s it whenever they want to. I am looking very hard at partitioning the data since the tables are already becoming very difficult to deal with at the 2-= 3 year point. The main table of our system has a record number and a status. This is my problem table. The record number is a combination of a 2 digit year and a serial column. I have been thinking of partitioning the table based upon those two fields. From my research, 95% + of the records are in 2 status that are considered to be closed, the other 5% are the records that are being actively manipulated by the end user. I have been thinking of partitioning our main table like this: main_table_live (constraint status not in ('C','D); main_table_2003 (constraint rec_number < 040000000 and status in ('C','D'))= ; main_table_2004 (constraint rec_number < 050000000 and rec_number > 039999999 and status in ('C','D')); main_table_2005 (constraint rec_number < 060000000 and rec_number > 049999999 and status in ('C','D')) main_table_2006 (constraint rec_number < 070000000 and rec_number > 059999999 and status in ('C','D')) main_table_2007 (constraint rec_number < 080000000 and rec_number > 069999999 and status in ('C','D')) The problem I am having is how can you safely move records between main_table_live and one of the other partition tables? Obviously, this wil= l have to happen as our users work the data and the records enter into on of the 2 closed statuses ('C' or 'D'). When the status is changed to C or D, = I need to first move the record and run an update against the moved record to make sure all fields are updated and our auditing triggers are fired. Also, is there a way to make foreign keys work with the partitioned table ( i.e. can I create a foriegn key from a normal unpartitioned table to a tabl= e that has been partitioned where the key data may be in multiple partitions)= ? Thanks for any help, Chris ------=_Part_1809_12075737.1139842210854 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Has anyone got a working system using the new 8.1 contraint_exclusion parti= tioning on OLTP tables?

Here is my situation.

The main system I administer is required by law to keep 7 years of data.  Management wants to keep all 7 years online so the user community can access it whenever they want to.  I am looking very hard at partitioning the data since the tables are already becoming very difficult to deal with at the 2-3 year point. 

The main t= able of our system has a record number and a status.  This is my problem table. The record number is a combination of a 2 digit year and a serial column.  I have been thinking of partitioning the table based upon those two fields.  From my research, 95% + of the records are in 2 status that are considered to be closed, the other 5% are the records that are being actively manipulated by the end user.

I have been thinking of partitioning our main table like t= his:

main_table_live (constraint status not in ('C','D);
main_tab= le_2003 (constraint rec_number < 040000000 and status in ('C','D'));
main_table_2004 (constraint rec_number < 050000000 and rec_number &g= t; 039999999 and status in ('C','D'));
main_table_2005 (constraint rec_n= umber < 060000000 and rec_number > 049999999 and status in ('C','D'))
main_table_2006 (constraint rec_number < 070000000 and rec_number &g= t; 059999999 and status in ('C','D'))
main_table_2007 (constraint rec_nu= mber < 080000000 and rec_number > 069999999 and status in ('C','D'))

The problem I am having is how can you safely move records between main_table_live and one of the other partition tables?  Obviously, this will have to happen as our users work the data and the records enter into on of the 2 closed statuses ('C' or 'D').  When the status is changed to C or D, I need to first move the record and run an update against the moved record to make sure all fields are updated and our auditing triggers are fired.

Also, is there a way to make foreign keys work with the partitioned table (i.e. can I create a foriegn key from a normal unpartitioned table to a table that has been partitioned where the key data may be in multiple partitions)?

Thanks for any help,

Chris
------=_Part_1809_12075737.1139842210854--