Re: Syntax for partitioning

On Thu, Nov 10, 2011 at 1:19 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)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