Postgres 12: DBA Life Just Got Better!

Postgres Getting Bigger and Stronger

Congratulations everyone for Postgres 12 release on October-3 2019.

It’s magic when like-minded people from all over the world come together and work towards a common goal to make Postgres better every single day.

Thank you for all the community members (Core members, developers, testers, build management and documentation team) to make this possible.

Postgres 12 has some really cool features for the end-user. Some of them are a major enhancement and some with minor changes in existing functionality. It’s going to benefit the open-source database world in a lot of different ways. But What a Database Admin would gain out of Postgres 12?

Below are some features which I am willing to use to make Database operations better and easier.

1.   Partitioning Improvements: Partitioning performance enhancements, including improved query performance on tables with thousands of partitions, improved insertion performance with INSERT and COPY, and the ability to execute ALTER TABLE ATTACH PARTITION without blocking queries. Also, there is an improvement for foreign keys to reference partitioned tables.

2.   Checksum Control: pg_checksums can enable/disable page checksums (used for detecting data corruption) in an offline cluster.- With an earlier version of Postgres, the only option was to reinitialize the database using initdb.

3.   Reindex Concurrently: Being a DBA indexing database without downtime(write) has been an issue. REINDEX CONCURRENTLY can rebuild an index without blocking writes to its table. Well this features also comes with a cost. Let me explain. Usually, PostgreSQL locks the table whose index is rebuilt against writes and performs the entire index build with a single scan of the table. Rest of the transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index rebuild is finished. This is a prob for a Production system. A very large tables can take many hours to be indexed, and even for smaller tables, an index rebuild can lock out writers for periods that are unacceptably long for a production system.

PostgreSQL supports rebuilding indexes with minimum locking of writes. This method is invoked by specifying the CONCURRENTLY option of REINDEX. When this option is used, PostgreSQL must perform two scans of the table for each index that needs to be rebuilt and wait for termination of all existing transactions that could potentially use the index. This method requires more total work than a standard index rebuild and takes significantly longer to complete as it needs to wait for unfinished transactions that might modify the index. As it allows normal operations, this method is useful for rebuilding indexes in a production environment. Of course, the extra CPU, memory and I/O load imposed by the index rebuild may slow down other operations.

4. Reporting information: Progress reports statistics for CREATE INDEX, REINDEX, CLUSTER, VACUUM FULL, and pg_checksums

CREATE INDEX and REINDEX operations: use pg_stat_progress_create_index system view.

for CLUSTER and VACUUM FULL: use pg_stat_progress_cluster

5. Enhance security with MFA: Multi-factor authentication, using the clientcert=verify-full option combined with an additional authentication method in pg_hba.conf.

There has been some changes to improve existing funtionality

1.   psql command \dP to list partitioned tables and indexes.

2.   Time-based server parameters to use units of microseconds

3.   fractional input for integer server parameters.
set work_mem = ‘30.1GB’

4.   wal_recycle and wal_init_zero server parameters to control WAL file recycling.

5.   VACUUM to skip index cleanup
This change adds a VACUUM command option INDEX_CLEANUP as well as a table storage option vacuum_index_cleanup. The use of this option reduces the ability to reclaim space and can lead to index bloat, but it is helpful when the main goal is to freeze old tuples.

6.   vacuumdb to select tables for vacuum based on their wraparound horizon
The options are –min-xid-age and –min-mxid-age.

7.   pg_upgrade to use the file system’s cloning feature.

8.   –exclude-database option to pg_dumpall.

I hope you could use some of the features to solve some of real-time problems. I would love to hear back from you about the features which can solve your database problems.