The PostgreSQL team released the PostgreSQL 14 database, providing users of the widely deployed relational database with new features.
PostgreSQL, also known as “Postgres”, is an open-source, object-relational database management system. It has been around for over 30 years and advertises itself as “the most advanced open-source relational database in the world”. It is developed by a worldwide team of volunteers and it is quite popular due to its stability and advanced features.
PostgreSQL is perceived as a go-to solution for performing complicated, high-volume data operations. That’s because PostgreSQL is better at handling extraordinary database situations. Additional points that set PostgreSQL apart are the fact it is object-relational, ACID-compliant, highly concurrent, and offers NoSQL support. And the latest PostgreSQL 14 makes things even better.
The new PostgreSQL release is the first major update for the database since the PostgreSQL 13 version that debuted in October 2020.
Related: Running PostgreSQL in Docker, A Quick and Practical Guide
A telling fact is that PostgreSQL has been named Database Management System of the Year 2020 by DB-Engines. PostgreSQL is the only database to have received this prestigious award three times based on PostgreSQL worldwide popularity.
What’s New in PostgreSQL 14
This latest release of PostgreSQL advances our users’ ability to manage data workloads at scale, enhances observability, and contains new features that help application developers.
Above all, this release includes several improvements to PostgreSQL’s query parallelism support, including better performance of parallel sequential scans, and the ability for
PL/pgSQL to execute parallel queries when using the
RETURN QUERY command. PostgreSQL 14 has improved dramatically for the number of connections over a few thousand.
With Postgres 14, the stored procedures which allow for transaction control in a block of code, can now return data by using
On top on that, extended statistics can now be used in PostgreSQL 14 for expressions. Additionally, window functions can now benefit from incremental sorts, a feature introduced in PostgreSQL 13.
Another area of improvement in PostgreSQL 14 involves the administration. A new predefined role named
pg_database_owner has been added that contains only the current database’s owner. This is especially useful in template databases.
In addition to the security enhancements, PostgreSQL 14 makes it convenient to assign read-only and write-only privileges to users on tables, views, and schemas using the
pg_write_all_data predefined roles. In other words, if you want to give read-only access to your database, there is one simple grant you can run:
GRANT pg_read_all_data TO [username].
As you probably know, PostgreSQL has support for JSON since version 9.2, but it has had a specific syntax for retrieving data. In Postgresql 14, support for subscripts has been added, making it easier for developers to retrieve JSON data using a recognizable syntax.
The terminal-based front-end
psql has also received improvements in this release. The shortcut
\df now allow you to see function and operator arguments. This helps reduce the number of matches for overloaded entries. In addition,
\dX will now give you more insights around extended statistics.
For more information about all changes in the new version, you can refer to the full changelog or you can visit the project’s website.