{"id":473010,"date":"2025-09-02T15:10:07","date_gmt":"2025-09-02T15:10:07","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=473010"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=473010","title":{"rendered":"<span>Partition and rule: sharing practical knowledge about partitioning in Postgres Pro<\/span>"},"content":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/f0e\/f38\/3e9\/f0ef383e9ab261a92a3a975cdce50987.png\" width=\"1625\" height=\"917\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/f0e\/f38\/3e9\/f0ef383e9ab261a92a3a975cdce50987.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/f0e\/f38\/3e9\/f0ef383e9ab261a92a3a975cdce50987.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/figure>\n<p>Declarative partitioning may sound complex, but in reality it\u2019s just a way to tell your database how best to organize large tables \u2014 so it can optimize queries and make maintenance easier. Let\u2019s walk through how it works and when declarative partitioning can save the day.<\/p>\n<h2>What is partitioning and when you might need it<\/h2>\n<p>Partitioning is the process of splitting a single logical table into separate physical parts (partitions). The split is done row-wise, based on the value of a specific column or set of columns called the partition key.<\/p>\n<p>For example, let\u2019s say you have a table named\u00a0<code>city<\/code>, and you partition it into\u00a0<code>city_msk<\/code>,\u00a0<code>city_spb<\/code>, and\u00a0<code>city_nsk<\/code>. In this case, the\u00a0<code>city_code<\/code>\u00a0column is the partition key.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/6bd\/7de\/1f0\/6bd7de1f0eddbfeb0413cd4675d1794a.png\" alt=\"Partitioned table city\" title=\"Partitioned table city\" width=\"1560\" height=\"778\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/6bd\/7de\/1f0\/6bd7de1f0eddbfeb0413cd4675d1794a.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/6bd\/7de\/1f0\/6bd7de1f0eddbfeb0413cd4675d1794a.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Partitioned table <code>city<\/code><\/figcaption><\/div>\n<\/figure>\n<p>In a partitioned table like\u00a0<code>city<\/code>, no rows are actually stored in the main table \u2014 they\u2019re all stored in the partitions. Which partition a row ends up in depends on the city code. If there\u2019s no suitable partition, the row goes into a\u00a0<code>DEFAULT<\/code>\u00a0partition. If there\u2019s no default, the system throws a \u201cno partition found for row\u201d error.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/fb7\/eef\/fe1\/fb7eeffe1baa656118aaa99a6db51559.png\" alt=\"Partitioned table city\" title=\"Partitioned table city\" width=\"1560\" height=\"1145\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/fb7\/eef\/fe1\/fb7eeffe1baa656118aaa99a6db51559.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/fb7\/eef\/fe1\/fb7eeffe1baa656118aaa99a6db51559.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Partitioned table <code>city<\/code><\/figcaption><\/div>\n<\/figure>\n<p>From the application\u2019s point of view, partitioning is transparent. All read and write operations go through the parent table. The database planner determines which partition is used for reading or writing.<\/p>\n<p>Partitioning helps:<\/p>\n<ul>\n<li>\n<p>Boost query performance: the query planner skips partitions that don\u2019t contain matching rows.<\/p>\n<\/li>\n<li>\n<p>Make large-table maintenance easier: tasks like\u00a0<code>VACUUM FULL<\/code>,\u00a0<code>REINDEX<\/code>, or\u00a0<code>pg_dump<\/code>\u00a0run faster on individual partitions than on a monolithic table.<\/p>\n<\/li>\n<li>\n<p>Manage data flexibly: for example, move historical or rarely used data to cheaper storage. More on that\u00a0in this <a href=\"https:\/\/habr.com\/ru\/companies\/postgrespro\/articles\/891878\/\" rel=\"noopener noreferrer nofollow\">article<\/a>.<\/p>\n<\/li>\n<\/ul>\n<h2>Declarative or not? Understanding declarative partitioning<\/h2>\n<p>This mechanism lets you declare from the start that a table is partitioned.<\/p>\n<p>In the\u00a0<code>create table<\/code>\u00a0statement, you specify the partitioning method (e.g.\u00a0<code>partition by range<\/code>) and list the columns that make up the partition key.<\/p>\n<p>Transactional DDL makes this a powerful tool for managing partitioned tables and enables automation.<\/p>\n<details class=\"spoiler\">\n<summary>What transactional DDL means<\/summary>\n<div class=\"spoiler__content\">\n<p>When creating a table, the following properties are guaranteed:<\/p>\n<ul>\n<li>\n<p><strong>Atomicity<\/strong>: if the transaction fails, there\u2019s no leftover junk to clean up.<\/p>\n<\/li>\n<li>\n<p><strong>Consistency<\/strong>: table creation won\u2019t break data integrity or cause conflicts.<\/p>\n<\/li>\n<li>\n<p><strong>Isolation<\/strong>: the operation runs separately from other transactions.<\/p>\n<\/li>\n<li>\n<p><strong>Durability<\/strong>: once created, the table\u2019s metadata is safely stored on disk.<\/p>\n<\/li>\n<\/ul>\n<\/div>\n<\/details>\n<pre><code class=\"pgsql\">CREATE TABLE measurement (  city_id int not null,  logdate date not null,  ) PARTITION BY RANGE (logdate);   CREATE TABLE measurement_y2006m02 PARTITION OF measurement  FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');  CREATE TABLE measurement_y2006m03 PARTITION OF measurement  FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');<\/code><\/pre>\n<p>But declarative partitioning does come with a few limitations:<\/p>\n<ul>\n<li>\n<p>All partitions must have the same set of columns as the parent table.<\/p>\n<\/li>\n<li>\n<p>The partition key must be a subset of the primary key, if one exists. That means if the table has a\u00a0<code>PRIMARY KEY<\/code>, you can only partition it by the columns that are included in that key.<\/p>\n<\/li>\n<\/ul>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/588\/d26\/dd7\/588d26dd7e369c818e98206651451799.png\" alt=\"|PARTITIONING KEY|\u00a0\u00a0\u2282\u00a0 |PRIMARY KEY|\" title=\"|PARTITIONING KEY|\u00a0\u00a0\u2282\u00a0 |PRIMARY KEY|\" width=\"3180\" height=\"5046\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/588\/d26\/dd7\/588d26dd7e369c818e98206651451799.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/588\/d26\/dd7\/588d26dd7e369c818e98206651451799.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>|PARTITIONING KEY|\u00a0\u00a0\u2282\u00a0 |PRIMARY KEY|<\/figcaption><\/div>\n<\/figure>\n<ul>\n<li>\n<p><code>before row<\/code>\u00a0triggers for\u00a0<code>insert<\/code>\u00a0cannot change which partition a row goes to.<\/p>\n<\/li>\n<li>\n<p>You cannot declaratively partition an existing table.<\/p>\n<\/li>\n<\/ul>\n<details class=\"spoiler\">\n<summary>How to partition an existing table<\/summary>\n<div class=\"spoiler__content\">\n<p>There are two options:<\/p>\n<ol>\n<li>\n<p>Attach the existing table as a historical partition to a new partitioned table<\/p>\n<\/li>\n<\/ol>\n<p>First, create a new partitioned table with the same structure:<\/p>\n<pre><code class=\"pgsql\">create table measurement_p (like measurement) partition by range (logdate);<\/code><\/pre>\n<p>And a future partition:<\/p>\n<pre><code class=\"pgsql\">create table measurements_2025 partition of measurements_p for values from ('2025-01-01') to ('2026-01-01');<\/code><\/pre>\n<p>Prepare the old table to avoid long locks:<\/p>\n<pre><code class=\"pgsql\">begin; set local statement_timeout to '1s';  alter table measurements add constraint measurements_partbound_check check (logdate &lt; '2025-01-01' and created_at is not null) not valid; commit; alter table measurements validate constraint measurements_partbound_check;<\/code><\/pre>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/25d\/c65\/f71\/25dc65f713c924064dc23d51e6544c94.png\" alt=\"Preparation steps\" title=\"Preparation steps\" width=\"1213\" height=\"819\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/25d\/c65\/f71\/25dc65f713c924064dc23d51e6544c94.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/25d\/c65\/f71\/25dc65f713c924064dc23d51e6544c94.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Preparation steps<\/figcaption><\/div>\n<\/figure>\n<p>Rename the tables and attach the old one as a partition:<\/p>\n<pre><code class=\"sql\">begin; set statement_timeout to '1s'; alter table measurements rename to measurements_history; alter table measurements_p rename to measurements; alter table measurements attach partition measurements_archive   for values from (MINVALUE) to ('2025-01-01'); commit;<\/code><\/pre>\n<p>Now the system switches over in under a second. Data before Jan 1, 2025 goes to the old table, and new data goes to the partitioned one.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/610\/eff\/9bb\/610eff9bbd02cc37324f6fddb14a6970.png\" alt=\"Attaching a historical partition to a new table\" title=\"Attaching a historical partition to a new table\" width=\"1600\" height=\"927\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/610\/eff\/9bb\/610eff9bbd02cc37324f6fddb14a6970.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/610\/eff\/9bb\/610eff9bbd02cc37324f6fddb14a6970.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Attaching a historical partition to a new table<\/figcaption><\/div>\n<\/figure>\n<p>2. Fully repartition the existing table<\/p>\n<p>If you need to physically split the data, use this approach. Create a new partitioned table with a different name and define the necessary partitions.<\/p>\n<p>Copy the data from the old table:<\/p>\n<pre><code class=\"pgsql\">INSERT INTO measurement_p SELECT * FROM measurement<\/code><\/pre>\n<p>or:<\/p>\n<pre><code class=\"pgsql\">COPY TO measurement_p COPY FROM measurement<\/code><\/pre>\n<p>Since the transfer might take time, we don\u2019t want to block access to the original table. To keep things in sync, create\u00a0<code>insert<\/code>,\u00a0<code>update<\/code>, and\u00a0<code>delete<\/code>\u00a0triggers on the old table to replicate changes into the new one during the transfer.<\/p>\n<p>After the data is moved, rename the new partitioned table to match the old name.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/0d7\/9ca\/94c\/0d79ca94cfc4de42570421cb4dd2d2e0.png\" alt=\"The process of transferring data into a partitioned table\" title=\"The process of transferring data into a partitioned table\" width=\"1600\" height=\"813\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/0d7\/9ca\/94c\/0d79ca94cfc4de42570421cb4dd2d2e0.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/0d7\/9ca\/94c\/0d79ca94cfc4de42570421cb4dd2d2e0.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>The process of transferring data into a partitioned table<\/figcaption><\/div>\n<\/figure>\n<\/div>\n<\/details>\n<h2>Partitioning by hash, list, and range<\/h2>\n<p>Declarative partitioning supports three methods:<\/p>\n<ul>\n<li>\n<p>by hash<\/p>\n<\/li>\n<li>\n<p>by list of values<\/p>\n<\/li>\n<li>\n<p>by range<\/p>\n<\/li>\n<\/ul>\n<p>When working with partitioned tables, there\u2019s an optimization called\u00a0partition pruning. To enable pruning, the planner needs context about how the table is partitioned. The system catalog stores information about how many partitions exist, which ranges they cover, and so on. When a query is sent to the database with a predicate (a\u00a0<code>WHERE<\/code>\u00a0condition), the planner can use this metadata to exclude unnecessary partitions from the query plan.<\/p>\n<p>To enable this behavior, the parameter\u00a0<code>enable_partition_pruning<\/code>\u00a0must be set to\u00a0<code>on<\/code>.<\/p>\n<p>Let\u2019s take a closer look at the partitioning methods.<\/p>\n<h4>1. Hash partitioning<\/h4>\n<pre><code class=\"pgsql\">CREATE TABLE orders_1 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 0);\u00a0 CREATE TABLE orders_2 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 1);\u00a0 CREATE TABLE orders_3 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 2);<\/code><\/pre>\n<p>Hash partitioning is typically used when you need to split a table into equally sized partitions. Since hash function values are distributed uniformly, the rows are also distributed evenly across partitions. The partitioning key is determined by the remainder of the key\u2019s numeric hash divided by the total number of partitions.<\/p>\n<p>If you imagine all possible key values arranged along a line (the allowed value range), hash partitioning can be visualized like this:<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/b8c\/22a\/bed\/b8c22abedb32f1154f114404eeadea17.png\" alt=\"How hash partitioning looks\" title=\"How hash partitioning looks\" width=\"1600\" height=\"384\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/b8c\/22a\/bed\/b8c22abedb32f1154f114404eeadea17.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/b8c\/22a\/bed\/b8c22abedb32f1154f114404eeadea17.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>How hash partitioning looks<\/figcaption><\/div>\n<\/figure>\n<p>Comparison operations like greater than or less than do not apply to hash values, so partition pruning works only for equality joins and queries with strict equality conditions.<\/p>\n<p>The number of partitions is fixed at creation time. While you cannot change the total count afterward, you can attach or detach partitions dynamically.<\/p>\n<h4>2. List partitioning<\/h4>\n<pre><code class=\"pgsql\">CREATE TABLE part_1 PARTITION OF city FOR VALUES IN ('MSK');\u00a0 CREATE TABLE part_2 PARTITION OF city FOR VALUES IN ('NSK');\u00a0 CREATE TABLE part_3 PARTITION OF city FOR VALUES IN ('SPB\u2019);<\/code><\/pre>\n<p>Here, the partition key matches one of the listed values.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/d5a\/b89\/25b\/d5ab8925b7ab22aa8492ae14c360c341.png\" alt=\"How list partitioning looks\" title=\"How list partitioning looks\" width=\"1600\" height=\"424\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/d5a\/b89\/25b\/d5ab8925b7ab22aa8492ae14c360c341.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/d5a\/b89\/25b\/d5ab8925b7ab22aa8492ae14c360c341.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>How list partitioning looks<\/figcaption><\/div>\n<\/figure>\n<h4>3. Range partitioning<\/h4>\n<pre><code class=\"pgsql\">CREATE TABLE part_1 PARTITION OF sales FOR VALUES FROM ('2006-01-01') TO ('2010-01-01');\u00a0  CREATE TABLE part_2 PARTITION OF sales FOR VALUES FROM ('2013-01-01') TO ('2016-01-01');\u00a0  CREATE TABLE part_3 PARTITION OF sales FOR VALUES FROM ('2016-01-01') TO ('2020-01-01\u2019);\u00a0<\/code><\/pre>\n<p>Range partitioning is the most commonly used method. In this approach, key values are matched to specified intervals, which can vary in size and may have gaps between them. When creating each partition, you define the range it covers to determine which values belong to that partition.<\/p>\n<p>Each partition\u2019s range is defined by two values: a start (\u201cfrom\u201d) and an end (\u201cto\u201d). The start value is inclusive, while the end value is exclusive. Therefore, to create two contiguous partitions without gaps, the end value of the first partition should be the start value of the next one.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/6d5\/689\/7d6\/6d56897d641b788fe4b54bc106f4dd7a.png\" alt=\"How range partitioning looks\" title=\"How range partitioning looks\" width=\"1600\" height=\"476\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/6d5\/689\/7d6\/6d56897d641b788fe4b54bc106f4dd7a.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/6d5\/689\/7d6\/6d56897d641b788fe4b54bc106f4dd7a.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>How range partitioning looks<\/figcaption><\/div>\n<\/figure>\n<h2>Vanilla partitioning tools<\/h2>\n<p>Let\u2019s look at\u00a0<code>attach<\/code>,\u00a0<code>detach<\/code>,\u00a0<code>default<\/code>, and multi-level partitioning.<\/p>\n<h4>Attach and detach<\/h4>\n<p><code>ATTACH<\/code>\u00a0lets you add a table as a partition to a partitioned table.<\/p>\n<p>When attaching a table as a partition, you must specify which partition key values it covers according to the partitioning method. For range partitioning \u2014 the interval, for list partitioning \u2014 the set of values, for hash partitioning \u2014 the remainder value.<\/p>\n<pre><code class=\"pgsql\">ALTER TABLE measurement ATTACH PARTITION measurement_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01' );<\/code><\/pre>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/d92\/0a3\/c38\/d920a3c38b1e089b00dad122923ee785.png\" alt=\"Attaching a partition to a table\" title=\"Attaching a partition to a table\" width=\"1600\" height=\"956\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/d92\/0a3\/c38\/d920a3c38b1e089b00dad122923ee785.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/d92\/0a3\/c38\/d920a3c38b1e089b00dad122923ee785.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Attaching a partition to a table<\/figcaption><\/div>\n<\/figure>\n<p>This operation requires a lock. When attaching a partition, a corresponding <code>CHECK<\/code> constraint is created to ensure that the partition does not contain values outside the specified boundaries. You can reduce lock time by creating this constraint manually in advance.<\/p>\n<p><code>DETACH<\/code>\u00a0removes a partition, turning it into a standalone table:<\/p>\n<pre><code class=\"pgsql\">ALTER TABLE measurement DETACH PARTITION measurement_2023;\u00a0<\/code><\/pre>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/e1b\/c74\/fa6\/e1bc74fa6031e621a5cc49563b8edde1.png\" alt=\"Detaching a partition from a table\" title=\"Detaching a partition from a table\" width=\"1600\" height=\"934\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/e1b\/c74\/fa6\/e1bc74fa6031e621a5cc49563b8edde1.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/e1b\/c74\/fa6\/e1bc74fa6031e621a5cc49563b8edde1.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Detaching a partition from a table<\/figcaption><\/div>\n<\/figure>\n<h4>Default<\/h4>\n<p>The default partition catches rows that don\u2019t match any other partition:<\/p>\n<p><code>CREATE TABLE \u2026 PARTITION OF \u2026 DEFAULT ;<\/code><\/p>\n<p><strong>Multi-level partitioning<\/strong><\/p>\n<p>Multi-level partitioning (or sub-partitioning) means that partitions themselves are partitioned tables.<\/p>\n<p>Since you cannot declaratively partition an existing table, to build a multi-level structure you must first create a partitioned table and then use\u00a0<code>attach<\/code>\u00a0to add it as a partition to another partitioned table. The partitioning methods can differ between levels.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/d15\/a2f\/275\/d15a2f275f2b67a6230cf0c0007978e5.png\" alt=\"How multi-level partitioning looks\" title=\"How multi-level partitioning looks\" width=\"1600\" height=\"919\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/d15\/a2f\/275\/d15a2f275f2b67a6230cf0c0007978e5.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/d15\/a2f\/275\/d15a2f275f2b67a6230cf0c0007978e5.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>How multi-level partitioning looks<\/figcaption><\/div>\n<\/figure>\n<h2>Replicating partitioned tables<\/h2>\n<p>Physical replication creates a complete copy of the database on a standby server. Logical replication is more flexible: you create a publication on selected tables on one server and subscribe to it from others.<\/p>\n<p>Logical replication is typically used in two cases:<\/p>\n<ul>\n<li>\n<p>Data consolidation. Suppose you have a central server and several regional servers. Each regional server publishes changes, and the central one subscribes to them. New data appearing in regional tables is pushed to the central server, where it is consolidated.<\/p>\n<\/li>\n<li>\n<p>Updating reference data dictionaries. For instance, you store reference data centrally and want to push it to regional nodes. The central server publishes the table, and others subscribe to it.<\/p>\n<\/li>\n<\/ul>\n<pre><code class=\"pgsql\">create publication name [ with ( publish_via_partition_root = on|off [, ...] ) ] <\/code><\/pre>\n<p>The\u00a0<code>publish_via_partition_root<\/code>\u00a0parameter controls how partitioned tables are replicated to subscribers.<\/p>\n<p>When\u00a0<code>publish_via_partition_root = off<\/code>, insert, update, delete, and truncate commands are replicated using the names of the partitions the rows belong to. For example, if an insert targets the\u00a0<code>city<\/code>\u00a0table with value\u00a0<code>msk<\/code>, the subscriber receives an\u00a0<code>insert into city_msk<\/code>, as if each partition were published separately. Subscribers must have matching tables, but they don&#8217;t need to be part of a partitioned\u00a0<code>city<\/code>\u00a0table \u2014 standalone tables work too.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/336\/90a\/6d4\/33690a6d44a4a2dd71c5ba2f367acef1.png\" alt=\"Replication with publish_via_partition_root = off\" title=\"Replication with publish_via_partition_root = off\" width=\"1600\" height=\"641\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/336\/90a\/6d4\/33690a6d44a4a2dd71c5ba2f367acef1.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/336\/90a\/6d4\/33690a6d44a4a2dd71c5ba2f367acef1.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Replication with <code>publish_via_partition_root = off<\/code><\/figcaption><\/div>\n<\/figure>\n<p>When the option is enabled (<code>publish_via_partition_root = on<\/code>), all changes are replicated through the main (parent) table, not through its individual partitions.<\/p>\n<p>For example, if you have a partitioned table\u00a0<code>city<\/code>\u00a0and you insert new data into it using an\u00a0<code>INSERT<\/code>\u00a0command, those changes will be sent to subscribers as operations on the\u00a0<code>city<\/code>\u00a0table, not on its specific partitions.<\/p>\n<p>This means that on the subscriber side, the\u00a0<code>city<\/code>\u00a0table may be partitioned differently or not partitioned at all. The subscriber will still be able to receive and apply changes, even if its partitioning structure differs from that of the publisher.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/91c\/fc7\/720\/91cfc772009e35e70e907f7379fe30ae.png\" alt=\"Replication with publish_via_partition_root = on\" title=\"Replication with publish_via_partition_root = on\" width=\"1600\" height=\"741\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/91c\/fc7\/720\/91cfc772009e35e70e907f7379fe30ae.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/91c\/fc7\/720\/91cfc772009e35e70e907f7379fe30ae.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Replication with <code>publish_via_partition_root = on<\/code><\/figcaption><\/div>\n<\/figure>\n<h2>Advanced partition operations in Postgres Pro<\/h2>\n<p>Let\u2019s look at additional operations available for partitions in Postgres Pro.<\/p>\n<p>1. <strong>SPLIT<\/strong>\u00a0lets you divide a partition into smaller parts:<\/p>\n<pre><code class=\"pgsql\">alter table measurement split partition partition_name into (   partition partition_name_1 { for values ... | default },   partition partition_name_2 { for values ... | default } );<\/code><\/pre>\n<p>For example, if the 2024 partition grew too large, you can split it into quarterly partitions.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/f35\/4b7\/c2e\/f354b7c2e32fe08a8772d256df78ba6d.png\" alt=\"Splitting a partition with SPLIT\" title=\"Splitting a partition with SPLIT\" width=\"1600\" height=\"724\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/f35\/4b7\/c2e\/f354b7c2e32fe08a8772d256df78ba6d.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/f35\/4b7\/c2e\/f354b7c2e32fe08a8772d256df78ba6d.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Splitting a partition with SPLIT<\/figcaption><\/div>\n<\/figure>\n<p>The operation requires an exclusive lock and must meet these conditions:<\/p>\n<ul>\n<li>\n<p>If there is no DEFAULT partition, the combined range of new partitions must fully cover the original partition\u2019s range.<\/p>\n<\/li>\n<li>\n<p>If splitting the DEFAULT partition, the resulting set must include a DEFAULT as well.<\/p>\n<\/li>\n<\/ul>\n<p>2.<strong> MERGE<\/strong>\u00a0combines multiple partitions into one:<\/p>\n<pre><code class=\"pgsql\">alter table measurement merge partitions (partition_1, partition_2) into merged_partition;<\/code><\/pre>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/5cd\/c93\/504\/5cdc9350446ee5ae6f7b8ab617ca8887.png\" alt=\"Combining partitions with MERGE\" title=\"Combining partitions with MERGE\" width=\"1600\" height=\"724\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/5cd\/c93\/504\/5cdc9350446ee5ae6f7b8ab617ca8887.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/5cd\/c93\/504\/5cdc9350446ee5ae6f7b8ab617ca8887.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Combining partitions with MERGE<\/figcaption><\/div>\n<\/figure>\n<p>3. <a href=\"https:\/\/postgrespro.ru\/docs\/postgrespro\/17\/pgpro-autopart?lang=en\" rel=\"noopener noreferrer nofollow\">Pgpro_autopart<\/a> extension \u2014 on-demand auto-partitioning<\/p>\n<p>Partitioning is an extremely useful tool, but to work with it, you need to prepare partitions in advance by creating them manually. This adds a significant workload for DBAs, considering that partitions are usually counted in the hundreds. Pgpro_autopart allows partitions to be created dynamically when a row is inserted and a suitable partition does not exist.<\/p>\n<p>The extension includes a function, ap_enable_automatic_partition_creation, where you need to specify the table name (tablename), the interval (interval), and the starting value (first_value) \u2014 the reference point from which intervals will be offset.<\/p>\n<p>Pgpro_autopart works only with new tables because it cannot track partitions that were created earlier.<\/p>\n<p>The ap_tables_view view allows you to monitor tables with automatic partitioning enabled.<\/p>\n<p>The extension is available since version 17 of Postgres Pro and replaces the now deprecated pg_pathman functionality.<\/p>\n<details class=\"spoiler\">\n<summary>How the pgpro_autopart extension works<\/summary>\n<div class=\"spoiler__content\">\n<p>Let\u2019s say we want to create a table that supports automatic partition creation.<\/p>\n<p><strong>Step 1.<\/strong>\u00a0Create an empty table\u00a0measurement\u00a0partitioned by\u00a0id\u00a0using range:<\/p>\n<pre><code class=\"pgsql\">create table measurement (     id int,     ... ) partition by range (id); <\/code><\/pre>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/fba\/b66\/726\/fbab66726efc69ac7c83018bf0e7e1a9.png\" width=\"1405\" height=\"638\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/fba\/b66\/726\/fbab66726efc69ac7c83018bf0e7e1a9.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/fba\/b66\/726\/fbab66726efc69ac7c83018bf0e7e1a9.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/figure>\n<p><strong>Step 2<\/strong>. Call the function from the extension, specifying the table name, interval, and start value:<\/p>\n<pre><code class=\"pgsql\">select ap_enable_automatic_partition_creation('measurement', 10, 100);<\/code><\/pre>\n<p>where 10 is the interval, and 100 is the start value.<\/p>\n<ol>\n<li>\n<p>At this point, the table is automatically renamed with the prefix\u00a0real.<\/p>\n<p>A view is created with the old table\u2019s name \u2014\u00a0<code>measurement<\/code>. The view is defined as\u00a0<code>SELECT * FROM real_measurement<\/code>. It is needed so that the trigger on this view can check whether a suitable partition exists \u2014 since such logic cannot be implemented directly in a trigger on the table itself. Importantly, all this happens transparently for the application; the application will now simply query the view.<\/p>\n<\/li>\n<li>\n<p>An INSTEAD OF INSERT, UPDATE trigger is automatically created, which dynamically creates partitions when no suitable partition is found for the inserted row.<\/p>\n<\/li>\n<\/ol>\n<figure class=\"\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/750\/396\/6cb\/7503966cb2c8eb948d146a74a91c740b.png\" width=\"279\" height=\"300\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/750\/396\/6cb\/7503966cb2c8eb948d146a74a91c740b.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/750\/396\/6cb\/7503966cb2c8eb948d146a74a91c740b.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/figure>\n<p><strong>Step 3.<\/strong>\u00a0Insert the first row with\u00a0id = 111:<\/p>\n<pre><code class=\"pgsql\">INSERT INTO measurement VALUES (111, \u2018text\u2019 );\u00a0  NOTICE: New partition \"public\".\"real_measurement_110_120\" created\u00a0<\/code><\/pre>\n<p>At the same time, the first partition is created automatically. Its name includes the boundaries of the interval it covers:<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/b72\/0ba\/f17\/b720baf1789065492c7864ed1e248fd8.png\" width=\"1767\" height=\"921\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/b72\/0ba\/f17\/b720baf1789065492c7864ed1e248fd8.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/b72\/0ba\/f17\/b720baf1789065492c7864ed1e248fd8.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/figure>\n<p><strong>Step 4.<\/strong>\u00a0If you later UPDATE the row and change\u00a0<code>id<\/code>\u00a0from\u00a0<code>111<\/code>\u00a0to\u00a0<code>-55<\/code>, a new partition is created automatically.<\/p>\n<pre><code class=\"pgsql\">UPDATE measurement SET id = -55 WHERE id = 111 RETURNING *;\u00a0  NOTICE: New partition \"public\".\"real_measurement_-60_-50\" created\u00a0<\/code><\/pre>\n<p>UPDATE, INSERT, DELETE\u00a0with\u00a0RETURNING\u00a0are handled correctly.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/e35\/35a\/3f2\/e3535a3f2a7173d3a241e2409f3882f9.png\" width=\"1842\" height=\"1067\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/e35\/35a\/3f2\/e3535a3f2a7173d3a241e2409f3882f9.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/e35\/35a\/3f2\/e3535a3f2a7173d3a241e2409f3882f9.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/figure>\n<\/div>\n<\/details>\n<h2>What\u2019s next<\/h2>\n<p>In Q1 2025, global indexes were introduced. These are shared indexes across all partitions, supporting uniqueness constraints. You can now create a unique constraint on any column, not just the partition key.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/fb5\/6f1\/034\/fb56f1034faf9c6252940af30d73b8db.png\" alt=\"Regular indexes apply only to individual partitions. Global indexes span the entire table\" title=\"Regular indexes apply only to individual partitions. Global indexes span the entire table\" width=\"2555\" height=\"1571\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/fb5\/6f1\/034\/fb56f1034faf9c6252940af30d73b8db.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/fb5\/6f1\/034\/fb56f1034faf9c6252940af30d73b8db.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Regular indexes apply only to individual partitions. Global indexes span the entire table<\/figcaption><\/div>\n<\/figure>\n<p>In the first quarter of 2026, reference (or foreign key) partitioning will be introduced. In this method, a created table is partitioned not explicitly by its own PRIMARY KEY, but through a reference to the parent table via a FOREIGN KEY, using the same key ranges as in the parent table. This way, the partitions of the parent and child tables become linked, and when planning a query, identifying one partition allows us to determine the related partition without scanning the entire hierarchy.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/44a\/767\/9fe\/44a7679fe46d2304e1706a18dcd2565a.png\" alt=\"How reference partitioning looks\" title=\"How reference partitioning looks\" width=\"1600\" height=\"723\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/44a\/767\/9fe\/44a7679fe46d2304e1706a18dcd2565a.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/44a\/767\/9fe\/44a7679fe46d2304e1706a18dcd2565a.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>How reference partitioning looks<\/figcaption><\/div>\n<\/figure>\n<p>When a partition is attached (ATTACH) to the parent table, it is also attached to the child table; similarly, when it is detached (DETACH) from the parent, it is detached from the child table as well.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/f0c\/062\/c25\/f0c062c2546a0a8ff13d56c4e8fa800e.png\" alt=\"Child table partitions are added or removed automatically, based on parent changes\" title=\"Child table partitions are added or removed automatically, based on parent changes\" width=\"3650\" height=\"1650\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/f0c\/062\/c25\/f0c062c2546a0a8ff13d56c4e8fa800e.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/f0c\/062\/c25\/f0c062c2546a0a8ff13d56c4e8fa800e.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>Child table partitions are added or removed automatically, based on parent changes<\/figcaption><\/div>\n<\/figure>\n<details class=\"spoiler\">\n<summary>Example of reference partitioning<\/summary>\n<div class=\"spoiler__content\">\n<p>Suppose there is an ORDERS table partitioned by intervals (months). When creating a new table, we can reference the parent table as a reference, and the child partitioned table will be divided into the same intervals. It will have as many partitions as the parent table. Moreover, when new partitions are added to the parent table, they will also appear in the child table.<\/p>\n<figure class=\"full-width\"><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/e2d\/b48\/331\/e2db483313911291d82098a298d5abb0.png\" alt=\"When applying automatic partitioning to the ORDERS table, similar partitions are created in the child table ORDER_ITEMS\" title=\"When applying automatic partitioning to the ORDERS table, similar partitions are created in the child table ORDER_ITEMS\" width=\"3200\" height=\"2458\" sizes=\"auto, (max-width: 780px) 100vw, 50vw\" srcset=\"https:\/\/habrastorage.org\/r\/w780\/getpro\/habr\/upload_files\/e2d\/b48\/331\/e2db483313911291d82098a298d5abb0.png 780w,&#10;       https:\/\/habrastorage.org\/r\/w1560\/getpro\/habr\/upload_files\/e2d\/b48\/331\/e2db483313911291d82098a298d5abb0.png 781w\" loading=\"lazy\" decode=\"async\"\/><\/p>\n<div><figcaption>When applying automatic partitioning to the ORDERS table, similar partitions are created in the child table ORDER_ITEMS<\/figcaption><\/div>\n<\/figure>\n<\/div>\n<\/details>\n<p>That&#8217;s all. Drop us a comment if you have questions.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/920564\/\"> https:\/\/habr.com\/ru\/articles\/920564\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<figure class=\"full-width\"><\/figure>\n<p>Declarative partitioning may sound complex, but in reality it\u2019s just a way to tell your database how best to organize large tables \u2014 so it can optimize queries and make maintenance easier. Let\u2019s walk through how it works and when declarative partitioning can save the day.<\/p>\n<h2>What is partitioning and when you might need it<\/h2>\n<p>Partitioning is the process of splitting a single logical table into separate physical parts (partitions). The split is done row-wise, based on the value of a specific column or set of columns called the partition key.<\/p>\n<p>For example, let\u2019s say you have a table named\u00a0<code>city<\/code>, and you partition it into\u00a0<code>city_msk<\/code>,\u00a0<code>city_spb<\/code>, and\u00a0<code>city_nsk<\/code>. In this case, the\u00a0<code>city_code<\/code>\u00a0column is the partition key.<\/p>\n<figure class=\"full-width\">\n<div><figcaption>Partitioned table <code>city<\/code><\/figcaption><\/div>\n<\/figure>\n<p>In a partitioned table like\u00a0<code>city<\/code>, no rows are actually stored in the main table \u2014 they\u2019re all stored in the partitions. Which partition a row ends up in depends on the city code. If there\u2019s no suitable partition, the row goes into a\u00a0<code>DEFAULT<\/code>\u00a0partition. If there\u2019s no default, the system throws a \u201cno partition found for row\u201d error.<\/p>\n<figure class=\"full-width\">\n<div><figcaption>Partitioned table <code>city<\/code><\/figcaption><\/div>\n<\/figure>\n<p>From the application\u2019s point of view, partitioning is transparent. All read and write operations go through the parent table. The database planner determines which partition is used for reading or writing.<\/p>\n<p>Partitioning helps:<\/p>\n<ul>\n<li>\n<p>Boost query performance: the query planner skips partitions that don\u2019t contain matching rows.<\/p>\n<\/li>\n<li>\n<p>Make large-table maintenance easier: tasks like\u00a0<code>VACUUM FULL<\/code>,\u00a0<code>REINDEX<\/code>, or\u00a0<code>pg_dump<\/code>\u00a0run faster on individual partitions than on a monolithic table.<\/p>\n<\/li>\n<li>\n<p>Manage data flexibly: for example, move historical or rarely used data to cheaper storage. More on that\u00a0in this <a href=\"https:\/\/habr.com\/ru\/companies\/postgrespro\/articles\/891878\/\" rel=\"noopener noreferrer nofollow\">article<\/a>.<\/p>\n<\/li>\n<\/ul>\n<h2>Declarative or not? Understanding declarative partitioning<\/h2>\n<p>This mechanism lets you declare from the start that a table is partitioned.<\/p>\n<p>In the\u00a0<code>create table<\/code>\u00a0statement, you specify the partitioning method (e.g.\u00a0<code>partition by range<\/code>) and list the columns that make up the partition key.<\/p>\n<p>Transactional DDL makes this a powerful tool for managing partitioned tables and enables automation.<\/p>\n<details class=\"spoiler\">\n<summary>What transactional DDL means<\/summary>\n<div class=\"spoiler__content\">\n<p>When creating a table, the following properties are guaranteed:<\/p>\n<ul>\n<li>\n<p><strong>Atomicity<\/strong>: if the transaction fails, there\u2019s no leftover junk to clean up.<\/p>\n<\/li>\n<li>\n<p><strong>Consistency<\/strong>: table creation won\u2019t break data integrity or cause conflicts.<\/p>\n<\/li>\n<li>\n<p><strong>Isolation<\/strong>: the operation runs separately from other transactions.<\/p>\n<\/li>\n<li>\n<p><strong>Durability<\/strong>: once created, the table\u2019s metadata is safely stored on disk.<\/p>\n<\/li>\n<\/ul>\n<\/div>\n<\/details>\n<pre><code class=\"pgsql\">CREATE TABLE measurement (  city_id int not null,  logdate date not null,  ) PARTITION BY RANGE (logdate);   CREATE TABLE measurement_y2006m02 PARTITION OF measurement  FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');  CREATE TABLE measurement_y2006m03 PARTITION OF measurement  FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');<\/code><\/pre>\n<p>But declarative partitioning does come with a few limitations:<\/p>\n<ul>\n<li>\n<p>All partitions must have the same set of columns as the parent table.<\/p>\n<\/li>\n<li>\n<p>The partition key must be a subset of the primary key, if one exists. That means if the table has a\u00a0<code>PRIMARY KEY<\/code>, you can only partition it by the columns that are included in that key.<\/p>\n<\/li>\n<\/ul>\n<figure class=\"full-width\">\n<div><figcaption>|PARTITIONING KEY|\u00a0\u00a0\u2282\u00a0 |PRIMARY KEY|<\/figcaption><\/div>\n<\/figure>\n<ul>\n<li>\n<p><code>before row<\/code>\u00a0triggers for\u00a0<code>insert<\/code>\u00a0cannot change which partition a row goes to.<\/p>\n<\/li>\n<li>\n<p>You cannot declaratively partition an existing table.<\/p>\n<\/li>\n<\/ul>\n<details class=\"spoiler\">\n<summary>How to partition an existing table<\/summary>\n<div class=\"spoiler__content\">\n<p>There are two options:<\/p>\n<ol>\n<li>\n<p>Attach the existing table as a historical partition to a new partitioned table<\/p>\n<\/li>\n<\/ol>\n<p>First, create a new partitioned table with the same structure:<\/p>\n<pre><code class=\"pgsql\">create table measurement_p (like measurement) partition by range (logdate);<\/code><\/pre>\n<p>And a future partition:<\/p>\n<pre><code class=\"pgsql\">create table measurements_2025 partition of measurements_p for values from ('2025-01-01') to ('2026-01-01');<\/code><\/pre>\n<p>Prepare the old table to avoid long locks:<\/p>\n<pre><code class=\"pgsql\">begin; set local statement_timeout to '1s';  alter table measurements add constraint measurements_partbound_check check (logdate &lt; '2025-01-01' and created_at is not null) not valid; commit; alter table measurements validate constraint measurements_partbound_check;<\/code><\/pre>\n<figure class=\"full-width\">\n<div><figcaption>Preparation steps<\/figcaption><\/div>\n<\/figure>\n<p>Rename the tables and attach the old one as a partition:<\/p>\n<pre><code class=\"sql\">begin; set statement_timeout to '1s'; alter table measurements rename to measurements_history; alter table measurements_p rename to measurements; alter table measurements attach partition measurements_archive   for values from (MINVALUE) to ('2025-01-01'); commit;<\/code><\/pre>\n<p>Now the system switches over in under a second. Data before Jan 1, 2025 goes to the old table, and new data goes to the partitioned one.<\/p>\n<figure class=\"full-width\">\n<div><figcaption>Attaching a historical partition to a new table<\/figcaption><\/div>\n<\/figure>\n<p>2. Fully repartition the existing table<\/p>\n<p>If you need to physically split the data, use this approach. Create a new partitioned table with a different name and define the necessary partitions.<\/p>\n<p>Copy the data from the old table:<\/p>\n<pre><code class=\"pgsql\">INSERT INTO measurement_p SELECT * FROM measurement<\/code><\/pre>\n<p>or:<\/p>\n<pre><code class=\"pgsql\">COPY TO measurement_p COPY FROM measurement<\/code><\/pre>\n<p>Since the transfer might take time, we don\u2019t want to block access to the original table. To keep things in sync, create\u00a0<code>insert<\/code>,\u00a0<code>update<\/code>, and\u00a0<code>delete<\/code>\u00a0triggers on the old table to replicate changes into the new one during the transfer.<\/p>\n<p>After the data is moved, rename the new partitioned table to match the old name.<\/p>\n<figure class=\"full-width\">\n<div><figcaption>The process of transferring data into a partitioned table<\/figcaption><\/div>\n<\/figure>\n<\/div>\n<\/details>\n<h2>Partitioning by hash, list, and range<\/h2>\n<p>Declarative partitioning supports three methods:<\/p>\n<ul>\n<li>\n<p>by hash<\/p>\n<\/li>\n<li>\n<p>by list of values<\/p>\n<\/li>\n<li>\n<p>by range<\/p>\n<\/li>\n<\/ul>\n<p>When working with partitioned tables, there\u2019s an optimization called\u00a0partition pruning. To enable pruning, the planner needs context about how the table is partitioned. The system catalog stores information about how many partitions exist, which ranges they cover, and so on. When a query is sent to the database with a predicate (a\u00a0<code>WHERE<\/code>\u00a0condition), the planner can use this metadata to exclude unnecessary partitions from the query plan.<\/p>\n<p>To enable this behavior, the parameter\u00a0<code>enable_partition_pruning<\/code>\u00a0must be set to\u00a0<code>on<\/code>.<\/p>\n<p>Let\u2019s take a closer look at the partitioning methods.<\/p>\n<h4>1. Hash partitioning<\/h4>\n<pre><code class=\"pgsql\">CREATE TABLE orders_1 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 0);\u00a0 CREATE TABLE orders_2 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 1);\u00a0 CREATE TABLE orders_3 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 2);<\/code><\/pre>\n<p>Hash partitioning is typically used when you need to split a table into equally sized partitions. Since hash function values are distributed uniformly, the rows are also distributed evenly across partitions. The partitioning key is determined by the remainder of the key\u2019s numeric hash divided by the total number of partitions.<\/p>\n<p>If you imagine all possible key values arranged along a line (the allowed value range), hash partitioning can be visualized like this:<\/p>\n<figure class=\"full-width\">\n<div><figcaption>How hash partitioning looks<\/figcaption><\/div>\n<\/figure>\n<p>Comparison operations like greater than or less than do not apply to hash values, so partition pruning works only for equality joins and queries with strict equality conditions.<\/p>\n<p>The number of partitions is fixed at creation time. While you cannot change the total count afterward, you can attach or detach partitions dynamically.<\/p>\n<h4>2. List partitioning<\/h4>\n<pre><code class=\"pgsql\">CREATE TABLE part_1 PARTITION OF city FOR VALUES IN ('MSK');\u00a0 CREATE TABLE part_2 PARTITION OF city FOR VALUES IN ('NSK');\u00a0 CREATE TABLE part_3 PARTITION OF city FOR VALUES IN ('SPB\u2019);<\/code><\/pre>\n<p>Here, the partition key matches one of the listed values.<\/p>\n<figure class=\"full-width\">\n<div><figcaption>How list partitioning looks<\/figcaption><\/div>\n<\/figure>\n<h4>3. Range partitioning<\/h4>\n<pre><code class=\"pgsql\">CREATE TABLE part_1 PARTITION OF sales FOR VALUES FROM ('2006-01-01') TO ('2010-01-01');\u00a0  CREATE TABLE part_2 PARTITION OF sales FOR VALUES FROM ('2013-01-01') TO ('2016-01-01');\u00a0  CREATE TABLE part_3 PARTITION OF sales FOR VALUES FROM ('2016-01-01') TO ('2020-01-01\u2019);\u00a0<\/code><\/pre>\n<p>Range partitioning is the most commonly used method. In this approach, key values are matched to specified intervals, which can vary in size and may have gaps between them. When creating each partition, you define the range it covers to determine which values belong to that partition.<\/p>\n<p>Each partition\u2019s range is defined by two values: a start (\u201cfrom\u201d) and an end (\u201cto\u201d). The start value is inclusive, while the end value is exclusive. Therefore, to create two contiguous partitions without gaps, the end value of the first partition should be the start value of the next one.<\/p>\n<figure class=\"full-width\">\n<div><figcaption>How range partitioning looks<\/figcaption><\/div>\n<\/figure>\n<h2>Vanilla partitioning tools<\/h2>\n<p>Let\u2019s look at\u00a0<code>attach<\/code>,\u00a0<code>detach<\/code>,\u00a0<code>default<\/code>, and multi-level partitioning.<\/p>\n<h4>Attach and detach<\/h4>\n<p><code>ATTACH<\/code>\u00a0lets you add a table as a partition to a partitioned table.<\/p>\n<p>When attaching a table as a partition, you must specify which partition key values it covers according to the partitioning method. For range partitioning \u2014 the interval, for list partitioning \u2014 the set of values, for hash partitioning \u2014 the remainder value.<\/p>\n<pre><code class=\"pgsql\">ALTER TABLE measurement ATTACH PARTITION measurement_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01' );<\/code><\/pre>\n<figure class=\"full-width\">\n<div><figcaption>Attaching a partition to a table<\/figcaption><\/div>\n<\/figure>\n<p>This operation requires a lock. When attaching a partition, a corresponding <code>CHECK<\/code> constraint is created to ensure that the partition does not contain values outside the specified boundaries. You can reduce lock time by creating this constraint manually in advance.<\/p>\n<p><code>DETACH<\/code>\u00a0removes a partition, turning it into a standalone table:<\/p>\n<pre><code class=\"pgsql\">ALTER TABLE measurement DETACH PARTITION measurement_2023;\u00a0<\/code><\/pre>\n<figure class=\"full-width\">\n<div><figcaption>Detaching a partition from a table<\/figcaption><\/div>\n<\/figure>\n<h4>Default<\/h4>\n<p>The default partition catches rows that don\u2019t match any other partition:<\/p>\n<p><code>CREATE TABLE \u2026 PARTITION OF \u2026 DEFAULT ;<\/code><\/p>\n<p><strong>Multi-level partitioning<\/strong><\/p>\n<p>Multi-level partitioning (or sub-partitioning) means that partitions themselves are partitioned tables.<\/p>\n<p>Since you cannot declaratively partition an existing table, to build a multi-level structure you must first create a partitioned table and then use\u00a0<code>attach<\/code>\u00a0to add it as a partition to another partitioned table. The partitioning methods can differ between levels.<\/p>\n<figure class=\"full-width\">\n<div><figcaption>How multi-level partitioning looks<\/figcaption><\/div>\n<\/figure>\n<h2>Replicating partitioned tables<\/h2>\n<p>Physical replication creates a complete copy of the database on a standby server. Logical replication is more flexible: you create a publication on selected tables on one server and subscribe to it from others.<\/p>\n<p>Logical replication is typically used in two cases:<\/p>\n<ul>\n<li>\n<p>Data consolidation. Suppose you have a central server and <\/p>\n<\/li>\n<\/ul>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-473010","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/473010","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=473010"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/473010\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=473010"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=473010"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=473010"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}