Re: constraint_exclusion on OLTP tables

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

Chris Hoover <revoohc(at)gmail(dot)com> writes:
> The problem I am having is how can you safely move records between
> main_table_live and one of the other partition tables?

My advice is "don't do that". Partition just based on the year.
You can exploit your knowledge about status distribution by creating
suitable indexes.

> Also, is there a way to make foreign keys work with the partitioned table

No, not at present, which is another reason you don't want records
moving around.

regards, tom lane

On Mon, 2006-02-13 at 09:50 -0500, Chris Hoover wrote:

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

My not just delete from the live table and insert into the partitioned
history table? That can be done atomically and with triggers.

Alternatively, keep all versions of the record as it develops over time
so the key becomes PK+timestamp and all writes are inserts. That way you
don't need an audit table, since you never change data, so probably
overall a smaller system and one less prone to bugs in the data update
processes.

Best Regards, Simon Riggs