hei, thanks everybody for this great feedback. I'll sure look deeper into this. Joao On Sat, 2008-09-13 at 16:48 -0400, Robert Treat wrote: > 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. >
