@@ -2833,8 +2833,9 @@ VALUES ('Albany', NULL, NULL, 'NY');
28332833 </listitem>
28342834 </itemizedlist>
28352835
2836- These deficiencies will probably be fixed in some future release,
2837- but in the meantime considerable care is needed in deciding whether
2836+ Some functionality not implemented for inheritance hierarchies is
2837+ implemented for declarative partitioning.
2838+ Considerable care is needed in deciding whether partitioning with legacy
28382839 inheritance is useful for your application.
28392840 </para>
28402841
@@ -4057,6 +4058,87 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
40574058 </itemizedlist>
40584059 </para>
40594060 </sect2>
4061+
4062+ <sect2 id="ddl-partitioning-declarative-best-practices">
4063+ <title>Declarative Partitioning Best Practices</title>
4064+
4065+ <para>
4066+ The choice of how to partition a table should be made carefully as the
4067+ performance of query planning and execution can be negatively affected by
4068+ poor design.
4069+ </para>
4070+
4071+ <para>
4072+ One of the most critical design decisions will be the column or columns
4073+ by which you partition your data. Often the best choice will be to
4074+ partition by the column or set of columns which most commonly appear in
4075+ <literal>WHERE</literal> clauses of queries being executed on the
4076+ partitioned table. <literal>WHERE</literal> clause items that match and
4077+ are compatible with the partition key can be used to prune unneeded
4078+ partitions. However, you may be forced into making other decisions by
4079+ requirements for the <literal>PRIMARY KEY</literal> or a
4080+ <literal>UNIQUE</literal> constraint. Removal of unwanted data is also a
4081+ factor to consider when planning your partitioning strategy. An entire
4082+ partition can be detached fairly quickly, so it may be beneficial to
4083+ design the partition strategy in such a way that all data to be removed
4084+ at once is located in a single partition.
4085+ </para>
4086+
4087+ <para>
4088+ Choosing the target number of partitions that the table should be divided
4089+ into is also a critical decision to make. Not having enough partitions
4090+ may mean that indexes remain too large and that data locality remains poor
4091+ which could result in low cache hit ratios. However, dividing the table
4092+ into too many partitions can also cause issues. Too many partitions can
4093+ mean longer query planning times and higher memory consumption during both
4094+ query planning and execution. When choosing how to partition your table,
4095+ it's also important to consider what changes may occur in the future. For
4096+ example, if you choose to have one partition per customer and you
4097+ currently have a small number of large customers, consider the
4098+ implications if in several years you instead find yourself with a large
4099+ number of small customers. In this case, it may be better to choose to
4100+ partition by <literal>HASH</literal> and choose a reasonable number of
4101+ partitions rather than trying to partition by <literal>LIST</literal> and
4102+ hoping that the number of customers does not increase beyond what it is
4103+ practical to partition the data by.
4104+ </para>
4105+
4106+ <para>
4107+ Sub-partitioning can be useful to further divide partitions that are
4108+ expected to become larger than other partitions, although excessive
4109+ sub-partitioning can easily lead to large numbers of partitions and can
4110+ cause the same problems mentioned in the preceding paragraph.
4111+ </para>
4112+
4113+ <para>
4114+ It is also important to consider the overhead of partitioning during
4115+ query planning and execution. The query planner is generally able to
4116+ handle partition hierarchies up a few hundred partitions fairly well,
4117+ provided that typical queries allow the query planner to prune all but a
4118+ small number of partitions. Planning times become longer and memory
4119+ consumption becomes higher as more partitions are added. This is
4120+ particularly true for the <command>UPDATE</command> and
4121+ <command>DELETE</command> commands. Another reason to be concerned about
4122+ having a large number of partitions is that the server's memory
4123+ consumption may grow significantly over a period of time, especially if
4124+ many sessions touch large numbers of partitions. That's because each
4125+ partition requires its metadata to be loaded into the local memory of
4126+ each session that touches it.
4127+ </para>
4128+
4129+ <para>
4130+ With data warehouse type workloads, it can make sense to use a larger
4131+ number of partitions than with an <acronym>OLTP</acronym> type workload.
4132+ Generally, in data warehouses, query planning time is less of a concern as
4133+ the majority of processing time is spent during query execution. With
4134+ either of these two types of workload, it is important to make the right
4135+ decisions early, as re-partitioning large quantities of data can be
4136+ painfully slow. Simulations of the intended workload are often beneficial
4137+ for optimizing the partitioning strategy. Never assume that more
4138+ partitions are better than fewer partitions and vice-versa.
4139+ </para>
4140+ </sect2>
4141+
40604142 </sect1>
40614143
40624144 <sect1 id="ddl-foreign-data">