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
+74-36Lines changed: 74 additions & 36 deletions
Original file line number
Diff line number
Diff line change
@@ -62,77 +62,94 @@ 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
-
partitions_countINTEGER)
67
+
partitions_countINTEGER,
68
+
partition_nameTEXT DEFAULTNULL)
68
69
```
69
-
Performs HASH partitioning for`relation` by integer key`attribute`.Creates`partitions_count` partitionsand trigger on INSERT. Allthe data will be automatically copied from the parent to partitions.
70
+
Performs HASH partitioning for`relation` by integer key`attribute`.The`partitions_count`parameter specifies the number ofpartitionsto create; it cannot be changed afterwards. If`partition_data` is`true` then allthe data will be automatically copied from the parenttableto 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.
70
71
71
72
```plpgsql
72
-
create_range_partitions(relationTEXT,
73
-
attributeTEXT,
74
-
start_value ANYELEMENT,
75
-
interval ANYELEMENT,
76
-
premakeINTEGER DEFAULTNULL)
73
+
create_range_partitions(relation REGCLASS,
74
+
attributeTEXT,
75
+
start_value ANYELEMENT,
76
+
interval ANYELEMENT,
77
+
countINTEGER DEFAULTNULL
78
+
partition_dataBOOLEAN DEFAULT true)
79
+
80
+
create_range_partitions(relationTEXT,
81
+
attributeTEXT,
82
+
start_value ANYELEMENT,
83
+
interval INTERVAL,
84
+
countINTEGER DEFAULTNULL,
85
+
partition_dataBOOLEAN DEFAULT true)
86
+
```
87
+
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).
77
88
78
-
create_range_partitions(relationTEXT,
79
-
attributeTEXT,
80
-
start_value ANYELEMENT,
81
-
interval INTERVAL,
82
-
premakeINTEGER DEFAULTNULL)
89
+
```plpgsql
90
+
create_partitions_from_range(relation REGCLASS,
91
+
attributeTEXT,
92
+
start_value ANYELEMENT,
93
+
end_value ANYELEMENT,
94
+
interval ANYELEMENT,
95
+
partition_dataBOOLEAN DEFAULT true)
96
+
97
+
create_partitions_from_range(relation REGCLASS,
98
+
attributeTEXT,
99
+
start_value ANYELEMENT,
100
+
end_value ANYELEMENT,
101
+
interval INTERVAL,
102
+
partition_dataBOOLEAN DEFAULT true)
83
103
```
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.
104
+
Performs RANGE-partitioningfrom specified rangefor`relation` by partitioning key`attribute`.
85
105
86
-
```plpgsql
87
-
create_partitions_from_range(relationTEXT,
88
-
attributeTEXT,
89
-
start_value ANYELEMENT,
90
-
end_value ANYELEMENT,
91
-
interval ANYELEMENT)
106
+
###Data migration
92
107
93
-
create_partitions_from_range(relationTEXT,
94
-
attributeTEXT,
95
-
start_value ANYELEMENT,
96
-
end_value ANYELEMENT,
97
-
interval INTERVAL)
108
+
```plpgsql
109
+
partition_data_concurrent(relation REGCLASS)
98
110
```
99
-
Performs RANGE-partitioning from specified range for`relation` by partitioning key`attribute`. Data will be copiedtopartitions as well.
111
+
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
112
101
113
###Triggers
102
114
```plpgsql
103
-
create_hash_update_trigger(parentTEXT)
115
+
create_hash_update_trigger(parentREGCLASS)
104
116
```
105
117
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
118
```plpgsql
107
-
create_range_update_trigger(parentTEXT)
119
+
create_range_update_trigger(parentREGCLASS)
108
120
```
109
121
Same as above, but for a RANGE-partitioned table.
110
122
111
123
###Post-creation partition management
112
124
```plpgsql
113
-
split_range_partition(partitionTEXT, value ANYELEMENT)
Detach partition from the existing RANGE-partitioned relation.
156
173
157
174
```plpgsql
158
-
disable_partitioning(relationTEXT)
175
+
disable_pathman_for(relationTEXT)
159
176
```
160
177
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
178
179
+
```plpgsql
180
+
drop_partitions(parent REGCLASS,
181
+
delete_dataBOOLEAN DEFAULT FALSE)
182
+
```
183
+
Drop partitions of the`parent` table. If`delete_data` is`false` then the data is copied to the parent table first. Default is`false`.
184
+
185
+
186
+
###Additional parameters
187
+
188
+
```plpgsql
189
+
enable_parent(relation REGCLASS)
190
+
disable_parent(relation REGCLASS)
191
+
```
192
+
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.
193
+
194
+
```plpgsql
195
+
enable_auto(relation REGCLASS)
196
+
disable_auto(relation REGCLASS)
197
+
```
198
+
Enable/disable auto partition propagation (only for RANGE partitioning). It is enabled by default.
199
+
162
200
##Custom plan nodes
163
201
`pg_pathman` provides a couple of[custom plan nodes](https://wiki.postgresql.org/wiki/CustomScanAPI) which aim to reduce execution time, namely: