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

Partitioning by status?

From: Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Partitioning by status?
Date: 2012-01-10 16:57:04
Message-ID: CANPAkgt9mGoF7SyA_n7jX3t6siLGPyA=d1tR2bPwKiAex3bDMQ@mail.gmail.com (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-performance by date

Next: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
Previous:From: darklowDate: 2012-01-10 12:30:41
Subject: 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-2014 The PostgreSQL Global Development Group