Skip site navigation (1) Skip section navigation (2)

Re: Partitioning by status?

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Partitioning by status?
Date: 2012-01-10 17:09:37
Message-ID: 20120110170937.GA20023@tux (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Mike Blackwell <mike(dot)blackwell(at)rrd(dot)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.

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°

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2012-01-10 21:21:26
Subject: Re: pg_upgrade failure "contrib" issue?
Previous:From: Tom LaneDate: 2012-01-10 17:04:34
Subject: Re: Query planner doesn't use index scan on tsvector GIN index if LIMIT is specifiedQuery planner doesn't use index scan on tsvector GIN index if LIMIT is specified

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group