Thread: Partitioning by status?
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
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°
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
> > 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 ?
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
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. :)