pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Joe Conway <mail@joeconway.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
Date: Wed, 15 Sep 2004 13:56:17 -0700
Message-ID: <4148AC71.80908@joeconway.com> (raw)
In-Reply-To: <NOEFLCFHBPDAFHEIPGBOEEJACEAA.simon@2ndquadrant.com>
References: <NOEFLCFHBPDAFHEIPGBOEEJACEAA.simon@2ndquadrant.com>

Simon Riggs wrote:
> Joe,
> 
> Your application is very interesting. I've just read your OSCON paper. I'd
> like to talk more about that. Very similar to Kalido.
> 
> ...but back to partitioning momentarily: Does the performance gain come from
> partition elimination of the inherited tables under the root?

I think the major part of the peformance gain comes from the fact that 
the source database has different needs in terms of partitioning 
criteria because of it's different purpose. The data is basically 
partitioned by customer installation instead of by date. Our converted 
scheme partitions by date, which is in line with the analytical queries 
run at the corporate office. Again, this is an argument in favor of not 
simply porting what you're handed.

We might get similar query performance with a single large table and 
multiple partial indexes (e.g. one per month), but there would be one 
tradeoff and one disadvantage to that:
1) The indexes would need to be generated periodically -- this is a 
tradeoff since we currently need to create inherited tables at the same 
periodicity
2) It would be much more difficult to "roll off" a month's worth of data 
when needed. The general idea is that each month we create a new monthly 
table, then archive and drop the oldest monthly table. If all the data 
were in one big table we would have to delete many millions of rows from 
a (possibly) multibillion row table, and then vacuum that table -- no 
thanks ;-)

Joe



view thread (52+ messages)  latest in thread

Message-ID: <4148AC71.80908@joeconway.com>
Permalink:  ../4148AC71.80908@joeconway.com/
Also on:    postgresql.org/message-id/4148AC71.80908@joeconway.com

 · 

reply
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: mail@joeconway.com, simon@2ndquadrant.com, cbbrowne@acm.org
  Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
  In-Reply-To: <4148AC71.80908@joeconway.com>

* 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