pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Hoover <revoohc@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: constraint_exclusion on OLTP tables
Date: Mon, 13 Feb 2006 09:50:10 -0500
Message-ID: <1d219a6f0602130650l690a255amf4bb68ceab914e3b@mail.gmail.com> (raw)

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 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 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 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

view thread (3+ messages)  latest in thread

Message-ID: <1d219a6f0602130650l690a255amf4bb68ceab914e3b@mail.gmail.com>
Permalink:  ../1d219a6f0602130650l690a255amf4bb68ceab914e3b@mail.gmail.com/
Also on:    postgresql.org/message-id/1d219a6f0602130650l690a255amf4bb68ceab914e3b@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: revoohc@gmail.com
  Subject: Re: constraint_exclusion on OLTP tables
  In-Reply-To: <1d219a6f0602130650l690a255amf4bb68ceab914e3b@mail.gmail.com>
  References: <1d219a6f0602130650l690a255amf4bb68ceab914e3b@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox