codekingpro/portable-devtools
114k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>5.11. Table Partitioning</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ddl-inherit.html" title="5.10. Inheritance" /><link rel="next" href="ddl-foreign-data.html" title="5.12. Foreign Data" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">5.11. Table Partitioning</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-inherit.html" title="5.10. Inheritance">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><th width="60%" align="center">Chapter 5. Data Definition</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-foreign-data.html" title="5.12. Foreign Data">Next</a></td></tr></table><hr /></div><div class="sect1" id="DDL-PARTITIONING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.11. Table Partitioning <a href="#DDL-PARTITIONING" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW">5.11.1. Overview</a></span></dt><dt><span class="sect2"><a href="ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE">5.11.2. Declarative Partitioning</a></span></dt><dt><span class="sect2"><a href="ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE">5.11.3. Partitioning Using Inheritance</a></span></dt><dt><span class="sect2"><a href="ddl-partitioning.html#DDL-PARTITION-PRUNING">5.11.4. Partition Pruning</a></span></dt><dt><span class="sect2"><a href="ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION">5.11.5. Partitioning and Constraint Exclusion</a></span></dt><dt><span class="sect2"><a href="ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES">5.11.6. Best Practices for Declarative Partitioning</a></span></dt></dl></div><a id="id-1.5.4.13.2" class="indexterm"></a><a id="id-1.5.4.13.3" class="indexterm"></a><a id="id-1.5.4.13.4" class="indexterm"></a><p>3 <span class="productname">PostgreSQL</span> supports basic table4 partitioning. This section describes why and how to implement5 partitioning as part of your database design.6 </p><div class="sect2" id="DDL-PARTITIONING-OVERVIEW"><div class="titlepage"><div><div><h3 class="title">5.11.1. Overview <a href="#DDL-PARTITIONING-OVERVIEW" class="id_link">#</a></h3></div></div></div><p>7 Partitioning refers to splitting what is logically one large table into8 smaller physical pieces. Partitioning can provide several benefits:9 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>10 Query performance can be improved dramatically in certain situations,11 particularly when most of the heavily accessed rows of the table are in a12 single partition or a small number of partitions. Partitioning13 effectively substitutes for the upper tree levels of indexes,14 making it more likely that the heavily-used parts of the indexes15 fit in memory.16 </p></li><li class="listitem"><p>17 When queries or updates access a large percentage of a single18 partition, performance can be improved by using a19 sequential scan of that partition instead of using an20 index, which would require random-access reads scattered across the21 whole table.22 </p></li><li class="listitem"><p>23 Bulk loads and deletes can be accomplished by adding or removing24 partitions, if the usage pattern is accounted for in the25 partitioning design. Dropping an individual partition26 using <code class="command">DROP TABLE</code>, or doing <code class="command">ALTER TABLE27 DETACH PARTITION</code>, is far faster than a bulk28 operation. These commands also entirely avoid the29 <code class="command">VACUUM</code> overhead caused by a bulk <code class="command">DELETE</code>.30 </p></li><li class="listitem"><p>31 Seldom-used data can be migrated to cheaper and slower storage media.32 </p></li></ul></div><p>33 34 These benefits will normally be worthwhile only when a table would35 otherwise be very large. The exact point at which a table will36 benefit from partitioning depends on the application, although a37 rule of thumb is that the size of the table should exceed the physical38 memory of the database server.39 </p><p>40 <span class="productname">PostgreSQL</span> offers built-in support for the41 following forms of partitioning:42 43 </p><div class="variablelist"><dl class="variablelist"><dt id="DDL-PARTITIONING-OVERVIEW-RANGE"><span class="term">Range Partitioning</span> <a href="#DDL-PARTITIONING-OVERVIEW-RANGE" class="id_link">#</a></dt><dd><p>44 The table is partitioned into <span class="quote">“<span class="quote">ranges</span>”</span> defined45 by a key column or set of columns, with no overlap between46 the ranges of values assigned to different partitions. For47 example, one might partition by date ranges, or by ranges of48 identifiers for particular business objects.49 Each range's bounds are understood as being inclusive at the50 lower end and exclusive at the upper end. For example, if one51 partition's range is from <code class="literal">1</code>52 to <code class="literal">10</code>, and the next one's range is53 from <code class="literal">10</code> to <code class="literal">20</code>, then54 value <code class="literal">10</code> belongs to the second partition not55 the first.56 </p></dd><dt id="DDL-PARTITIONING-OVERVIEW-LIST"><span class="term">List Partitioning</span> <a href="#DDL-PARTITIONING-OVERVIEW-LIST" class="id_link">#</a></dt><dd><p>57 The table is partitioned by explicitly listing which key value(s)58 appear in each partition.59 </p></dd><dt id="DDL-PARTITIONING-OVERVIEW-HASH"><span class="term">Hash Partitioning</span> <a href="#DDL-PARTITIONING-OVERVIEW-HASH" class="id_link">#</a></dt><dd><p>60 The table is partitioned by specifying a modulus and a remainder for61 each partition. Each partition will hold the rows for which the hash62 value of the partition key divided by the specified modulus will63 produce the specified remainder.64 </p></dd></dl></div><p>65 66 If your application needs to use other forms of partitioning not listed67 above, alternative methods such as inheritance and68 <code class="literal">UNION ALL</code> views can be used instead. Such methods69 offer flexibility but do not have some of the performance benefits70 of built-in declarative partitioning.71 </p></div><div class="sect2" id="DDL-PARTITIONING-DECLARATIVE"><div class="titlepage"><div><div><h3 class="title">5.11.2. Declarative Partitioning <a href="#DDL-PARTITIONING-DECLARATIVE" class="id_link">#</a></h3></div></div></div><p>72 <span class="productname">PostgreSQL</span> allows you to declare73 that a table is divided into partitions. The table that is divided74 is referred to as a <em class="firstterm">partitioned table</em>. The75 declaration includes the <em class="firstterm">partitioning method</em>76 as described above, plus a list of columns or expressions to be used77 as the <em class="firstterm">partition key</em>.78 </p><p>79 The partitioned table itself is a <span class="quote">“<span class="quote">virtual</span>”</span> table having80 no storage of its own. Instead, the storage belongs81 to <em class="firstterm">partitions</em>, which are otherwise-ordinary82 tables associated with the partitioned table.83 Each partition stores a subset of the data as defined by its84 <em class="firstterm">partition bounds</em>.85 All rows inserted into a partitioned table will be routed to the86 appropriate one of the partitions based on the values of the partition87 key column(s).88 Updating the partition key of a row will cause it to be moved into a89 different partition if it no longer satisfies the partition bounds90 of its original partition.91 </p><p>92 Partitions may themselves be defined as partitioned tables, resulting93 in <em class="firstterm">sub-partitioning</em>. Although all partitions94 must have the same columns as their partitioned parent, partitions may95 have their96 own indexes, constraints and default values, distinct from those of other97 partitions. See <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a> for more details on98 creating partitioned tables and partitions.99 </p><p>100 It is not possible to turn a regular table into a partitioned table or101 vice versa. However, it is possible to add an existing regular or102 partitioned table as a partition of a partitioned table, or remove a103 partition from a partitioned table turning it into a standalone table;104 this can simplify and speed up many maintenance processes.105 See <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a> to learn more about the106 <code class="command">ATTACH PARTITION</code> and <code class="command">DETACH PARTITION</code>107 sub-commands.108 </p><p>109 Partitions can also be <a class="link" href="ddl-foreign-data.html" title="5.12. Foreign Data">foreign110 tables</a>, although considerable care is needed because it is then111 the user's responsibility that the contents of the foreign table112 satisfy the partitioning rule. There are some other restrictions as113 well. See <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a> for more114 information.115 </p><div class="sect3" id="DDL-PARTITIONING-DECLARATIVE-EXAMPLE"><div class="titlepage"><div><div><h4 class="title">5.11.2.1. Example <a href="#DDL-PARTITIONING-DECLARATIVE-EXAMPLE" class="id_link">#</a></h4></div></div></div><p>116 Suppose we are constructing a database for a large ice cream company.117 The company measures peak temperatures every day as well as ice cream118 sales in each region. Conceptually, we want a table like:119 120</p><pre class="programlisting">121CREATE TABLE measurement (122 city_id int not null,123 logdate date not null,124 peaktemp int,125 unitsales int126);127</pre><p>128 129 We know that most queries will access just the last week's, month's or130 quarter's data, since the main use of this table will be to prepare131 online reports for management. To reduce the amount of old data that132 needs to be stored, we decide to keep only the most recent 3 years133 worth of data. At the beginning of each month we will remove the oldest134 month's data. In this situation we can use partitioning to help us meet135 all of our different requirements for the measurements table.136 </p><p>137 To use declarative partitioning in this case, use the following steps:138 139 </p><div class="orderedlist"><ol class="orderedlist compact" type="1"><li class="listitem"><p>140 Create the <code class="structname">measurement</code> table as a partitioned141 table by specifying the <code class="literal">PARTITION BY</code> clause, which142 includes the partitioning method (<code class="literal">RANGE</code> in this143 case) and the list of column(s) to use as the partition key.144 145</p><pre class="programlisting">146CREATE TABLE measurement (147 city_id int not null,148 logdate date not null,149 peaktemp int,150 unitsales int151) PARTITION BY RANGE (logdate);152</pre><p>153 </p></li><li class="listitem"><p>154 Create partitions. Each partition's definition must specify bounds155 that correspond to the partitioning method and partition key of the156 parent. Note that specifying bounds such that the new partition's157 values would overlap with those in one or more existing partitions will158 cause an error.159 </p><p>160 Partitions thus created are in every way normal161 <span class="productname">PostgreSQL</span>162 tables (or, possibly, foreign tables). It is possible to specify a163 tablespace and storage parameters for each partition separately.164 </p><p>165 For our example, each partition should hold one month's worth of166 data, to match the requirement of deleting one month's data at a167 time. So the commands might look like:168 169</p><pre class="programlisting">170CREATE TABLE measurement_y2006m02 PARTITION OF measurement171 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');172 173CREATE TABLE measurement_y2006m03 PARTITION OF measurement174 FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');175 176...177CREATE TABLE measurement_y2007m11 PARTITION OF measurement178 FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');179 180CREATE TABLE measurement_y2007m12 PARTITION OF measurement181 FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')182 TABLESPACE fasttablespace;183 184CREATE TABLE measurement_y2008m01 PARTITION OF measurement185 FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')186 WITH (parallel_workers = 4)187 TABLESPACE fasttablespace;188</pre><p>189 190 (Recall that adjacent partitions can share a bound value, since191 range upper bounds are treated as exclusive bounds.)192 </p><p>193 If you wish to implement sub-partitioning, again specify the194 <code class="literal">PARTITION BY</code> clause in the commands used to create195 individual partitions, for example:196 197</p><pre class="programlisting">198CREATE TABLE measurement_y2006m02 PARTITION OF measurement199 FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')200 PARTITION BY RANGE (peaktemp);201</pre><p>202 203 After creating partitions of <code class="structname">measurement_y2006m02</code>,204 any data inserted into <code class="structname">measurement</code> that is mapped to205 <code class="structname">measurement_y2006m02</code> (or data that is206 directly inserted into <code class="structname">measurement_y2006m02</code>,207 which is allowed provided its partition constraint is satisfied)208 will be further redirected to one of its209 partitions based on the <code class="structfield">peaktemp</code> column. The partition210 key specified may overlap with the parent's partition key, although211 care should be taken when specifying the bounds of a sub-partition212 such that the set of data it accepts constitutes a subset of what213 the partition's own bounds allow; the system does not try to check214 whether that's really the case.215 </p><p>216 Inserting data into the parent table that does not map217 to one of the existing partitions will cause an error; an appropriate218 partition must be added manually.219 </p><p>220 It is not necessary to manually create table constraints describing221 the partition boundary conditions for partitions. Such constraints222 will be created automatically.223 </p></li><li class="listitem"><p>224 Create an index on the key column(s), as well as any other indexes you225 might want, on the partitioned table. (The key index is not strictly226 necessary, but in most scenarios it is helpful.)227 This automatically creates a matching index on each partition, and228 any partitions you create or attach later will also have such an229 index.230 An index or unique constraint declared on a partitioned table231 is <span class="quote">“<span class="quote">virtual</span>”</span> in the same way that the partitioned table232 is: the actual data is in child indexes on the individual partition233 tables.234 235</p><pre class="programlisting">236CREATE INDEX ON measurement (logdate);237</pre><p>238 </p></li><li class="listitem"><p>239 Ensure that the <a class="xref" href="runtime-config-query.html#GUC-ENABLE-PARTITION-PRUNING">enable_partition_pruning</a>240 configuration parameter is not disabled in <code class="filename">postgresql.conf</code>.241 If it is, queries will not be optimized as desired.242 </p></li></ol></div><p>243 </p><p>244 In the above example we would be creating a new partition each month, so245 it might be wise to write a script that generates the required DDL246 automatically.247 </p></div><div class="sect3" id="DDL-PARTITIONING-DECLARATIVE-MAINTENANCE"><div class="titlepage"><div><div><h4 class="title">5.11.2.2. Partition Maintenance <a href="#DDL-PARTITIONING-DECLARATIVE-MAINTENANCE" class="id_link">#</a></h4></div></div></div><p>248 Normally the set of partitions established when initially defining the249 table is not intended to remain static. It is common to want to250 remove partitions holding old data and periodically add new partitions for251 new data. One of the most important advantages of partitioning is252 precisely that it allows this otherwise painful task to be executed253 nearly instantaneously by manipulating the partition structure, rather254 than physically moving large amounts of data around.255 </p><p>256 The simplest option for removing old data is to drop the partition that257 is no longer necessary:258</p><pre class="programlisting">259DROP TABLE measurement_y2006m02;260</pre><p>261 This can very quickly delete millions of records because it doesn't have262 to individually delete every record. Note however that the above command263 requires taking an <code class="literal">ACCESS EXCLUSIVE</code> lock on the parent264 table.265 </p><p>266 Another option that is often preferable is to remove the partition from267 the partitioned table but retain access to it as a table in its own268 right. This has two forms:269 270</p><pre class="programlisting">271ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;272ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;273</pre><p>274 275 These allow further operations to be performed on the data before276 it is dropped. For example, this is often a useful time to back up277 the data using <code class="command">COPY</code>, <span class="application">pg_dump</span>, or278 similar tools. It might also be a useful time to aggregate data279 into smaller formats, perform other data manipulations, or run280 reports. The first form of the command requires an281 <code class="literal">ACCESS EXCLUSIVE</code> lock on the parent table.282 Adding the <code class="literal">CONCURRENTLY</code> qualifier as in the second283 form allows the detach operation to require only284 <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock on the parent table, but see285 <a class="link" href="sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION"><code class="literal">ALTER TABLE ... DETACH PARTITION</code></a>286 for details on the restrictions.287 </p><p>288 Similarly we can add a new partition to handle new data. We can create an289 empty partition in the partitioned table just as the original partitions290 were created above:291 292</p><pre class="programlisting">293CREATE TABLE measurement_y2008m02 PARTITION OF measurement294 FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')295 TABLESPACE fasttablespace;296</pre><p>297 298 As an alternative, it is sometimes more convenient to create the299 new table outside the partition structure, and attach it as a300 partition later. This allows new data to be loaded, checked, and301 transformed prior to it appearing in the partitioned table.302 Moreover, the <code class="literal">ATTACH PARTITION</code> operation requires303 only <code class="literal">SHARE UPDATE EXCLUSIVE</code> lock on the304 partitioned table, as opposed to the <code class="literal">ACCESS305 EXCLUSIVE</code> lock that is required by <code class="command">CREATE TABLE306 ... PARTITION OF</code>, so it is more friendly to concurrent307 operations on the partitioned table.308 The <code class="literal">CREATE TABLE ... LIKE</code> option is helpful309 to avoid tediously repeating the parent table's definition:310 311</p><pre class="programlisting">312CREATE TABLE measurement_y2008m02313 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)314 TABLESPACE fasttablespace;315 316ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02317 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );318 319\copy measurement_y2008m02 from 'measurement_y2008m02'320-- possibly some other data preparation work321 322ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02323 FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );324</pre><p>325 </p><p>326 Before running the <code class="command">ATTACH PARTITION</code> command, it is327 recommended to create a <code class="literal">CHECK</code> constraint on the table to328 be attached that matches the expected partition constraint, as329 illustrated above. That way, the system will be able to skip the scan330 which is otherwise needed to validate the implicit331 partition constraint. Without the <code class="literal">CHECK</code> constraint,332 the table will be scanned to validate the partition constraint while333 holding an <code class="literal">ACCESS EXCLUSIVE</code> lock on that partition.334 It is recommended to drop the now-redundant <code class="literal">CHECK</code>335 constraint after the <code class="command">ATTACH PARTITION</code> is complete. If336 the table being attached is itself a partitioned table, then each of its337 sub-partitions will be recursively locked and scanned until either a338 suitable <code class="literal">CHECK</code> constraint is encountered or the leaf339 partitions are reached.340 </p><p>341 Similarly, if the partitioned table has a <code class="literal">DEFAULT</code>342 partition, it is recommended to create a <code class="literal">CHECK</code>343 constraint which excludes the to-be-attached partition's constraint. If344 this is not done then the <code class="literal">DEFAULT</code> partition will be345 scanned to verify that it contains no records which should be located in346 the partition being attached. This operation will be performed whilst347 holding an <code class="literal">ACCESS EXCLUSIVE</code> lock on the <code class="literal">348 DEFAULT</code> partition. If the <code class="literal">DEFAULT</code> partition349 is itself a partitioned table, then each of its partitions will be350 recursively checked in the same way as the table being attached, as351 mentioned above.352 </p><p>353 As explained above, it is possible to create indexes on partitioned tables354 so that they are applied automatically to the entire hierarchy.355 This is very356 convenient, as not only will the existing partitions become indexed, but357 also any partitions that are created in the future will. One limitation is358 that it's not possible to use the <code class="literal">CONCURRENTLY</code>359 qualifier when creating such a partitioned index. To avoid long lock360 times, it is possible to use <code class="command">CREATE INDEX ON ONLY</code>361 the partitioned table; such an index is marked invalid, and the partitions362 do not get the index applied automatically. The indexes on partitions can363 be created individually using <code class="literal">CONCURRENTLY</code>, and then364 <em class="firstterm">attached</em> to the index on the parent using365 <code class="command">ALTER INDEX .. ATTACH PARTITION</code>. Once indexes for all366 partitions are attached to the parent index, the parent index is marked367 valid automatically. Example:368</p><pre class="programlisting">369CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);370 371CREATE INDEX CONCURRENTLY measurement_usls_200602_idx372 ON measurement_y2006m02 (unitsales);373ALTER INDEX measurement_usls_idx374 ATTACH PARTITION measurement_usls_200602_idx;375...376</pre><p>377 378 This technique can be used with <code class="literal">UNIQUE</code> and379 <code class="literal">PRIMARY KEY</code> constraints too; the indexes are created380 implicitly when the constraint is created. Example:381</p><pre class="programlisting">382ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);383 384ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);385ALTER INDEX measurement_city_id_logdate_key386 ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;387...388</pre><p>389 </p></div><div class="sect3" id="DDL-PARTITIONING-DECLARATIVE-LIMITATIONS"><div class="titlepage"><div><div><h4 class="title">5.11.2.3. Limitations <a href="#DDL-PARTITIONING-DECLARATIVE-LIMITATIONS" class="id_link">#</a></h4></div></div></div><p>390 The following limitations apply to partitioned tables:391 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>392 To create a unique or primary key constraint on a partitioned table,393 the partition keys must not include any expressions or function calls394 and the constraint's columns must include all of the partition key395 columns. This limitation exists because the individual indexes making396 up the constraint can only directly enforce uniqueness within their own397 partitions; therefore, the partition structure itself must guarantee398 that there are not duplicates in different partitions.399 </p></li><li class="listitem"><p>400 There is no way to create an exclusion constraint spanning the401 whole partitioned table. It is only possible to put such a402 constraint on each leaf partition individually. Again, this403 limitation stems from not being able to enforce cross-partition404 restrictions.405 </p></li><li class="listitem"><p>406 <code class="literal">BEFORE ROW</code> triggers on <code class="literal">INSERT</code>407 cannot change which partition is the final destination for a new row.408 </p></li><li class="listitem"><p>409 Mixing temporary and permanent relations in the same partition tree is410 not allowed. Hence, if the partitioned table is permanent, so must be411 its partitions and likewise if the partitioned table is temporary. When412 using temporary relations, all members of the partition tree have to be413 from the same session.414 </p></li></ul></div><p>415 </p><p>416 Individual partitions are linked to their partitioned table using417 inheritance behind-the-scenes. However, it is not possible to use418 all of the generic features of inheritance with declaratively419 partitioned tables or their partitions, as discussed below. Notably,420 a partition cannot have any parents other than the partitioned table421 it is a partition of, nor can a table inherit from both a partitioned422 table and a regular table. That means partitioned tables and their423 partitions never share an inheritance hierarchy with regular tables.424 </p><p>425 Since a partition hierarchy consisting of the partitioned table and its426 partitions is still an inheritance hierarchy,427 <code class="structfield">tableoid</code> and all the normal rules of428 inheritance apply as described in <a class="xref" href="ddl-inherit.html" title="5.10. Inheritance">Section 5.10</a>, with429 a few exceptions:430 431 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>432 Partitions cannot have columns that are not present in the parent. It433 is not possible to specify columns when creating partitions with434 <code class="command">CREATE TABLE</code>, nor is it possible to add columns to435 partitions after-the-fact using <code class="command">ALTER TABLE</code>.436 Tables may be added as a partition with <code class="command">ALTER TABLE437 ... ATTACH PARTITION</code> only if their columns exactly match438 the parent.439 </p></li><li class="listitem"><p>440 Both <code class="literal">CHECK</code> and <code class="literal">NOT NULL</code>441 constraints of a partitioned table are always inherited by all its442 partitions. <code class="literal">CHECK</code> constraints that are marked443 <code class="literal">NO INHERIT</code> are not allowed to be created on444 partitioned tables.445 You cannot drop a <code class="literal">NOT NULL</code> constraint on a446 partition's column if the same constraint is present in the parent447 table.448 </p></li><li class="listitem"><p>449 Using <code class="literal">ONLY</code> to add or drop a constraint on only450 the partitioned table is supported as long as there are no451 partitions. Once partitions exist, using <code class="literal">ONLY</code>452 will result in an error for any constraints other than453 <code class="literal">UNIQUE</code> and <code class="literal">PRIMARY KEY</code>.454 Instead, constraints on the partitions455 themselves can be added and (if they are not present in the parent456 table) dropped.457 </p></li><li class="listitem"><p>458 As a partitioned table does not have any data itself, attempts to use459 <code class="command">TRUNCATE</code> <code class="literal">ONLY</code> on a partitioned460 table will always return an error.461 </p></li></ul></div><p>462 </p></div></div><div class="sect2" id="DDL-PARTITIONING-USING-INHERITANCE"><div class="titlepage"><div><div><h3 class="title">5.11.3. Partitioning Using Inheritance <a href="#DDL-PARTITIONING-USING-INHERITANCE" class="id_link">#</a></h3></div></div></div><p>463 While the built-in declarative partitioning is suitable for most464 common use cases, there are some circumstances where a more flexible465 approach may be useful. Partitioning can be implemented using table466 inheritance, which allows for several features not supported467 by declarative partitioning, such as:468 469 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>470 For declarative partitioning, partitions must have exactly the same set471 of columns as the partitioned table, whereas with table inheritance,472 child tables may have extra columns not present in the parent.473 </p></li><li class="listitem"><p>474 Table inheritance allows for multiple inheritance.475 </p></li><li class="listitem"><p>476 Declarative partitioning only supports range, list and hash477 partitioning, whereas table inheritance allows data to be divided in a478 manner of the user's choosing. (Note, however, that if constraint479 exclusion is unable to prune child tables effectively, query performance480 might be poor.)481 </p></li></ul></div><p>482 </p><div class="sect3" id="DDL-PARTITIONING-INHERITANCE-EXAMPLE"><div class="titlepage"><div><div><h4 class="title">5.11.3.1. Example <a href="#DDL-PARTITIONING-INHERITANCE-EXAMPLE" class="id_link">#</a></h4></div></div></div><p>483 This example builds a partitioning structure equivalent to the484 declarative partitioning example above. Use485 the following steps:486 487 </p><div class="orderedlist"><ol class="orderedlist compact" type="1"><li class="listitem"><p>488 Create the <span class="quote">“<span class="quote">root</span>”</span> table, from which all of the489 <span class="quote">“<span class="quote">child</span>”</span> tables will inherit. This table will contain no data. Do not490 define any check constraints on this table, unless you intend them491 to be applied equally to all child tables. There is no point in492 defining any indexes or unique constraints on it, either. For our493 example, the root table is the <code class="structname">measurement</code>494 table as originally defined:495 496</p><pre class="programlisting">497CREATE TABLE measurement (498 city_id int not null,499 logdate date not null,500 peaktemp int,501 unitsales int502);503</pre><p>504 </p></li><li class="listitem"><p>505 Create several <span class="quote">“<span class="quote">child</span>”</span> tables that each inherit from506 the root table. Normally, these tables will not add any columns507 to the set inherited from the root. Just as with declarative508 partitioning, these tables are in every way normal509 <span class="productname">PostgreSQL</span> tables (or foreign tables).510 </p><p>511</p><pre class="programlisting">512CREATE TABLE measurement_y2006m02 () INHERITS (measurement);513CREATE TABLE measurement_y2006m03 () INHERITS (measurement);514...515CREATE TABLE measurement_y2007m11 () INHERITS (measurement);516CREATE TABLE measurement_y2007m12 () INHERITS (measurement);517CREATE TABLE measurement_y2008m01 () INHERITS (measurement);518</pre><p>519 </p></li><li class="listitem"><p>520 Add non-overlapping table constraints to the child tables to521 define the allowed key values in each.522 </p><p>523 Typical examples would be:524</p><pre class="programlisting">525CHECK ( x = 1 )526CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))527CHECK ( outletID >= 100 AND outletID < 200 )528</pre><p>529 Ensure that the constraints guarantee that there is no overlap530 between the key values permitted in different child tables. A common531 mistake is to set up range constraints like:532</p><pre class="programlisting">533CHECK ( outletID BETWEEN 100 AND 200 )534CHECK ( outletID BETWEEN 200 AND 300 )535</pre><p>536 This is wrong since it is not clear which child table the key537 value 200 belongs in.538 Instead, ranges should be defined in this style:539 540</p><pre class="programlisting">541CREATE TABLE measurement_y2006m02 (542 CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )543) INHERITS (measurement);544 545CREATE TABLE measurement_y2006m03 (546 CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )547) INHERITS (measurement);548 549...550CREATE TABLE measurement_y2007m11 (551 CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )552) INHERITS (measurement);553 554CREATE TABLE measurement_y2007m12 (555 CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )556) INHERITS (measurement);557 558CREATE TABLE measurement_y2008m01 (559 CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )560) INHERITS (measurement);561</pre><p>562 </p></li><li class="listitem"><p>563 For each child table, create an index on the key column(s),564 as well as any other indexes you might want.565</p><pre class="programlisting">566CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);567CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);568CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);569CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);570CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);571</pre><p>572 </p></li><li class="listitem"><p>573 We want our application to be able to say <code class="literal">INSERT INTO574 measurement ...</code> and have the data be redirected into the575 appropriate child table. We can arrange that by attaching576 a suitable trigger function to the root table.577 If data will be added only to the latest child, we can578 use a very simple trigger function:579 580</p><pre class="programlisting">581CREATE OR REPLACE FUNCTION measurement_insert_trigger()582RETURNS TRIGGER AS $$583BEGIN584 INSERT INTO measurement_y2008m01 VALUES (NEW.*);585 RETURN NULL;586END;587$$588LANGUAGE plpgsql;589</pre><p>590 </p><p>591 After creating the function, we create a trigger which592 calls the trigger function:593 594</p><pre class="programlisting">595CREATE TRIGGER insert_measurement_trigger596 BEFORE INSERT ON measurement597 FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();598</pre><p>599 600 We must redefine the trigger function each month so that it always601 inserts into the current child table. The trigger definition does602 not need to be updated, however.603 </p><p>604 We might want to insert data and have the server automatically605 locate the child table into which the row should be added. We606 could do this with a more complex trigger function, for example:607 608</p><pre class="programlisting">609CREATE OR REPLACE FUNCTION measurement_insert_trigger()610RETURNS TRIGGER AS $$611BEGIN612 IF ( NEW.logdate >= DATE '2006-02-01' AND613 NEW.logdate < DATE '2006-03-01' ) THEN614 INSERT INTO measurement_y2006m02 VALUES (NEW.*);615 ELSIF ( NEW.logdate >= DATE '2006-03-01' AND616 NEW.logdate < DATE '2006-04-01' ) THEN617 INSERT INTO measurement_y2006m03 VALUES (NEW.*);618 ...619 ELSIF ( NEW.logdate >= DATE '2008-01-01' AND620 NEW.logdate < DATE '2008-02-01' ) THEN621 INSERT INTO measurement_y2008m01 VALUES (NEW.*);622 ELSE623 RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';624 END IF;625 RETURN NULL;626END;627$$628LANGUAGE plpgsql;629</pre><p>630 631 The trigger definition is the same as before.632 Note that each <code class="literal">IF</code> test must exactly match the633 <code class="literal">CHECK</code> constraint for its child table.634 </p><p>635 While this function is more complex than the single-month case,636 it doesn't need to be updated as often, since branches can be637 added in advance of being needed.638 </p><div class="note"><h3 class="title">Note</h3><p>639 In practice, it might be best to check the newest child first,640 if most inserts go into that child. For simplicity, we have641 shown the trigger's tests in the same order as in other parts642 of this example.643 </p></div><p>644 A different approach to redirecting inserts into the appropriate645 child table is to set up rules, instead of a trigger, on the646 root table. For example:647 648</p><pre class="programlisting">649CREATE RULE measurement_insert_y2006m02 AS650ON INSERT TO measurement WHERE651 ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )652DO INSTEAD653 INSERT INTO measurement_y2006m02 VALUES (NEW.*);654...655CREATE RULE measurement_insert_y2008m01 AS656ON INSERT TO measurement WHERE657 ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )658DO INSTEAD659 INSERT INTO measurement_y2008m01 VALUES (NEW.*);660</pre><p>661 662 A rule has significantly more overhead than a trigger, but the663 overhead is paid once per query rather than once per row, so this664 method might be advantageous for bulk-insert situations. In most665 cases, however, the trigger method will offer better performance.666 </p><p>667 Be aware that <code class="command">COPY</code> ignores rules. If you want to668 use <code class="command">COPY</code> to insert data, you'll need to copy into the669 correct child table rather than directly into the root. <code class="command">COPY</code>670 does fire triggers, so you can use it normally if you use the trigger671 approach.672 </p><p>673 Another disadvantage of the rule approach is that there is no simple674 way to force an error if the set of rules doesn't cover the insertion675 date; the data will silently go into the root table instead.676 </p></li><li class="listitem"><p>677 Ensure that the <a class="xref" href="runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION">constraint_exclusion</a>678 configuration parameter is not disabled in679 <code class="filename">postgresql.conf</code>; otherwise680 child tables may be accessed unnecessarily.681 </p></li></ol></div><p>682 </p><p>683 As we can see, a complex table hierarchy could require a684 substantial amount of DDL. In the above example we would be creating685 a new child table each month, so it might be wise to write a script that686 generates the required DDL automatically.687 </p></div><div class="sect3" id="DDL-PARTITIONING-INHERITANCE-MAINTENANCE"><div class="titlepage"><div><div><h4 class="title">5.11.3.2. Maintenance for Inheritance Partitioning <a href="#DDL-PARTITIONING-INHERITANCE-MAINTENANCE" class="id_link">#</a></h4></div></div></div><p>688 To remove old data quickly, simply drop the child table that is no longer689 necessary:690</p><pre class="programlisting">691DROP TABLE measurement_y2006m02;692</pre><p>693 </p><p>694 To remove the child table from the inheritance hierarchy table but retain access to695 it as a table in its own right:696 697</p><pre class="programlisting">698ALTER TABLE measurement_y2006m02 NO INHERIT measurement;699</pre><p>700 </p><p>701 To add a new child table to handle new data, create an empty child table702 just as the original children were created above:703 704</p><pre class="programlisting">705CREATE TABLE measurement_y2008m02 (706 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )707) INHERITS (measurement);708</pre><p>709 710 Alternatively, one may want to create and populate the new child table711 before adding it to the table hierarchy. This could allow data to be712 loaded, checked, and transformed before being made visible to queries on713 the parent table.714 715</p><pre class="programlisting">716CREATE TABLE measurement_y2008m02717 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);718ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02719 CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );720\copy measurement_y2008m02 from 'measurement_y2008m02'721-- possibly some other data preparation work722ALTER TABLE measurement_y2008m02 INHERIT measurement;723</pre><p>724 </p></div><div class="sect3" id="DDL-PARTITIONING-INHERITANCE-CAVEATS"><div class="titlepage"><div><div><h4 class="title">5.11.3.3. Caveats <a href="#DDL-PARTITIONING-INHERITANCE-CAVEATS" class="id_link">#</a></h4></div></div></div><p>725 The following caveats apply to partitioning implemented using726 inheritance:727 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>728 There is no automatic way to verify that all of the729 <code class="literal">CHECK</code> constraints are mutually730 exclusive. It is safer to create code that generates731 child tables and creates and/or modifies associated objects than732 to write each by hand.733 </p></li><li class="listitem"><p>734 Indexes and foreign key constraints apply to single tables and not735 to their inheritance children, hence they have some736 <a class="link" href="ddl-inherit.html#DDL-INHERIT-CAVEATS" title="5.10.1. Caveats">caveats</a> to be aware of.737 </p></li><li class="listitem"><p>738 The schemes shown here assume that the values of a row's key column(s)739 never change, or at least do not change enough to require it to move to another partition.740 An <code class="command">UPDATE</code> that attempts741 to do that will fail because of the <code class="literal">CHECK</code> constraints.742 If you need to handle such cases, you can put suitable update triggers743 on the child tables, but it makes management of the structure744 much more complicated.745 </p></li><li class="listitem"><p>746 If you are using manual <code class="command">VACUUM</code> or747 <code class="command">ANALYZE</code> commands, don't forget that748 you need to run them on each child table individually. A command like:749</p><pre class="programlisting">750ANALYZE measurement;751</pre><p>752 will only process the root table.753 </p></li><li class="listitem"><p>754 <code class="command">INSERT</code> statements with <code class="literal">ON CONFLICT</code>755 clauses are unlikely to work as expected, as the <code class="literal">ON CONFLICT</code>756 action is only taken in case of unique violations on the specified757 target relation, not its child relations.758 </p></li><li class="listitem"><p>759 Triggers or rules will be needed to route rows to the desired760 child table, unless the application is explicitly aware of the761 partitioning scheme. Triggers may be complicated to write, and will762 be much slower than the tuple routing performed internally by763 declarative partitioning.764 </p></li></ul></div><p>765 </p></div></div><div class="sect2" id="DDL-PARTITION-PRUNING"><div class="titlepage"><div><div><h3 class="title">5.11.4. Partition Pruning <a href="#DDL-PARTITION-PRUNING" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.13.9.2" class="indexterm"></a><p>766 <em class="firstterm">Partition pruning</em> is a query optimization technique767 that improves performance for declaratively partitioned tables.768 As an example:769 770</p><pre class="programlisting">771SET enable_partition_pruning = on; -- the default772SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';773</pre><p>774 775 Without partition pruning, the above query would scan each of the776 partitions of the <code class="structname">measurement</code> table. With777 partition pruning enabled, the planner will examine the definition778 of each partition and prove that the partition need not779 be scanned because it could not contain any rows meeting the query's780 <code class="literal">WHERE</code> clause. When the planner can prove this, it781 excludes (<em class="firstterm">prunes</em>) the partition from the query782 plan.783 </p><p>784 By using the EXPLAIN command and the <a class="xref" href="runtime-config-query.html#GUC-ENABLE-PARTITION-PRUNING">enable_partition_pruning</a> configuration parameter, it's785 possible to show the difference between a plan for which partitions have786 been pruned and one for which they have not. A typical unoptimized787 plan for this type of table setup is:788</p><pre class="programlisting">789SET enable_partition_pruning = off;790EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';791 QUERY PLAN792-----------------------------------------------------------------------------------793 Aggregate (cost=188.76..188.77 rows=1 width=8)794 -> Append (cost=0.00..181.05 rows=3085 width=0)795 -> Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=617 width=0)796 Filter: (logdate >= '2008-01-01'::date)797 -> Seq Scan on measurement_y2006m03 (cost=0.00..33.12 rows=617 width=0)798 Filter: (logdate >= '2008-01-01'::date)799...800 -> Seq Scan on measurement_y2007m11 (cost=0.00..33.12 rows=617 width=0)801 Filter: (logdate >= '2008-01-01'::date)802 -> Seq Scan on measurement_y2007m12 (cost=0.00..33.12 rows=617 width=0)803 Filter: (logdate >= '2008-01-01'::date)804 -> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0)805 Filter: (logdate >= '2008-01-01'::date)806</pre><p>807 808 Some or all of the partitions might use index scans instead of809 full-table sequential scans, but the point here is that there810 is no need to scan the older partitions at all to answer this query.811 When we enable partition pruning, we get a significantly812 cheaper plan that will deliver the same answer:813</p><pre class="programlisting">814SET enable_partition_pruning = on;815EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';816 QUERY PLAN817-----------------------------------------------------------------------------------818 Aggregate (cost=37.75..37.76 rows=1 width=8)819 -> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0)820 Filter: (logdate >= '2008-01-01'::date)821</pre><p>822 </p><p>823 Note that partition pruning is driven only by the constraints defined824 implicitly by the partition keys, not by the presence of indexes.825 Therefore it isn't necessary to define indexes on the key columns.826 Whether an index needs to be created for a given partition depends on827 whether you expect that queries that scan the partition will828 generally scan a large part of the partition or just a small part.829 An index will be helpful in the latter case but not the former.830 </p><p>831 Partition pruning can be performed not only during the planning of a832 given query, but also during its execution. This is useful as it can833 allow more partitions to be pruned when clauses contain expressions834 whose values are not known at query planning time, for example,835 parameters defined in a <code class="command">PREPARE</code> statement, using a836 value obtained from a subquery, or using a parameterized value on the837 inner side of a nested loop join. Partition pruning during execution838 can be performed at any of the following times:839 840 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>841 During initialization of the query plan. Partition pruning can be842 performed here for parameter values which are known during the843 initialization phase of execution. Partitions which are pruned844 during this stage will not show up in the query's845 <code class="command">EXPLAIN</code> or <code class="command">EXPLAIN ANALYZE</code>.846 It is possible to determine the number of partitions which were847 removed during this phase by observing the848 <span class="quote">“<span class="quote">Subplans Removed</span>”</span> property in the849 <code class="command">EXPLAIN</code> output.850 </p></li><li class="listitem"><p>851 During actual execution of the query plan. Partition pruning may852 also be performed here to remove partitions using values which are853 only known during actual query execution. This includes values854 from subqueries and values from execution-time parameters such as855 those from parameterized nested loop joins. Since the value of856 these parameters may change many times during the execution of the857 query, partition pruning is performed whenever one of the858 execution parameters being used by partition pruning changes.859 Determining if partitions were pruned during this phase requires860 careful inspection of the <code class="literal">loops</code> property in861 the <code class="command">EXPLAIN ANALYZE</code> output. Subplans862 corresponding to different partitions may have different values863 for it depending on how many times each of them was pruned during864 execution. Some may be shown as <code class="literal">(never executed)</code>865 if they were pruned every time.866 </p></li></ul></div><p>867 </p><p>868 Partition pruning can be disabled using the869 <a class="xref" href="runtime-config-query.html#GUC-ENABLE-PARTITION-PRUNING">enable_partition_pruning</a> setting.870 </p></div><div class="sect2" id="DDL-PARTITIONING-CONSTRAINT-EXCLUSION"><div class="titlepage"><div><div><h3 class="title">5.11.5. Partitioning and Constraint Exclusion <a href="#DDL-PARTITIONING-CONSTRAINT-EXCLUSION" class="id_link">#</a></h3></div></div></div><a id="id-1.5.4.13.10.2" class="indexterm"></a><p>871 <em class="firstterm">Constraint exclusion</em> is a query optimization872 technique similar to partition pruning. While it is primarily used873 for partitioning implemented using the legacy inheritance method, it can be874 used for other purposes, including with declarative partitioning.875 </p><p>876 Constraint exclusion works in a very similar way to partition877 pruning, except that it uses each table's <code class="literal">CHECK</code>878 constraints — which gives it its name — whereas partition879 pruning uses the table's partition bounds, which exist only in the880 case of declarative partitioning. Another difference is that881 constraint exclusion is only applied at plan time; there is no attempt882 to remove partitions at execution time.883 </p><p>884 The fact that constraint exclusion uses <code class="literal">CHECK</code>885 constraints, which makes it slow compared to partition pruning, can886 sometimes be used as an advantage: because constraints can be defined887 even on declaratively-partitioned tables, in addition to their internal888 partition bounds, constraint exclusion may be able889 to elide additional partitions from the query plan.890 </p><p>891 The default (and recommended) setting of892 <a class="xref" href="runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION">constraint_exclusion</a> is neither893 <code class="literal">on</code> nor <code class="literal">off</code>, but an intermediate setting894 called <code class="literal">partition</code>, which causes the technique to be895 applied only to queries that are likely to be working on inheritance partitioned896 tables. The <code class="literal">on</code> setting causes the planner to examine897 <code class="literal">CHECK</code> constraints in all queries, even simple ones that898 are unlikely to benefit.899 </p><p>900 The following caveats apply to constraint exclusion:901 902 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>903 Constraint exclusion is only applied during query planning, unlike904 partition pruning, which can also be applied during query execution.905 </p></li><li class="listitem"><p>906 Constraint exclusion only works when the query's <code class="literal">WHERE</code>907 clause contains constants (or externally supplied parameters).908 For example, a comparison against a non-immutable function such as909 <code class="function">CURRENT_TIMESTAMP</code> cannot be optimized, since the910 planner cannot know which child table the function's value might fall911 into at run time.912 </p></li><li class="listitem"><p>913 Keep the partitioning constraints simple, else the planner may not be914 able to prove that child tables might not need to be visited. Use simple915 equality conditions for list partitioning, or simple916 range tests for range partitioning, as illustrated in the preceding917 examples. A good rule of thumb is that partitioning constraints should918 contain only comparisons of the partitioning column(s) to constants919 using B-tree-indexable operators, because only B-tree-indexable920 column(s) are allowed in the partition key.921 </p></li><li class="listitem"><p>922 All constraints on all children of the parent table are examined923 during constraint exclusion, so large numbers of children are likely924 to increase query planning time considerably. So the legacy925 inheritance based partitioning will work well with up to perhaps a926 hundred child tables; don't try to use many thousands of children.927 </p></li></ul></div><p>928 </p></div><div class="sect2" id="DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES"><div class="titlepage"><div><div><h3 class="title">5.11.6. Best Practices for Declarative Partitioning <a href="#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES" class="id_link">#</a></h3></div></div></div><p>929 The choice of how to partition a table should be made carefully, as the930 performance of query planning and execution can be negatively affected by931 poor design.932 </p><p>933 One of the most critical design decisions will be the column or columns934 by which you partition your data. Often the best choice will be to935 partition by the column or set of columns which most commonly appear in936 <code class="literal">WHERE</code> clauses of queries being executed on the937 partitioned table. <code class="literal">WHERE</code> clauses that are compatible938 with the partition bound constraints can be used to prune unneeded939 partitions. However, you may be forced into making other decisions by940 requirements for the <code class="literal">PRIMARY KEY</code> or a941 <code class="literal">UNIQUE</code> constraint. Removal of unwanted data is also a942 factor to consider when planning your partitioning strategy. An entire943 partition can be detached fairly quickly, so it may be beneficial to944 design the partition strategy in such a way that all data to be removed945 at once is located in a single partition.946 </p><p>947 Choosing the target number of partitions that the table should be divided948 into is also a critical decision to make. Not having enough partitions949 may mean that indexes remain too large and that data locality remains poor950 which could result in low cache hit ratios. However, dividing the table951 into too many partitions can also cause issues. Too many partitions can952 mean longer query planning times and higher memory consumption during both953 query planning and execution, as further described below.954 When choosing how to partition your table,955 it's also important to consider what changes may occur in the future. For956 example, if you choose to have one partition per customer and you957 currently have a small number of large customers, consider the958 implications if in several years you instead find yourself with a large959 number of small customers. In this case, it may be better to choose to960 partition by <code class="literal">HASH</code> and choose a reasonable number of961 partitions rather than trying to partition by <code class="literal">LIST</code> and962 hoping that the number of customers does not increase beyond what it is963 practical to partition the data by.964 </p><p>965 Sub-partitioning can be useful to further divide partitions that are966 expected to become larger than other partitions.967 Another option is to use range partitioning with multiple columns in968 the partition key.969 Either of these can easily lead to excessive numbers of partitions,970 so restraint is advisable.971 </p><p>972 It is important to consider the overhead of partitioning during973 query planning and execution. The query planner is generally able to974 handle partition hierarchies with up to a few thousand partitions fairly975 well, provided that typical queries allow the query planner to prune all976 but a small number of partitions. Planning times become longer and memory977 consumption becomes higher when more partitions remain after the planner978 performs partition pruning. Another979 reason to be concerned about having a large number of partitions is that980 the server's memory consumption may grow significantly over981 time, especially if many sessions touch large numbers of partitions.982 That's because each partition requires its metadata to be loaded into the983 local memory of each session that touches it.984 </p><p>985 With data warehouse type workloads, it can make sense to use a larger986 number of partitions than with an <acronym class="acronym">OLTP</acronym> type workload.987 Generally, in data warehouses, query planning time is less of a concern as988 the majority of processing time is spent during query execution. With989 either of these two types of workload, it is important to make the right990 decisions early, as re-partitioning large quantities of data can be991 painfully slow. Simulations of the intended workload are often beneficial992 for optimizing the partitioning strategy. Never just assume that more993 partitions are better than fewer partitions, nor vice-versa.994 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl-inherit.html" title="5.10. Inheritance">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-foreign-data.html" title="5.12. Foreign Data">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.10. Inheritance </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.12. Foreign Data</td></tr></table></div></body></html>