Re: On partitioning

From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Amit Kapila
Sent: Saturday, December 06, 2014 5:06 PM
To: Robert Haas
Cc: Amit Langote; Andres Freund; Alvaro Herrera; Bruce Momjian; Pg Hackers
Subject: Re: [HACKERS] On partitioning

On Fri, Dec 5, 2014 at 10:12 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Dec 5, 2014 at 2:18 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > Do we really need to support dml or pg_dump for individual partitions?
>
> I think we do. It's quite reasonable for a DBA (or developer or
> whatever) to want to dump all the data that's in a single partition;
> for example, maybe they have the table partitioned, but also spread
> across several servers. When the data on one machine grows too big,
> they want to dump that partition, move it to a new machine, and drop
> the partition from the old machine. That needs to be easy and
> efficient.
>
> More generally, with inheritance, I've seen the ability to reference
> individual inheritance children be a real life-saver on any number of
> occasions. Now, a new partitioning system that is not as clunky as
> constraint exclusion will hopefully be fast enough that people don't
> need to do it very often any more. But I would be really cautious
> about removing the option. That is the equivalent of installing a new
> fire suppression system and then boarding up the emergency exit.
> Yeah, you *hope* the new fire suppression system is good enough that
> nobody will ever need to go out that way any more. But if you're
> wrong, people will die, so getting rid of it isn't prudent. The
> stakes are not quite so high here, but the principle is the same.
>
>
> Sure, I don't feel we should not provide anyway to take dump
> for individual partition but not at level of independent table.
> May be something like --table <table_name>
> --partition <partition_name>.
>

This does sound cleaner.

> In general, I think we should try to avoid exposing that partitions are
> individual tables as that might hinder any future enhancement in that
> area (example if we someone finds a different and better way to
> arrange the partition data, then due to the currently exposed syntax,
> we might feel blocked).

Sounds like a concern. I guess you are referring to whether we allow a partition relation to be included in the range table and then some other cases. In the former case we could allow referring to individual partitions by some additional syntax if it doesn’t end up looking too ugly or invite a bunch of other issues.

This seems to have been discussed a little bit upthread (for example, see "Open Questions" in Alvaro's original proposal and Hannu Krosing's reply).

Regards,
Amit