PostgreSQL 10 partitioning and more

Many of the PostgreSQL 10 version achievements have a registration in the Partitioning section. And this is true: it is obvious that in the transition from 9.6 to 10 there was a powerful technological leap. In previous versions, sections were built and managed by them, by the means that already existed: the inheritance mechanism with all its limitations and inconveniences.

In version 10, they moved to more specialized mechanisms and more familiar (including for Oracle users, and this has to be considered) syntax. This jump in the transition from 10 to version 11 was supposed to be supported by important additions that should expand the functionality and improve the performance of operations using partitioning. But from the results of the last komitfest (it ended on April 8) it is clear that not all of our plans were able to be brought to working condition, which means that not everything will fall into version 11.

In addition, over the past couple of years, the pg_pathman module has been developed in parallel in Postgres Professional. Some important features intersected, some remained unique to PostgreSQL and pg_pathman (which works with the vanilla version, that is, PostgreSQL 10 + pg_pathman already gives quite an impressive amount of functionality). This will be a separate article. Comments related to version 11 and to pg_pathman are italicized for convenience.

This article is a revised and updated part of the book Nouveaulités de PostgreSQL 10. (c) Dalibo, translated from the French by Igor Levshin ( original ). Examples from the book are checked, sometimes adapted and localized for greater clarity.

Old partitioning approach

Prior to version 10, partitioning in PostgreSQL was based on the mechanism of table inheritance. Child tables are created as heirs of one - the parent. In this case, a request to read the parent table means reading the data not only of the parent, but also of the child tables. Subsequent additions in PostgreSQL made it so that reading child tables does not occur when integrity constraints ensure that these sections do not contain the data you are looking for. Such selective reading is therefore determined by the optimizer.

Recording happens differently. Inserting into the parent table is not automatically redirected to the corresponding child tables: you must add a trigger that will cancel the insertion into the parent table, redirecting the data to the correct child table. You cannot update values ​​in columns with a split key. Finally, the deletion happens correctly automatically.

All this requires a lot of work when creating sections. Managing sections is not always easy. It is necessary to ensure that the sections were created correctly from the very beginning so that the insert triggers could work correctly.

Among other inconveniences mention problems with indexes. It is not possible to create a global index (that is, an index on several tables), it is also impossible to create a global primary key on a partitioned table. In essence, this means that it is impossible to limit uniqueness.

In other words, this mechanism may be interesting in some very specific cases, and you need to have good guarantees that it will not create other problems, especially in performance. In all other cases, it is better to do without such partitioning.

New approach to partitioning

Version 10 introduced a new partitioning system, based on the infrastructure that already existed in PostgreSQL.

The goal of the developers was to simplify the creation and administration of partitioned tables. To existing SQL statements (for example, CREATE TABLE and ALTER TABLE ), special offers have been added for creating, attaching / detaching sections. Here is a list of changes.

• Creating and administering sections is simplified by integrating into the database engine.
• Triggers are no longer needed. Now:
  
  • insertion speeds up
  • automatic redirection of the inserted data to the correct section appeared
  • an error is generated if sent to the wrong section
• Now when working with sections:
  
  • can attach / detach sections
  • there are obvious limitations to the integrity of the sections
  • possible partitioning by expression in the partitioning key
  • you can create subsections
• Changes in the system directory:
  
  • new columns in pg_class
  • new pg_partitioned_table directory

It is easy to make sure that the creation of sections is simplified: it is no longer necessary to create special functions-triggers to control inserts and updates (an example where the old-style triggers are created is at the end of the article). The data is now automatically routed by functions created when a section is defined. In the case when a suitable section is not found for the input data, an error is simply generated. Such automatic routing is not only convenient, it also speeds up the insertion.

The pg_class directory pg_class been modified and now contains the following information:

• is the table a section (if yes, then: relispartition = 't' )
• is the table partitioned (if yes, then: relkind = 'p' ) or is it a regular table ( relkind = 't' )
• internal representation of section boundaries ( relpartbound )

The pg_partitioned_table directory contains the following columns:

*) For example, values ​​1 and 3 will mean that the partitioning key is the first and third columns of the table. A zero in this array means that the corresponding part of the partitioning key is an expression, and not a reference to a single column.
**) For partitioning or 0 if the data type of this column is not sortable.
***) Expression trees (in the nodeToString () view) for parts of the split key that are not simple column references. This list contains one element for each zero value in partattrs. The value can be NULL if all parts of the partitioning key are simple column indications.

Types of sectioning

• by the list
• by range
• by hash
• by expression
• by composite key
• with subsections

PostgreSQL 10 supports everything except the third. We will now focus on the first two.

Listing of values

Create the main table and its sections:

 habr_10=# CREATE TABLE parti_1(c1 integer, c2 text) PARTITION BY LIST (c1); CREATE TABLE habr_10=# CREATE TABLE parti_1_a PARTITION OF parti_1 FOR VALUES IN (1, 2, 3); CREATE TABLE habr_10=# CREATE TABLE parti_1_b PARTITION OF parti_1 FOR VALUES IN (4, 5); CREATE TABLE 

We can disconnect and connect sections:

Disconnect:

 habr_10=# ALTER TABLE parti_1 DETACH PARTITION parti_1_a; 

Now it is an independent table. Attach it back:

 habr_10=# ALTER TABLE parti_1 ATTACH PARTITION parti_1_a FOR VALUES IN (1, 2, 3); 

Fill it with data. First try to enter a value not from the list. If there is no partition corresponding to the partitioning key, an error is generated:

 habr_10=# INSERT INTO parti_1 VALUES (0); ERROR: no PARTITION OF relation "parti_1" found for row DETAIL: Partition key of the failing row contains (c1) = (0). habr_10=# INSERT INTO parti_1 VALUES (6); ERROR: no PARTITION OF relation "parti_1" found for row DETAIL: Partition key of the failing row contains (c1) = (6). 

Now we enter the correct data:

 habr_10=# INSERT INTO parti_1 VALUES (1); INSERT 0 1 habr_10=# INSERT INTO parti_1 VALUES (2); INSERT 0 1 habr_10=# INSERT INTO parti_1 VALUES (5); INSERT 0 1 habr_10=# SELECT * FROM parti_1_a; c1 | c2 ----+---- 1 | 2 | (2 rows) habr_10=# SELECT * FROM parti_1_b; c1 | c2 ----+---- 5 | (1 row) 

In the process of inserting data is correctly distributed into sections. The parent partitioned table is empty: no data is stored in the parent partitioned table. You can verify this by using the SELECT statement with an ONLY statement:

 habr_10=# SELECT * FROM ONLY parti_1; c1 | c2 ----+---- (0 rows) 

Range Partitioning

Create the main table and one section:

 habr_10=# CREATE TABLE parti_2(c1 integer, c2 text) PARTITION BY RANGE (c1); CREATE TABLE habr_10=# CREATE TABLE parti_2_1 PARTITION OF parti_2 FOR VALUES FROM (1) to (100); CREATE TABLE 

Fill the data section:

 habr_10=# INSERT INTO parti_2 VALUES (0); ERROR: no PARTITION OF relation "parti_2" found for row DETAIL: Partition key of the failing row contains (c1) = (0). habr_10=# INSERT INTO parti_2 VALUES (1); INSERT 0 1 habr_10=# INSERT INTO parti_2 VALUES (2); INSERT 0 1 habr_10=# INSERT INTO parti_2 VALUES (5); INSERT 0 1 habr_10=# INSERT INTO parti_2 VALUES (101); ERROR: no PARTITION OF relation "parti_2" found for row DETAIL: Partition key of the failing row contains (c1) = (101). 

If there is no partition corresponding to the partitioning key, an error is generated.

Composite partitioning key

