|
| 1 | +#pg_pathman |
| 2 | + |
| 3 | +The`pg_pathman` module provides optimized partitioning mechanism and functions to manage partitions. |
| 4 | + |
| 5 | +##pg_pathman Concepts |
| 6 | + |
| 7 | +Partitioning refers to splitting one large table into smaller pieces. Each row in such table assigns to a single partition based on partitioning key. PostgreSQL supports partitioning via table inheritance. Each partition must be created as child table with CHECK CONSTRAINT. For example: |
| 8 | + |
| 9 | +``` |
| 10 | +CREATE TABLE test (id SERIAL PRIMARY KEY, title TEXT); |
| 11 | +CREATE TABLE test_1 (CHECK ( id >= 100 AND id < 200 )) INHERITS (test); |
| 12 | +CREATE TABLE test_2 (CHECK ( id >= 200 AND id < 300 )) INHERITS (test); |
| 13 | +``` |
| 14 | + |
| 15 | +Despite the flexibility this approach forces the planner to perform an exhaustive search and check constraints for each partition to determine which one should present in the plan. If the number of partitions is large the overhead may be significant. |
| 16 | + |
| 17 | +The`pg_pathman` module provides functions to manage partitions and partitioning mechanism optimized based on knowledge of the partitions structure. It stores partitioning configuration in the`pathman_config` table, each row of which contains single entry for partitioned table (relation name, partitioning key and type). During initialization the`pg_pathman` module caches information about child partitions in shared memory in form convenient to perform rapid search. When SELECT query executes`pg_pathman` analyzes conditions tree looking for conditions like: |
| 18 | + |
| 19 | +``` |
| 20 | +VARIABLE OP CONST |
| 21 | +``` |
| 22 | +where`VARIABLE` is partitioning key,`OP` is comparison operator (supported operators are =, <, <=, >, >=),`CONST` is scalar value. For example: |
| 23 | + |
| 24 | +``` |
| 25 | +WHERE id = 150 |
| 26 | +``` |
| 27 | + |
| 28 | +Based on partitioning type and operator the`pg_pathman` searches corresponding partitions and builds the plan. Current version of`pg_pathman` supports two partitioning types: |
| 29 | + |
| 30 | +* RANGE - maps data to partitions based on ranges of partitioning key. Optimization is achieved by using binary search algorithm. |
| 31 | +* HASH - maps rows to partitions based on hash function values (only INTEGER attributes at the moment); |
| 32 | + |
| 33 | +##Roadmap |
| 34 | + |
| 35 | +* Execute time sections selections (useful for nested loops and prepared statements); |
| 36 | +* Optimization of ordering output from patitioned tables (useful for merge join and order by); |
| 37 | +* Optimization of hash join when both tables are patitioned by join key; |
| 38 | +* LIST-patitioning; |
| 39 | +* HASH-patitioning by non integer attribtes. |
| 40 | + |
| 41 | +##Installation |
| 42 | + |
| 43 | +To install pg_pathman run in psql: |
| 44 | +``` |
| 45 | +CREATE EXTENSION pg_pathman; |
| 46 | +``` |
| 47 | +Then modify shared_preload_libraries parameter in postgres.conf as following: |
| 48 | +``` |
| 49 | +shared_preload_libraries = 'pg_pathman' |
| 50 | +``` |
| 51 | +It will require to restart the PostgreSQL instance. |
| 52 | + |
| 53 | +##pg_pathman Functions |
| 54 | + |
| 55 | +###Partitions Creation |
| 56 | +``` |
| 57 | +create_hash_partitions( |
| 58 | + relation TEXT, |
| 59 | + attribute TEXT, |
| 60 | + partitions_count INTEGER) |
| 61 | +``` |
| 62 | +Performs HASH partitioning for`relation` by integer key`attribute`. Creates`partitions_count` partitions and trigger on INSERT. All the data will be automatically copied from the parent to partitions. |
| 63 | + |
| 64 | +``` |
| 65 | +create_range_partitions( |
| 66 | + relation TEXT, |
| 67 | + attribute TEXT, |
| 68 | + start_value ANYELEMENT, |
| 69 | + interval ANYELEMENT, |
| 70 | + premake INTEGER DEFAULT NULL) |
| 71 | +
|
| 72 | +create_range_partitions( |
| 73 | + relation TEXT, |
| 74 | + attribute TEXT, |
| 75 | + start_value ANYELEMENT, |
| 76 | + interval INTERVAL, |
| 77 | + premake INTEGER DEFAULT NULL) |
| 78 | +``` |
| 79 | +Performs RANGE partitioning for`relation` by partitioning key`attribute`.`start_value` argument specifies initial value,`interval` sets the range of values in a single partition,`premake` is the number of premade partitions (if not set then pathman tries to determine it based on attribute values). All the data will be automatically copied from the parent to partitions. |
| 80 | + |
| 81 | +``` |
| 82 | +create_partitions_from_range( |
| 83 | + relation TEXT, |
| 84 | + attribute TEXT, |
| 85 | + start_value ANYELEMENT, |
| 86 | + end_value ANYELEMENT, |
| 87 | + interval ANYELEMENT) |
| 88 | +
|
| 89 | +create_partitions_from_range( |
| 90 | + relation TEXT, |
| 91 | + attribute TEXT, |
| 92 | + start_value ANYELEMENT, |
| 93 | + end_value ANYELEMENT, |
| 94 | + interval INTERVAL) |
| 95 | +``` |
| 96 | +Performs RANGE-partitioning from specified range for`relation` by partitioning key`attribute`. Data will be copied to partitions as well. |
| 97 | + |
| 98 | +###Triggers |
| 99 | +``` |
| 100 | +create_hash_update_trigger(parent TEXT) |
| 101 | +``` |
| 102 | +Creates the trigger on UPDATE for HASH partitions. The UPDATE trigger isn't created by default because of overhead. It is useful in cases when key attribute could be changed. |
| 103 | +``` |
| 104 | +create_range_update_trigger(parent TEXT) |
| 105 | +``` |
| 106 | +Same as above for RANGE partitioned table. |
| 107 | + |
| 108 | +###Partitions management |
| 109 | +``` |
| 110 | +split_range_partition(partition TEXT, value ANYELEMENT) |
| 111 | +``` |
| 112 | +Splits RANGE`partition` in two by`value`. |
| 113 | +``` |
| 114 | +merge_range_partitions(partition1 TEXT, partition2 TEXT) |
| 115 | +``` |
| 116 | +Merge two adjacent RANGE partitions. Data from`partition2` is copied to`partition1`. Then the`partition2` is removed. |
| 117 | +``` |
| 118 | +append_range_partition(p_relation TEXT) |
| 119 | +``` |
| 120 | +Appends new RANGE partition and returns |
| 121 | +``` |
| 122 | +prepend_range_partition(p_relation TEXT) |
| 123 | +``` |
| 124 | +Prepends new RANGE partition. |
| 125 | + |
| 126 | +``` |
| 127 | +add_range_partition( |
| 128 | + relation TEXT, |
| 129 | + start_value ANYELEMENT, |
| 130 | + end_value ANYELEMENT) |
| 131 | +``` |
| 132 | +Creates new RANGE partition for`relation` with specified values range. |
| 133 | + |
| 134 | +``` |
| 135 | +drop_range_partition(partition TEXT) |
| 136 | +``` |
| 137 | +Drops RANGE partition and all its data. |
| 138 | + |
| 139 | +``` |
| 140 | +attach_range_partition( |
| 141 | + relation TEXT, |
| 142 | + partition TEXT, |
| 143 | + start_value ANYELEMENT, |
| 144 | + end_value ANYELEMENT) |
| 145 | +``` |
| 146 | +Attaches partition to existing RANGE partitioned relation. The table being attached must have exact same structure as the parent one. |
| 147 | + |
| 148 | +``` |
| 149 | +detach_range_partition(partition TEXT) |
| 150 | +``` |
| 151 | +Detaches partition from existing RANGE partitioned relation. |
| 152 | + |
| 153 | + |
| 154 | +``` |
| 155 | +disable_partitioning(relation TEXT) |
| 156 | +``` |
| 157 | +Disables`pg_pathman` partitioning mechanism for the specified parent table and removes an insert trigger. Partitions itself remain unchanged. |
| 158 | + |
| 159 | +##Examples |
| 160 | +###HASH |
| 161 | +Consider an example of HASH partitioning. First create a table with some integer column: |
| 162 | +``` |
| 163 | +CREATE TABLE items ( |
| 164 | + id SERIAL PRIMARY KEY, |
| 165 | + name TEXT, |
| 166 | + code BIGINT); |
| 167 | +
|
| 168 | +INSERT INTO items (id, name, code) |
| 169 | +SELECT g, md5(g::text), random() * 100000 |
| 170 | +FROM generate_series(1, 100000) as g; |
| 171 | +``` |
| 172 | +If partitions are supposed to have indexes, then they should be created for parent table before partitioning. In this case pg_pathman will automaticaly create indexes for partitions. Then run create_hash_partitions() function with appropriate arguments: |
| 173 | +``` |
| 174 | +SELECT create_hash_partitions('items', 'id', 100); |
| 175 | +``` |
| 176 | +This will create new partitions and move the data from parent to partitions. |
| 177 | +Here is an example of the query with filtering by partitioning key and its plan: |
| 178 | +``` |
| 179 | +SELECT * FROM items WHERE id = 1234; |
| 180 | + id | name | code |
| 181 | +------+----------------------------------+------ |
| 182 | + 1234 | 81dc9bdb52d04dc20036dbd8313ed055 | 1855 |
| 183 | +(1 row) |
| 184 | +
|
| 185 | +EXPLAIN SELECT * FROM items WHERE id = 1234; |
| 186 | + QUERY PLAN |
| 187 | +------------------------------------------------------------------------------------ |
| 188 | + Append (cost=0.28..8.29 rows=0 width=0) |
| 189 | + -> Index Scan using items_34_pkey on items_34 (cost=0.28..8.29 rows=0 width=0) |
| 190 | + Index Cond: (id = 1234) |
| 191 | +``` |
| 192 | +Note that pg_pathman exludes parent table from the query plan. To access parent table use ONLY modifier: |
| 193 | +``` |
| 194 | +EXPLAIN SELECT * FROM ONLY items; |
| 195 | + QUERY PLAN |
| 196 | +------------------------------------------------------ |
| 197 | + Seq Scan on items (cost=0.00..0.00 rows=1 width=45) |
| 198 | +``` |
| 199 | +###RANGE |
| 200 | +Consider an example of RANGE partitioning. Let's create a table to store log data: |
| 201 | +``` |
| 202 | +CREATE TABLE journal ( |
| 203 | + id SERIAL, |
| 204 | + dt TIMESTAMP NOT NULL, |
| 205 | + level INTEGER, |
| 206 | + msg TEXT |
| 207 | +); |
| 208 | +CREATE INDEX ON journal(dt); |
| 209 | +
|
| 210 | +INSERT INTO journal (dt, level, msg) |
| 211 | +SELECT g, random()*6, md5(g::text) |
| 212 | +FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 minute') as g; |
| 213 | +``` |
| 214 | +Run create_range_partitions() function to create partitions so that each partition would contain data for one day: |
| 215 | +``` |
| 216 | +SELECT create_range_partitions('journal', 'dt', '2015-01-01'::date, '1 day'::interval); |
| 217 | +``` |
| 218 | +It will create 365 partitions and move the data from parent to partitions. |
| 219 | + |
| 220 | +New partitions are appended automaticaly by insert trigger. But it can be done manually with the following functions: |
| 221 | +``` |
| 222 | +SELECT add_range_partition('journal', '2016-01-01'::date, '2016-01-07'::date); |
| 223 | +SELECT append_range_partition('journal'); |
| 224 | +``` |
| 225 | +The first one creates partition with specified range. The second one creates partition with default interval and appends it to the partition list. It is also possible to attach an existing table as partition. For example we may want to attach an archive table (or even foreign table from another server) for outdated data: |
| 226 | + |
| 227 | +``` |
| 228 | +CREATE FOREIGN TABLE journal_archive ( |
| 229 | + id INTEGER NOT NULL, |
| 230 | + dt TIMESTAMP NOT NULL, |
| 231 | + level INTEGER, |
| 232 | + msg TEXT |
| 233 | +) SERVER archive_server; |
| 234 | +
|
| 235 | +SELECT attach_range_partition('journal', 'journal_archive', '2014-01-01'::date, '2015-01-01'::date); |
| 236 | +``` |
| 237 | +>Important: the structure of the table being attched must exactly match the parent. |
| 238 | +
|
| 239 | +To merge to adjacent partitions use function: |
| 240 | +``` |
| 241 | +SELECT merge_range_partitions('journal_archive', 'journal_1'); |
| 242 | +``` |
| 243 | +To split partition by value use function: |
| 244 | +``` |
| 245 | +SELECT split_range_partition('journal_366', '2016-01-03'::date); |
| 246 | +``` |
| 247 | +To detach partition use: |
| 248 | +``` |
| 249 | +SELECT detach_range_partition('journal_archive'); |
| 250 | +``` |
| 251 | + |
| 252 | +Here is an example of the query with filtering by partitioning key and its plan: |
| 253 | +``` |
| 254 | +SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03'; |
| 255 | + id | dt | level | msg |
| 256 | +--------+---------------------+-------+---------------------------------- |
| 257 | + 217441 | 2015-06-01 00:00:00 | 2 | 15053892d993ce19f580a128f87e3dbf |
| 258 | + 217442 | 2015-06-01 00:01:00 | 1 | 3a7c46f18a952d62ce5418ac2056010c |
| 259 | + 217443 | 2015-06-01 00:02:00 | 0 | 92c8de8f82faf0b139a3d99f2792311d |
| 260 | + ... |
| 261 | +(2880 rows) |
| 262 | +
|
| 263 | +EXPLAIN SELECT * FROM journal WHERE dt >= '2015-06-01' AND dt < '2015-06-03'; |
| 264 | + QUERY PLAN |
| 265 | +------------------------------------------------------------------ |
| 266 | + Append (cost=0.00..58.80 rows=0 width=0) |
| 267 | + -> Seq Scan on journal_152 (cost=0.00..29.40 rows=0 width=0) |
| 268 | + -> Seq Scan on journal_153 (cost=0.00..29.40 rows=0 width=0) |
| 269 | +(3 rows) |
| 270 | +``` |
| 271 | + |
| 272 | +###Disable pg_pathman |
| 273 | +To disable pg_pathman for some previously partitioned table use disable_partitioning() function: |
| 274 | +``` |
| 275 | +SELECT disable_partitioning('range_rel'); |
| 276 | +``` |
| 277 | +All sections and data will stay available and will be handled by standard PostgreSQL partitioning mechanism. |