On Thu, Nov 10, 2011 at 1:19 PM, Dimitri Fontaine <dimi...@2ndquadrant.fr> wrote: > Now the aim would be to be able to implement the operation you describe > by using the new segment map, which is an index pointing to sequential > ranges of on-disk blocks where the data is known to share a common key > range over the columns you're segmenting on. I would imagine this SQL: > > TRUNCATE foo WHERE year < 2009; > > As the on-disk location of the data that qualify this WHERE clause is > known, it could be possible to (predicate) lock it and bulk remove it, > unlinking whole segments (1GB) at a time when relevant.
I am basically in agreement with you. After wanting better partitioning (Oracle-style) in Postgres for some time just to be free of the mechanically painful table-inheritance version, I have come around to thinking it's basically a bad idea, but one that with a little bit of finessing can be made a good idea. The reason I have started to think this is because of an old feature that works very well: CREATE INDEX. In spite of what people might think, I think it's pretty clear that CREATE INDEX is not DDL: it's actually physical advice to the system. I have seen the fourth-generation-language promise delivered upon quite a few times in production, now: we witness an access pattern that becomes problematic, we run CREATE INDEX CONCURRENTLY, the problem is solved without any change to the application, and the index definition is backported to our application bootstrapping process. It would be hard for me to understate how valuable this has been to avoid both premature optimization and excessive panic when dealing with change. Similar to the overall project stance on query hints, I don't think Postgres should retreat on its ground from being a 4GL system. I think both indexes and a hypothetical partitioning feature should be clearly isolated as directives to the system about how to physically organize and access data, and any partitioning feature that creates new relation namespace entries and expects you to manipulate them to gain the benefits seems like extra, non-desirable surface area to me. I think this becomes especially apparent once one considers on-line repartitioning (I am exposing a bias here, but any feature in Postgres that cannot be done concurrently -- like VACUUM FULL -- is very dangerous to both me and my customers, whereas it may not be useless or dangerous to a build-your-own data warehouse). It feels like it would be desirable to have the physical partitions exist in an inconsistent-state whereby they are being brought into alignment with the newly desired physical description. Finally, I think a legitimate objection to this inclination is that it can be really easy to issue a DELETE that is usually fast, but when any mistake or change creeps in becomes very slow: I have heard from some friends making heavy use of table partitioning via inheritance that one of the problems is not quite exactly matching the table constraint, and then hosing their hardware. As a result, they mangle partitions explicitly in the application to prevent foot-gunning. That's clearly lame (and they know it), but I think may indicate a need to instead allow for some kind of physical-access-method assertion checking quite apart from the logical content of the query that can deliver a clear, crisp error to application developers if a preferred access pattern is not usable. My experience suggests that while solving problems is good, turning problems into flat-out errors is *nearly* as good, and worth some more investigation. -- fdr -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers