PostgreSQL 10 released

In fact, two days have passed, but no one has yet written an article on Habr, so I will have to eliminate this omission, which I do with pleasure.

So, what's new in this version of PostgreSQL?

First, the versioning itself has changed. Up to the “dozens” we observed a lot of minor versions 9.x, which came out about once a year and at the same time made serious, far from minor changes. Therefore, from version 10 it was decided to number 10, 11, 12, etc. By the way, MySQL seems to have gone the same way, jumped from 5.7 to 8.0

Okay, it's all the little things, let's get to the point

Logical replication

This is what everyone has been waiting for a long time. Replacing various extensions a la slony (replication on triggers) and other crutches.

Now you can replicate individual tables out of the box to other databases.

Replication is done using the CREATE PUBLICATION and CREATE SUBSCRIPTION commands. Everything is quite simple.

It is clear that the feature is quite new, so at the moment some features are missing in logical replication.

• no schema replication / DDL
• no sequence replication
• TRUNCATE command is not replicated

Nevertheless, it is still a huge step forward, in some cases, you can throw slony!

Partitioning

If earlier partitions could have been done through table inheritance , then in the top ten a built-in tool appeared for this, which is called declarative partitioning.

For this, the main table adds the keyword PARTITON BY RANGE (or LIST ), which says that this table is partitioned (or how to say it correctly in Russian?).

Specific partitions use the PARTITION OF ... FOR VALUES FROM (...) TO (...) expression to specify the range of their data.

Partitions have a number of limitations that you should keep in mind before using in production: Limitations of declarative partitioning in PostgreSQL 10

Identity columns

In short, the opportunity to write

 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 

instead

 id serial PRIMARY KEY 

What does this give?
The fact is that the word serial is roughly an alias to the DEFAULT nextval ('sequence name') construction. Those. in fact, the table is separate, the sequence is separate. It is convenient, but it is not. For example, this leads to the fact that if you give rights to insert into a table (GRANT INSERT), you have to give grants for sequencing separately.

By the way, the new entry conforms to the SQL standard.

Other

• Parallelism has improved, in particular, Parallel Bitmap Heap Scan, Parallel Index Scan, Parallel Merge Join, etc. Details can be found in the blog of Robert Haas.
• Improved physical replication performance
• Hash indexes become replicable.
• Full-text search support in jsonb columns
• SCRAM authentication
• improved xml support
• the query scheduler is improved when using join: if the scheduler realizes that merged strings cannot produce more than one row in a join, then you can not waste too much time searching for other rows. Examples of requests can be found in the tests of this committee.

Full list of changes can be found here .

If someone has already managed to try v10 in battle, share, pliz, your impressions in the comments.

UPDATE. It is believed that the version system is not very. It was necessary to call PostgreSQL X to be in trend :)