Re: Partition DB Tables by month

"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.

Regards
Gaetano Mendola