PostgreSQL 10 released

In fact, it's been two days already, 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. Prior to the "dozens", we observed a lot of minor versions of 9.x that came out about once a year and at the same time introduced serious, far from minor changes. Therefore, since version 10, it has been 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 trivia, let's move on to the essence of the question
Logical replication
This is what everyone was waiting for a long time ago. Replace with different extensions for a slony (replication on flip-flops) and other crutches.
Now you can replicate individual tables to other databases out of the box.
Replication is done using the CREATE PUBLICATION and CREATE SUBSCRIPTION . Everything is simple enough.
It's clear that the feature is quite new, so at the moment there are some features in logical replication.

no schema replication / DDL
no replication of the sequences
the TRUNCATE command is not replicated

Nevertheless, it is still a huge step forward, in some cases you can throw slony!
If earlier the partition could be crooned through table inheritance , then in the top ten for this The built-in , which is called declarative partitioning.
To do this, the keyword PATITON BY BY RANGE (or LIST) is added to the main table, which says that this table is partitioned (or how correctly does it say in Russian?).
For specific partitions, the range of their data is given by the expression PARTITION OF ... FOR VALUES FROM (...) TO (...) .
Partitions have a number of limitations that you should keep in mind before using them on production: Limitations of declarative partitioning in PostgreSQL 10
Identity columns
In short, the opportunity to write
id serial PRIMARY KEY What does it give?
The fact is that the word serial is roughly speaking an alias to the DEFAULT construct nextval ('name of the sequence'). Those. in fact the table itself, the sequence is separate. This is convenient, but sometimes it does not. For example, this leads to the fact that if you give the rights to insert into the table (GRANT INSERT), you have to separately give grants for the sequence.
By the way, the new record corresponds to the SQL standard.

Improved paralelism, in particular Parallel Bitmap Heap Scan, Parallel Index Scan, Parallel Merge Join, etc. Details can be found in the Robert Haas blog
Improved performance of physical replication
Hash indexes become replicable
Full-text search support on jsonb columns
SCRAM authentication
improved support for working with xml
Improved query scheduler when using join: if the scheduler understands that the merged rows can not produce more than one line in the JOIN, then you can not spend too much time looking for other rows. Examples of requests can be found in the tests for this comit

You can see the full list of changes here .
If someone has already tried v10 in combat, share, pliz, your impressions in the comments.
UPDATE. There is an opinion that the version system is not very. It was necessary to call PostgreSQL X to be in the trend :)
xially 8 november 2017, 15:03
Vote for this post
Bring it to the Main Page


Leave a Reply

Avaible tags
  • <b>...</b>highlighting important text on the page in bold
  • <i>..</i>highlighting important text on the page in italic
  • <u>...</u>allocated with tag <u> text shownas underlined
  • <s>...</s>allocated with tag <s> text shown as strikethrough
  • <sup>...</sup>, <sub>...</sub>text in the tag <sup> appears as a superscript, <sub> - subscript
  • <blockquote>...</blockquote>For  highlight citation, use the tag <blockquote>
  • <code lang="lang">...</code>highlighting the program code (supported by bash, cpp, cs, css, xml, html, java, javascript, lisp, lua, php, perl, python, ruby, sql, scala, text)
  • <a href="http://...">...</a>link, specify the desired Internet address in the href attribute
  • <img src="http://..." alt="text" />specify the full path of image in the src attribute