4 new key features in PostgreSQL 16

4 new key features in PostgreSQL 16

PostgreSQL Global Development Group has released PostgreSQL 16. With this latest update, Postgres sets new standards for database management, data replication, system monitoring, and performance optimization, marking an important milestone for the community, developers, and EDB as the leading contributor to the PostgreSQL code.

With PostgreSQL 16 comes a lot of new features and improvements, Info World lists the key ones.

Administrator rights

One of the important changes in PostgreSQL 16 is the reworking of privilege administration. Previous versions often required a superuser account for many administrative tasks, which could be impractical in large organizations with multiple administrators.

PostgreSQL 16 addresses this issue by allowing users to grant privileges only to those who have them ADMIN OPTION. This change enables administrators to define more specific roles and assign privileges accordingly, simplifying permission management. This change not only improves security, but also simplifies overall user management.

Improved logical replication

Logical replication has been a flexible solution for data replication and distribution since it was first included in PostgreSQL 10 almost 6 years ago and allowed for different options. Since then, every release of Postgres has improved logical replication, and Postgres 16 is no exception. This release covers not only the necessary internal improvements to improve performance and reliability, but also enables new and more complex architectures.

Postgres 16 now supports logical replication from physical replication pools. Along with reducing the load on the main server that receives all the records in the cluster, simpler geodistribution architectures are now possible. A primary can have a replica in another region that can send data to a third system in that region instead of duplicating data twice from one region to another. Nova pg_log_standby_snapshot()function makes this possible.

Other enhancements to logical replication include initial synchronization of tables in binary format, replication without a primary key, and improved security by requiring subscription holders to have SET ROLEpermissions to all tables in the replication set or be superuser.

Productivity improvement

PostgreSQL 16 focuses on performance improvements. Advanced query execution capabilities enable parallel execution of functions FULLand RIGHT JOINs, as well as aggregate functions string_agg. Requests SELECT DISTINCT get a productivity boost thanks to incremental sorting. Competitive batch loading of data using COPY also has significant performance improvements of up to 300%.

This release also introduces features such as caching RANGEand LISTpartition lookups that help bulk load data in partitioned tables and better control shared buffer usage with VACUUMand ANALYZEmaking your database run more efficiently than ever.

Comprehensive monitoring functions

PostgreSQL database monitoring has never been more detailed and complete. PostgreSQL 16 presents pg_stat_io, which allows you to better understand the I/O activity of your Postgres system. System-wide I/O statistics are now query-only, allowing you to view read, write, and expand activity (resizing data files on the system) by different types of backends, such as regular client backends VACUUM.

PostgreSQL 16 records statistics of the last sequential and index table scans, adds information about speculative locking to the view pg_locksand makes several improvements to wait events, making PostgreSQL monitoring more comprehensive than ever.

What makes PostgreSQL 16 truly exceptional is its potential to impact not only PostgreSQL users, but the entire industry.

EDB’s commitment to its community and customers has culminated in a reliable, secure and user-centric database system that promises innovation and performance across sectors. That’s why EDB is building enterprise-ready Postgres-based capabilities into EDB Postgres Advanced Server, with features like privilege analysis and new options for transparent data encryption coming this November.

Additionally, PostgreSQL 16 will debut on EDB BigAnimal next month. This cloud-ready enterprise database platform as a service is available to organizations worldwide, enabling them to leverage the full power of PostgreSQL 16 in their preferred public cloud environments.

Also read on ProIT: What SQL users need to know about time series data.

Related posts