On 12/4/10 3:38 PM, Jochen Erwied wrote: > Sunday, December 5, 2010, 12:19:29 AM you wrote: > >> Hmmm, what happens if I need 10 years of data, in monthly partitions? It >> would be 120 partitions. Can you please elaborate on that limitation? >> Any plans on lifting that restriction? > > I'm running a partitioning scheme using 256 tables with a maximum of 16 > million rows (namely IPv4-addresses) and a current total of about 2.5 > billion rows, there are no deletes though, but lots of updates. > > Using triggers or rules on the main table in my case showed to be not very > effective, so I reverted to updating the inherited tables directly. This > way you still can use a SELECT on the main table letting the optimizer do > it's work, but do not run into the problem of oversized shared memory usage > when doing DELETEs or UPDATEs > > IMHO if you are using large partitioning schemes, handle the logic of which > table to update or delete in your application. In most cases extending the > underlying application will be much less work and more flexible than trying > to write a dynamic rule/trigger to do the same job. >
Sounds like my experience exactly, however I am considering forgoing an update altogether, by just combining a DELETE with an INSERT. I'm not sure how that might affect indexing performance as compared to an UPDATE. I also had trouble with triggers; but found that if you use the "ONLY" keyword, they work again: see my original post of this thread. In that case, the application SQL still retrains some simplicity. On this topic, I think there's quite a bit of confusion and updates to the documentation would help greatly. John -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