The composite partitioning key only works when partitioned. Create a main table with a composite key:

 habr_10=# CREATE TABLE parti_3(c1 integer, c2 text, c3 date) PARTITION BY RANGE (c1, c3); 

Add a section using the composite partitioning key:

 habr_10=# CREATE TABLE parti_3_a PARTITION of parti_3 FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11'); 

We illustrate the sharing of different table spaces in the example below. Start by creating tablespaces. Recall that the tablespace directory must exist, be empty, and belong to the OS user running Postgres. For example:

 test#mkdir /tmp/tablespaces/ts0 test#sudo chown postgres /tmp/tablespaces/ts0 

Now :

 habr_10=# CREATE TABLESPACE ts0 LOCATION '/tmp/tablespaces/ts0'; CREATE TABLESPACE habr_10=# CREATE TABLESPACE ts1 LOCATION '/tmp/tablespaces/ts1'; CREATE TABLESPACE habr_10=# CREATE TABLESPACE ts2 LOCATION '/tmp/tablespaces/ts2'; CREATE TABLESPACE habr_10=# CREATE TABLESPACE ts3 LOCATION '/tmp/tablespaces/ts3'; CREATE TABLESPACE 

Create a partitioned table and two sections:

 habr_10=# DROP TABLE parti_3; DROP TABLE 

(Sections are deleted in cascade)

 habr_10=# CREATE TABLE parti_3(c1 integer, c2 text, c3 date not null) PARTITION BY RANGE (c1, c3); CREATE TABLE habr_10=# CREATE TABLE parti_3_1 PARTITION OF parti_3 FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11') TABLESPACE ts1; CREATE TABLE habr_10=# CREATE TABLE parti_3_2 PARTITION OF parti_3 FOR VALUES FROM (100,'2017-08-11') TO (200, '2017-08-12') TABLESPACE ts2; CREATE TABLE 

If the values ​​do not exceed the boundaries of the sections:

 habr_10=# INSERT INTO parti_3 VALUES (1, 'test', '2017-08-10'); INSERT 0 1 habr_10=# INSERT INTO parti_3 VALUES (150, 'test2', '2017-08-11'); INSERT 0 1 

If c1 too small:

 habr_10=# INSERT INTO parti_3 VALUES (0, 'test', '2017-08-10'); ERROR: no partition of relation "parti_3" found for row DETAIL : Partition key of the failing row contains (c1, c3) = (0, 2017-08-10). 

If the value of c3 (column with date data type) precedes the lower boundary of the time range:

 habr_10=# INSERT INTO parti_3 VALUES (1, 'test', '2017-08-09'); ERROR: no partition of relation "parti_3" found for row DETAIL : Partition key of the failing row contains (c1, c3) = (1, 2017-08-09). 

Special values ​​MINVALUE and MAXVALUE allow not to specify the value of one of the boundaries. For example, the parti_3_0 and parti_3_3 sections can be declared as shown below and successfully inserted the lines that caused the error message above.

Note: some online articles that were published prior to the release of the beta3 version mention the special value UNBOUNDED, which was later replaced by MINVALUE and MAXVALUE.

 habr_10=# CREATE TABLE parti_3_0 PARTITION OF parti_3 FOR VALUES FROM (MINVALUE, MINVALUE) TO (1,'2017-08-10') TABLESPACE ts0; habr_10=# CREATE TABLE parti_3_3 PARTITION OF parti_3 FOR VALUES FROM (200,'2017-08-12') TO (MAXVALUE, MAXVALUE) TABLESPACE ts3; 

Finally, you can handle the pg_class table to make sure that the created sections of various types exist:

 habr_10=# ANALYZE parti_3; ANALYZE habr_10=# SELECT relname,relispartition,relkind,reltuples FROM pg_class WHERE relname LIKE 'parti_3%'; relname | relispartition | relkind | reltuples -------------+----------------+---------+----------- parti_3 | f | p | 0 parti_3_1 | t | r | 1 parti_3_2 | t | r | 1 (5 rows) 

