PostgreSQL Summary and Achievements in 2023

Our team at HexaCluster likes to extend our heartfelt thanks to the vibrant community of Users, IT leaders, Contributors and the people behind PostgreSQL who made PostgreSQL what it is today. Your passion and support have made PostgreSQL a versatile and robust database system. PostgreSQL is one of the only Open Source databases known for its relational, unstructured, columnar, and vector database functionalities. Let us take a couple of minutes of our time to witness the PostgreSQL summary and achievements in 2023.

PostgreSQL's Popularity

DB-Engines Rankings is widely recognized in the tech industry as a valuable tool for understanding trends and shifts in database technology. The rankings are updated monthly and provide insights into which databases are currently leading in terms of popularity and usage. To understand how the ranking is calculated, you may see this documentation. DB-Engines usually announces the DBMS of the Year upon comparing the popularity scores of January, 2022 and January, 2023.

And the great news is that PostgreSQL is the DBMS of the Year 2023. This success reflects the ever-growing community's preference for open-source solutions that offer both scalability and security.

Emergence as a Premier Vector Database

In 2023, PostgreSQL evolved into a leading vector database, with extensions like pgvector playing a crucial role. The popularity of the extension : pgvector surged, thanks to its advanced indexing capabilities, essential for efficient vector database management. Two notable index types popularizing this extension are IVFFlat and HNSW for Approximate Nearest Neighbor search to optimize Similarity search.

Major Release: PostgreSQL 16

On September 14, 2023, the PostgreSQL community witnessed the much-anticipated release of PostgreSQL 16. The community announces a major version usually in the last quarter of an year. This major version brought a lot of enhancements, focusing on improved performance, security, and user-friendly features. Among its key features were advanced query optimization techniques, better JSON handling capabilities, and enhanced partitioning functionality, all designed to cater to the diverse needs of modern applications.

Some of the notable features of PostgreSQL 16 are :

1. libpq now supports load balancing across multiple PostgreSQL servers.
2. Allow logical replication from Standby servers and allows us to use the standby server for decoding of WAL segments.
3. A new view for monitoring I/O statistics : pg_stat_io.
4. An optimization to reduce the frequency of full-table vacuum freeze by enbaling the freezing of pages during non-freeze operations where it is appropriate.
5. Enhanced handling of JSON data using SQL/JSON Constructors. This release introduces new functions such as JSON_ARRAY(), JSON_ARRAYAGG(), JSON_OBJECT(), and JSON_OBJECTAGG().
6. Regular Expression Support is now added in configuration files like pg_hba.conf, and pg_ident.conf for more flexibility. This makes the management of the configuration files better with reduced number of lines in these files.
7. Introduction of new built-in functions and enhancement to some of the existing functions.
8. A variety of performance enhancements such as -
   1. parallelization of FULL and internal right OUTER hash joins and certain aggregate functions.
   2. incremental sorts for DISTINCT and some more cases
   3. support for aggregates having ORDER BY or DISTINCT to use pre-sorted data
   4. more efficient addition of heap and index pages
   5. Optimizations in the vacuum freezing process.
   6. Improved GIN index access optimizer costs.
   7. Window functions optimization for faster internal processing.
   8. Caching for RANGE and LIST partition lookups to speed up operations.

Minor Versions Released in 2023

The PostgreSQL Community typically releases minor updates on the second Thursday of the second month in each quarter. However, this timing can vary, especially if there are critical issues or urgent security vulnerabilities or bugs that could impact data integrity, necessitating a quicker community response and release of fixes. In the year 2023, the following minor versions have been released for each of the major version.

• PostgreSQL 16 - 16.1
• PostgreSQL 15 - 15.2, 15.3, 15.4, 15.5
• PostgreSQL 14 - 14.7, 14.8, 14.9, 14.10
• PostgreSQL 13 - 13.10, 13.11, 13.12, 13.13
• PostgreSQL 12 - 12.14, 12.15, 12.16, 12.17
• PostgreSQL 11 - 11.19, 11.20, 11.21, 11.22

