Thread: Partitioning by status?

Partitioning by status?

From
Mike Blackwell
Date:
We have a set of large tables.  One of the columns is a status indicator (active / archived).  The queries against these tables almost always include the status, so partitioning against that seems to makes sense from a logical standpoint, especially given most of the data is "archived" and most of the processes want active records.

Is it practical to partition on the status column and, eg, use triggers to move a row between the two partitions when status is updated?  Any surprises to watch for, given the status column is actually NULL for active data and contains a value when archived?

Mike

Re: Partitioning by status?

From
Andreas Kretschmer
Date:
Mike Blackwell <mike.blackwell@rrd.com> wrote:

> We have a set of large tables.  One of the columns is a status indicator
> (active / archived).  The queries against these tables almost always include
> the status, so partitioning against that seems to makes sense from a logical
> standpoint, especially given most of the data is "archived" and most of the
> processes want active records.
>
> Is it practical to partition on the status column and, eg, use triggers to move
> a row between the two partitions when status is updated?  Any surprises to
> watch for, given the status column is actually NULL for active data and
> contains a value when archived?

If i where you, i would try a partial index where status is null. But
yes, partitioning is an other option, depends on your workload.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Partitioning by status?

From
Josh Berkus
Date:
Mike,

> Is it practical to partition on the status column and, eg, use triggers to
> move a row between the two partitions when status is updated?  Any
> surprises to watch for, given the status column is actually NULL for active
> data and contains a value when archived?

When I've done this before, I've had a setup like the following:

1. One "active" partition

2. Multiple "archive" partitions, also partitioned by time (month or year)

3. stored procedure for archiving a record or records.

I'd recommend against triggers because they'll be extremely inefficient
if you need to archive a large number of rows at once.

Also, (2) only really works if you're going to obsolesce (remove)
archive records after a certain period of time.  Otherwise the
sub-partitioning hurts performance.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: Partitioning by status?

From
alexandre - aldeia digital
Date:
>
> Also, (2) only really works if you're going to obsolesce (remove)
> archive records after a certain period of time.  Otherwise the
> sub-partitioning hurts performance.
>

Is there any moves to include the "easy" table partitioning in the 9.2
version ?


Re: Partitioning by status?

From
Josh Berkus
Date:
On 1/13/12 2:44 AM, alexandre - aldeia digital wrote:
>>
>> Also, (2) only really works if you're going to obsolesce (remove)
>> archive records after a certain period of time.  Otherwise the
>> sub-partitioning hurts performance.
>>
>
> Is there any moves to include the "easy" table partitioning in the 9.2
> version ?

Nobody has been submitting patches.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: Partitioning by status?

From
alexandre - aldeia digital
Date:
Em 13-01-2012 17:05, Josh Berkus escreveu:
> On 1/13/12 2:44 AM, alexandre - aldeia digital wrote:
>>>
>>> Also, (2) only really works if you're going to obsolesce (remove)
>>> archive records after a certain period of time.  Otherwise the
>>> sub-partitioning hurts performance.
>>>
>>
>> Is there any moves to include the "easy" table partitioning in the 9.2
>> version ?
>
> Nobody has been submitting patches.
>

I'm sorry hear this. Table partitioning is a very good helper in a large
number of performance issues. If there was a bounty to help anyone to
make this, I would be a happy contributor. :)