Default sections and automatic section creation

It would be convenient to have a section where all the records that fall outside the bounds of the specified sections would fall in order not to stop each time due to an error. This feature will appear in version 11.

In the pg_pathman module there are very convenient functions that allow you not to think about whether the value falls within the assigned range boundaries. There you can set the interval of values ​​for the sections, and the module itself will create as many of them as needed to hold the entered values. This will work on sections like BY RANGE.

It should be borne in mind that PostgreSQL 10 does not have hash partitioning. This serious flaw was corrected at 11: the corresponding patch passed the commit fest, so it remains to wait for the release of PostgreSQL 11. If hash partitioning is necessary or desirable right now, pg_pathman can be used.

Insert performance

Table no_parti is a non -partitioned table. We create it as follows:

 CREATE TABLE no_parti (c1 integer, c2 text); INSERT INTO no_parti SELECT i, 'something' FROM generate_series(0, 9999999) i; Time: 10097.098 ms (00:10.097) 

Create the parti_new table using the new PostgreSQL 10 version functionality:

 CREATE TABLE parti_new (c1 integer, c2 text) PARTITION BY RANGE (c1); CREATE TABLE parti_new_1 PARTITION OF parti_new FOR VALUES FROM ( 0) TO ( 1000000); CREATE TABLE parti_new_2 PARTITION OF parti_new FOR VALUES FROM (1000000) TO ( 2000000); CREATE TABLE parti_new_3 PARTITION OF parti_new FOR VALUES FROM (2000000) TO ( 3000000); CREATE TABLE parti_new_4 PARTITION OF parti_new FOR VALUES FROM (3000000) TO ( 4000000); CREATE TABLE parti_new_5 PARTITION OF parti_new FOR VALUES FROM (4000000) TO ( 5000000); CREATE TABLE parti_new_6 PARTITION OF parti_new FOR VALUES FROM (5000000) TO ( 6000000); CREATE TABLE parti_new_7 PARTITION OF parti_new FOR VALUES FROM (6000000) TO ( 7000000); CREATE TABLE parti_new_8 PARTITION OF parti_new FOR VALUES FROM (7000000) TO ( 8000000); CREATE TABLE parti_new_9 PARTITION OF parti_new FOR VALUES FROM (8000000) TO ( 9000000); CREATE TABLE parti_new_0 PARTITION OF parti_new FOR VALUES FROM (9000000) TO (10000000); INSERT INTO parti_new SELECT i, 'something' FROM generate_series(0, 9999999) i; Time: 11448.867 ms (00:11.449) 

