@@ -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
@@ -3927,6 +3928,84 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
39273928 </itemizedlist>
39283929 </para>
39293930 </sect2>
3931+
3932+ <sect2 id="ddl-partitioning-declarative-best-practices">
3933+ <title>Declarative Partitioning Best Practices</title>
3934+
3935+ <para>
3936+ The choice of how to partition a table should be made carefully as the
3937+ performance of query planning and execution can be negatively affected by
3938+ poor design.
3939+ </para>
3940+
3941+ <para>
3942+ One of the most critical design decisions will be the column or columns
3943+ by which you partition your data. Often the best choice will be to
3944+ partition by the column or set of columns which most commonly appear in
3945+ <literal>WHERE</literal> clauses of queries being executed on the
3946+ partitioned table. <literal>WHERE</literal> clause items that match and
3947+ are compatible with the partition key can be used to prune unneeded
3948+ partitions. Removal of unwanted data is also a factor to consider when
3949+ planning your partitioning strategy. An entire partition can be detached
3950+ fairly quickly, so it may be beneficial to design the partition strategy
3951+ in such a way that all data to be removed at once is located in a single
3952+ partition.
3953+ </para>
3954+
3955+ <para>
3956+ Choosing the target number of partitions that the table should be divided
3957+ into is also a critical decision to make. Not having enough partitions
3958+ may mean that indexes remain too large and that data locality remains poor
3959+ which could result in low cache hit ratios. However, dividing the table
3960+ into too many partitions can also cause issues. Too many partitions can
3961+ mean longer query planning times and higher memory consumption during both
3962+ query planning and execution. When choosing how to partition your table,
3963+ it's also important to consider what changes may occur in the future. For
3964+ example, if you choose to have one partition per customer and you
3965+ currently have a small number of large customers, consider the
3966+ implications if in several years you instead find yourself with a large
3967+ number of small customers. In this case, it may be better to choose to
3968+ partition by <literal>RANGE</literal> and choose a reasonable number of
3969+ partitions, each containing a fixed number of customers, rather than
3970+ trying to partition by <literal>LIST</literal> and hoping that the number
3971+ of customers does not increase beyond what it is practical to partition
3972+ the data by.
3973+ </para>
3974+
3975+ <para>
3976+ Sub-partitioning can be useful to further divide partitions that are
3977+ expected to become larger than other partitions, although excessive
3978+ sub-partitioning can easily lead to large numbers of partitions and can
3979+ cause the same problems mentioned in the preceding paragraph.
3980+ </para>
3981+
3982+ <para>
3983+ It is also important to consider the overhead of partitioning during
3984+ query planning and execution. The query planner is generally able to
3985+ handle partition hierarchies up a few hundred partitions. Planning times
3986+ become longer and memory consumption becomes higher as more partitions are
3987+ added. This is particularly true for the <command>UPDATE</command> and
3988+ <command>DELETE</command> commands. Another reason to be concerned about
3989+ having a large number of partitions is that the server's memory
3990+ consumption may grow significantly over a period of time, especially if
3991+ many sessions touch large numbers of partitions. That's because each
3992+ partition requires its metadata to be loaded into the local memory of
3993+ each session that touches it.
3994+ </para>
3995+
3996+ <para>
3997+ With data warehouse type workloads, it can make sense to use a larger
3998+ number of partitions than with an <acronym>OLTP</acronym> type workload.
3999+ Generally, in data warehouses, query planning time is less of a concern as
4000+ the majority of processing time is spent during query execution. With
4001+ either of these two types of workload, it is important to make the right
4002+ decisions early, as re-partitioning large quantities of data can be
4003+ painfully slow. Simulations of the intended workload are often beneficial
4004+ for optimizing the partitioning strategy. Never assume that more
4005+ partitions are better than fewer partitions and vice-versa.
4006+ </para>
4007+ </sect2>
4008+
39304009 </sect1>
39314010
39324011 <sect1 id="ddl-foreign-data">