The Postgres version 10 had released almost a year ago. And yet, I see many organizations still running their workload on the earlier versions. Postgres 10 has some very interesting and cool features which you cannot give a miss –

• Better table partitioning
• Improvement in FDW making it easier to handle a big workload in an efficient way
• Enhancement in replication and parallel query features that help to improve the performance of Postgres database server
• Addition to the security features and many more…

I wanted to write an article for you’ll which explains the features of version 10 in better depth. So, here it is –

1. Logical replication:

This is the feature I most like in Postgres 10. Until version 9.6, it was possible to configure physical standby server streaming WAL files in almost real-time from master to standby server. This is a good solution if you are looking for data high availability and want to scale-out read queries across all available standby servers. There are some limitations of streaming replication, firstly, replication is byte by byte due to which the replicated standby server is always a mirror copy of the master database server. It is not possible to replicate only schemas or certain tables. Secondly, the standby server is always in read-only mode.
With the introduction of Logical Replication in Postgres 10, you have a choice to replicate only specific tables instead of the whole Postgres instance and not only that, if needed you can also add an additional column to those table on the target replicated server. Replication is unidirectional and the replicated table in the target server is available for a write operation, as well. This in-turn makes the creation of a data hub easier. One can create a central data store and replicate the tables of interest to this central data store from various source Postgres servers. Later, the data store can be used for reporting and analytics purpose.
Logical replication can also be used for an upgrade between major versions of Postgres.

2. Improved parallel scan:

The parallel scan is a major feature that was introduced in Postgres 9.6 and it seemed like it would continue to be a major feature in the next release cycle too. The earlier version had a parallel scan for sequential joint and aggregate operations. This release has come with Parallel Index Scan where multiple workers can parallelly scan the Index Table thus reducing the execution time, significantly.
Apart from this, the performance of Bitmap heap scan can also be improved with the parallel scan option. In parallel bitmap heap scan, one process scans the index and builds the data structure of heap pages in the memory and then all worker processes can perform heap scan in parallel.

3. Declarative Table Partitioning

Until version 9.6 came along, table partitioning meant, a DBA had to put in a lot of effort to create and manage the table partitions. Starting Version 10, DBAs no longer have to worry about writing trigger functions and trigger procedures or overlapping constraint values. To understand this better refer the below Orderliness table that has been created using the new partitioning feature:
postgres=# CREATE TABLE orderlines (
orderlineid integer NOT NULL,
orderid integer NOT NULL,
prod_id integer NOT NULL,
quantity smallint NOT NULL,
orderdate date NOT NULL
) partition by range (orderdate);
CREATE TABLE
postgres=# CREATE TABLE orderlines_2018_09
PARTITION OF orderlines
FOR VALUES FROM (‘2018-09-01 00:00:00’) TO (‘2018-10-01 00:00:00’);
CREATE TABLE
postgres=# CREATE TABLE orderlines_2018_08
PARTITION OF orderlines
FOR VALUES FROM (‘2018-08-01 00:00:00’) TO (‘2018-09-01 00:00:00’);
It is also possible to partition tables using ‘RANGE’ and ‘LIST’ partitioning syntax which was not available in the earlier versions and the performance of ‘INSERT’ queries has also improved to a great extent.

4. Crash-safe Hash Index:

Hash indexes were earlier logged in WAL files and made crash safe. In Postgres 10, Hash index performs better than the standard B-tree index for a simple search on a large amount of data.
[bctt tweet=”Hash index performs better than the standard B-tree index for a simple search on a large amount of data in Postgres 10″]

5. Improvements to Postgres_fdw:

Postgres_fdw now pushes the aggregate and joins operation on the remote server. This help in reducing the amount of data that will be fetched from remote servers and the time spent in reading and processing the data.

6. Quorum commit:

In Postgres 9.6 and earlier versions, the Master server would wait for ‘COMMIT’ confirmation from standby that appeared first in the synchronous_standby_names parameter.
synchronous_standby_names = FIRST 2(node1,node2,node 3);
The new synchronous_standby_names keyword ‘ANY’ allows waiting for any number of standbys irrespective of their ordering which is known as a Quorum commit.
synchronous_standby_names = ANY 2(node1,node2,node 3);
In the example cited above, when you specify ‘ANY’ option, rather than waiting for ‘COMMIT’ confirmation from first two standby servers in the list, the master should get COMMIT confirmation from any of the two out of three standby servers.

7. SCRUM Authentication:

Password and MD5 authentication has always been the choice of authentication for users who want to use Postgres to provide authentication method. The problem with Password method is, it just sends the user-supplied password over the wire whereas MD5 sends a hashed and salted version of the password over the wire. Anyone who can intercept the connection could read and decrypt the password.
The SCRUM authentication feature as introduced in Postgres 10 performs SCRAM-SHA-256 authentication. It uses a challenge-response that prevents password sniffing or unauthorized connections and stores password in a hashed form which is more secure.
The above are just a few features of Postgres 10. Just like these, there are many more interesting features like Transition tables, Wait for event column addition in pg_stat_activity view, Addition of new monitoring roles like pg_monitor, pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables that have been introduced in this version.
Plus, the pg_xlog and pg_clog directories have been renamed to pg_wal  and pg_xact to avoid any confusion and accidental deletion of files inside these directories.
You have a query on Postgres? Feel free to drop in a line on https://ashnik.com/ask-ashnik/ and we’ll answer it for you!