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’
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.
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.
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.
