No Comments

Top 10 Highlights of the much awaited PostgreSQL 12!

Sushant Pawar | Database Solution Consultant, Ashnik
Mumbai, 22 Aug 2019
Sushant-FImg

by , , No Comments

22-Aug-2019

PostgreSQL 12 beta was released on May-2019 and GA is expected soon. PostgreSQL 12 is on the verge of a release, and we are excited! I thought it would be great to share some essential and fresh updates users are looking forward to. So, here you go:

1. Remember Just-In-Time (JIT)?It was introduced in Version 11and helped improve the performance of complex queries. However, it had to be enabled manually. Starting with Version 12, JIT will be enabled by default

2. Detection of data corruption has become easier with the introduction of additional columns in pg_stat_database view. Data_checksum parameter should be enabled to detect the corruption on the storage side. Until now, in case of any data corruption, the error used to get captured in error log files. In version 12, it is going to be easier to monitor the checksum error with the additions of these new columns.

top-image19082019-01

The checksum_failures gives cumulative counts of errors for a specific database and checksum_last_failure gives the timestamp of last checksum failure on the database.

3. Postgres 12 has many enhancements to table partitioning.

  • The partitioning syntax will now allow partition bound to be any expression. Earlier, only constant values were allowed for partition bounds.
  • \dp will now list the partitioned tables in the list.

top-image19082019-02
4. Earlier, ‘alter table detach partition’ did not drop the objects like child indexes. That is resolved now as a bug fix.

5. New functions like pg_partition_root(), pg_partition_ancestors() and pg_partition_tree() will now give detailed information about the partitions.

top-image19082019-03
6. There are many improvements on the index front as well. In the B-tree index, the internal pages and min/max-leaf page indicator now only stores index key until change key rather than all index keys. This would make Multi-column b-tree indexes to be smaller in size.
There is a performance improvement for Index only scans with multiple columns.
Re-index operation can be performed concurrently due to reduced locking requirements.

7. On the Monitoring front, we know that the parameter log_min_duration_statement has been there for a long time which logged all queries taking more time than a specified duration. Now it is possible to control to log only a percentage of transactions using log_transaction_sample_rateparameter

8. A pg_stat_progress_cluster view is being introduced that can be used to monitor the progress of Vacuum full and cluster operations.

top-image19082019-04

9.  The recovery.conf file will no longer be available in Postgres 12. The file is now merged in Postgresql.conf and presence of recovery.conf file causes the Postgres process not to start.

top-image19082019-05
To put the Postgres instance in standby mode the standby.signal file should be created in the data directory. This will put Postgres instance in recovery mode and it will try to do the recovery by trying to connect to master database specified by primary_conninfo parameter.

top-image19082019-06
To recover the server to target recovery state without putting in standby node, recovery.signal file should be created in the data directory.

10. On the security front, GSSAPI support server-side and client-side encryptions – can be specified using hostgssenc and hostnogssenc parameters in pg_hba.conf file.

By the time the production-ready version of Postgres 12 is available, there might be more changes that we may see. I am eagerly waiting for the official release and I guess so are you.

0
0

  • Sushant is working as a Database Solution Consultant with Ashnik, having more than 9 years of experience in Database Administration. He has worked for companies like Capgemini, J.P. Morgan in the past on a variety of Database projects. Sushant has experience in managing the team of DBA’s and well versed with Oracle Single instance, RAC Database implementation, HA Dataguard configuration, Database Administration, Migration and Performance tuning. He is an EnterpriseDB certified PostgreSQL 9.5 Associate and has also acquired Oracle 12C, 10g professional certification.

More From Sushant Pawar | Database Solution Consultant, Ashnik :
22-Aug-2019