Re: [GENERAL] about partitioning

On Thursday 11 September 2008 07:47:00 Joao Ferreira gmail wrote:
> Hello all,
>
> my application is coming to a point on which 'partitioning' seems to be
> the solution for many problems:
>
> - query speed up
> - data elimination speed up
>
> I'dd like to get the feeling of it by talking to people who use
> partitioning, in general..
>
> - good, bad,

good :-)

> - hard to manage, easy to manage,

I think the upfront costs for managing a partitioning setup are higher with
postgres than other systems, but there is nothing that you shouldn't be able
to automate in a cron script (at which point management becomes easy), plus
postgres gives you some interesting flexibility that is harder to find in
other setups.

> - processing over-head during INSERT/UPDATE,

you can setup inserts to have relativly little overhead, but it requires more
management/maintence work up front. Updates within a partition also have
relativly little extra overhead, especially if you put in a little
application logic to figure out how to work on a partition directly. Updates
where you are changing the partition key value are always more problematic
though.

> - stability/compatibility of pg_dump and restore operations,

no real issues here as long as your on recent enough versions to do wildcard
table matching for individual tables.

> - how many partitions would be reasonable for read _and_ write access
> optimal speed;
>

again, this depends on how exactly your working on the data. For example, we
have tables with over a thousand partitions on them; in those scenarios all
data is written into a single partition (with a new partition created daily),
and the qeury patterns are really straightforward... last month gets a lot of
queries, lasat three months not so much, last year barely any, and beyond
that is pretty much just archive info. That said, we have other systems where
that wouldnt work at all (for example, a static number of partitions, all of
which are queried activly).

For some more info, I've given at least one presentation on the topic, which
seems to be missing from the omniti site, but I've uploaded it to
slideshare...
http://www.slideshare.net/xzilla/postgresql-partitioning-pgcon-2007-presentation

HTH.

--
Robert Treat
http://www.omniti.com
Database: Scalability: Consulting:

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general