Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes14kdownloads
ddl-partitioning.html994 linesDownload Raw Back to html
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 &gt;= DATE '2008-02-01' AND logdate &lt; 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 &gt;= 100 AND outletID &lt; 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 &gt;= DATE '2006-02-01' AND logdate &lt; DATE '2006-03-01' )543) INHERITS (measurement);544 545CREATE TABLE measurement_y2006m03 (546    CHECK ( logdate &gt;= DATE '2006-03-01' AND logdate &lt; DATE '2006-04-01' )547) INHERITS (measurement);548 549...550CREATE TABLE measurement_y2007m11 (551    CHECK ( logdate &gt;= DATE '2007-11-01' AND logdate &lt; DATE '2007-12-01' )552) INHERITS (measurement);553 554CREATE TABLE measurement_y2007m12 (555    CHECK ( logdate &gt;= DATE '2007-12-01' AND logdate &lt; DATE '2008-01-01' )556) INHERITS (measurement);557 558CREATE TABLE measurement_y2008m01 (559    CHECK ( logdate &gt;= DATE '2008-01-01' AND logdate &lt; 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 &gt;= DATE '2006-02-01' AND613         NEW.logdate &lt; DATE '2006-03-01' ) THEN614        INSERT INTO measurement_y2006m02 VALUES (NEW.*);615    ELSIF ( NEW.logdate &gt;= DATE '2006-03-01' AND616            NEW.logdate &lt; DATE '2006-04-01' ) THEN617        INSERT INTO measurement_y2006m03 VALUES (NEW.*);618    ...619    ELSIF ( NEW.logdate &gt;= DATE '2008-01-01' AND620            NEW.logdate &lt; 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 &gt;= DATE '2006-02-01' AND logdate &lt; DATE '2006-03-01' )652DO INSTEAD653    INSERT INTO measurement_y2006m02 VALUES (NEW.*);654...655CREATE RULE measurement_insert_y2008m01 AS656ON INSERT TO measurement WHERE657    ( logdate &gt;= DATE '2008-01-01' AND logdate &lt; 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 &gt;= DATE '2008-02-01' AND logdate &lt; 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 &gt;= DATE '2008-02-01' AND logdate &lt; 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 &gt;= 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 &gt;= DATE '2008-01-01';791                                    QUERY PLAN792-------------------------------------------------------------------​----------------793 Aggregate  (cost=188.76..188.77 rows=1 width=8)794   -&gt;  Append  (cost=0.00..181.05 rows=3085 width=0)795         -&gt;  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)796               Filter: (logdate &gt;= '2008-01-01'::date)797         -&gt;  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)798               Filter: (logdate &gt;= '2008-01-01'::date)799...800         -&gt;  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)801               Filter: (logdate &gt;= '2008-01-01'::date)802         -&gt;  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)803               Filter: (logdate &gt;= '2008-01-01'::date)804         -&gt;  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)805               Filter: (logdate &gt;= '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 &gt;= DATE '2008-01-01';816                                    QUERY PLAN817-------------------------------------------------------------------​----------------818 Aggregate  (cost=37.75..37.76 rows=1 width=8)819   -&gt;  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)820         Filter: (logdate &gt;= '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>
codekingpro/portable-devtools · Team Ai