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
+12-9Lines changed: 12 additions & 9 deletions
Original file line number
Diff line number
Diff line change
@@ -12,17 +12,27 @@ The extension is compatible with:
12
12
* Postgres Pro Standard 9.5, 9.6;
13
13
* Postgres Pro Enterprise;
14
14
15
-
By the way, we have a growing Wiki [out there](https://github.com/postgrespro/pg_pathman/wiki).
15
+
Take a look at our Wiki [out there](https://github.com/postgrespro/pg_pathman/wiki).
16
16
17
17
## Overview
18
-
**Partitioning** means splitting one large table into smaller pieces. Each row in such table is moved to a single partition according to the partitioning key. PostgreSQL supports partitioning via table inheritance: each partition must be created as a child table with CHECK CONSTRAINT. For example:
18
+
**Partitioning** means splitting one large table into smaller pieces. Each row in such table is moved to a single partition according to the partitioning key. PostgreSQL <= 10 supports partitioning via table inheritance: each partition must be created as a child table with CHECK CONSTRAINT:
19
19
20
20
```plpgsql
21
21
CREATETABLEtest (id SERIALPRIMARY KEY, title TEXT);
22
22
CREATETABLEtest_1 (CHECK ( id >=100AND id <200 )) INHERITS (test);
23
23
CREATETABLEtest_2 (CHECK ( id >=200AND id <300 )) INHERITS (test);
24
24
```
25
25
26
+
PostgreSQL 10 provides native partitioning:
27
+
28
+
```plpgsql
29
+
CREATETABLEtest(id int4, value text) PARTITION BY RANGE(id);
30
+
CREATETABLEtest_1 PARTITION OF test FOR VALUESFROM (1) TO (10);
31
+
CREATETABLEtest_2 PARTITION OF test FOR VALUESFROM (10) TO (20);
32
+
```
33
+
34
+
It's not so different from the classic approach; there are implicit check constraints, and most of its limitations are still relevant.
35
+
26
36
Despite the flexibility, this approach forces the planner to perform an exhaustive search and to check constraints on each partition to determine whether it should be present in the plan or not. Large amount of partitions may result in significant planning overhead.
27
37
28
38
The `pg_pathman` module features partition managing functions and optimized planning mechanism which utilizes knowledge of the partitions' structure. It stores partitioning configuration in the `pathman_config` table; each row contains a single entry for a partitioned table (relation name, partitioning column and its type). During the initialization stage the `pg_pathman` module caches some information about child partitions in the shared memory, which is used later for plan construction. Before a SELECT query is executed, `pg_pathman` traverses the condition tree in search of expressions like:
@@ -60,13 +70,6 @@ More interesting features are yet to come. Stay tuned!
0 commit comments