On behalf of the PostgreSQL community and the team at MigOps, we wish all the readers a very Happy New Year - 2022. It is no wonder that PostgreSQL is the only Open Source database with a consistent increase in its adoption every year. The Year 2021 has been another milestone in the PostgreSQL world. Every user of PostgreSQL has either directly or indirectly created a positive impact to the PostgreSQL adoption through - patch submissions or reviews, feature requests, blogs and articles, speaking at PostgreSQL conferences, discussions in postgres mailing lists about proposed features, etc. In this article we are going to see a summary of the year 2021 in the PostgreSQL ecosystem.
What’s being discussed in this Article ?
- DB-Engines Rankings - PostgreSQL is the runner-up in the DB-Engines Ranking in 2021.
- PostgreSQL 14 released - Let us see some of its features.
- Minor versions released in 2021.
- PostgreSQL versions that got EOL in 2021.
- Extensions that took birth in 2021.
- Security Vulnerabilities that got detected and patched
- Ora2Pg includes some more features simplifying Oracle to PostgreSQL migrations.
- Core Team report for 2021.
DB-Engines Rankings - PostgreSQL is the runner-up in the DB-Engines Ranking in 2021
DB-Engines determines the DBMS of the Year by subtracting the popularity score of the current year from the popularity score of the previous year for all the databases. Similarly, when MigOps subtracted the popularity score of the databases in Dec, 2020 from the score of Dec, 2021 to find the DBMS of the Year - 2021, PostgreSQL was proudly winning the race.
However, there was a drastic increase in the popularity score of Snowflake, in the month of December, 2021. This made snowflake, the DBMS of the Year 2021 PostgreSQL wins as a runner up with a difference of 7 points.
As per DB-Engines Rankings, PostgreSQL has already been the DBMS of the Year in 2017, 2018 and 2020. It now becomes the only database to win this title 3 times in the past 5 years.
PostgreSQL 14 released - Some of its features
The PostgreSQL community has announced the release of PostgreSQL 14 in the year 2021. This release included several improvements in performance of heavy and distributed workloads, administration, observability and security.
Some of the PostgreSQL 14 features
- Stored procedures supports OUT parameters
- CTE hierarchical queries now support CYCLE and SEARCH.
- Skip inessential vacuum cleanup when the database starts to approach a transaction ID wraparound condition. This is a huge improvement for databases with millions of transactions every hour.
- Fine tuned B-Tree indexes, which results in lesser bloat.
- Pipelined queries can now be used from PostgreSQL 14. This allows multiple queries/results to be sent/received in a single network transaction, thus improving the heavy workloads with high latency connections.
- PostgreSQL FDW can now take the benefit of parallel query and bulk inserts.
- JSON now supports Multirange and data subscripts.
- Simplified read/write access using predefined roles - pg_read_all_data and pg_write_all_data.
- From PostgreSQL 14 onwards, the default client authentication is SCRAM-SHA-256.
- Some more system views have now been added for further visibility into the database activity - pg_stat_wal, pg_stat_progress_copy, pg_backend_memory_context.
- From PostgreSQL 14 onwards, in-progress transactions will also stream to subscribers in logical replication.
- Statistics can now be gathered on expressions, which gives better query planning results.
- Many performance improvements on parallel sql queries, heavy concurrent workloads, vacuum, partitioned tables, etc.
Minor versions released in 2021
Minor versions for supported PostgreSQL major versions are usually released on the second Tuesday of the second month of each quarter. PostgreSQL 14 has already had 1 minor version released in 2021. Rest of the PostgreSQL major versions such as 9.6, 10, 11, 12 and 13 have all had 4 minor versions released this year.
Following are the minor versions released in 2021 for respective major versions. Please verify if you are using the latest minor version.
- PostgreSQL 14 - 14.1
- PostgreSQL 13 - 13.2, 13.3, 13.4, 13.5
- PostgreSQL 12 - 12.6, 12.7, 12.8, 12.9
- PostgreSQL 11 - 11.11, 11.12, 11.13, 11.14
- PostgreSQL 10 - 10.16, 10.17, 10.18, 10.19
- PostgreSQL 9.6 - 9.6.21, 9.6.22, 9.6.23, 9.6.24
PostgreSQL versions that got EOL after 2021
PostgreSQL 9.6 had its final minor version 9.6.24, released on 11th Nov, 2021. After this release, the PostgreSQL community is not going to release any security patches or bug fixes for this major release, 9.6. This does create an urgency for customers using PostgreSQL 9.6 who have not planned an upgrade sooner. MigOps has already blogged about an approach to upgrade the PostgreSQL 9.6 to PostgreSQL 13 using pg_upgrade with hard links. The blogged approach does work for a direct upgrade from 9.6 or higher to PostgreSQL 14.
Please see the PostgreSQL versioning policy to understand the dates after which a major version is considered as unsupported.
Extensions that took birth in 2021
One of the beautiful features of PostgreSQL is the ability to have extensions serving various purposes. Developers across the world contribute to several new extensions and we are going to list out some of the extensions that took birth in 2021.
- pg_dbms_job : Oracle compatible DBMS_JOB functionality for PostgreSQL. (Announced by MigOps)
- orafce_mail : Reduces the effort required during Oracle to PostgreSQL migrations by providing some compatibility to UTL_MAIL and DBMS_MAIL packages of Oracle in PostgreSQL.
- credcheck : An extension to enforce strict user and password policies for PostgreSQL (Announced by MigOps)
- pg_dbms_errlog : Emulates Oracle style DBMS_ERRLOG compatibility to PostgreSQL. This reduces migration time by avoiding a huge amount of changes in the application code. (Announced by MigOps)
- jdbc_fdw : Supports write capabilities to foreign tables using JDBC_FDW unlike the jdbc_fdw which supports only reads.
- dynamodb_fdw : Foreign data wrapper for AWS dynamodb that supports selects, writes and push-down of where conditions.
- pg_validate_ext_upgrade : A friendly tool for developers to validate PostgreSQL extension upgrade scripts. Please see this blog post for more details.
Security vulnerabilities identified and fixed
There were 35 security vulnerabilities in total that were detected in 2021 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 - 29
- PostgreSQL 11 - 25
- PostgreSQL 12 - 15
- PostgreSQL 13 - 11
- PostgreSQL 14 - 2
Contact MigOps today, if you are not aware whether your PostgreSQL database is affected with any security vulnerability. MigOps also offers Health Audit to identify the performance bottlenecks, 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 Oracle to PostgreSQL migrations
Ora2Pg has been the only Open Source tool supporting Oracle to PostgreSQL migrations, active for 21 years. It now includes features optimizing the migration speed with the integration of Oracle_FDW as seen in one of these articles. In addition to that, Ora2Pg now supports validation of row counts and migrated data upon migration from Oracle to PostgreSQL. There are many features that got added through 3 releases this year. Please refer to the release notes of v23.0 and v22.0 and v21.0 for more details.
By the way, Ora2Pg is proudly sponsored by MigOps. We are dedicated to adding more features to Ora2Pg and simplify the database migration process from Oracle to PostgreSQL.
Subscribe to our newsletters to receive latest updates about PostgreSQL and the solutions supporting migrations to PostgreSQL.
Core Team report for 2021
Core Team provides transparency into the activities performed through a detailed report. The report hasn’t been published since 2019 but the team has now published the report for the activities performed from June, 2019 until December, 2021. You can view the report here.
Conclusion
In this blog post, you can see that the popularity of the PostgreSQL database has been increasing year over year. At MigOps, we have experienced increased requests for Migration Assessments and Database Migration support in 2021. Leadership teams in larger organizations are considering cost savings using (1) Migration to PostgreSQL and paying zero license fees 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 or simply fill the following form.