You may visit the official documentation for the minor release roadmap for 2024.

PostgreSQL versions that are End of Life

The PostgreSQL Global Development Group follows a versioning policy. PostgreSQL 11 has seen its final release (11.22) on 9th Nov, 2023. This means that the Community would not provide any security or bug fixes for PostgreSQL 11 going forward. We recommend all the PostgreSQL users to plan for their PostgreSQL major version upgrades as soon as possible. Latest major versions always provide a variety of new features and performance improvements that can help us scale our database environments and improve user experience. Contact us today to get our support for PostgreSQL Upgrades or Optimizations.

Ora2Pg supporting migrations from Oracle, SQL Server and MySQL to PostgreSQL

Ora2Pg is one of widely used database migration tool used by Oracle users since 2001. HexaCluster proudly supports Ora2Pg and its development. Our CTO, Gilles Darold is the primary author for Ora2Pg and our team at HexaCluster is always excited about contributing to Ora2Pg.

Ora2Pg has announced 2 new releases this year : v24.1 and v24.0.

One of the exciting announcement is that Ora2Pg now supports SQL Server to PostgreSQL migrations. So, it is not just the Oracle and MySQL users, but also the SQL Server users can take the advantage of Ora2Pg as a free tool to perform their migrations to PostgreSQL and save database software license costs.

Security vulnerabilities fixed in 2023

In the year 2023, there were 6 security fixes for core server. All of these security fixes are very important for us to fix through appropriate minor versions upgrades. Following are the references for the vulnerabilities addressed.

CVE-2023-39418 : MERGE fails to enforce UPDATE or SELECT row security policies. Major versions impacted : PostgreSQL 15 Fixed through minor versions : 15.4

CVE-2023-39417 : Extension script @substitutions@ within quoting allow SQL injection Major versions impacted : PostgreSQL 15, 14, 13, 12 Fixed through minor versions : 15.4, 14.9, 13.12, 12.16

CVE-2023-5870 - Extension script @substitutions@ within quoting allow SQL injection Major Version Impacted : 16, 15, 14, 13, 12 Fixed through minor versions : 16.1, 15.5, 14.10, 13.13, 12.17

CVE-2023-5869 - Buffer overrun from integer overflow in array modification Major Version Impacted : 16, 15, 14, 13, 12 Fixed through minor versions : 16.1, 15.5, 14.10, 13.13, 12.17

CVE-2023-5870 - Memory disclosure in aggregate function calls Major Version Impacted : 16, 15, 14, 13, 12 Fixed through minor versions : 16.1, 15.5, 14.10, 13.13, 12.17

CVE-2023-2455 - Row security policies disregard user ID changes after inlining Major Version Impacted : 15, 14, 13, 12 Fixed through minor versions : 15.3, 14.8, 13.11, 12.15

CVE-2023-2454 - CREATE SCHEMA ... schema_element defeats protective search_path changes Major Version Impacted : 15, 14, 13, 12 Fixed through minor versions : 15.3, 14.8, 13.11, 12.15

Conclusion

As PostgreSQL continues to evolve, it remains steadfast in its commitment to excellence and innovation. The advancements made in 2023 are just a glimpse of its potential to revolutionize database management. We eagerly look forward to its continued growth and the new milestones it will undoubtedly achieve in 2024 and the years to come.

If you are looking to perform a POC for your migration from Oracle to PostgreSQL, or from SQL Server to PostgreSQL, contact us today for a free technical assessment. HexaCluster provides several PostgreSQL services such as : PostgreSQL DBA Services, Database Health Assessments, Trainings and 24/7 Support. Contact us today or fill the below form and one of our team members will contact you.

Subscribe to our Newsletters and stay tuned for more interesting topics.

Contact Us Today!