pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning by status?
6+ messages / 4 participants
[nested] [flat]
* Partitioning by status?
@ 2012-01-10 16:57 Mike Blackwell <mike.blackwell@rrd.com>
0 siblings, 2 replies; 6+ messages in thread
From: Mike Blackwell @ 2012-01-10 16:57 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Partitioning by status?
@ 2012-01-10 17:09 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Mike Blackwell <mike.blackwell@rrd.com>
1 sibling, 0 replies; 6+ messages in thread
From: Andreas Kretschmer @ 2012-01-10 17:09 UTC (permalink / raw)
To: pgsql-performance
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°
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Partitioning by status?
@ 2012-01-12 18:24 Josh Berkus <josh@agliodbs.com>
parent: Mike Blackwell <mike.blackwell@rrd.com>
1 sibling, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2012-01-12 18:24 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Partitioning by status?
@ 2012-01-13 10:44 alexandre - aldeia digital <adaldeia@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: alexandre - aldeia digital @ 2012-01-13 10:44 UTC (permalink / raw)
To: pgsql-performance
>
> 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 ?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Partitioning by status?
@ 2012-01-13 19:05 Josh Berkus <josh@agliodbs.com>
parent: alexandre - aldeia digital <adaldeia@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2012-01-13 19:05 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Partitioning by status?
@ 2012-01-23 17:22 alexandre - aldeia digital <adaldeia@gmail.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: alexandre - aldeia digital @ 2012-01-23 17:22 UTC (permalink / raw)
To: pgsql-performance
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. :)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-01-23 17:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-10 16:57 Partitioning by status? Mike Blackwell <mike.blackwell@rrd.com>
2012-01-10 17:09 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-01-12 18:24 ` Josh Berkus <josh@agliodbs.com>
2012-01-13 10:44 ` alexandre - aldeia digital <adaldeia@gmail.com>
2012-01-13 19:05 ` Josh Berkus <josh@agliodbs.com>
2012-01-23 17:22 ` alexandre - aldeia digital <adaldeia@gmail.com>
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