The latest YugabyteDB preview release supports PostgreSQL 15. This represents a significant upgrade from the previous version, which was compatible with PostgreSQL 11.
YugabyteDB 2.25 brings many new, long-awaited features, and this blog discusses the top 15 features (from PG12 to PG15) that will significantly benefit YugabyteDB developers.
I've run the following examples in YugabyteDB 2.25, and the full version shows that it is fully compatible with PostgreSQL 15:
yugabyte=> select * from version();
version
-------------------------------------------------------------------------
PostgreSQL 15.2-YB-2.25.0.0-b0 on aarch64-unknown-linux-gnu, compiled by clang version 17.0.6 (https://github.com/yugabyte/llvm-project.git 9b881774e40024e901fc6f3d313607b071c08631), 64-bit
(1 row)
Feature 1: Generate UUID Without PgCrypto (PG13)
This was already available with the pgcrypto extension, but now gen_random_uuid() is part of core PostgreSQL.
Without any CREATE EXTENSION command, I can create a table that will automatically assign a UUID primary key:
yugabyte=> create table message (
primary key (message_id)
,message_id uuid default gen_random_uuid()
,sender_name text
,sender_country text
,message text
,meta jsonb
);
CREATE TABLE
yugabyte=> \x
Expanded display is on.
yugabyte=> insert into message
(message, sender_name, sender_country, meta)
values ('Hello', 'Franck', 'CH','{ "date": "FEB-08, 2025"}')
returning *
;
-[ RECORD 1 ]--+-------------------------------------
message_id | bb8dbedf-6826-4872-a4f4-323186e815c0
sender_name | Franck
sender_country | CH
message | Hello
meta | {"date": "FEB-08, 2025"}
yugabyte=> \x
Expanded display is off.
Feature 2: Generated Column (PG12)
It was already possible to generate from a sequence with GENERATE ALWAYS AS IDENTITY, but adding a column calculated from others was impossible.
Here, I have created a table where I insert or update the height in centimeters, and the height in inches is automatically calculated and stored:
yugabyte=> create table people (
primary key ( country, name )
, country text
, name text
, height_cm numeric(3,0)
, height_in numeric(3,1)
generated always as (height_cm / 2.54) stored
) partition by list(country)
;
yugabyte=> create table people_eu
partition of people
for values in ('CH','FR','DE')
tablespace "eu_west1"
;
yugabyte=> insert into people
values ('CH','Franck',174) returning *
;
country | name | height_cm | height_in
---------+--------+-----------+-----------
CH | Franck | 174 | 68.5
(1 row)
I've created the table as partitioned to demonstrate the next feature, which was one of the most requested before YugabyteDB was PostgreSQL 15 compatible.
Feature 3: Foreign Key Referencing Partitioned Table (PG12)
Referential integrity is now possible with a partitioned parent table.
In my example, a "message" references a sender from "people":
yugabyte=> alter table message add constraint sender_fk
foreign key ( sender_country, sender_name )
references people ( country, name )
;
ALTER TABLE
Feature 4: Accelerated Partition Pruning (PG12)
YugabyteDB 2.25 includes many query layer performance improvements. When the query planner sees that only one partition can be read, it optimizes the execution plan accordingly.
The following query would have generated an Append and Sort operation in previous versions, because of the ORDER BY. Because the query planner knows that only one partition needs to be read, it skips those operations:
yugabyte=> explain (costs off)
select * from people
where country='CH'
order by name
;
QUERY PLAN
-----------------------------------------------------
Index Scan using people_eu_pkey on people_eu people
Index Cond: (country = 'CH'::text)
(2 rows)
This is an excellent benefit for YugabyteDB geo-partitioning and simplifies the execution plan when querying a single region.
To compare, here was the plan in PostgreSQL 11:
QUERY PLAN
-----------------------------------------------------------------------------------------
Merge Append
Sort Key: people_eu.name
-> Index Scan using people_eu_pkey on people_eu
Index Cond: (country = 'CH'::text)
Feature 5: Plan Cache Mode (PG12)
With prepared statements, PostgreSQL starts with custom plans (optimized for each value) and can switch to a generic plan (with a parameter) after five executions if the cost doesn't appear more expensive.
yugabyte=> prepare q1 as
select * from people
where country=$1 order by name
;
yugabyte=> explain execute q1('CH')
yugabyte-> \watch c=6 i=0.1
Sun 12 Jan 2025 08:57:08 PM GMT (every 0.1s)
QUERY PLAN
----------------------------------------------------
Index Scan using people_eu_pkey on people_eu people (cost=180.00..568.41 rows=5 width=128)
Index Cond: (country = 'CH'::text)
(2 rows)
Sun 12 Jan 2025 08:57:08 PM GMT (every 0.1s)
QUERY PLAN
----------------------------------------------------
Index Scan using people_eu_pkey on people_eu people (cost=180.00..568.41 rows=5 width=128)
Index Cond: (country = 'CH'::text)
(2 rows)
Sun 12 Jan 2025 08:57:08 PM GMT (every 0.1s)
QUERY PLAN
----------------------------------------------------
Index Scan using people_eu_pkey on people_eu people (cost=180.00..568.41 rows=5 width=128)
Index Cond: (country = 'CH'::text)
(2 rows)
Sun 12 Jan 2025 08:57:09 PM GMT (every 0.1s)
QUERY PLAN
----------------------------------------------------
Index Scan using people_eu_pkey on people_eu people (cost=180.00..568.41 rows=5 width=128)
Index Cond: (country = 'CH'::text)
(2 rows)
Sun 12 Jan 2025 08:57:09 PM GMT (every 0.1s)
QUERY PLAN
----------------------------------------------------
Index Scan using people_eu_pkey on people_eu people (cost=180.00..568.41 rows=5 width=128)
Index Cond: (country = 'CH'::text)
(2 rows)
Sun 12 Jan 2025 08:57:09 PM GMT (every 0.1s)
QUERY PLAN
----------------------------------------------------
Index Scan using people_eu_pkey on people_eu people (cost=180.00..568.41 rows=5 width=128)
Index Cond: (country = $1)
(2 rows)
It is now possible to control this behavior with the plan_cache_mode parameter. This defaults to auto to provide the same as previous versions, but can be set to always use a custom plan (with literals), or a generic plan (with parameters):
yugabyte=> show plan_cache_mode;
plan_cache_mode
-----------------
auto
(1 row)
yugabyte=> set plan_cache_mode to force_custom_plan;
SET
yugabyte=> explain execute q1('CH');
QUERY PLAN
----------------------------------------------------
Index Scan using people_eu_pkey on people_eu people (cost=180.00..568.41 rows=5 width=128)
Index Cond: (country = 'CH'::text)
(2 rows)
yugabyte=> set plan_cache_mode to force_generic_plan;
SET
yugabyte=> explain execute q1('CH');
QUERY PLAN
----------------------------------------------------
Index Scan using people_eu_pkey on people_eu people (cost=180.00..568.41 rows=5 width=128)
Index Cond: (country = $1)
(2 rows)
yugabyte=> set plan_cache_mode to auto;
SET
Feature 6: Show Parameters in psql (PG15)
It is now easy to list the parameters with a wildcard.
Here are all the YugabyteDB parameters (start with yb) with transaction in their name:
yugabyte=> \dconfig yb*transaction*
List of configuration parameters
Parameter | Value
-------------------------------------------+-------------------------------------------
yb_default_copy_from_rows_per_transaction | 20000
yb_disable_transactional_writes | off
yb_effective_transaction_isolation_level | read committed
yb_locks_max_transactions | 16
yb_transaction_priority | 0.000000000 (Normal priority transaction)
yb_transaction_priority_lower_bound | 0
yb_transaction_priority_upper_bound | 1
(7 rows)
When talking about parameters, a new feature makes it possible to set some parameters with decimals:
yugabyte=> set work_mem='1.5MB';
SET
Feature 7: Control the Unique Index behavior with Nulls (PG15)
By default, according to the SQL standard, nulls are distinct. Because nulls represent unknown values, their comparison is unknown. Two nulls do not violate a unique constraint. It is now possible to change this behavior when creating a unique index with NULLS DISTINCT (the default) or NULLS NOT DISTINCT.
By default, a unique index on the sender and message text would allow many rows for the same sender with a null message. I can create an index that will allow only one row with null:
yugabyte=> create unique index message_unq
on message( sender_name, sender_country, message)
nulls not distinct
;
CREATE INDEX
yugabyte=> insert into message (sender_name, sender_country)
values ('Franck', 'CH')
;
INSERT 0 1
yugabyte=> insert into message (sender_name, sender_country)
values ('Franck', 'CH')
;
ERROR: duplicate key value violates unique constraint "message_unq"
This will help migrations from Oracle Database or Microsoft SQL Server.
Feature 8: Fetch First With Ties (PG13)
ORDER BY cols LIMIT 1 or ORDER BY ... FETCH FIRST n ROWS returns at maximum n rows. If the values of the ORDER BY columns are the same, you may want to return all of them (even if it is more than n) rather than picking some randomly.
The following limits to one row, but two have the same sender name on which it is sorted:
yugabyte=> \x
Expanded display is on.
yugabyte=> select * from message
order by sender_name
fetch first 1 row with ties
;
-[ RECORD 1 ]--+-------------------------------------
message_id | bb8dbedf-6826-4872-a4f4-323186e815c0
sender_name | Franck
sender_country | CH
message | Hello
meta | {"date": "FEB-08, 2025"}
-[ RECORD 2 ]--+-------------------------------------
message_id | 99a637c6-c068-468d-afba-a30832a41536
sender_name | Franck
sender_country | CH
message |
meta |
yugabyte=> \x
Expanded display is off.
yugabyte=>
Feature 9: Incremental Sort (PG13)
Yugabyte 2.25 introduces many Hash and Sort operation optimizations. One is the possibility of avoiding a complete sort when an index is partially sorted on one column. This reduces the working set and gives more chances to be sorted in memory.
The following query has an ORDER BY on three columns, and an index starts with two of them. The query planner identified the pre-sorted prefix and does an incremental sort on the remaining column:
yugabyte=> explain /*+ IndexScan(message) */
select * from message
order by sender_name, sender_country, message_id
;
QUERY PLAN
-----------------------------------------------------
Incremental Sort (cost=2670.73..11321.51 rows=1000 width=144)
Sort Key: sender_name, sender_country, message_id
Presorted Key: sender_name, sender_country
-> Index Scan using message_unq on message (cost=2627.35..11281.96 rows=1000 width=144)
(4 rows)
Feature 10: More Regular Expression Functions (PG15)
More regexp functions are now available: regexp_count(), regexp_instr(), and regexp_like():
yugabyte=> select message
, regexp_like(message ,'l{2}')
, regexp_count(message,'l{2}')
, regexp_instr(message,'l{2}')
from message
;
message | regexp_like | regexp_count | regexp_instr
---------+-------------+--------------+--------------
Hello | t | 1 | 3
| | |
(2 rows)
Feature 11: More JSON Functions (PG13)
YugabyteDB 2.25 supports the JSON path expressions and datetime() function. Here is an example that reads a JSON attribute representing a date in a specific format and using it as a timestamp:
yugabyte=> SELECT message, meta
FROM message
WHERE (
jsonb_path_query_first(
meta, '$.date.datetime("MON-DD, YYYY")'
)::text::timestamp
) > now() - interval '1 month';
message | meta
---------+--------------------------
Hello | {"date": "FEB-08, 2025"}
(1 row)
Feature 12: Multi-Range Intervals (PG14)
In addition to ranges of values, you can now store and use multi-ranges.
I add an age target as multiple ranges of ages and used it to find messages that cover a specific age range:
yugabyte=> update message
set meta = jsonb_set(
meta, '{target_age}',
'"{[7, 18],[42, 77]}"'::jsonb
)
where message='Hello'
;
UPDATE 1
yugabyte=> select message,meta from message
where (meta->>'target_age')::int8multirange
&& int8range(20, 50)
;
message | meta
---------+--------------------------------------------------------------
Hello | {"date": "FEB-08, 2025", "target_age": "{[7, 18],[42, 77]}"}
(1 row)
Feature 13: Filtering When Loading With COPY (PG12)
Adding a WHERE clause to a COPY FROM allows you to filter what is imported.
The following imports messages from a list of words, excluding those less than thirty characters:
yugabyte=> copy message(message)
from '/usr/share/dict/words'
where length(message)>=30
;
COPY 3
yugabyte=> select message from message;
message
-----------------------------------------------
Hello
dichlorodiphenyltrichloroethane
half-embracinghalf-embracingly
pneumonoultramicroscopicsilicovolcanoconiosis
(5 rows)
Feature 14: Privileges For Setting Configuration Parameters (PG15)
Some parameters can only be set by a superuser. More fine-grained privileges can now be granted to specific parameters.
This creates a user that can set its temporary file limit:
yugabyte=> create user superdev;
CREATE ROLE
yugabyte=> grant set on parameter temp_file_limit
to superdev
;
GRANT
yugabyte=>
Feature 15: Inlined Common Table Expressions (PG12)
Writing a complex SQL query by module with a WITH clause can make it more readable. This is also known as Common Table Expressions (CTE).
In previous versions, it was a query planner frontier that may have limited the access path possibilities. The query planner can now inline them. The previous behavior, which may be used to restrict the query planner on purpose, is available with an additional MATERIALIZED keyword.
The following uses MATERIALIZED to isolate the CTE:
yugabyte=> explain (costs off)
with msg as materialized (
select sender_country, sender_name
, message
from message
where meta->'date' is not null
) , ppl as (
select country as sender_country, name as sender_name
from people
) select * from msg natural join ppl
;
QUERY PLAN
---------------------------------------------------
Merge Join
Merge Cond: ((people.country = msg.sender_country) AND (people.name = msg.sender_name))
CTE msg
-> Seq Scan on message
Storage Filter: ((meta -> 'date'::text) IS NOT NULL)
-> Index Scan using people_eu_pkey on people_eu people
-> Sort
Sort Key: msg.sender_country, msg.sender_name
-> CTE Scan on msg
(9 rows)
Without this keyword, There's no CTE in the execution plan and this allowed the join condition to be pushed down to the inner table:
yugabyte=> explain (costs off)
with msg as (
select sender_country, sender_name
, message
from message
where meta->'date' is not null
) , ppl as (
select country as sender_country, name as sender_name
from people
) select * from msg natural join ppl
;
QUERY PLAN
----------------------------------------------------
Nested Loop
-> Seq Scan on message
Storage Filter: ((meta -> 'date'::text) IS NOT NULL)
-> Index Scan using people_eu_pkey on people_eu people
Index Cond: ((country = message.sender_country) AND (name = message.sender_name))
(5 rows)
Plus Many More!
In this blog, I arbitrarily limited myself to sharing the top 15 features introduced in YugabyteDB 2.25. There are many more exciting features, including improved algorithms for hash and sort operations that have to spill to disk, more observability (like having the planning time in pg_stat_statements), and improved security with invoker rights for views.
The best part is that introducing these features has no downtime, as YugabyteDB can perform rolling upgrades even when they involve higher PostgreSQL compatibility.
Find out for yourself! Download YugabyteDB 2.25 (PREVIEW) today!
Top comments (0)