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:
