We have earlier posted the summary of the year 2021 in the PostgreSQL world. On behalf of the PostgreSQL community and the team at MigOps, we wish all the readers a very Happy New Year - 2023. The year 2022 has been another great year for PostgreSQL with new features, patches to existing extensions, numerous new extensions and contributions supporting migrations to PostgreSQL. In this article we are going to see a summary of the year 2022 in the PostgreSQL world.
It is now very common to see PostgreSQL as the migration target by both Oracle and SQL Server users. PostgreSQL has transformed from being chosen for ZERO license to a database that offers extensive enterprise features. This is not possible today without the efforts of every user of PostgreSQL who is the reason for increased PostgreSQL adoption. Special thanks to everyone involved in patch submissions or reviews, feature requests, blogs and articles, speaking at PostgreSQL conferences, discussions in postgres mailing lists and majorly the people behind PostgreSQL. Also, see our article about the Contributions by MigOps to PostgreSQL ecosystem in 2022.
Summary of interesting PostgreSQL updates from 2022
- DB-Engines Rankings - PostgreSQL is among the top 3 DBMS’s for the Year 2022.
- PostgreSQL 15 released - Let us see some of its features.
- Minor versions released in 2022.
- PostgreSQL versions that got EOL in 2022.
- Extensions that took birth in 2022.
- Security Vulnerabilities that got detected and patched
- Ora2Pg includes some more features simplifying migrations to PostgreSQL.
- Core Team report.
DB-Engines Rankings - PostgreSQL is among the top 3 databases for the Year 2022
DB-Engines database popularity ranking for the Years - 2017, 2018 and 2020 had ranked PostgreSQL as the DBMS of the Year. In the year 2021, PostgreSQL ranked as the runner-up while Snowflake ranked first. For the year 2022, Snowflake continued to be the first with Google BigQuery being the second and PostgreSQL being the third in rankings. This greatly shows the popularity of PostgreSQL being better than relational databases like MySQL, Oracle and SQL Server for the year 2022.
PostgreSQL 15 released - Some of its features
The PostgreSQL community announced the release of PostgreSQL 15 on 13th October, 2022. This release included most promising features with several improvements in performance of heavy and distributed workloads, administration, observability and security.
PostgreSQL 15 features and improvements -
- SQL MERGE command is supported from PostgreSQL 15.
- Compressed backups using pg_baesbackup. It now supports both server side and client side - gzip, LZ4, and Zstandard compression.
- Server log output structure can now be JSON through GUC log_destination as jsonlog.
- pg_stat_statements now provides I/O timing information for temp in the collected statistics.
- Allows PUBLICATION in logical replication to have its content filtered through row level and column level restriction. CREATE PUBLICATION command now supports specifying all tables of a SCHEMA along with its future tables.
- Optionally avoid behavior of treating NULL entries as DISTINCT using a NULLS NOT DISTINCT clause while creating Unique Constraints or Indexes.
- Postgres 15 now supports negative scale or a scale greater than the precision for a NUMERIC column. Also read this interesting article on NUMERIC data type and trailing zeroes.
- Non-superusers can now be granted SET and ALTER SYSTEM privileges on GUC parameters.
- CHECKPOINT can be issued by non-superusers through the pg_checkpoint role.
- Huge performance improvements involved while sorting a single column. Sorting a single column will no longer involve storing the entire tuple.
- Generation memory allocator instead of “aset” allocator now allows to utilize lesser memory for sort operations. See this article for more details.
- Supports parallel commit on postgres_fdw server optimizing distributed workloads.
- A new module pg_walinspect is now made available to inspect contents of WAL segments using SQL functions. This is similar to pg_waldump but accessible through SQL.
- Improved query performance for window functions : row_number(), rank(), dense_rank() and count() and queries referencing multiple foreign tables through parallel foreign table scans.
- Planning time for queries referencing Partitioned Tables are further improved in PostgreSQL 15.
- CLUSTER command is now supported on Partitioned Tables.
- SELECT DISTINCT can now be parallelized for faster execution at the cost of more parallel workers.
- New regular expression functions regexp_count(), regexp_instr(), regexp_like(), and regexp_substr() added for compatibility with other relational databases. See this article for more details.
- The algorithm for random() function is further improved.
Minor versions released in 2022
Minor versions for supported PostgreSQL major versions are usually released on the second Tuesday of the second month of each quarter. PostgreSQL 15 has already had 1 minor version released in 2022. Rest of the PostgreSQL major versions such as 10, 11, 12, 13 and 14 have all had 4 minor versions released this year.
Following are the minor versions released in 2022 for respective major versions. Please verify if you are using the latest minor version of the major version.
- PostgreSQL 15 - 15.1
- PostgreSQL 14 - 14.2, 14.3, 14.4, 14.5, 14.6
- PostgreSQL 13 - 13.6, 13.7, 13.8, 13.9
- PostgreSQL 12 - 12.10, 12.11, 12.12, 12.13
- PostgreSQL 11 - 11.15, 11.16, 11.17, 11.18
- PostgreSQL 10 - 10.20, 10.21, 10.22, 10.23 (This version is now End of Life. Contact Us for support on Upgrade)
PostgreSQL versions that got EOL after 2022
PostgreSQL 10 had its final minor version 10.23, released on 10th Nov, 2022. The PostgreSQL community is not going to release any security patches or bug fixes for this major release, 10, going forward. This does create an urgency for customers using PostgreSQL 10 who have not planned an upgrade sooner. You may use the article from MigOps as an example for upgrading to a supported major version using pg_upgrade with hard links.
Please see the PostgreSQL versioning policy to understand the dates after which a major version is considered as unsupported.
Extensions that took birth in 2022
PostgreSQL extensions are one of the major reasons for increased migrations from Oracle to PostgreSQL and SQL Server to PostgreSQL. Numerous developers across the world have contributed their patches or ideas that resulted in the birth of many new extensions in 2022. Following are some of the new extensions that are released in 2022.
- pg_ivm : The pg_ivm module provides Incremental View Maintenance (IVM) of Materialized Views in PostgreSQL. It can updated materialized views more efficiently when compared to the recomputing required through REFRESH MATERIALIZED VIEW.
- PGSpider : PGSpider Extension(pgspider_ext) is an extension to construct High-Performance SQL Cluster Engine for distributed big data. pgspider_ext enables PostgreSQL to access a number of data sources using Foreign Data Wrapper(FDW) and retrieves the distributed data source vertically.
- PLHaskell : This project is a "procedural language" extension of PostgreSQL allowing the execution of code in Haskell within SQL code.
- rapidrows : RapidRows is an open-source, zero-dependency, single-binary API server that can be configured to run SQL queries, perform scheduled jobs and forward PostgreSQL notifications to websockets.
- pg_enquo : A PostgreSQL extension to provide Encrypted Query Operations (enquo).
- pg_show_rewritten_query : Display a query as it will be executed, that is after the analyze and rewrite steps (PostgreSQL 15+).
- oracle_fnd : Functions that emulate the FND_GLOBAL and FND_PROFILE package's API using custom variables. (Announced by MigOps).
- pg_wkhtmltopdf : PostgreSQL implementation of Convert HTML to PDF using Webkit (QtWebKit).
- pg_rowalesce : The pg_rowalesce PostgreSQL extensions its defining feature is the rowalesce() function. rowalesce() is like coalesce(), but for rows and other composite types. From its arbitrary number of argument rows, for each field/column, rowalesce() takes the value from the first row for which that particular field/column has a not null value.
- pg_mupdf : PostgreSQL implementation of Convert HTML to PDF using MuPDF.*
- pg_mockable : The pg_mockable PostgreSQL extension can be used to create mockable versions of functions from other schemas.
- pg_migrate : pg_migrate is a PostgreSQL extension and CLI which lets you make schema changes to tables and indexes. Unlike ALTER TABLE it works online, without holding a long lived exclusive lock on the processed tables during the migration. It builds a copy of the target table and swaps them.
- pg_injection : PostgreSQL detection of sql injection.
- pg_icu_parser : PostgreSQL text search parser using ICU boundary analysis.
- pg_diffix : pg_diffix is a PostgreSQL extension for strong dynamic anonymization. It ensures that answers to simple SQL queries are anonymous. For more information, visit the Open Diffix website.
- notify_now : This simple extension allows you to return multiple responses from a single query using the built-in PostgreSQL NOTIFY API.
- lower_quantile : An extension to calculate lower quantile aggregates.
Security vulnerabilities identified and fixed
There were 2 security vulnerabilities in total that were detected in 2022 for core server, client library, contrib module, client contrib module and binary packaging. The same have been fixed through patches pushed through minor versions as seen in this security documentation maintained by the PostgreSQL community.
In a nutshell, the number of security vulnerabilities that got detected and fixed are as follows for each PostgreSQL major version. Please ensure to validate whether you are using the latest stable version as of current date as seen in PostgreSQL release notes.
PostgreSQL 10 is no longer supported in 2023. It is very important that you upgrade to the latest PostgreSQL major versions upon testing.
- PostgreSQL 11 - 2
- PostgreSQL 12 - 2
- PostgreSQL 13 - 2
- PostgreSQL 14 - 2
Contact MigOps today, if you are not aware whether your PostgreSQL database is affected with any security vulnerability. MigOps also offers a Health Audit to identify the performance bottlenecks and security vulnerabilities, the need for rightsizing of your databases and provide short-term, mid-term and long-term recommendations for the increasing workloads.
Ora2Pg includes some more features simplifying migrations to PostgreSQL
Ora2Pg is one of the major reasons for simplified migrations to PostgreSQL. Ora2Pg has been the only Open Source tool supporting Oracle to PostgreSQL migrations, active for 22 years. In the year 2022, there are 2 major releases of Ora2Pg that include numerous features supporting migration of complex code objects along with CDC. You may refer to the release notes of v23.1 and v23.2. You may see the consolidated improvements made to Ora2Pg in this article published by MigOps.
Core Team report for 2022
Core Team provides transparency into the activities performed through a detailed report. The report hasn’t been published for 2022 yet, however, you may be interested in reading the report for the activities performed from June, 2019 until December, 2021. You can view the report here.
The popularity of PostgreSQL and its adoption has been increasing year over year. At MigOps, we have experienced a 300% growth compared to 2021 in the number of customers migrating from Oracle to PostgreSQL. We also started customers showing interests in migrations from SQL to PostgreSQL, Informix to PostgreSQL and DB2 to PostgreSQL. We also noticed increased requests for Migration Assessments and Database Migration support in 2022. Leadership teams in larger organizations are considering cost savings using (1) Migration to PostgreSQL and paying zero license fees with better features supporting enterprises and (2) Migrations to cloud to reduce infrastructure maintenance costs. MigOps is glad to have a team to support you with migrations to PostgreSQL and cloud. Contact Us today to see how we can simplify your database migrations.