You signed in with another tab or window.Reload to refresh your session.You signed out in another tab or window.Reload to refresh your session.You switched accounts on another tab or window.Reload to refresh your session.Dismiss alert
Copy file name to clipboardExpand all lines: README.md
+71-34Lines changed: 71 additions & 34 deletions
Original file line number
Diff line number
Diff line change
@@ -62,77 +62,93 @@ Done! Now it's time to setup your partitioning schemes.
62
62
63
63
###Partition creation
64
64
```plpgsql
65
-
create_hash_partitions(relationTEXT,
65
+
create_hash_partitions(relationREGCLASS,
66
66
attributeTEXT,
67
67
partitions_countINTEGER)
68
68
```
69
69
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.
70
70
71
71
```plpgsql
72
-
create_range_partitions(relationTEXT,
73
-
attributeTEXT,
74
-
start_value ANYELEMENT,
75
-
interval ANYELEMENT,
76
-
premakeINTEGER DEFAULTNULL)
72
+
create_range_partitions(relation REGCLASS,
73
+
attributeTEXT,
74
+
start_value ANYELEMENT,
75
+
interval ANYELEMENT,
76
+
countINTEGER DEFAULTNULL
77
+
partition_dataBOOLEAN DEFAULT true)
78
+
79
+
create_range_partitions(relationTEXT,
80
+
attributeTEXT,
81
+
start_value ANYELEMENT,
82
+
interval INTERVAL,
83
+
countINTEGER DEFAULTNULL,
84
+
partition_dataBOOLEAN DEFAULT true)
85
+
```
86
+
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,`count` is the number of premade partitions (if not set then pathman tries to determine it based on attribute values). If`partition_data` is`true` then all the data will be automatically copied from the parent table to partitions. Note that data migration may took a while to finish and the table will be locked until transaction commits. See`partition_data_concurrent()` for a lock-free way to migrate data.
77
87
78
-
create_range_partitions(relationTEXT,
79
-
attributeTEXT,
80
-
start_value ANYELEMENT,
81
-
interval INTERVAL,
82
-
premakeINTEGER DEFAULTNULL)
88
+
```plpgsql
89
+
create_partitions_from_range(relation REGCLASS,
90
+
attributeTEXT,
91
+
start_value ANYELEMENT,
92
+
end_value ANYELEMENT,
93
+
interval ANYELEMENT,
94
+
partition_dataBOOLEAN DEFAULT true)
95
+
96
+
create_partitions_from_range(relation REGCLASS,
97
+
attributeTEXT,
98
+
start_value ANYELEMENT,
99
+
end_value ANYELEMENT,
100
+
interval INTERVAL,
101
+
partition_dataBOOLEAN DEFAULT true)
83
102
```
84
-
Performs RANGEpartitioning 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.
103
+
Performs RANGE-partitioningfrom specified rangefor`relation` by partitioning key`attribute`.
85
104
86
-
```plpgsql
87
-
create_partitions_from_range(relationTEXT,
88
-
attributeTEXT,
89
-
start_value ANYELEMENT,
90
-
end_value ANYELEMENT,
91
-
interval ANYELEMENT)
105
+
###Data migration
92
106
93
-
create_partitions_from_range(relationTEXT,
94
-
attributeTEXT,
95
-
start_value ANYELEMENT,
96
-
end_value ANYELEMENT,
97
-
interval INTERVAL)
107
+
```plpgsql
108
+
partition_data_concurrent(relation REGCLASS)
98
109
```
99
-
Performs RANGE-partitioning from specified range for`relation` by partitioning key`attribute`. Data will be copiedtopartitions as well.
110
+
Starts a background worker to copy data from parent table to partitions. The worker utilize short transactionstocopy small bunches of data (up to 10K rows per transaction) and thus doesn't significantly interfere with users activity.
100
111
101
112
###Triggers
102
113
```plpgsql
103
-
create_hash_update_trigger(parentTEXT)
114
+
create_hash_update_trigger(parentREGCLASS)
104
115
```
105
116
Creates the trigger on UPDATE for HASH partitions. The UPDATE trigger isn't created by default because of the overhead. It's useful in cases when the key attribute might change.
106
117
```plpgsql
107
-
create_range_update_trigger(parentTEXT)
118
+
create_range_update_trigger(parentREGCLASS)
108
119
```
109
120
Same as above, but for a RANGE-partitioned table.
110
121
111
122
###Post-creation partition management
112
123
```plpgsql
113
-
split_range_partition(partitionTEXT, value ANYELEMENT)
Detach partition from the existing RANGE-partitioned relation.
156
172
157
173
```plpgsql
158
-
disable_partitioning(relationTEXT)
174
+
disable_pathman_for(relationTEXT)
159
175
```
160
176
Permanently disable`pg_pathman` partitioning mechanism for the specified parent table and remove the insert trigger if it exists. All partitions and data remain unchanged.
161
177
178
+
```plpgsql
179
+
drop_partitions(parent REGCLASS,
180
+
delete_dataBOOLEAN DEFAULT FALSE)
181
+
```
182
+
Drop partitions of the`parent` table. If`delete_data` is`false` then the data is copied to the parent table first. Default is`false`.
183
+
184
+
185
+
###Additional parameters
186
+
187
+
```plpgsql
188
+
enable_parent(relation REGCLASS)
189
+
disable_parent(relation REGCLASS)
190
+
```
191
+
Include/exclude parent table into/from query plan. In original PostgreSQL planner parent table is always included into query plan even if it's empty which can lead to additional overhead. You can use`disable_parent()` if you are never going to use parent table as a storage. Default value depends on the`partition_data` parameter that was specified during initial partitioning in`create_range_partitions()` or`create_partitions_from_range()` functions. If the`partition_data` parameter was`true` then all data have already been migrated to partitions and parent table disabled. Otherwise it is enabled.
192
+
193
+
```plpgsql
194
+
enable_auto(relation REGCLASS)
195
+
disable_auto(relation REGCLASS)
196
+
```
197
+
Enable/disable auto partition propagation (only for RANGE partitioning). It is enabled by default.
198
+
162
199
##Custom plan nodes
163
200
`pg_pathman` provides a couple of[custom plan nodes](https://wiki.postgresql.org/wiki/CustomScanAPI) which aim to reduce execution time, namely: