Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commitcac452e

Browse files
committed
Merge branch 'pathman_pgpro9_5' into PGPRO9_5
2 parents340b392 +9f570e8 commitcac452e

22 files changed

+6963
-3
lines changed

‎contrib/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -55,7 +55,8 @@ SUBDIRS = \
5555
tsm_system_time\
5656
tsearch2\
5757
unaccent\
58-
vacuumlo
58+
vacuumlo\
59+
pg_pathman
5960

6061
ifeq ($(with_openssl),yes)
6162
SUBDIRS += sslinfo

‎contrib/pg_pathman/.gitignore

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
*.o
2+
*.so
3+
pg_pathman--*.sql

‎contrib/pg_pathman/Makefile

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
# contrib/pg_pathman/Makefile
2+
3+
4+
MODULE_big = pg_pathman
5+
OBJS = init.o pg_pathman.o dsm_array.o rangeset.o pl_funcs.o worker.o$(WIN32RES)
6+
7+
EXTENSION = pg_pathman
8+
EXTVERSION = 0.1
9+
DATA_built =$(EXTENSION)--$(EXTVERSION).sql
10+
PGFILEDESC = "pg_pathman - partitioning tool"
11+
12+
REGRESS = pg_pathman
13+
EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
14+
EXTRA_CLEAN =$(EXTENSION)--$(EXTVERSION).sql ./isolation_output
15+
16+
ifdefUSE_PGXS
17+
PG_CONFIG = pg_config
18+
PGXS :=$(shell$(PG_CONFIG) --pgxs)
19+
include$(PGXS)
20+
else
21+
subdir = contrib/pg_pathman
22+
top_builddir = ../..
23+
include$(top_builddir)/src/Makefile.global
24+
include$(top_srcdir)/contrib/contrib-global.mk
25+
endif
26+
27+
$(EXTENSION)--$(EXTVERSION).sql: init.sql hash.sql range.sql
28+
cat$^>$@
29+
30+
ISOLATIONCHECKS=insert_trigger
31+
32+
submake-isolation:
33+
$(MAKE) -C$(top_builddir)/src/test/isolation all
34+
35+
isolationcheck: | submake-isolation
36+
$(MKDIR_P) isolation_output
37+
$(pg_isolation_regress_check)\
38+
--temp-config=$(top_srcdir)/$(subdir)/conf.add\
39+
--outputdir=./isolation_output\
40+
$(ISOLATIONCHECKS)

‎contrib/pg_pathman/README.md

Lines changed: 277 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,277 @@
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.

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp