PostgreSQL brings more performance, security to open-source database

The open-source PostgreSQL 16 database is out today, adding new features that will help improve performance for all types of workloads, including AI.

PostgreSQL, also sometimes referred to as Postgres, is one of the most widely used and deployed open-source database technologies and has been steadily iterated since its first release back in 1996. The open-source project benefits from a robust community of contributors and vendors that support the database’s continued development.

Major cloud providers including Microsoft Azure, Google Cloud Platform (GCP) and Amazon Web Services (AWS) all provide hosted versions of PostgreSQL and there are numerous commercial providers including EDB and Percona that also have enterprise and cloud platforms based on the technology.

PostgreSQL at its core is a relational database technology, though its usage has expanded in recent years as a base for analytical database technologies such as Google’s AlloyDB and it is also widely used as a foundation for vector database capabilities.

With PostgreSQL 16, the open source database has been enhanced with improvements that  support bulk loading and querying of data, concurrency improvements and more options for supporting parallel queries. This release also expands PostgreSQL’s support for the SQL/JSON standard and includes more abilities to run logical replication at a very large scale.

“PostgreSQL 16 contains many performance enhancements that help> everyday workloads regardless of scale,” Jonathan Katz, a core team member and contributor to the PostgreSQL Global Development Group, told VentureBeat. 

How logical replication and monitoring have been improved in PostgreSQL 16

At the heart of every database is data, and making sure that data can be replicated and monitored is something that is always being improved in PostgreSQL.

Among the updated capabilities in the new database are a series of enhancements to logical replication. Katz explained that PostgreSQL 16 enables users to set up logical replication from a standby node. He noted that this capability is helpful for users who already have significant workloads on their primary instance and want to offload logical replication to a less-busy standby.

“PostgreSQL 16 also supports parallel apply of large transactions on a subscriber, which can speed up replay and make data available more quickly on a subscriber,” Katz said.

PostgreSQL has long had multiple native built-in monitoring capabilities for database operations that are now being expanded with the addition of the pg_stat_io  measurement. Katz explained that pg_stat_io lets database administrators look at  I/O [input/output] utilization stats, for example, the total number of read/write operations, how much data has been read/written and more. 

“This new view gives greater insight into how your PostgreSQL instance is interacting with your storage layer,” Katz said.

Database administration and security get a boost

While scaling and monitoring data is critical, so too is securing data. In PostgreSQL 16 there are a series of updates that should serve to help improve security.

One of the most important areas of security updates comes to privilege administration capabilities in PostgreSQL. Privileges in a database define what users are able to do and not do with a given database. A challenge with prior versions of PostgreSQL is that for many core database administration operations, a ‘superuser’ that is a user with full access to everything, was required. That’s an approach that isn’t an issue for smaller database deployments, but rapidly becomes an issue in larger environments.

PostgreSQL 16 now provides more granular control, for privilege management of the CREATE ROLE command that defines database roles. A database role defines a collection of database-related privileges that allow a user to carry out certain tasks.

“In short, the new role changes improve security by restricting the privileges of CREATE ROLE and its ability to modify other roles,” Adam Wright, Sr. Product Manager at EDB, told VentureBeat.

Wright said that the problem that the new role administration changes addressed is that users with the CREATE ROLE role were permitted to make changes to roles that they did not create, including in some cases SUPERUSER roles.  He explained now, with the addition of the ADMIN OPTION permission, such changes require the role requesting the change to have the ADMIN OPTION permission. The overall result is more control and security for the database.

Additionally, driven by EDB’s experience running its BigAnimal cloud database service, which is based on PostgreSQL, Wright said that EDB contributed a number of changes related to Role membership. 

“These changes allow Postgres-as-a-Service providers and administrators more fine-grained control of what users, including admins, can do in Postgres,” Wright said. 

How AI workloads fit into PostgreSQL

PostgreSQL is increasingly being used to support vector database workloads, typically via the pgvector extension.

“What’s great about PostgreSQL is its extensibility, that allows for developers to rapidly build extensions like pgvector that can support production workloads for AI/ML data,” Katz said. “PostgreSQL contains frameworks for building custom data types, indexing methods, and table storage methods,> and with enhancements to bulk loading capabilities in PostgreSQL 16,  it’ll be even easier to use PostgreSQL with AI/ML use cases.”