PostgreSQL 15 features TECH PREVIEW
The YugabyteDB YSQL API reuses a fork of the PostgreSQL query layer. This architecture allows YSQL to support most PostgreSQL features, such as data types, queries, expressions, operators and functions, stored procedures, triggers, extensions, and so on - all of which are expected to work identically on both database systems.
Initially based on PostgreSQL 11.2, YSQL has been continuously enhanced with features, improvements, and security fixes pulled from PostgreSQL 12+, in addition to our own improvements. With v2.25, YugabyteDB now adds support for PostgreSQL 15, which brings many new features and improvements.
Try it out
PostgreSQL 15 support is in Tech Preview and included with the YugabyteDB 2.25 preview release.
| Product | To try it out |
|---|---|
| YugabyteDB | Follow the instructions in Quick Start. |
| YugabyteDB Anywhere | Install YugabyteDB Anywhere v2.25.0.0 or later and create a universe using DB Version 2.25.0.0 or later. |
| YugabyteDB Aeon | Create a Sandbox cluster with the Database version set to Preview Track (v2.25). |
What's new
Major features
| Feature | Description |
|---|---|
| Stored generated columns | Define columns whose values are automatically calculated and stored based on expressions involving other columns. |
| Before row triggers on partitioned tables | Enforce custom business logic, perform validation, or even modify row data before it is written to the appropriate partition. |
| Non distinct NULLs in a Unique index | Enforce that only one NULL value is permitted instead of allowing multiple NULL entries in a unique index. |
| Filter rows while importing data | Use COPY FROM with WHERE clause while importing data into a table and eliminate preprocessing data files. |
| UUID generation | Provide gen_random_uuid natively, eliminating the need for external libraries or custom implementations to generate UUIDs. |
| Foreign key references for partitioned tables | Define foreign keys that reference partitioned tables and use partitioned tables as parent tables in a foreign key relationship. |
| Multi-range and range aggregates | Simplify handling complex range-based data by grouping multiple discrete ranges together while maintaining their distinctness. |
| Pipeline mode | Enable a client to send multiple queries to the server without waiting for each query's response before sending the next one. This reduces the round-trip time between client and server and significantly boosts the performance when running many queries in quick succession. |
| Replace triggers | Replace an existing trigger with a new one without first having to drop the old trigger and create a new one. |
Query execution optimizations
| Feature | Description |
|---|---|
| Incremental sort | On a sorted result set, perform additional sorting on the remaining keys. |
| Memoization | Store results in memory when the inner side of a nested-loop join is small. |
| Disk-based hash aggregation | Opt for disk-based aggregation when hash table grows beyond work_mem. |
Observability
| Feature | Description |
|---|---|
| Query ID | Unique query ID to track a query across pg_stat_activity, EXPLAIN VERBOSE, and pg_stat_statements. |
| Stats on planning times | pg_stat_statements can now track the planning time of statements. |
| Granular stats reset | Resetting statistics via pg_stat_statements_reset is now granular at user, database, and query levels. |
| Sampled logging | Log a fraction of the statements rather than all statements. |
Security
| Feature | Description |
|---|---|
| Stricter public schema | CREATE Privilege for PUBLIC Role has been revoked ensuring tighter control over object creation, reducing the risk of accidental or malicious interference in shared schemas. |
| Simplified privilege management | New roles have been added to streamline permission assignments by grouping commonly needed privileges for read and write operations. |
Coming soon
The following PG15 features are not yet implemented but are planned for the future.
| Feature | Description |
|---|---|
| Extended statistics | Gather additional statistics using the CREATE STATISTICS command. |
| Merge command | INSERT, UPDATE or DELETE in one statement. |
| Scram authentication as default | Scram authentication is supported in YugabyteDB but still has md5 as default authentication method. |
| Nondeterministic collations | Consider strings to be equal even if they consist of different bytes, for example, case-insensitive, or accent-insensitive comparisons. |
Features not yet implemented
The following features supported in v2024.2 and earlier are not yet available in v2.25:
What's changed
The following features have different behaviors as compared to previous versions of YugabyteDB due to changes in the underlying PostgreSQL implementation.
When upgrading a YugabyteDB cluster from PostgreSQL 11-compatible versions (v2024.2 and earlier) to a PostgreSQL 15-compatible version (v2.25 and later), review the following to understand how they may affect your upgrade.
Upgrading to v2.25
Upgrading to v2.25 from previous versions (v2.23) is not yet available.ysqlsh
To ensure compatibility, make sure you are using the latest ysqlsh client (v2.25) with YugabyteDB v2.25.
Due to the addition of the --csv option in psql (and hence ysqlsh), you can no longer use the --c (double-hyphen) flag in place of --command. Use either -c (single hyphen) or --command instead.
Syntax
Queries written like the following now fail with a parsing error:
SELECT * FROM t WHERE migration_UUID=$1ORDER BY schema_name
To avoid this issue, add a space between $1 and ORDER, as follows:
SELECT * FROM t WHERE migration_UUID=$1 ORDER BY schema_name
TLS and authentication
The clientcert=1 option is no longer supported in pg_hba.conf. You need to use a string value. This change was made in PostgreSQL 12 in the PostgreSQL documentation. For more information on TLS in YugabyteDB, refer to TLS and authentication.
CREATE permission on public schema revoked for new users
In versions of YugabyteDB prior to v2.25 (and versions of PostgreSQL prior to 15), whenever you create a database user, that user is granted CREATE and USAGE privileges on the public schema by default.
Starting from YugabyteDB 2.25 (PostgreSQL 15), database users are no longer automatically granted creation permission on the public schema. The USAGE privilege is still present, as in previous versions. Database users with superuser privileges or who are database owners by default have the CREATE permission on the public schema. Any schema that is explicitly created is not impacted by this change, as they are already restricted with the default privileges.
extra_float_digits
The value of extra_float_digits has changed from 0 to 1 in PostgreSQL 15, resulting in values output in shortest-precise format. This format is both fast and precise, preserving the original binary float value exactly when correctly read.