Date: Tue, 10 Jan 2012 18:09:37 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Partitioning by status?
Message-ID: <20120110170937.GA20023@tux>
References: 
 <CANPAkgt9mGoF7SyA_n7jX3t6siLGPyA=d1tR2bPwKiAex3bDMQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CANPAkgt9mGoF7SyA_n7jX3t6siLGPyA=d1tR2bPwKiAex3bDMQ@mail.gmail.com>
User-Agent: Mutt/1.5.18 (2008-05-17)

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°