Create the parti_old table using the old partitioning method:

 CREATE TABLE parti_old (c1 integer, c2 text); CREATE TABLE parti_old_1 (CHECK (c1 BETWEEN 0 AND 1000000)) INHERITS (parti_old); CREATE TABLE parti_old_2 (CHECK (c1 BETWEEN 1000000 AND 2000000)) INHERITS (parti_old); CREATE TABLE parti_old_3 (CHECK (c1 BETWEEN 2000000 AND 3000000)) INHERITS (parti_old); CREATE TABLE parti_old_4 (CHECK (c1 BETWEEN 3000000 AND 4000000)) INHERITS (parti_old); CREATE TABLE parti_old_5 (CHECK (c1 BETWEEN 4000000 AND 5000000)) INHERITS (parti_old); CREATE TABLE parti_old_6 (CHECK (c1 BETWEEN 5000000 AND 6000000)) INHERITS (parti_old); CREATE TABLE parti_old_7 (CHECK (c1 BETWEEN 6000000 AND 7000000)) INHERITS (parti_old); CREATE TABLE parti_old_8 (CHECK (c1 BETWEEN 7000000 AND 8000000)) INHERITS (parti_old); CREATE TABLE parti_old_9 (CHECK (c1 BETWEEN 8000000 AND 9000000)) INHERITS (parti_old); CREATE TABLE parti_old_0 (CHECK (c1 BETWEEN 9000000 AND 10000000)) INHERITS (parti_old); CREATE OR REPLACE FUNCTION insert_into() RETURNS TRIGGER LANGUAGE plpgsql AS $FUNC$ BEGIN IF NEW.c1 BETWEEN 0 AND 1000000 THEN INSERT INTO parti_old_1 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 1000000 AND 2000000 THEN INSERT INTO parti_old_2 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 2000000 AND 3000000 THEN INSERT INTO parti_old_3 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 3000000 AND 4000000 THEN INSERT INTO parti_old_4 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 4000000 AND 5000000 THEN INSERT INTO parti_old_5 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 5000000 AND 6000000 THEN INSERT INTO parti_old_6 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 6000000 AND 7000000 THEN INSERT INTO parti_old_7 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 7000000 AND 8000000 THEN INSERT INTO parti_old_8 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 8000000 AND 9000000 THEN INSERT INTO parti_old_9 VALUES (NEW.*); ELSIF NEW.c1 BETWEEN 9000000 AND 10000000 THEN INSERT INTO parti_old_0 VALUES (NEW.*); END IF; RETURN NULL; END; $FUNC$; CREATE TRIGGER tr_insert_parti_old BEFORE INSERT ON parti_old FOR EACH ROW EXECUTE PROCEDURE insert_into(); INSERT INTO parti_old SELECT i, 'something' FROM generate_series(0, 9999999) i; Time: 125351.918 ms (02:05.352) 

We see that the insertion rate for the new approach to partitioning is much higher.

Version 11 has a patch that significantly increases the insertion speed when the data in the partitioning key grow monotonously or almost monotonously.

With regard to reading performance, at stage 10 -> 11 fundamental changes have occurred: it has become possible to effectively exclude from the plan a section in which there is obviously no data (partition pruning). This can be done at the stage of execution, when the condition of hitting a particular section is not known in advance. This happens, for example, in cases of a subquery.

So far, section exclusion (reliably) works only in list sectioning. In the case when there are a lot of sections (and in real projects there are thousands, or even tens of thousands), the exclusion of sections can seriously reduce the request execution time. In pg_pathman exclusion of unnecessary sections has been implemented almost from the very beginning of the project.

Comparing old and new sectioning with examples

We will understand the differences in the approach to partitioning in versions 9.6 and 10.

Create a table of temperatures associated with the place and date. We will create sections for each place and each month.

