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

Re: Partition DB Tables by month

From: "Mendola Gaetano" <mendola(at)bigfoot(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Cc: "Dani Oderbolz" <oderbolz(at)ecologic(dot)de>
Subject: Re: Partition DB Tables by month
Date: 2003-07-29 23:53:51
Message-ID: 00b701c3562c$a9aa83e0$ (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
"Dani Oderbolz" <oderbolz(at)ecologic(dot)de> wrote:
> Ray Ontko wrote:
> >One limitation to the UNION approach is that you can't 
> >insert, update, or delete through the UNION view.  At
> >some point the application needs to understand how the
> >virtual table is partitioned into these month-specific
> >tables.
> >
> >Romido: Why not simply delete the rows each month instead
> >of dropping tables each month?
> >
> Hmm,
> but it wouls surely be possible (at the cost of some performace)
> to put a trigger on the view to actually sort this all out.
> I guess deleting is a really bad option, as
> 1. The DB needs to do all kinds of logging which you donmm't want (you 
> dont want to rollback ever)
> 2. This operations leaves you with a big Vacuum job
> Therefore, I think, Partitioning could be a good thing.
> BDW: This might be a really important reason for a
> company to switch their Data Warehouse to Postgres,
> as this is almost impossible without it.

If the goal is have the query optimized for the last month
you can easilly accomplish this using a partial index.

Gaetano Mendola

In response to


pgsql-admin by date

Next:From: Renney ThomasDate: 2003-07-29 23:54:21
Subject: Re: 7.3.4 and OpenSSl
Previous:From: Mendola GaetanoDate: 2003-07-29 23:49:48
Subject: Re: Postgres db corrupted ?

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