Home • Explore • Blog • CI
☰



postgresql
11th chunk of `doc/src/sgml/ref/create_table.sgml`
fc317513ceb075f4bea3e17ef06fc1b62cd82dda04b501d70000000100000fb7
 available for hash-partitioned tables.  A partition key value
      not fitting into any other partition of the given parent will be
      routed to the default partition.
     </para>

     <para>
      When a table has an existing <literal>DEFAULT</literal> partition and
      a new partition is added to it, the default partition must
      be scanned to verify that it does not contain any rows which properly
      belong in the new partition.  If the default partition contains a
      large number of rows, this may be slow.  The scan will be skipped if
      the default partition is a foreign table or if it has a constraint which
      proves that it cannot contain rows which should be placed in the new
      partition.
     </para>

     <para>
      When creating a hash partition, a modulus and remainder must be specified.
      The modulus must be a positive integer, and the remainder must be a
      non-negative integer less than the modulus.  Typically, when initially
      setting up a hash-partitioned table, you should choose a modulus equal to
      the number of partitions and assign every table the same modulus and a
      different remainder (see examples, below).   However, it is not required
      that every partition have the same modulus, only that every modulus which
      occurs among the partitions of a hash-partitioned table is a factor of the
      next larger modulus.  This allows the number of partitions to be increased
      incrementally without needing to move all the data at once.  For example,
      suppose you have a hash-partitioned table with 8 partitions, each of which
      has modulus 8, but find it necessary to increase the number of partitions
      to 16.  You can detach one of the modulus-8 partitions, create two new
      modulus-16 partitions covering the same portion of the key space (one with
      a remainder equal to the remainder of the detached partition, and the
      other with a remainder equal to that value plus 8), and repopulate them
      with data.  You can then repeat this -- perhaps at a later time -- for
      each modulus-8 partition until none remain.  While this may still involve
      a large amount of data movement at each step, it is still better than
      having to create a whole new table and move all the data at once.
     </para>

     <para>
      A partition must have the same column names and types as the partitioned
      table to which it belongs. Modifications to the column names or types of
      a partitioned table will automatically propagate to all partitions.
      <literal>CHECK</literal> constraints will be inherited automatically by
      every partition, but an individual partition may specify additional
      <literal>CHECK</literal> constraints; additional constraints with the
      same name and condition as in the parent will be merged with the parent
      constraint.  Defaults may be specified separately for each partition.
      But note that a partition's default value is not applied when inserting
      a tuple through a partitioned table.
     </para>

     <para>
      Rows inserted into a partitioned table will be automatically routed to
      the correct partition.  If no suitable partition exists, an error will
      occur.
     </para>

     <para>
      Operations such as <command>TRUNCATE</command>
      which normally affect a table and all of its
      inheritance children will cascade to all partitions, but may also be
      performed on an individual partition.
     </para>

     <para>
      Note that creating a partition using <literal>PARTITION OF</literal>
      requires taking an <literal>ACCESS EXCLUSIVE</literal> lock on the
      parent partitioned table.  Likewise, dropping a partition
      with <command>DROP TABLE</command> requires taking
      an <literal>ACCESS EXCLUSIVE</literal> lock on the parent table.
      It is possible to use <link linkend="sql-altertable"><command>ALTER
      TABLE ATTACH/DETACH PARTITION</command></link>
Title: CREATE TABLE Parameters: Hash Partitions and Partition Properties
Summary
This section focuses on hash partitions, detailing the necessity of specifying a modulus and remainder. It explains how to incrementally increase partitions without moving all data by detaching and re-populating partitions with different moduli. It also describes partition properties, including inheriting column names, types, and CHECK constraints, while allowing for partition-specific constraints and default values. It clarifies that partition defaults aren't applied when inserting tuples through the partitioned table. Rows inserted are automatically routed, and operations like TRUNCATE cascade but can be performed individually. Finally, it mentions the ACCESS EXCLUSIVE lock required when creating/dropping a partition and suggests using ALTER TABLE ATTACH/DETACH PARTITION.