Next PostgreSQL version is approaching. This version comes with an impressive feature list :

• Native partionning
• Sorts and aggregation improvements
• Better parallelism support : parallel index scan, parallel hash join, parallelism for subquery
• Extended statistics
• ICU collation: enable use of “abbreviated keys”, disabled in 9.5.2 due to libc bug. Abbreviated keys brings sort improvements (arround 20-30%). It is usefull when a query need a sort or for index creation.
• … look at wiki page New in Postgres 10 or releases notes.

Another attended feature is logical replication. I will present it in a serie of articles.

1. PostgreSQL 10 : Logical replication - Overview
2. PostgreSQL 10 : Logical replication - Setup
3. PostgreSQL 10 : Logical replication - Limitations

Reminder

Replication already exists in PostgreSQL ¹. It was based on “physical” replication: Postgres does not replicate queries but the result. More precisely modifications of the blocks.

However it has some limitations :

• there is no granularity, we have to replicate the whole instance
• it is not possible to replicate between different architectures (x86, ARM …).
• standby does not accept any request for writing. It is not possible to create custom views or indexes.

Operation

Unlike physical replication, logical replication does not replicate data blocks. It decodes the result of queries that are sent to secondary. Then, the secondary applies SQL changes from the logical replication stream.

In fact, secondary server is a primary server, in the sense that it is a server that accepts write requests like any other primary instance.

It will be possible to choose tables to replicate, add views and indexes on the secondary server etc …

1. A “publication” is created on the primary server (“publisher”), which includes tables belonging to the publication.
2. The secondary subscribes to this publication, it is a subscriber
3. Then a special process is launched: the bgworker logical replication. It will connect to a replication slot on the primary.
4. The primary will logically decode WAL to retrieve the results of the SQL statements.
5. The logical stream is passed to the secondary that applies them.

Implementation in next article…