From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: Partitioning by status?
Date: Tue, 10 Jan 2012 18:09:37 +0100
Message-ID: <20120110170937.GA20023@tux> (raw)
In-Reply-To: <CANPAkgt9mGoF7SyA_n7jX3t6siLGPyA=d1tR2bPwKiAex3bDMQ@mail.gmail.com>
References: <CANPAkgt9mGoF7SyA_n7jX3t6siLGPyA=d1tR2bPwKiAex3bDMQ@mail.gmail.com>
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°
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: akretschmer@spamfence.net
Subject: Re: Partitioning by status?
In-Reply-To: <20120110170937.GA20023@tux>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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