Re: [HACKERS] Documentation improvements for partitioning - Mailing list pgsql-hackers

On Wed, Feb 15, 2017 at 11:34 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> I think new-style partitioning is supposed to consider each partition as
> an implementation detail of the table; the fact that you can manipulate
> partitions separately does not really mean that they are their own
> independent object.  You don't stop to think "do I really want to drop
> the TOAST table attached to this main table?" and attach a CASCADE
> clause if so.  You just drop the main table, and the toast one is
> dropped automatically.  I think new-style partitions should behave
> equivalently.

That's a reasonable point of view.  I'd like to get some more opinions
on this topic.  I'm happy to have us do whatever most people want, but
I'm worried that having table inheritance and table partitioning work
differently will be create confusion.  I'm also suspicious that there
may be some implementation difficulties.  On the hand, it does seem a
little silly to say that DROP TABLE partitioned_table should always
fail except in the degenerate case where there are no partitions, so
maybe changing it is for the best.

> Now that partitions are declarative, the underlying implementation could
> change away from inheritance.  It's now just an implementation artifact.

I don't really agree with that.  It's true that, for example, we could
decide to store the inheritance information for partitions someplace
other than pg_inherits, but from a user perspective these are always
going to be closely-related features.  Also, there are quite a number
of ways in which it's very noticeable that the objects are separate.
They are dumped separately.  They have separate indexes, and even if
we provide some facility to create a common indexing scheme across all
partitions automatically, you'll still be able to REINDEX or CLUSTER
one of those tables individually.  They can have different storage
properties, like one can be UNLOGGED while another is not.  They show
up in EXPLAIN plans.  The partitioning structure affects what you can
and can't do with foreign keys.  All of those are user-visible things
that make this look and feel like a collection of tables, not just a
single table that happens to have several relfilenodes under the hood.
I think that's actually a really important feature, not a design
defect.

As you may or may not know, EDB has had a proprietary implementation
of table partitioning since Advanced Server 9.1, and one of the things
we've learned from that implementation is that users really like to be
able to fiddle with the individual partitions.  They want to things
like make them individually unlogged, rename them, vacuum them, add
contraints, add triggers, put them in different tablespaces, vary
indexing strategies, all the stuff that you normally do with
standalone tables.  Any time one of those things didn't work quite
like it would for a standalone table, we got complaints.  One of the
things that has become really clear over the five years that feature
has been out of the field is that users value the ability to do
different things with different child tables.  Now that of course does
not mean that they don't want to be able to operate on the hierarchy
as a whole; we have numerous feature requests in that direction, too.
But, at least among the base of customers that have talked to us about
the proprietary partitioning feature in Advanced Server, wanting to
treat a partition as a table and do some arbitrary thing to it that
can be done to a table is extremely common.  Of course, I can't
promise (and am not trying to argue) that the reaction among
PostgreSQL users generally will necessarily be similar to the
experience we've had with our Advanced Server customers, but this
experience has definitely caused me to lean in the direction of
wanting partitions to be first-class objects.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company