Commands for creating such tables in 9.6:

 CREATE TABLE meteo ( t_id serial, place text NOT NULL, hour_mesure timestamp DEFAULT now(), temperature real NOT NULL ); CREATE TABLE meteo_moscow_201709 ( CHECK ( place = 'Moscow' AND hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_moscow_201710 ( CHECK ( place = 'Moscow' AND hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_sochi_201709 ( CHECK ( place = 'Sochi' AND hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_sochi_201710 ( CHECK ( place = 'Sochi' AND hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_magadan_201709 ( CHECK ( place = 'Magadan' AND hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) ) INHERITS (meteo); CREATE TABLE meteo_magadan_201710 ( CHECK ( place = 'Magadan' AND hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) ) INHERITS (meteo); CREATE OR REPLACE FUNCTION meteo_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.place = 'Moscow' ) THEN IF ( NEW.hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) THEN INSERT INTO meteo_moscow_201709 VALUES (NEW.*); ELSIF ( NEW.hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) THEN INSERT INTO meteo_moscow_201710 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date does not fit meteo_insert_trigger(Moscow)'; END IF; ELSIF ( NEW.place = 'Sochi' ) THEN IF ( NEW.hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) THEN INSERT INTO meteo_sochi_201709 VALUES (NEW.*); ELSIF ( NEW.hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) THEN INSERT INTO meteo_sochi_201710 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date does not fit meteo_insert_trigger(Sochi)'; END IF; ELSIF ( NEW.place = 'Magadan' ) THEN IF ( NEW.hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) THEN INSERT INTO meteo_magadan_201709 VALUES (NEW.*); ELSIF ( NEW.hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND NEW.hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) THEN INSERT INTO meteo_magadan_201710 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date does not fit meteo_insert_trigger(Magadan)'; END IF; ELSE RAISE EXCEPTION 'Date does not fit meteo_insert_trigger() !'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insert_meteo_trigger BEFORE INSERT ON meteo FOR EACH ROW EXECUTE PROCEDURE meteo_insert_trigger(); 

Commands for creating such tables in 10;

 CREATE TABLE meteo ( t_id integer GENERATED BY DEFAULT AS IDENTITY, place text NOT NULL, hour_mesure timestamp DEFAULT now(), temperature real NOT NULL ) PARTITION BY RANGE (place, hour_mesure); CREATE TABLE meteo_moscow_201709 PARTITION of meteo FOR VALUES FROM ('Moscow', '2017-09-01 00:00:00') TO ('Moscow', '2017-10-01 00:00:00'); CREATE TABLE meteo_moscow_201710 PARTITION of meteo FOR VALUES FROM ('Moscow', '2017-10-01 00:00:00') TO ('Moscow', '2017-11-01 00:00:00'); CREATE TABLE meteo_sochi_201709 PARTITION of meteo FOR VALUES FROM ('Sochi', '2017-09-01 00:00:00') TO ('Sochi', '2017-10-01 00:00:00'); CREATE TABLE meteo_sochi_201710 PARTITION of meteo FOR VALUES FROM ('Sochi', '2017-10-01 00:00:00') TO ('Sochi', '2017-11-01 00:00:00'); CREATE TABLE meteo_paris_201709 PARTITION of meteo FOR VALUES FROM ('Magadan', '2017-09-01 00:00:00') TO ('Magadan', '2017-10-01 00:00:00'); CREATE TABLE meteo_paris_201710 PARTITION of meteo FOR VALUES FROM ('Magadan', '2017-10-01 00:00:00') TO ('Magadan', '2017-11-01 00:00:00'); 

Note that the declarative syntax of version 10 is much simpler than the syntax 9.6. In it, as can be seen from the example, the most tedious part of the work is the creation of trigger functions.

( In pg_pathman developers went even further. There are functions available that cut the existing sections, merge neighboring ones and many others. )

Here is an example of a function that creates random rows in a table:

 CREATE OR REPLACE FUNCTION populate_meteo() RETURNS TEXT AS $$ DECLARE placex text[] := '{}'; v_place text; v_hour timestamp; v_temperature real; v_nb_insertions integer := 500000; v_insertion integer; BEGIN placex[0]='Moscow'; placex[1]='Sochi'; placex[2]='Magadan'; FOR v_insertion IN 1 .. v_nb_insertions LOOP v_place=placex[floor((random()*3))::int]; v_hour='2017-09-01'::timestamp + make_interval(days => floor((random()*60))::int, secs => floor((random()*86400))::int); v_temperature:=round(((random()*14))::numeric+10,2); IF EXTRACT(MONTH FROM v_hour) = 10 THEN v_temperature:=v_temperature-4; END IF; IF EXTRACT(HOUR FROM v_hour) <= 9 OR EXTRACT(HOUR FROM v_hour) >= 20 THEN v_temperature:=v_temperature-5; ELSEIF EXTRACT(HOUR FROM v_hour) >= 12 AND EXTRACT(HOUR FROM v_hour) <= 17 THEN v_temperature:=v_temperature+5; END IF; INSERT INTO meteo (place,hour_mesure,temperature) VALUES (v_place,v_hour,v_temperature); END LOOP; RETURN v_nb_insertions||' mesures de température insérées'; END; $$ LANGUAGE plpgsql; 

Insert rows into the meteo tables from habr_9_6 and habr_10:

 habr_9_6=# EXPLAIN ANALYSE SELECT populate_meteo(); QUERY PLAN ------------------------------------------------------------------------------------------ Result (cost=0.00..0.26 rows=1 width=32) (actual time=33315.067..33315.068 rows=1 loops=1) Planning time: 0.034 ms Execution time: 33315.084 ms (3 rows) habr_10=# EXPLAIN ANALYSE SELECT populate_meteo(); QUERY PLAN --------------------------------------------------------------------------------------- Result (cost=0.00..0.26 rows=1 width=32) (actual time=14976.438..14976.438 rows=1 loops= 1) Planning time: 0.016 ms Execution time: 14976.499 ms (3 rows) 

We see that in version 10 data is inserted twice as fast.

Manage the entire section family at once

For operations with sections created using inheritance mechanisms, their explicit listing is required.

 habr_9_6=# SELECT 'VACUUM ANALYZE '||relname AS operation FROM pg_stat_user_tables WHERE relname LIKE 'meteo_%'; operation ------------------------------------ VACUUM ANALYZE meteo_moscow_201709 VACUUM ANALYZE meteo_moscow_201710 VACUUM ANALYZE meteo_sochi_201709 VACUUM ANALYZE meteo_sochi_201710 VACUUM ANALYZE meteo_paris_201709 VACUUM ANALYZE meteo_paris_201710 (6 rows) habr_9_6=# \gexec VACUUM VACUUM VACUUM VACUUM VACUUM VACUUM 

10 , VACUUM ANALYSE , :

 habr_10=# VACUUM ANALYZE meteo; VACUUM habr_10=# SELECT now() AS date,relname,last_vacuum,last_analyze FROM pg_stat_user_tables WHERE relname LIKE 'meteo_sochi%'; -[ RECORD 1 ]+------------------------------ date | 2018-04-06 23:38:48.59511+03 relname | meteo_sochi_201709 last_vacuum | 2018-04-06 23:37:05.931573+03 last_analyze | 2018-04-06 23:37:05.958845+03 -[ RECORD 2 ]+------------------------------ date | 2018-04-06 23:38:48.59511+03 relname | meteo_sochi_201710 last_vacuum | 2018-04-06 23:37:05.973254+03 last_analyze | 2018-04-06 23:37:06.002487+03 

10

Index

10 - :

 habr_10=# CREATE INDEX meteo_hour_mesure_idx ON meteo (hour_mesure); ERROR: cannot create index on partitioned table "meteo" 

, , .

, .

, 9.6, :

 habr_10=# CREATE INDEX meteo_moscow_201710_hour_idx ON meteo_moscow_201710 (hour_mesure); CREATE INDEX 

11 ( ). , (FOREIGN KEY)

10 UPDATE , .

 habr_10=# UPDATE meteo SET place='Sochi' WHERE place='Moscow'; ERROR: new row for relation "meteo_moscow_201709" violates partition constraint DETAIL : Failing row contains (5, Sochi, 2017-09-15 05:09:23, 9.43). 

11 : .

10 :

 CREATE TABLE meteo_moscow_ancienne PARTITION of meteo FOR VALUES FROM ('Moscow', MINVALUE) TO ('Moscow', '2017-09-01 00:00:00'); CREATE TABLE meteo_sochi_ancienne PARTITION of meteo FOR VALUES FROM ('Sochi', MINVALUE) TO ('Sochi', '2017-09-01 00:00:00'); CREATE TABLE meteo_paris_ancienne PARTITION of meteo FOR VALUES FROM ('Magadan', MINVALUE) TO ('Magadan', '2017-09-01 00:00:00'); 

• NULL , ()
• , , ( FDW — CREATE FOREIGN TABLE ... PARTITION OF ... ).
• 
  
  • ( )
  • CHECK .

11.

PostgreSQL , , , . .

[ postgres- ]