Re: Documentation improvements for partitioning

On Thu, Mar 9, 2017 at 8:23 PM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2017/03/10 3:26, Robert Haas wrote:
>> I think you might have the titles for 0002 and 0003 backwards.
>
> Oops, you're right.
>
>> On Fri, Mar 3, 2017 at 2:51 AM, Amit Langote wrote:
>>> 0002: some cosmetic fixes to create_table.sgml
>>
>> I think this sentence may be unclear to some readers:
>>
>> + One might however want to set it for only some partitions,
>> + which is possible by doing <literal>SET NOT NULL</literal> on individual
>> + partitions.
>>
>> I think you could replace this with something like: Even if there is
>> no <literal>NOT NULL</> constraint on the parent, such a constraint
>> can still be added to individual partitions, if desired; that is, the
>> children can disallow nulls even if the parent allows them, but not
>> the other way around.
>
> Reads much better, done that way. Thanks.
>
>>> 0003: add clarification about NOT NULL constraint on partition columns in
>>> alter_table.sgml
>>
>> This is about list-ifying a note, but I think we should try to
>> de-note-ify it. It's a giant block of text that is not obviously more
>> noteworthy than the surrounding text; I think <note> should be saved
>> for things that particularly need to be called out.
>
> OK. The patch is now just about de-note-ifying the block of text. Since
> I don't see any other lists in the Parameters portion of the page, I also
> take back my list-ifying proposal.
>
> Attached updated patches.

Committed 0002, 0003.

I think the section on BRIN in 0001 is just silly. BRIN is a very
useful index type, possibly more useful than anything except btree,
but I think documenting it as an alternative method of partitioning is
over the top.

+ The following forms of partitioning can be implemented in
+ <productname>PostgreSQL</productname>:

Any form of partitioning can be implemented, at least to some degree,
using inheritance or UNION ALL views. I think what this should say is
that PostgreSQL has native support for list and range partitioning,
and then it can go on top say that if this built-in support is not
suitable for a particular use case (either because you need some other
partitioning scheme or due to some other limitation), inheritance or
UNION ALL views can be used instead, adding flexibility but losing
some of the performance benefits of built-in declarative partitioning.

<para>
Partitions may have their own indexes, constraints and default values,
- distinct from other partitions. Partitions do not inherit indexes from
- the partitioned table.
+ distinct from other partitions. Partitions do not currently inherit
+ indexes from the partitioned table.
+ </para>
+
+ <para>
+ See <xref linkend="sql-createtable"> for more details creating partitioned
+ tables and partitions.
</para>

I don't think we should add "currently"; that amounts to speculation
about what will happen in future versions. Also, I favor collapsing
these into one paragraph. A single-sentence paragraph tends to look
OK when you're reading the SGML directly, but it looks funny in the
rendered version.

+ <firstterm>sub-partitioning</firstterm>. It is not currently possible to
+ alter a regular table into a partitioned table or vice versa. However,
+ it is possible to add a regular or partitioned table containing data into
+ a partition of a partitioned table, or remove a partition; see

I think we should say "as a partition" rather than "into a partition",
assuming you're talking about ATTACH PARTITION here.

- partitioned tables. For example, specifying <literal>ONLY</literal>
- when querying data from a partitioned table would not make much sense,
- because all the data is contained in partitions, so this raises an
- error. Specifying <literal>ONLY</literal> when modifying schema is
- not desirable in certain cases with partitioned tables where it may be
- fine for regular inheritance parents (for example, dropping a column
- from only the parent); an error will be thrown in that case.
+ partitioned tables. Specifying <literal>ONLY</literal> when modifying
+ schema is not desirable in certain cases with partitioned tables
+ whereas it may be fine for regular inheritance parents (for example,
+ dropping a column from only the parent); an error will be thrown in
+ that case.

I don't see why this is an improvement.

- data inserted into the partitioned table cannot be routed to foreign table
- partitions.
+ data inserted into the partitioned table is currently not routed to foreign
+ table partitions.

Again, let's not speculate about the future.

+ Note that it is currently not supported to propagate index definition
+ from the master partitioned table to its partitions; in fact, it is
+ not possible to define indexes on partitioned tables in the first
+ place. This might change in future releases.

Same here.

+ There are currently the following limitations of using partitioned tables:

And here. Better to write "The following limitations apply to
partitioned tables:"

+ It is currently not possible to define indexes on partitioned tables
+ that include all rows from all partitions in one global index.
+ Consequently, it is not possible to create constraints that are realized
+ using an index such as <literal>UNIQUE</>.

This doesn't seem very grammatical, and it kind of overlaps with the
previous point, and the following point. How about just adding a
sentence to the previous paragraph: This also means that there is no
way to create a primary key, unique constraint, or exclusion
constraint spanning all partitions; it is only possible to constrain
each leaf partition individually.

+ <command>INSERT</command> statements with <literal>ON CONFLICT</>
+ clause are currently not allowed on partitioned tables.

Obsolete.

+ implicit partition constraint of the original partition. This might
+ change in future releases.

Remove speculation.

+ In some cases, one may want to add columns to partitions that are not
+ present in the parent table which is not possible to do with the above
+ method. For such cases, partitioning can be implemented using
+ inheritance (see <xref linkend="ddl-inherit">).

Hmm, I bet that's not the only advantage. And it doesn't seem like
the way to lead.

e.g.

While the built-in declarative partitioning is suitable for most
common use cases, there are some circumstances where a more flexible
approach may be useful. Partitioning can be implemented using table
inheritance, which allows for several features which are not supported
by declarative partitioning, such as:

- Partitioning enforces a rule that all partitions must have exactly
the same set of columns as the parent, but table inheritance allows
children to have extra columns not present in the parent.

- Table inheritance allows for multiple inheritance.

- Declarative partitioning only supports list and range partitioning,
whereas table inheritance allows data to be divided in a manner of the
user's choosing. (Note, however, that if constraint exclusion is
unable to prune partitions effectively, query performance will be very
poor.)

- Some operations require a stronger lock when using declarative
partitioning than when using table inheritance. (list these)

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