DEV Community: YugabyteDB

UUIDv7 in YugabyteDB

Franck Pachot — Wed, 05 Feb 2025 00:16:38 +0000

When creating a user-defined function for inserts, such as one that generates a default value, you should ensure it does not cause a row-by-row flush (Write Buffering) in YugabyteDB. The function must be a simple expression (no generate_series, no with recursive). Here is an example of how to generate a UUIDv7.

Here is a function that generates a UUIDv7 from a timestamp (defaulting to now()) and a shift interval from this timestamp (defaulting to no shift):

create or replace function
 uuid7(shift_interval interval DEFAULT '0 milliseconds', p_timestamp timestamp with time zone default now())
 returns uuid as $$
  select (
    -- Calculate timestamp in microseconds (shifted by the interval if provided)
    lpad(to_hex(trunc(extract(epoch from p_timestamp + shift_interval )*10^3)::bigint), 12, '0') ||  -- Timestamp in microseconds
    -- Calculate random part A, apply version (UUIDv7), and convert to hex with padding
    lpad(to_hex(
     trunc((extract(epoch from p_timestamp + shift_interval ) * 10^6  -- Convert timestamp to microseconds
     - trunc(extract(epoch from p_timestamp) * 10^3) * 10^3) * 4.096  -- Create random value based on microseconds
     )::bigint | x'0000000000007000'::bigint), 4, '0') ||  -- Set version to '7' (UUIDv7 version)
    -- Generate random part B (using random numbers for 80-bit random value), apply variant, and convert to hex with padding
    lpad(to_hex(
     trunc(random() * 2^30)::bigint << 32 |  -- First part of the random value (30 bits shifted)
     trunc(random() * 2^32)::bigint |  -- Second part of the random value (32 bits)
     x'8000000000000000'::bigint), 16, '0')  -- Set variant to '10xx...' (UUIDv7 variant)
   )::uuid;  -- Combine everything into a final UUID and cast as uuid
$$ language sql;

This function can be used as the DEFAULT column clause of a CREATE TABLE. I apply a modulo eight on the PID for the shift interval. This helps distribute the inserts into eight segments for improved throughput while ensuring that entries inserted simultaneously are colocated within each range:

create table demo ( 
 id uuid primary key 
         default uuid7( pg_backend_pid()%8 * interval '1 year') 
);

explain (analyze, dist, costs off)
insert into demo select from generate_series(1,1000) returning *;

Let's check the scalability in a distributed SQL database with explain(analyze, dist) to display the number of write operations and the number of flushes:

yugabyte=> explain (analyze, dist, costs off)
insert into demo select from generate_series(1,1000) returning *;
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Insert on demo (actual time=0.127..6.836 rows=1000 loops=1)
   Storage Table Write Requests: 1000
   ->  Function Scan on generate_series (actual time=0.068..2.585 rows=1000 loops=1)
 Planning Time: 0.231 ms
 Execution Time: 17.823 ms
 Storage Read Requests: 0
 Storage Rows Scanned: 0
 Storage Write Requests: 1000
 Catalog Read Requests: 0
 Catalog Write Requests: 0
 Storage Flush Requests: 1
 Storage Flush Execution Time: 9.960 ms
 Storage Execution Time: 9.960 ms
 Peak Memory Usage: 140 kB

The plan indicates that 1,000 Write Requests have been grouped into one Flush Request. This approach is efficient, as the function did not require a flush for each row.

⛔ ORDER BY in Subquery

Franck Pachot — Thu, 30 Jan 2025 14:55:11 +0000

Here is a quick post to show the danger of not understanding how SQL and ORDER BY works.
In relational databases, SQL operates on unordered sets of rows. The sets or rows are tables, views, results of functions, or subqueries. You may think there's an order because the RDBMS may store the rows physically ordered and retrieve them with this apparent order. But it's just a side effect of the implementation, ready to break at runtime (with different physical ordering, different execution plans, different parallelization, or any other smart optimization).
The ORDER BY in SQL is different. It's not a relational operation on sets of rows but an operation on the result exposed outside the database when the application wants to fetch this set of rows in a specific order.

There are two consequences to keep in mind:

If you have no ORDER BY in the outer query, you can assume a random order, even if there's an ORDER BY in a subquery.
If you use OFFSET and LIMIT without an ORDER BY, you can assume that you pick a random subset from the result.

Here is an example in PostgreSQL:

create table t1 ( id uuid primary key default gen_random_uuid(), val1 int );
create table t2 ( id uuid primary key default gen_random_uuid(), val2 int );
insert into t1 ( val1 ) select generate_series(1,100);
insert into t2 select id val1 from t1;

I run the following query to get OFFSET 10 LIMIT 10 from a query that has no ORDER BY in the outer query:

postgres=# select * from t1
 offset 10 limit 10
;
                  id                  | val1
--------------------------------------+------
 6e21f38d-18ed-4834-bea1-b9a0d91ed288 |   52
 a4c00d5f-fd7c-42fc-97ee-cd309af47a09 |   77
 a5dd4329-137c-4aa2-b503-dc885378f155 |   59
 0ca0ef72-e39a-4eb7-9777-b1304016d968 |   15
 e6add227-d551-4429-bb67-a32ff4fbf76b |   83
 58a9285f-d68a-4173-a8b5-bdc7e679ed3b |   89
 c1464177-c3e8-4054-8616-a805350de739 |   84
 b5f492cc-0690-47da-9081-70d990857cae |   92
 e7638b03-8716-4321-ab00-b47c1b91b3f1 |   61
 c6cb972e-309c-4ee0-b1f5-7f73482672d9 |   88
(10 rows)

The ordering of the rows is random, driven by how they were physically stored and read from the heap table.

With an ORDER BY, the rows are sorted before OFFSET and LIMIT, so the result becomes deterministic:

postgres=# select * from t1
 order by val1
 offset 10 limit 10
;
                  id                  | val1
--------------------------------------+------
 cc4e4a88-c92f-4cfa-8d29-8b462be60619 |   11
 7212d0ab-b810-4002-928e-fa89607c059d |   12
 8f256559-fe98-4c21-a896-896edb436000 |   13
 3897cc06-813e-4d96-aaa2-83493cf3cea1 |   14
 0ca0ef72-e39a-4eb7-9777-b1304016d968 |   15
 9e4fe2f0-81f6-4086-b0a9-21b0407ac021 |   16
 76167b8a-f920-4c60-b72d-cbf37b7497c9 |   17
 52397fbc-c814-4e17-8c9a-bfda5a2e72e3 |   18
 85fee8c0-1080-4431-b650-ed7f5ce424aa |   19
 13ec825d-cb2d-4031-934a-6f203e978ccf |   20
(10 rows)

If I keep the ORDER BY in a subquery and join it with another table, the pagination is random again:

postgres=# select * from
(
select * from t1
 order by val1 --> not a good idea in a subquery
) t1_ordered join t2 using(id)
 offset 10 limit 10
;
                  id                  | val1
--------------------------------------+------
 6a66b081-33ed-4a59-9772-9b11f6581ea8 |   92
 88cf2bd1-c54c-40c1-a794-f2df63cd2730 |   61
 b776d312-4e63-412a-8f8c-a6c11efd958a |   45
 6154c15c-1d34-414d-9ba3-41e7bbed453f |   64
 3ba9f440-25d1-4162-86bc-8f0ad36abaca |   66
 0bfda8d3-66b1-41b8-884f-fa85d43e37c4 |   33
 d64885a2-6b1c-447d-9be7-d7fe9726919c |   58
 baf08b7c-ea2c-475c-a2cb-6cbe47524a27 |   40
 2e698c37-5557-4998-b9e6-b4f17f599039 |   36
 6219f25c-23b6-48f1-9e5f-3ce33ef7c975 |   78
(10 rows)

PostgreSQL obeyed my ORDER BY, as an EXPLAIN shows a SORT operation, but the join method had another driving table, and the order was not preserved in the join result:

postgres=# explain (analyze, costs off, summary off)
select * from
(
select * from t1
 order by val1 --> not a good idea in a subquery
) t1_ordered join t2 using(id)
 offset 10 limit 10
;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Limit (actual time=0.056..0.061 rows=10 loops=1)
   ->  Hash Join (actual time=0.051..0.058 rows=20 loops=1)
         Hash Cond: (t2.id = t1.id)
         ->  Seq Scan on t2 (actual time=0.006..0.007 rows=20 loops=1)
         ->  Hash (actual time=0.040..0.040 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 14kB
               ->  Sort (actual time=0.024..0.029 rows=100 loops=1)
                     Sort Key: t1.val1
                     Sort Method: quicksort  Memory: 28kB
                     ->  Seq Scan on t1 (actual time=0.002..0.008 rows=100 loops=1)
(10 rows)

Unfortunately, during your tests, you may be "lucky" (I'll rather call that "unlucky" because you failed to find a bug) and have an execution plan that preserves the order:

postgres=# set enable_hashjoin=off;
SET
postgres=# set enable_mergejoin=off;
SET

postgres=# select * from
(
select * from t1
 order by val1 --> not a good idea in a subquery
) t1_ordered join t2 using(id)
 offset 10 limit 10
;
                  id                  | val1
--------------------------------------+------
 2fca8f19-7692-4eb1-918d-3bfaffd506a2 |   11
 1d78f1de-1c51-4215-9c0e-c52e1b43b735 |   12
 6187a2f1-2760-482c-9e88-21dadbf948ee |   13
 a2810410-63b9-4e12-8f36-e7e5fbeeb9e7 |   14
 65869c8f-4fae-474c-a724-37730be02267 |   15
 34719c89-131d-422f-bace-a169941daa87 |   16
 aab0465b-e9c2-4590-919d-eb4de46033dc |   17
 4f0c2070-7472-416f-a65b-acc6584f700d |   18
 73ad5b4d-b95e-48b2-88cb-942cecff27d3 |   19
 9476611c-b658-4653-9c3d-6dfb739ded36 |   20
(10 rows)

The reason is that a Nested Loop join driven by the subquery was used, and the order was preserved:

postgres=# explain (analyze, costs off, summary off)
select * from
(
select * from t1
 order by val1 --> not a good idea in a subquery
) t1_ordered join t2 using(id)
 offset 10 limit 10
;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Limit (actual time=0.061..0.070 rows=10 loops=1)
   ->  Nested Loop (actual time=0.049..0.068 rows=20 loops=1)
         ->  Sort (actual time=0.035..0.036 rows=20 loops=1)
               Sort Key: t1.val1
               Sort Method: quicksort  Memory: 28kB
               ->  Seq Scan on t1 (actual time=0.009..0.015 rows=100 loops=1)
         ->  Index Only Scan using t2_pkey on t2 (actual time=0.001..0.001 rows=1 loops=20)
               Index Cond: (id = t1.id)
               Heap Fetches: 0
(9 rows)

It is essential to understand that your query has a bug. You get the expected result as a side effect of a join algorithm that can change anytime during your application lifetime. It's the worst bug: it can pass all UAT and validations, but you will get the wrong results one day. There's only one way to find this bug: code review.

You can migrate to another database, like YugabyteDB, to benefit from resilience, elasticity, and better storage.

I execute the same on YugabyteDB, disabling hash and merge join to get a nested loop:

yugabyte=> select * from
(
select * from t1
 order by val1 --> not a good idea in a subquery
) t1_ordered join t2 using(id)
 offset 10 limit 10
;
                  id                  | val1
--------------------------------------+------
 40e79207-acd4-4967-9f64-be604a071b43 |   30
 3cf7efff-e35b-4367-a25b-882d0d354034 |  100
 3caee5cf-f5a0-4bbc-bfc6-fe01e731bf61 |   89
 3432d77f-2f5b-40d8-8a02-36a637a9f8ce |   22
 e556b1c7-61eb-4404-8ff1-7c37b58804a2 |   87
 6badf12b-b9cc-4552-9328-14a01fa53d16 |   83
 86906c1f-f2d7-4a68-813a-1babcac794ac |   23
 0f3c0f0b-5847-4560-a658-6e7f87421b4f |   34
 1c3e91ce-0c71-44ae-98d7-f7bfade33aff |   42
 3c620fa7-2e0d-46d6-b564-03643583414d |   91
(10 rows)

The result is not ordered. If you were unaware that you had a bug in your query, you might think that the new database is not PostgreSQL compatible: it was working before and is not working anymore.

No, it was not working before. An implementation detail just disguised your bug.

YugabyteDB optimizes the nested loop join by batching the results from the driving table and pushing them down as an array to the join condition:

yugabyte=> explain (analyze, costs off, summary off)
select * from
(
select * from t1
 order by val1 --> not a good idea in a subquery
) t1_ordered join t2 using(id)
 offset 10 limit 10
;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Limit (actual time=2.960..2.972 rows=10 loops=1)
   ->  YB Batched Nested Loop Join (actual time=2.144..2.967 rows=20 loops=1)
         Join Filter: (t1.id = t2.id)
         ->  Sort (actual time=1.532..1.541 rows=100 loops=1)
               Sort Key: t1.val1
               Sort Method: quicksort  Memory: 31kB
               ->  Seq Scan on t1 (actual time=0.714..1.492 rows=100 loops=1)
         ->  Index Scan using t2_pkey on t2 (actual time=0.630..0.635 rows=10 loops=2)
               Index Cond: (id = ANY (ARRAY[t1.id, $1, $2, ..., $1023]))
(9 rows)

The subquery was sorted, but the batching didn't preserve this order. There is no error in the YugabyteDB execution plan because this is how SQL works: without an ORDER BY, there's no guarantee of a sorted result.

There's only one solution: fix your SQL statement by adding an ORDER BY to the outer query:

yugabyte=> select * from
(
select * from t1
 order by val1 
) t1_ordered join t2 using(id)
 order by val1
 offset 10 limit 10
;
                  id                  | val1
--------------------------------------+------
 4262b2eb-0451-4cef-83be-443b7c6d2897 |   11
 ab7af8dc-1dbe-4147-bc30-2881a3c1c6dc |   12
 4c1a81d5-63a8-4f7c-9f1e-fed123be6ef5 |   13
 5cdf0c28-1a34-48e4-98aa-8c4ddb4dc9df |   14
 bf1308b4-9d32-47b5-81ed-922eb6993127 |   15
 11ff2dae-474b-494f-be0a-b083852e36f1 |   16
 44ce8afe-e622-4571-9fd4-f0a9cc8ece2a |   17
 28418fcc-6c88-40a3-88bb-1ec15d1b753f |   18
 73db374f-d020-4a28-b0d1-52933e1cf1c9 |   19
 b5a8091a-4a1b-4774-a48d-31385ce5465f |   20
(10 rows)

The result is correct. Of course, the ORDER BY in the subquery can be removed, but I left it there to show how YugabyteDB is smart. It didn't apply an additional sort operation and was still using a batched nested loop:

yugabyte=> explain (analyze, costs off, summary off)
select * from
(
select * from t1
 order by val1
) t1_ordered join t2 using(id)
 order by val1
 offset 10 limit 10
;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Limit (actual time=3.347..3.351 rows=10 loops=1)
   ->  YB Batched Nested Loop Join (actual time=2.080..3.348 rows=20 loops=1)
         Join Filter: (t1.id = t2.id)
         Sort Keys: t1.val1
         ->  Sort (actual time=1.484..1.493 rows=100 loops=1)
               Sort Key: t1.val1
               Sort Method: quicksort  Memory: 31kB
               ->  Seq Scan on t1 (actual time=0.698..1.445 rows=100 loops=1)
         ->  Index Scan using t2_pkey on t2 (actual time=0.795..0.813 rows=50 loops=2)
               Index Cond: (id = ANY (ARRAY[t1.id, $1, $2, ..., $1023]))
(10 rows)

There is only one line different here: Sort Keys: t1.val1
This is not an additional blocking sort operation, only a guarantee that the order is preserved, as the SQL statement requires.

Without an order in the subquery, the result would be the same but with a sort operation after the join instead of before.

Relying on a sorted outcome from a query lacking an ORDER BY clause in the final projection (the outermost select) constitutes a bug in your application, regardless of whether the result appears correct.

set parameter if exists

Franck Pachot — Wed, 29 Jan 2025 07:41:59 +0000

When you code a SQL script that must run on PostgreSQL or YugabyteDB, you may want to set additional parameters in YugabyteDB that are not recognized by PostgreSQL. Using SET or set_config in this case would raise an error on PostgreSQL.
One approach is to check if the version is compatible using version() LIKE '%-YB-%', but a simpler solution is to set all parameters and ignore any that do not exist.

For example, if you want to set the application name and, for YugabyteDB, default to range sharding, you can use the following statement:

with new_settings (name, setting) as ( values
 ('application_name', 'test'),
 ('yb_use_hash_splitting_by_default', 'on')
) update pg_settings as o set setting = n.setting
from new_settings n
where o.name = n.name and version() like '%-YB-%'
;

On PostgreSQL, only the application name will be set:

 set_config
------------
 test
(1 row)

UPDATE 0

On YugabyteDB, the same script sets both parameters:

 set_config
------------
 test
 on
(2 rows)

UPDATE 0

Note that a better option is to start the YugabyteDB cluster with "PostgreSQL Parity" enabled, which will set many parameters by default, and you should not have to set more YugabyteDB specific ones.

ESR (Equality, Sort, Range) rule applied to YugabyteDB and PostgreSQL indexes

Franck Pachot — Sun, 26 Jan 2025 18:50:56 +0000

TL;DR: To make developers' lives easier, MongoDB has documented a simple rule for creating an efficient index: the The ESR (Equality, Sort, Range) Rule. This rule applies to any databases with sorted indexes, such as B-trees or LSM trees.
Let's demonstrate it on YugabyteDB.

I recently read an article on LinkedIn (Optimizing Large Databases: Insights from PostgreSQL Index Experiments) where, in the section "What Surprised Me," it seems to suggest that a single-column index is superior to a composite index.

I responded that the composite index was a good approach for PostgreSQL but, given the filtering selectivity, the column order was not, and I provided an example: https://dbfiddle.uk/cFA1mGNe.

While I’ve written a comprehensive article on Improving Your SQL Indexing, and MongoDB published a more developer-friendly explanation of the same principle, The ESR (Equality, Sort, Range) Rule, that originates from Optimizing MongoDB Compound Indexes by A. Jesse Jiryu Davis, let's use this particular example to demonstrate it.

I've run the example on YugabyteDB 2.25, which behaves like PostgreSQL but shows more information in the execution plan with the dist and debug options.

YugabyteDB Index Scan with Sort and Range

I created the following table and inserted 100,000 rows:

create table transactions as
 select
  gen_random_uuid() as id,
  100*random() as user_id,
  now() - 90*random()*interval '1 day' as transaction_date
 from generate_series(1,100000) n
;

The following query gets 100 rows at offset 100 for a range of "user_id" sorted by "transaction_date" in descending order:

prepare q(int, int) as
 SELECT * FROM transactions 
 WHERE user_id BETWEEN $1 AND $2 
 ORDER BY transaction_date DESC LIMIT 100 OFFSET 100
;

index on (user_id, transaction_date )

With an index on (user_id, transaction_date ), it has to read 9167 index entries and 9167 table rows to filter on the "user_id" range, then sort the result and get the Top-200 to return 100 rows at offset 100:

yugabyte=> create index tx_usr_dat on transactions
           (user_id desc, transaction_date desc)
;
CREATE INDEX

yugabyte=> explain( analyze, dist, debug, costs off, summary off)
           execute q(1,10)
;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Limit (actual time=34.804..34.826 rows=100 loops=1)
   ->  Sort (actual time=34.785..34.804 rows=200 loops=1)
         Sort Key: transaction_date DESC
         Sort Method: top-N heapsort  Memory: 50kB
         ->  Index Scan using tx_usr_dat on transactions (actual time=4.249..33.520 rows=9167 loops=1)
               Index Cond: ((user_id >= '1'::double precision) AND (user_id <= '10'::double precision))
               Storage Table Read Requests: 9
               Storage Table Read Execution Time: 24.774 ms
               Storage Table Rows Scanned: 9167
               Storage Index Read Requests: 9
               Storage Index Read Execution Time: 1.775 ms
               Storage Index Rows Scanned: 9167
               Metric rocksdb_number_db_seek: 9176.000
               Metric rocksdb_number_db_next: 27468.000
               Metric docdb_keys_found: 18342.000

The query has two filters:

one in the WHERE clause on a range of "user_id" for which the selectivity is 9167 / 100,000 = 9%
one in the ORDER BY on "transaction_date" that limits to the Top-200 results, for which the selectivity is 200 / 100,000 = 0.2%

There is a third filtering with OFFSET that skips the first 100 rows, but there's no way to push it down to an index. In general, you should not use OFFSET for pagination, but keep track of the last page value and use it as an additional range predicate (Pagination with an OFFSET is better without OFFSET).

Given the selectivity of the filters that can be pushed down to an index, it is better to use the index range scan for the most selective and start the index key with "transaction_date":

yugabyte=> drop index tx_usr_dat;
DROP INDEX

yugabyte=> create index tx_dat
           on transactions (transaction_date desc)
;
CREATE INDEX

yugabyte=> explain( analyze, dist, debut, costs off, summary off) 
           execute q(1,10)
;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Limit (actual time=7.499..12.404 rows=100 loops=1)
   ->  Index Scan using tx_dat on transactions (actual time=2.630..12.380 rows=200 loops=1)
         Storage Filter: ((user_id >= '1'::double precision) AND (user_id <= '10'::double precision))
         Storage Table Read Requests: 3
         Storage Table Read Execution Time: 8.295 ms
         Storage Table Rows Scanned: 2248
         Storage Index Read Requests: 3
         Storage Index Read Execution Time: 2.925 ms
         Storage Index Rows Scanned: 2248
         Metric rocksdb_number_db_seek: 2251.000
         Metric rocksdb_number_db_next: 6769.000
         Metric docdb_keys_found: 4499.000

This has read 2,248 index entries and 2,248 table rows, four times less than with the other index. This execution plan avoids a sort operation, so it can read fewer rows (instead of reading all rows for the "user_id" range, sorting them, and obtaining the Top-200).

We can do better. The composite index was a good idea, but according to the ESR (Equality, Sort, Range) rule, the index key must start with the sort column, "transaction_date", as there is no equality predicate here, and have the range column after, "user_id":

yugabyte=> drop index tx_dat;
DROP INDEX

yugabyte=> create index tx_dat_usr on transactions 
           (transaction_date desc, user_id desc)
;
CREATE INDEX

yugabyte=> explain( analyze, dist, debug, costs off, summary off) 
           execute q(1,10)
;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Limit (actual time=3.565..3.608 rows=100 loops=1)
   ->  Index Scan using tx_dat_usr on transactions (actual time=3.520..3.586 rows=200 loops=1)
         Index Cond: ((user_id >= '1'::double precision) AND (user_id <= '10'::double precision))
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 1.236 ms
         Storage Table Rows Scanned: 200
         Storage Index Read Requests: 1
         Storage Index Read Execution Time: 2.049 ms
         Storage Index Rows Scanned: 200
         Metric rocksdb_number_db_seek: 201.000
         Metric rocksdb_number_db_next: 2615.000
         Metric docdb_keys_found: 401.000

(9 rows)

The composite index avoids sorting because the key starts with "transaction_date". It also allows additional filtering because the entries include "user_id".

The filtering to obtain the 200 rows needed for the result occurs during the initial operation of scanning the index entries. However, it requires reading more data because "user_id" is not confined to a single range. Instead, it is spread across each "transaction_date", as indicated by the number of seek and next operations.
Here, YugabyteDB reads the entries in descending order of "transaction_date", and for each entry, it successfully seeks (rocksdb_number_db_seek: 201) to the appropriate "user_id" range within it.
Two thousand index entries were read (rocksdb_number_db_next: 2615), which only occurred while scanning the index blocks.

Cost-Based Optimizer, Custom and Generic Plans

In some cases, the selectivity of the range predicate in the WHERE clause is higher than the selectivity of the selectivity of the ORDER BY ... LIMIT. Having the selective column used for the range predicate first in the index can make sense when this is the case. You can test it by changing the data inserted.

This is important because after five executions, the PostgreSQL and YugabyteDB query planner may opt for a generic plan, and the index choice may not be the best for the values you provide. There are two solutions to avoid that:

create only the necessary indexes. If you don't need an index starting with "user_id" for other queries, do not create it.
set plan_cache_mode to force_custom_plan rather than the default auto

PostgreSQL Index Scan with Sort and Range

You can run the same command in PostgreSQL and see the similar advantage of a composite index with the sort column before the range column. The differences with YugabyteDB are:

You must run VACUUM after inserting data to get reproducible behavior.
Use buffers instead of dist, debug formats for explain analyze. It will not display the number of rows scanned from the index and the table, but the number of shared buffer hits will indicate what is read.
An Index Scan in PostgreSQL will also show the rows filtered after reading the table as Rows Removed by Filter. We didn't observe that with YugabyteDB because it was pushed down to the storage even when filtering on the table's rows.
PostgreSQL doesn't do a skip scan, but reading too many index entries is less problematic since they are not distributed and can be read from the shared memory buffers.

Here is what I've run on PostgreSQL 17, creating two indexes:

create table transactions as
 select
  gen_random_uuid() as id,
  random(1,100) as user_id,
  now() - random(1,90)*interval '1 day' as transaction_date
 from generate_series(1,100000) n
;

create index tx_usr_dat on transactions (user_id, transaction_date);
create index tx_dat     on transactions (transaction_date);

vacuum analyze transactions;

prepare q(int, int) as
 SELECT * FROM transactions WHERE user_id BETWEEN $1 AND $2 ORDER BY transaction_date DESC LIMIT 100 OFFSET 100
;
explain( analyze, buffers) execute q(1,10);

Here is the execution plan for explain( analyze, buffers) execute q(1,10), picking the index starting with transaction_date to avoid a sort operation:

Limit  (cost=54.75..109.20 rows=100 width=28) (actual time=0.565..1.629 rows=100 loops=1)
  Buffers: shared hit=1067
  ->  Index Scan Backward using tx_dat on transactions  (cost=0.29..5440.29 rows=9990 width=28) (actual time=0.024..1.609 rows=200 loops=1)
        Filter: ((user_id >= 1) AND (user_id <= 10))
        Rows Removed by Filter: 1823
        Buffers: shared hit=1067
Planning:
  Buffers: shared hit=35
Planning Time: 2.211 ms
Execution Time: 1.873 ms

I replace this index with a composite one, adding "user_id" at the end of the key:


drop index tx_dat;
create index tx_dat_usr on transactions (transaction_date, user_id);

vacuum analyze transactions;

The plan using it reads fewer buffers (207 instead of 1067) and shows no Rows Removed by Filter because it was filtered by the Index Cond:

Limit  (cost=44.99..89.68 rows=100 width=28) (actual time=0.170..0.312 rows=100 loops=1)
  Buffers: shared hit=207
  ->  Index Scan Backward using tx_dat_usr on transactions  (cost=0.29..4533.64 rows=10143 width=28) (actual time=0.017..0.297 rows=200 loops=1)
        Index Cond: ((user_id >= 1) AND (user_id <= 10))
        Buffers: shared hit=207
Planning:
  Buffers: shared hit=31
Planning Time: 0.511 ms
Execution Time: 0.336 ms

The new index is covering the WHERE clause. I explained the various levels of how an index can cover a query in a recent article:

Covering Index nuances: which columns to cover (WHERE, ORDER BY, LIMIT, SELECT)?

I explained how covering indexes can reduce the required reads when scanning multiple rows in an SQL database. This is particularly useful in Distributed SQL databases, such as YugabyteDB, where additional reads may result in network latency.

linkedin.com

MongoDB Index Scan with Sort and Range

As explained in the MongoDB documentation, the ESR (Equality, Sort, Range) rule assists developers in creating composite indexes with optimal column ordering. Let's test the same example and examine the execution plans.

I created a similar dataset as documents in a collection:

db.transactions.insertMany(
  Array.from({ length: 100000 }, (_, i) => ({
    _id: new ObjectId(),
    user_id: Math.floor(Math.random() * 100),
    transaction_date: new Date(Date.now()-Math.random()*90*24*60*60*1000)
  }))
);

I created a function equivalent to the SQL prepared statement:

function queryTransactions(userIdStart, userIdEnd) {
  return db.transactions
    .find({ user_id: { $gte: userIdStart, $lte: userIdEnd } })
    .sort({ transaction_date: -1 })
    .skip(100)
    .limit(100);
}

I create the index that starts with "user_id", the range predicate before the sort, and display the execution plan:

mdb> db.transactions.createIndex({ user_id: -1, transaction_date: -1 });

user_id_-1_transaction_date_-1

mdb> queryTransactions(1, 10).explain("executionStats").executionStats;

{
  executionSuccess: true,
  nReturned: 100,
  executionTimeMillis: 12,
  totalKeysExamined: 9968,
  totalDocsExamined: 100,
  executionStages: {
    isCached: false,
    stage: 'FETCH',
    nReturned: 100,
    executionTimeMillisEstimate: 11,
    works: 10170,
    advanced: 100,
    needTime: 10069,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    docsExamined: 100,
    alreadyHasObj: 0,
    inputStage: {
      stage: 'SKIP',
      nReturned: 100,
      executionTimeMillisEstimate: 9,
      works: 10170,
      advanced: 100,
      needTime: 10069,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      skipAmount: 100,
      inputStage: {
        stage: 'SORT',
        nReturned: 200,
        executionTimeMillisEstimate: 9,
        works: 10170,
        advanced: 200,
        needTime: 9969,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        sortPattern: { transaction_date: -1 },
        memLimit: 33554432,
        limitAmount: 200,
        type: 'default',
        totalDataSizeSorted: 13800,
        usedDisk: false,
        spills: 0,
        spilledDataStorageSize: 0,
        inputStage: {
          stage: 'IXSCAN',
          nReturned: 9968,
          executionTimeMillisEstimate: 8,
          works: 9969,
          advanced: 9968,
          needTime: 0,
          needYield: 0,
          saveState: 0,
          restoreState: 0,
          isEOF: 1,
          keyPattern: { user_id: -1, transaction_date: -1 },
          indexName: 'user_id_-1_transaction_date_-1',
          isMultiKey: false,
          multiKeyPaths: { user_id: [], transaction_date: [] },
          isUnique: false,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: {
            user_id: [ '[10, 1]' ],
            transaction_date: [ '[MaxKey, MinKey]' ]
          },
          keysExamined: 9968,
          seeks: 1,
          dupsTested: 0,
          dupsDropped: 0
        }
      }
    }
  }
}

The index bounds array makes it clear how the index is scanned by ranges (It must be guessed from the Index Cond in PostgreSQL, or the number of seeks in YugabyteDB):

          indexBounds: {
            user_id: [ '[10, 1]' ],
            transaction_date: [ '[MaxKey, MinKey]' ]
          },
          keysExamined: 9968,

9968 keys have been examined, as they were filtered only on "user_id" with a full range of "transaction_date."
Those have been sorted to get the Top-200 (stage: 'SORT', limitAmount: 200) and skipped to get the offset (stage: 'SKIP', skipAmount: 100).

I ran the same after creating the index that follows the ESR rule:

mdb> db.transactions.createIndex({ transaction_date: -1, user_id: -1 });

transaction_date_-1_user_id_-1

mdb> queryTransactions(1, 10).explain("executionStats").executionStats
{
  executionSuccess: true,
  nReturned: 100,
  executionTimeMillis: 20,
  totalKeysExamined: 1902,
  totalDocsExamined: 1902,
  executionStages: {
    isCached: false,
    stage: 'LIMIT',
    nReturned: 100,
    executionTimeMillisEstimate: 18,
    works: 1903,
    advanced: 100,
    needTime: 1802,
    needYield: 0,
    saveState: 1,
    restoreState: 1,
    isEOF: 1,
    limitAmount: 100,
    inputStage: {
      stage: 'SKIP',
      nReturned: 100,
      executionTimeMillisEstimate: 18,
      works: 1902,
      advanced: 100,
      needTime: 1802,
      needYield: 0,
      saveState: 1,
      restoreState: 1,
      isEOF: 0,
      skipAmount: 100,
      inputStage: {
        stage: 'FETCH',
        filter: { '$and': [ { user_id: [Object] }, { user_id: [Object] } ] },
        nReturned: 200,
        executionTimeMillisEstimate: 17,
        works: 1902,
        advanced: 200,
        needTime: 1702,
        needYield: 0,
        saveState: 1,
        restoreState: 1,
        isEOF: 0,
        docsExamined: 1902,
        alreadyHasObj: 0,
        inputStage: {
          stage: 'IXSCAN',
          nReturned: 1902,
          executionTimeMillisEstimate: 3,
          works: 1902,
          advanced: 1902,
          needTime: 0,
          needYield: 0,
          saveState: 1,
          restoreState: 1,
          isEOF: 0,
          keyPattern: { transaction_date: -1, user_id: -1 },
          indexName: 'transaction_date_-1_user_id_-1',
          isMultiKey: false,
          multiKeyPaths: { transaction_date: [], user_id: [] },
          isUnique: false,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: {
            transaction_date: [ '[MaxKey, MinKey]' ],
            user_id: [ '[MaxKey, MinKey]' ]
          },
          keysExamined: 1902,
          seeks: 1,
          dupsTested: 0,
          dupsDropped: 0
        }
      }
    }
  }
}

The index bounds show that no range scan is applied:

          indexBounds: {
            transaction_date: [ '[MaxKey, MinKey]' ],
            user_id: [ '[MaxKey, MinKey]' ]
          },
          keysExamined: 1902,

Still, it examined less keys in IXSCAN because there's no SORT operation and the SKIP and LIMIT can operate without reading all entries.

However, even if the "user_id" is present in the index, the filtering happens in a FETCH operation, after getting the document:

      inputStage: {
        stage: 'FETCH',
        filter: { '$and': [ { user_id: [Object] }, { user_id: [Object] } ] },
        nReturned: 200,
        docsExamined: 1902,
        inputStage: {
          stage: 'IXSCAN',
          nReturned: 1902,

In this example (running in MongoDB 8.0), having "user_id" in the index was useless, and I would have the same execution plan with an index on { transaction_date: -1 } only. That may be a topic for a future blog post.

In short, when the first field of the index is used by sort, like here no "Equality" in "Equality,Sort,Range", there must be a predicate on it to push down the range predicate to the IXSCAN's index bounds. I add transaction_date: { $gte: MinKey }:

mdb> function queryTransactions(userIdStart, userIdEnd) {
  return db.transactions
    .find({
      user_id: { $gte: userIdStart, $lte: userIdEnd } ,
      transaction_date: { $gte: MinKey }
    }).sort({ transaction_date: -1 })
    .skip(100)
    .limit(100);
}

mdb> queryTransactions(1, 10).explain("executionStats").executionStats;

{
...
  nReturned: 100,
  totalKeysExamined: 2012,
  totalDocsExamined: 100,
  executionStages: {
    stage: 'LIMIT',
    nReturned: 100,
...
      inputStage: {
        stage: 'SKIP',
        nReturned: 100,
...
    inputStage: {
      stage: 'FETCH',
      nReturned: 100,
      docsExamined: 100,
...
        inputStage: {
          stage: 'IXSCAN',
          nReturned: 200,
          keyPattern: { transaction_date: -1, user_id: 1 },
          indexName: 'transaction_date_-1_user_id_1',
          multiKeyPaths: { transaction_date: [], user_id: [] },
          direction: 'forward',
          indexBounds: {
            transaction_date: [ '[MaxKey, MinKey]' ],
            user_id: [ '[1, 10]' ]
          },
          keysExamined: 2012,
          seeks: 1813,
...

This IXSCAN covers the range filter in addition to the sort, and only the necessary 200 documents have been examined by FETCH after the SKIP.

This demonstrates a key principle for creating composite indexes (ESR). When your query includes a selective equality predicate (E), your index key should begin with it to allow a single range to be read during the index scan. Particularly for pagination queries, the index key must add the sort (S) keys after, ensuring that the single range is returned in the correct order. If a selective range (R) predicate exists instead of a sort, it should be placed in the index key following the equality predicates. When both sort (S) and range (R) are present, the decision depends on selectivity and the database's ability to filter after a sorted range. Generally, avoiding a sort operation is preferable for scalability, as the sorting cost rises with the data volume.

This ESR rule applies to YugabyteDB:

the column used for equality predicate (E) must be in front of the index key and can be HASH, ASC, or DESC
the columns used to sort (S) for ORDER BY ... LIMIT follow in the index key, with ASC or DESC. Try to match the ORDER BY ASC/DESC to avoid backward scans that are more expensive in an LSM tree. If there's no sort to prevent it, the most selective range will follow
the columns used for range (R) or other predicate can follow in the key definition or the INCLUDE clause to filter the index entries before fetching the rows from the table. If it is the primary key index, there's no need to add it because all columns are in the primary index.

This article was full of links. Here is one more from a workshop on indexing best practices:

PostgreSQL 15 Compatibility in YugabyteDB 2.25: Top 15 Features!

Franck Pachot — Wed, 22 Jan 2025 07:15:45 +0000

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!

Aggregates with NULL: count(), min(), max(), sum()

Franck Pachot — Fri, 17 Jan 2025 13:33:18 +0000

In the previous blog, I explained that a NULL in a column indicates an unknown value rather than a non-existent one. In a table row, values must exist, with a value or a null, when the value is unknown. In a normalized data model, missing values are represented by the absence of rows, not by null. However, outer joins can create rows for non-existing values. All their columns, including the primary key, contain NULLs.

Arithmetic with NULL is simple when you consider it as unknown.

Many operations have an unknown result when one operand is unknown:

yugabyte=# with xyz(x,y,z) as (
 values ( 
     cast(0 as int), cast(300 as int), cast(null as int)
  )
) select x,y,z
 , x+y+z as "x+y+z"
 , x-y-z as "x-y-z"
 , x*y*z as "x*y*z"
from xyz
;
 x |  y  | z | x+y+z | x-y-z | x*y*z
---+-----+---+-------+-------+-------
 0 | 300 |   |       |       |
(1 row)

It makes sense. How can you know the sum without knowing all values?

Unfortunately, databases may have different implementations where a function is explicitly defined as ignoring null operands. For example, that's what PostgreSQL does for for greatest() and least() functions:

with xyz(x,y,z) as (
 values ( cast(0 as int), cast(300 as int), cast(null as int))
) select xyz.*
 , greatest(x,y,z) "greatest(x,y,z)"
 ,    least(x,y,z)    "least(x,y,z)"
from xyz
;

 x |  y  | z | greatest(x,y,z) | least(x,y,z)
---+-----+---+-----------------+--------------
 0 | 300 |   |             300 |            0
(1 row)

According to the SQL standard, these functions should return null when one argument is null. PostgreSQL does differently, and YugabyteDB is compatible with PostgreSQL.

The documentation explains the behavior, and Markus Winand compares all databases: https://modern-sql.com/caniuse/greatest-least#null

In the SQL standard, aggregate functions behave differently. They are defined as ignoring null values.

I'm using the table created in the previous post, where I had three employees with a commission: one unknown, one known to be zero, and another known to be 300.

yugabyte=# select * from commissions;
 empno | comm
-------+------
  7839 |
  7499 |  300
  7844 |    0
(3 rows)

yugabyte=# select 
 count(*), count(comm) , min(comm) , max(comm) , sum(comm)
 from commissions
;

 count | min | max | sum
-------+-----+-----+-----
     2 |   0 | 300 | 300
(1 row)

Unknown values are ignored, and two employees have commissions with known values: one at zero and another at 300. This distinction between zero and null is crucial and shows up here.

Furthermore, it's vital to distinguish between a non-existent commission and one that is existing but unknown or zero. Although I have only two known commissions, I know I have three in total, including an unknown one. This highlights the difference between counting column values and counting rows (which incorporate existing commissions that may be unknown):

yugabyte=# select count(*) as "count(*)", count(comm) as "count(comm)"
 from commissions
;

 count(*) | count(comm)
----------+-------------
        3 |           2
(1 row)

count(*) counts rows, including those with null values, but count() on a column counts the known values for this column.

In my last post, I discussed how a left outer join can display all employees alongside their commissions. Employees with existing but unknown commissions have their column value null, except their primary key. Employees with no existing commission show up in the outer join but with their primary key columns being also null.

Here are all counts on the left outer join, counting rows, counting null keys, and counting null columns:

yugabyte=# select employees.*, commissions.*
 from employees left outer join commissions using(empno)
;
 empno |    job    | ename  | sal  | empno | comm
-------+-----------+--------+------+-------+------
  7839 | PRESIDENT | KING   | 5000 |  7839 |
  7499 | SALESMAN  | ALLEN  | 1600 |  7499 |  300
  7844 | SALESMAN  | TURNER | 1500 |  7844 |    0
  7788 | ANALYST   | SCOTT  | 3000 |       |
(4 rows)

yugabyte=# select count(*) as "employees"
     , count(commissions.empno) as "emp. with commissions"
     , count(comm) "emp. with known commissions"
 from employees left outer join commissions using(empno)
;
 employees | emp. with commissions | emp. with known commissions
-----------+-----------------------+-----------------------------
         4 |                     3 |                           2
(1 row)

The databases show four employees. Three of them have a commission, and two of them have a known commission.

What does NULL mean in an OUTER JOIN? ("unknown" vs. "not exists")

Franck Pachot — Thu, 16 Jan 2025 18:45:38 +0000

In a previous post, I briefly mentioned that I consider a NULL in SQL databases as an existing but unknown value instead of a not existing one. But there's an exception in outer joins where a non-existent row will have null values in the results. Let's clarify it with an example. It’s important to distinguish between columns with a null value and columns from inexisting rows where all values are null, including the key.

Here is an example extracted from the traditional EMP/DEPT schema. I don't show the create table on purpose because the way it is traditionally built in SQL examples is incorrect normalization.

yugabyte=# select * from emp order by 1 
;

 empno | ename  |    job    | sal  | comm
-------+--------+-----------+------+------
  7499 | ALLEN  | SALESMAN  | 1600 |  300
  7788 | SCOTT  | ANALYST   | 3000 |
  7839 | KING   | PRESIDENT | 5000 |
  7844 | TURNER | SALESMAN  | 1500 |    0

NULL in the commission column may have different meanings.

ALLEN and TURNER are SALESMAN, and a commission is part of the package. The value exists and is known. The absence of a commission for TURNER is not a NULL but simply a zero amount, maybe because he didn't reach his objectives.
KING is the PRESIDENT. He may have a commission, but he doesn't want to disclose it, so a NULL was inserted instead. The commission exists but is unknown in the system.
SCOTT works as an ANALYST, and the package for this position doesn't include a commission. It's not a zero commission. The commission doesn't exist for this job. Some systems display that as N/A (not applicable). No commission has been inserted, but a NULL was stored because the table has this column and no default value. In SQL, a NULL signifies "unknown," but in this case, SCOTT has no commission, and this is known.

This appears to contradict the definition of NULL. Nevertheless, the issue is not with SQL definitions but instead with this application's data model.

The EMP table is not normalized. There's a dependency between the job and the existence of a commission. The commission should not belong to the employee as an attribute but a one-to-one association. Some jobs do not have a commission, so the association is more precisely a one-to-zero-or-one. If the commission is a row in another table, the absence of a row can represent its non-existence, and it is different from a row with a key but a null value that represents an existing but unknown value.

Here is the normalized data model:

create table employees (
 primary key (empno)
 , empno int
 , job text
 , ename text
 , sal   numeric
);

create table commissions (
 primary key (empno)
 , empno int references employees
 , comm  numeric
);

insert into employees values
 (7839,'PRESIDENT','KING'  ,5000)
,(7788,'ANALYST',  'SCOTT' ,3000)
,(7499,'SALESMAN', 'ALLEN' ,1600)
,(7844,'SALESMAN', 'TURNER',1500)
;

insert into commissions values
 (7839,null) -- KING   commission is unknown (undisclosed)
,(7499,300)  -- ALLEN  package includes a commission, he has 300
,(7844,0)   -- TURNER package includes a commission but has none
;

I still have four employees, but only three have a commission:

yugabyte=# select * from employees order by 1
;
 empno |    job    | ename  | sal
-------+-----------+--------+------
  7499 | SALESMAN  | ALLEN  | 1600
  7788 | ANALYST   | SCOTT  | 3000
  7839 | PRESIDENT | KING   | 5000
  7844 | SALESMAN  | TURNER | 1500
(4 rows)

yugabyte=# select * from commissions order by 1
;
 empno | comm
-------+------
  7499 |  300
  7839 |
  7844 |    0
(3 rows)

The problem with SQL, compared to document databases, is that the result of a query must be flattened to one table. This is where an outer join appears to materialize the result.

With my normalized model, when I want to list the employees with their commission I join them with an outer join:

yugabyte=# select * 
 from employees
 left outer join commissions
  using(empno)
  order by 1
;
 empno |    job    | ename  | sal  | comm
-------+-----------+--------+------+------
  7499 | SALESMAN  | ALLEN  | 1600 |  300
  7788 | ANALYST   | SCOTT  | 3000 |
  7839 | PRESIDENT | KING   | 5000 |
  7844 | SALESMAN  | TURNER | 1500 |    0
(4 rows)

Interestingly, that's the initial table I had at the beginning, but now it is a query result that doesn't have to be normalized. The joined tables were modeled correctly regarding nulls and inexisting rows, but the results have discarded this information.

To get this information, I can add an expression to the projection that will differentiate an existing commission from a commission with an unknown value. The existing one has a null in all columns, including the key. The unknown one has a non-nullable key, like all rows in an SQL database:

yugabyte=# select *
 , commissions.empno is not null as "commission?"
 from employees
 left outer join commissions
  using(empno)
  order by 1
;
 empno |    job    | ename  | sal  | comm | commission?
-------+-----------+--------+------+------+-------------
  7499 | SALESMAN  | ALLEN  | 1600 |  300 | true
  7788 | ANALYST   | SCOTT  | 3000 |      | false
  7839 | PRESIDENT | KING   | 5000 |      | true
  7844 | SALESMAN  | TURNER | 1500 |    0 | true
(4 rows)

In an SQL table, the columns belonging to the primary key cannot be null because the key must be known to identify the row. When the key columns become null in a query result, they result from the row's absence, which denotes inexisting information rather than unknown information.

If you store employees and their commissions in one table, you denormalize it and lose important information. This is why it seems that a NULL can have meanings beyond just unknown information. With a proper data model, the meanings are differentiated: a null column value indicates an existing but unknown value and a non-existing row signifies the absence of information.

PostgreSQL plan_cache_mode

Franck Pachot — Tue, 14 Jan 2025 14:10:09 +0000

Here is a small test case to demonstrate plan_cache_mode, a PostgreSQL 12 feature available in YugabyteDB 2.25

                                                                                            yugabyte=> \c
psql (16.2, server 15.2-YB-2.25.0.0-b0)
You are now connected to database "yugabyte" as user "yugabyte".

yugabyte=> drop table if exists t; 
DROP TABLE

yugabyte=> deallocate all;
show plan_cache_mode;
DEALLOCATE ALL

yugabyte=> show plan_cache_mode;
 plan_cache_mode
-----------------
 auto
(1 row)

I create a small table and a prepared query:

yugabyte=> create table t 
           as select generate_series(1,100000) val
;
SELECT 100000

yugabyte=> prepare query(int) 
           as select * from t where val=$1
;
PREPARE

yugabyte=> explain (analyze) execute query(42);

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=180.00..1098.09 rows=5 width=4) (actual time=56.087..56.092 rows=1 loops=1)
   Storage Filter: (val = 42)
 Planning Time: 5.451 ms
 Execution Time: 56.162 ms
 Peak Memory Usage: 48 kB
(5 rows)

The execution plan shows that the parameter $1 was replaced with the value 42 before query planning.

I execute the same prepared statement five more times:


yugabyte=> \watch count=5
                            Tue 14 Jan 2025 01:45:56 PM GMT (every 2s)

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=180.00..1098.09 rows=5 width=4) (actual time=88.165..88.170 rows=1 loops=1)
   Storage Filter: (val = 42)
 Planning Time: 0.091 ms
 Execution Time: 88.220 ms
 Peak Memory Usage: 24 kB
(5 rows)

                            Tue 14 Jan 2025 01:45:58 PM GMT (every 2s)

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=180.00..1098.09 rows=5 width=4) (actual time=22.839..22.845 rows=1 loops=1)
   Storage Filter: (val = 42)
 Planning Time: 0.128 ms
 Execution Time: 22.900 ms
 Peak Memory Usage: 24 kB
(5 rows)

                            Tue 14 Jan 2025 01:46:00 PM GMT (every 2s)

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=180.00..1098.09 rows=5 width=4) (actual time=36.821..36.826 rows=1 loops=1)
   Storage Filter: (val = 42)
 Planning Time: 0.133 ms
 Execution Time: 36.878 ms
 Peak Memory Usage: 24 kB
(5 rows)

                            Tue 14 Jan 2025 01:46:02 PM GMT (every 2s)

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=180.00..1098.09 rows=5 width=4) (actual time=22.879..22.885 rows=1 loops=1)
   Storage Filter: (val = 42)
 Planning Time: 0.093 ms
 Execution Time: 22.935 ms
 Peak Memory Usage: 24 kB
(5 rows)

                            Tue 14 Jan 2025 01:46:04 PM GMT (every 2s)

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=180.00..1098.09 rows=5 width=4) (actual time=22.278..22.283 rows=1 loops=1)
   Storage Filter: (val = $1)
 Planning Time: 0.107 ms
 Execution Time: 22.331 ms
 Peak Memory Usage: 24 kB
(5 rows)

After five executions, the query planner evaluates an execution plan that can be used for any value (val = $1 instead of val = 42). SQL Server would call that disabling parameter sniffing, and Oracle would call that disabling bind peeking. The plan may not be the best one for a specific value, but it will be stable and avoid spending more planning time in future executions:

yugabyte=> explain (analyze) execute query(42);

                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=180.00..1098.09 rows=5 width=4) (actual time=22.857..22.862 rows=1 loops=1)
   Storage Filter: (val = $1)
 Planning Time: 0.012 ms
 Execution Time: 22.919 ms
 Peak Memory Usage: 24 kB
(5 rows)

The difference in planning time is small for a simple query without indexes, but it matters for more complex queries.

For the demonstration, I'll create an index that is faster but works only for some values, and then cannot be used by a generic plan:

yugabyte=> create index on t(val) where val<50;
CREATE INDEX

My prepared statement still use the generic plan:

yugabyte=> explain (analyze) execute query(42);
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=180.00..1098.09 rows=5 width=4) (actual time=22.647..22.653 rows=1 loops=1)
   Storage Filter: (val = $1)
 Planning Time: 11.583 ms
 Execution Time: 22.719 ms
 Peak Memory Usage: 24 kB
(5 rows)

With plan_cache_mode I can force a custom or generic plan rather than relying on the automatic mechanism:

yugabyte=> set plan_cache_mode = force_custom_plan;
SET

yugabyte=> explain execute query(42);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

yugabyte=> set plan_cache_mode = force_generic_plan;
SET

yugabyte=> explain execute query(42);
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on t  (cost=180.00..1098.09 rows=5 width=4)
   Storage Filter: (val = $1)
(2 rows)

Given the cost of the custom plan for this value, it is better to use a custom plan rather than switching to a generic one. When using the auto plan cache mode, the query planner evaluates a generic plan after five executions but can decide to keep the custom plan if the cost is lower so that the estimated total execution (planning plus execution) remains cheaper than the execution of the generic plan:

yugabyte=> set plan_cache_mode = auto;
SET

yugabyte=> explain execute query(42);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

yugabyte=> \watch count=10 0.01
               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

               Tue 14 Jan 2025 01:56:29 PM GMT (every 0.01s)

                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Only Scan using t_val_idx on t  (cost=180.00..555.06 rows=5 width=4)
   Index Cond: (val = 42)
(2 rows)

The query planner never switched to the generic plan because of its higher estimated cost. I made this example with a partial index, it also happens when the custom plan allows partition pruning.

A final note: the five executions are hardcoded in PostgreSQL, but YugabyteDB made it configurable:

           List of configuration parameters
                  Parameter                   | Value
----------------------------------------------+-------
 plan_cache_mode                              | auto
 yb_planner_custom_plan_for_partition_pruning | on
 yb_test_planner_custom_plan_threshold        | 5
(3 rows)

It has a test in its name because the main reason is regression tests, where you want to see the behavior without executing it multiple times.

You may see a generic plan later with the JDBC driver because the five executions kick in only when the statement is prepared servrr-side, and pgJDBC doesn't do it immediately. It waits for the fith client-side execution before a server-side prepared statement is created, so it's finally the tenth execution that can switch to a generic plan. The client-side uses the same value, five, but it is configurable with the prepareThreshold JDBC property.

could not serialize access due to concurrent update

Franck Pachot — Tue, 14 Jan 2025 11:42:24 +0000

The following error message from YugabyteDB may not be clear if you don't know how isolation levels and read/write consistency works in YugabyteDB:

pq: could not serialize access due to concurrent update (query layer retry isn't possible because data was already sent, if this is the read committed isolation (or) the first statement in repeatable read/ serializable isolation transaction, consider increasing the tserver gflag ysql_output_buffer_size)

Here is an explanation I've given to a user, which may help others get the whole picture.

Your application performs a transaction that involves both reading and writing data. It is crucial that reads occur from a consistent state.

If you are using repeatable read/ serializable isolation transaction, this consistent state, the read time, is established at the start of the transaction. If using read committed isolation level, it is established at the start of each statement

You can verify the current isolation level by executing SELECT current_setting('yb_effective_transaction_isolation_level').

The read committed isolation level is generally sufficient, except in cases where initial reads impact subsequent updates. For example, if you read a list of available seats on a plane without using the 'FOR UPDATE' clause, and then attempt to book a seat afterward, you want to ensure your update fails if someone else booked that same seat in the interim. This scenario requires the repeatable read isolation level.

When writing, the application commits to the state as it exists at the time of the commit, corresponding to the end of the transaction. All write operations, including updates or the reads that use the 'SELECT FOR UPDATE' clause, lock the data they modify to ensure the state written to remains until the transaction is complete. That's how the state at the time of commitment matches the state at the time of the writing, but it's still a higher point in time than the read state.

To avoid readers blocking writers, the same (locking) doesn't happen for read, and the state that is read may be modified by other transactions. Your transaction must detect it because it cannot commit a write state that conflicts with the read state. Think about time-traveling movies where modifying the past makes the future inconsistent.

Suppose another transaction alters and commits a row that you have read and write later. The read and write states are inconsistent. A conflict will be detected in that case, resulting in an error message stating could not serialize access due to concurrent update. This occurs because committing both transactions would create inconsistency.

The likelihood of encountering such a conflict is more significant in the repeatable read isolation level than in read committed since the reading duration is longer (the duration of the entire transaction as opposed to a single statement).

If the conflicting statement is the first statement of the transaction, the database can roll back and restart using a newer read time without raising an error. If it is not the first statement, it can still restart at the read committed isolation level because this level allows for a newer read time. An implicit savepoint is taken before each statement. This means using read-committed isolation or being at the first statement in a repeatable read or serializable isolation transaction.

Another scenario in which the database cannot transparently roll back and restart is if data has already been sent to the application. The application may have performed non-transactional actions based on that data (such as writing to a file, sending an email, or pushing to a queue). The database must raise an error in such cases, as only the application can cancel or compensate for those non-transactional actions. Transparent query layer retry isn't possible because data was already sent.

Data returned to the application is buffered, but this situation may arise if a query returns many rows. You can mitigate this issue by increasing the tserver gflag ysql_output_buffer_size when starting the cluster.

Here is how to get the error when it is not the first statement:

yugabyte=> \set VERBOSITY verbose
yugabyte=> create table demo (id int primary key, value int);
CREATE TABLE
yugabyte=> insert into demo values (1,0),(2,0) ;
INSERT 0 2
yugabyte=> begin transaction isolation level repeatable read;
BEGIN
yugabyte=*>  select * from demo where id=1;
 id | value
----+-------
  1 |     0
(1 row)

yugabyte=*> -- another transaction
yugabyte=*> \! psql -c 'update demo set value=value+1 where id=2'
UPDATE 1
yugabyte=*>  -- not the first statement in repeatable read/ serializable isolation transaction
yugabyte=*>  update demo set value=2 where id=2 returning *;

ERROR:  40001: could not serialize access due to concurrent update (query layer retry isn't possible because data was already sent, if this is the read committed isolation (or) the first statement in repeatable read/ serializable isolation transaction, consider increasing the tserver gflag ysql_output_buffer_size)
DETAIL:  Conflict with concurrently committed data. Value write after transaction start: doc ht ({ physical: 1736852963319098 }) >= read time ({ physical: 1736852963105679 }), key: SubDocKey(DocKey([], [2]), []): kConflict

LOCATION:  conflict_resolution.cc:963

The SQLSTATE is 40001, a retryable error. You can implement a retry logic, but it is also essential to understand how it happened. This blog post explained all the terms in the error message and described what is behind it.

Large IntentsDB MemTable with Many Small SST Files

Franck Pachot — Wed, 08 Jan 2025 15:14:39 +0000

An SQL transaction comprises many sequential reads and writes that affect different objects. These may be stored on other servers in a distributed database.
The read and write operations include writing to table rows, which will be made visible and durable at commit (the A and D of ACID), and some lock information (aka transaction intents or provisional records) that must be visible to other concurrent transactions for consistency and concurrency control purposes (the C and I of ACID).
Once the transaction is committed, only a part of it needs to be stored for the long term: the effective modifications to the table rows and the commit time.

Recording, sharing, and cleaning the transaction intents

Some databases store intents, like locks, in memory to make them easy to access and clean up once committed. However, this doesn't scale. First, it cannot scale horizontally because memory cannot be shared efficiently between servers over the network. Even with a single server, memory is limited, and databases using this architecture escalate multiple locks, for example, locking a whole table when too many records are updated. SQL Server uses Lock Escalation.

Other databases store all intents in persistent data blocks. They add a reference to the transaction table so that readers can know if the related transaction is ongoing (and its changes are not visible to others) or committed (and its changes are visible to all transactions that read a state after the commit time). The provisional information stored in those blocks is cleaned up later to avoid this overhead of reads. Jonathan Lewis described this for Oracle Database in Clean it up. PostgreSQL is designed for asynchronous cleanup by running VACUUM frequently.

In a distributed SQL database, whether storing intents in memory or on disk, reading and writing during a transaction involve network calls and add latency. Aurora DSQL, with Optimistic Concurrency Control, avoids this by storing the transaction intents locally and synchronizing only at commit. To achieve this, it introduces many limits on the number of rows and memory used by transactions and the SQL isolation levels, which are incompatible with applications written for other databases.

YugabyteDB is PostgreSQL compatible, which requires sharing the transaction intents through the network using Raft consensus. In each Raft group, called a tablet, the long-term storage LSM tree, RegularDB, stores only clean data with a commit time and no unnecessary provisional intents once the transactions are committed. Before a transaction is committed, all provisional records are stored in another LSM tree, IntentsDB. Instead of a delayed cleanup, the committed intents are applied to RegularDB asynchronously. Once in RegularDB, no further cleanup is needed, except for the garbage collection of Multi-Version Concurrency Control (MVCC) done during SST file compaction.

Unlike Oracle or PostgreSQL, YugabyteDB does not piggyback delayed cleanup onto the read workload, which is advantageous for performance predictability and the performance of read replicas. A read in YugabyteDB does not generate Write-Ahead Logging (except with Serializable isolation level that must record read intents when not in a read-only transaction).

YugabyteDB PostgreSQL-compatible Distributed Transactions

It's all about trade-offs. A YugabyteDB transaction that reads a table row or an index entry must read from two databases: RegularDB, which is the long-term storage, and IntentsDB to see if there are ongoing transactions with locks or committed transactions that have not yet been applied to RegularDB. This would be a performance penalty in traditional databases. Still, YugabyteDB uses LSM trees, where reading from multiple sources is already the nominal read path as it merges reads from multiple SST files or MemTables. Reading from IntentsDB adds an entry to the iterator, usually a MemTable only, except with very long transactions that may have flushed to SST files. YugabyteDB was built for modern storage, SSD, where random reads are fast - this would not have been efficient with HDD.

I mentioned MemTable for IntentsDB and RegularDB. Memory access is fast, but even if replicated in YugabyteDB, it is protected by Write-Ahead Logging (WAL) for additional protection. YugabyteDB's LSM tree implementation is based on RocksDB. Still, it doesn't use RocksDB's WAL because logging is already generated for the Raft replication, and the Raft log can be used to recover the MemTable after a crash. This WAL protects the IntentsDB MemTable, which can be discarded when flushed into an SST file. In case of a crash, the WAL is read and applied to the IntentsDB MemTable, recovering the state with all uncommitted provisional records and some committed changes.

Write-Ahead Logging and MemTable Flush

There's one more detail. When the committed changes from IntentsDB are applied asynchronously to RegularDB, the provisional records are deleted from IntentsDB. This doesn't release memory (that's how RocksDB works, and it avoids managing fragmentation), but they are discarded when flushing to SST files. With many small transactions, you will see IntentsDB using memory but generating few or no SST files.
However, the committed records applied to RegularDB are not protected, as RocksDB WAL is not used. Once they are flushed from IntentsDB, the WAL from Raft log can be discarded. To recover from a crash, no unflushed MemTable must remain in RegularDB when their intents are flushed from IntentsDB. For this reason, when IntentsDB is flushed to release memory, a flush of RegularDB must happen. YugabyteDB checks if one happened recently (intents_flush_max_delay_ms, which defaults to two seconds) to force one. Note that the WAL is not discarded immediately after IntentsDB is flushed because it keeps a minimum of two files (the default for log_min_segments_to_retain and 15 minutes (the default for log_min_seconds_to_retain) to be able to resolve the gap with a Raft follower in case of short network failures.

Large allocated memory for IntentsDB

The following screenshot summarizes this. The memory allocation for the IntentsDB MemTable is larger than for RegularDB because the workload had many small transactions. They filled the IntentsDB MemTable until it reached the memory thresholds per tablet, with a default of 128MB defined by memstore_size_mb. However, in a busy system, this happens before reaching the per-tablet threshold because of two global cache thresholds: global_memstore_size_mb_max, which defaults to 2GB, and global_memstore_size_percentage, which defaults to 10% of the T-Server's memory.

Small SST files for RegularDB

Another consequence of flushing triggered by many deleted intents is the generation of small SST files for short transaction workloads, especially if some transactions have rollbacked. Because IntentsDB is flushed frequently and contains deleted provisional intents, it generates no or small SST files. However, it also generates small SST files for RegularDB, which are flushed simultaneously.

I described this to explain what you may observe and find counterintuitive if you are unaware of those implementation details. However, it doesn't mean that there is a problem with it. Keeping memory allocated for IntentsDB deleted records is an optimization to avoid the overhead of memory fragmentation. The memory is managed to flush tablets to release space. The screenshot above is from a test with only one tablet active with data ingest, and that's why IntentsDB reaches the 128MB limit. But in a busy database, the global cache thresholds trigger a flush before it reaches this size. It also shows that the number of SST files never exceeds six because they are compacted in the background, limiting the read amplification due to many small SST files.

Fast-path for single-shard transactions

Note that transactions not involving multiple nodes are optimized with a 'fast path' as the intents don't need to be synchronized through Raft. They bypass IntentsDB and write the committed changes directly to RegularDB. Here is a summary of the read-and-write transactional paths:

The full presentation is:

Index Filtering in PostgreSQL and YugabyteDB (Index Scan instead of Index Only Scan)

Franck Pachot — Fri, 03 Jan 2025 11:32:24 +0000

This is inspired by Henrietta Dombrovskaya's recent post (Can we use this index, please? – Why not?), which reveals an SQL optimization method that requires the user to rewrite the query in PostgreSQL. This is also a tiny counter-example to my general assertion that seeking the fully covering index, the Index Only Scan, isn't always necessary, as I explained in Covering Index nuances: which columns to cover (WHERE, ORDER BY, LIMIT, SELECT)?
In this scenario, with PostgreSQL, an Index-Only Scan was necessary to optimize the filtering, even with an index that includes all columns utilized by the filter condition.
Additionally, I will demonstrate that in some cases, YugabyteDB outperforms by filtering directly on the index entries, even during an Index Scan, and a smaller index can be good enough.

Example with an immutable function (EXTRACT on TIMESTAMP)

I'll start with an example that diverges from Hettie's case. For the demonstration with immutable function predicates, I use TIMESTAMP instead of TIMESTAMPTZ, although TIMESTAMPTZ is often recommended for storing points in time as they relate to a time zone.

PostgreSQL with immutable function

I created the following table where columns "category" and "created_at" are indexed:

create table demo (
 id bigserial
 ,category   int
 ,created_at timestamp
 ,filler text default lpad('x',9999,'x')
)
;

insert into demo(category,created_at)
 select 0, now() + generate_series(0,99999) * interval '1 minute'
;

create index on demo ( category, created_at )
;

vacuum analyze demo
;

This table is stored in 2223 pages of heap table and 388 pages of index, as visible from the query planner statistics and also when scanning them though explain (analyze buffers):

postgres=> select relname, relpages, reltuples from pg_class where oid in('demo'::regclass,'demo_category_created_at_idx'::regclass);
           relname            | relpages | reltuples
------------------------------+----------+-----------
 demo                         |     2223 |    100000
 demo_category_created_at_idx |      388 |    100000
(2 rows)

postgres=> explain (analyze, costs off, summary off, buffers)
select * from demo;
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on demo (actual time=0.008..5.409 rows=100000 loops=1)
   Buffers: shared hit=2223

postgres=> explain (analyze, costs off, summary off, buffers)
select category, created_at from demo;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Index Only Scan using demo_category_created_at_idx on demo (actual time=0.046..7.013 rows=100000 loops=1)
   Heap Fetches: 0
   Buffers: shared hit=389
(3 rows)

The following query reads rows for one "category" within a range of "created_at", not very selective, and an additional filter on the hour and minute, which is very selective:

postgres=> explain (analyze, costs off, summary off, buffers)
select demo.* from demo
 where category=0
 and created_at between '01-01-2025' and '12-31-2025'
 and extract (hour from created_at)=16 and extract (minute from created_at)=15
;
                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using demo_category_created_at_idx on demo (actual time=0.172..25.991 rows=70 loops=1)
   Index Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-31 00:00:00'::timestamp without time zone))
   Filter: ((EXTRACT(hour FROM created_at) = '16'::numeric) AND (EXTRACT(minute FROM created_at) = '15'::numeric))
   Rows Removed by Filter: 99930
   Buffers: shared hit=2609

The result is 70 rows, and all the WHERE clause predicates can be applied to the index entries, which include "category" and "created_at" in their key. Then, I could expect a maximum of 70 buffers from the table to get the non-indexed columns for the "SELECT *" projection. Seventy rows accessed by CTID should be fast and not require an Index Only Scan. However, this query read most of the table, 2609 pages from the shared buffers.

The reason is visible on the execution plan: the predicate on the hour and minute extracted from "created_at" is in Filter, which applies after the Index Scan, after reading the table. Its impact is visible in Rows Removed by Filter: 99930 rows have been fetched from the table to be discarded afterward when applying this filter.

Note that a bitmap scan execution plan differentiates the access to the index and the heap and it is more visible that the Filter applies on the Heap Scan and not the Index Scan:

postgres=> explain (analyze, costs off, summary off, buffers)
select demo.* from demo
 where category=0
 and created_at between '01-01-2025' and '12-31-2025'
 and extract (hour from created_at)=16 and extract (minute from created_at)=15
;
                                                                                                                                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on demo (actual time=4.271..23.077 rows=70 loops=1)
   Recheck Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-31 00:00:00'::timestamp without time zone))
   Filter: ((EXTRACT(hour FROM created_at) = '16'::numeric) AND (EXTRACT(minute FROM created_at) = '15'::numeric))
   Rows Removed by Filter: 99930
   Heap Blocks: exact=2223
   Buffers: shared hit=2609
   ->  Bitmap Index Scan on demo_category_created_at_idx (actual time=3.863..3.864 rows=100000 loops=1)
         Index Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-31 00:00:00'::timestamp without time zone))
         Buffers: shared hit=386

To filter after the index access but before the heap access, we can explicitly separate the index and table access, as Hettie exposed in her blog post:

postgres=> explain (analyze, costs off, summary off, buffers)
 select demo.* from demo where (category,created_at) in (
  select category,created_at from demo
   where category=0
   and created_at between '01-01-2025' and '12-31-2025'
   and extract (hour from created_at)=16 and extract (minute from created_at)=15
);
                                                                                           QUERY PLAN                                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop (actual time=18.120..18.449 rows=70 loops=1)
   Buffers: shared hit=669
   ->  Unique (actual time=18.090..18.103 rows=70 loops=1)
         Buffers: shared hit=389
         ->  Sort (actual time=18.087..18.090 rows=70 loops=1)
               Sort Key: demo_1.category, demo_1.created_at
               Sort Method: quicksort  Memory: 28kB
               Buffers: shared hit=389
               ->  Index Only Scan using demo_category_created_at_idx on demo demo_1 (actual time=0.182..18.063 rows=70 loops=1)
                     Index Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-31 00:00:00'::timestamp without time zone))
                     Filter: ((EXTRACT(hour FROM created_at) = '16'::numeric) AND (EXTRACT(minute FROM created_at) = '15'::numeric))
                     Rows Removed by Filter: 99930
                     Heap Fetches: 0
                     Buffers: shared hit=389
   ->  Index Scan using demo_category_created_at_idx on demo (actual time=0.004..0.005 rows=1 loops=70)
         Index Cond: ((category = 0) AND (created_at = demo_1.created_at))
         Buffers: shared hit=280

The subquery selects only the columns included in the index from an Index Only Scan, and all filtering happens there. Access to the table is more expensive, with an additional Index Scan on the same index, but it occurs on a limited number of rows in this case (70 rows instead of 70+99930=100000 in the previous case).

YugabyteDB and immutable function (EXTRACT on TIMESTAMP)

I have run the same on YugabyteDB. There's no need for any trick, and the dist option displays more information about the number of rows scanned from the index and the table:


yugabyte=# explain (analyze, costs off, summary off, dist)
select demo.* from demo
 where category=0
 and created_at between '01-01-2025' and '12-31-2025'
 and extract (hour from created_at)=16 and extract (minute from created_at)=15
;
                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using demo_category_created_at_idx on demo (actual time=87.445..87.968 rows=70 loops=1)
   Index Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-31 00:00:00'::timestamp without time zone))
   Storage Index Filter: ((EXTRACT(hour FROM created_at) = '16'::numeric) AND (EXTRACT(minute FROM created_at) = '15'::numeric))
   Storage Table Read Requests: 1
   Storage Table Read Execution Time: 1.630 ms
   Storage Table Rows Scanned: 70
   Storage Index Read Requests: 1
   Storage Index Read Execution Time: 85.479 ms
   Storage Index Rows Scanned: 100000

Like in PostgreSQL, it is an Index Scan because the index does not cover all columns selected, but there's a Storage Index Filter that is applied before the access to the table which would be seen as Storage Table Filter if pushed down, or Filter if not.

Like with PostgreSQL, 100000 index entries were read, but the filter applied there meant that only 70 table rows were read, similar to the query with Hettie's rewrite, without changing the query.

This is an optimization by Yugabyte to reduce the network transfer on distributed tables, which benefits this case. However, not all functions can be pushed down, mainly when they depend on server configuration. Then, the behavior reverts to PostgreSQL so that the configuration of the query layer applies. We will look at this later when using TIMESTAMPTZ instead of TIMESTAMP.

First, I would like to show that it is not a limitation of Index Scan vs. Index Only Scan, and partially covering indexes can help.

Example with an index covering the immutable function (EXTRACT on TIMESTAMP)

I used only immutable functions (extract from a TIMESTAMP), and it is possible to cover the predicates without a fully covering Index Only Scan:

create index on demo ( 
 category, created_at
 , (extract (hour from created_at))
 , (extract (minute from created_at))
)
;

PostgreSQL can apply the filter on the index entries, visible in Index Cond:

postgres=> explain (analyze, costs off, summary off, buffers)
select demo.* from demo
 where category=0
 and created_at between '01-01-2025' and '12-31-2025'
 and extract (hour from created_at)=16 and extract (minute from created_at)=15
;
                                                                                                                                       QUERY PLAN                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using demo_category_created_at_extract_extract1_idx1 on demo (actual time=0.058..4.436 rows=70 loops=1)
   Index Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-31 00:00:00'::timestamp without time zone) AND (EXTRACT(hour FROM created_at) = '16'::numeric) AND (EXTRACT(minute FROM created_at) = '15'::numeric))
   Buffers: shared hit=668

YugabyteDB does the same with additional information with the dist option of explain:

yugabyte=# explain (analyze, costs off, summary off, dist)
select demo.* from demo
 where category=0
 and created_at between '01-01-2025' and '12-31-2025'
 and extract (hour from created_at)=16 and extract (minute from created_at)=15
;
                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using demo_category_created_at_extract_extract1_idx on demo (actual time=101.617..102.129 rows=70 loops=1)
   Index Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2025-12-31 00:00:00'::timestamp without time zone) AND (EXTRACT(hour FROM created_at) = '16'::numeric) AND (EXTRACT(minute FROM created_at) = '15'::numeric))
   Storage Table Read Requests: 1
   Storage Table Read Execution Time: 1.834 ms
   Storage Table Rows Scanned: 70
   Storage Index Read Requests: 1
   Storage Index Read Execution Time: 99.426 ms
   Storage Index Rows Scanned: 70

In both cases, adding the function's result to the index allowed the query planner to push down the condition to the index scan.

However, this is possible only with immutable functions. Extracting the hour and minute from a TIMESTAMP is deterministic. This is not the case when the timestamp includes the timezone because it depends on the database configuration. Changing the default timezone would corrupt the index.

Example with non-immutable function (EXTRACT on TIMESTAMPTZ)

Hettie's case defined "created_at" as TIMESTAMPTZ (which is a good idea to store timestamps that can be compared with each other, but maybe not when extracting the hour and minute):

create table demo (
 id bigserial
 ,category   int
 ,created_at timestamptz
 ,filler text default lpad('x',9999,'x')
)
;

insert into demo(category,created_at)
 select 0, now() + generate_series(0,99999) * interval '1 minute'
;
create index on demo ( category, created_at )
;

vacuum analyze demo
;

Covering the extract is not possible anymore because it not deterministic:

postgres=> create index on demo (
 category, created_at
 , (extract (hour from created_at))
 , (extract (minute from created_at))
)
;
ERROR:  functions in index expression must be marked IMMUTABLE

extract(text, timestamp with time zone) is stable (deterministic within the scope of one SQL call) but not immutable (deterministic forever):

postgres=> \df+ extract
                                                                                                            List of functions
   Schema   |  Name   | Result data type |        Argument data types        | Type | Volatility | Parallel |    Owner     | Security | Access privileges | Language |    Internal name    |                 Description
------------+---------+------------------+-----------------------------------+------+------------+----------+--------------+----------+-------------------+----------+---------------------+---------------------------------------------
 pg_catalog | extract | numeric          | text, date                        | func | immutable  | safe     | zenith_admin | invoker  |                   | internal | extract_date        | extract field from date
 pg_catalog | extract | numeric          | text, interval                    | func | immutable  | safe     | zenith_admin | invoker  |                   | internal | extract_interval    | extract field from interval
 pg_catalog | extract | numeric          | text, time with time zone         | func | immutable  | safe     | zenith_admin | invoker  |                   | internal | extract_timetz      | extract field from time with time zone
 pg_catalog | extract | numeric          | text, time without time zone      | func | immutable  | safe     | zenith_admin | invoker  |                   | internal | extract_time        | extract field from time
 pg_catalog | extract | numeric          | text, timestamp with time zone    | func | stable     | safe     | zenith_admin | invoker  |                   | internal | extract_timestamptz | extract field from timestamp with time zone
 pg_catalog | extract | numeric          | text, timestamp without time zone | func | immutable  | safe     | zenith_admin | invoker  |                   | internal | extract_timestamp   | extract field from timestamp

It's not possible to add this function to the index.

YugabyteDB applied the filter on the index entries because the predicate was pushed down to the storage layer. However, this doesn't happen with this function:

yugabyte=# \d demo
                                       Table "public.demo"
   Column   |           Type           | Collation | Nullable |             Default
------------+--------------------------+-----------+----------+----------------------------------
 id         | bigint                   |           | not null | nextval('demo_id_seq'::regclass)
 category   | integer                  |           |          |
 created_at | timestamp with time zone |           |          |
 filler     | text                     |           |          | lpad('x'::text, 9999, 'x'::text)
Indexes:
    "demo_category_created_at_idx" lsm (category ASC, created_at ASC)

yugabyte=# explain (analyze, costs off, summary off, dist)
select demo.* from demo
 where category=0
 and created_at between '01-01-2025' and '12-31-2025'
 and extract (hour from created_at)=16 and extract (minute from created_at)=15
;
                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using demo_category_created_at_idx on demo (actual time=23.135..1925.909 rows=70 loops=1)
   Index Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2025-12-31 00:00:00+00'::timestamp with time zone))
   Filter: ((EXTRACT(hour FROM created_at) = '16'::numeric) AND (EXTRACT(minute FROM created_at) = '15'::numeric))
   Rows Removed by Filter: 99930
   Storage Table Read Requests: 98
   Storage Table Read Execution Time: 1057.112 ms
   Storage Table Rows Scanned: 100000
   Storage Index Read Requests: 98
   Storage Index Read Execution Time: 14.704 m

With a TIMESTAMPTZ argument, the extract is not pushed down, even though it is an immutable function. YugabyteDB behavior reverts to what PostgreSQL does: Filter after the Index Scan result, which fetches 100000 Table Rows Scanned from the storage (DocDB, distributed on YugabyteDB nodes) to the query layer (YSQL, the Postgres backend you are connected to) to discard 99930 Rows Removed by Filter.

This is not efficient. Hettie's trick helps to reduce the table rows scanned:

yugabyte=# explain (analyze, costs off, summary off, dist)
 select demo.* from demo where (category,created_at) in (
  select category,created_at from demo
   where category=0
   and created_at between '01-01-2025' and '12-31-2025'
   and extract (hour from created_at)=16 and extract (minute from created_at)=15
);
                                                                                        QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop (actual time=107.361..179.929 rows=70 loops=1)
   ->  HashAggregate (actual time=105.868..105.996 rows=70 loops=1)
         Group Key: demo_1.category, demo_1.created_at
         Batches: 1  Memory Usage: 32kB
         ->  Index Only Scan using demo_category_created_at_idx on demo demo_1 (actual time=2.232..105.641 rows=70 loops=1)
               Index Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2025-12-31 00:00:00+00'::timestamp with time zone))
               Filter: ((EXTRACT(hour FROM created_at) = '16'::numeric) AND (EXTRACT(minute FROM created_at) = '15'::numeric))
               Rows Removed by Filter: 99930
               Heap Fetches: 0
               Storage Index Read Requests: 98
               Storage Index Read Execution Time: 55.127 ms
               Storage Index Rows Scanned: 100000
   ->  Index Scan using demo_category_created_at_idx on demo (actual time=1.027..1.031 rows=1 loops=70)
         Index Cond: ((category = 0) AND (created_at = demo_1.created_at))
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 0.469 ms
         Storage Table Rows Scanned: 1
         Storage Index Read Requests: 1
         Storage Index Read Execution Time: 0.464 ms
         Storage Index Rows Scanned: 1

Note that this saves only the table rows scanned, but the predicate with the non-immutable extract function is still applied as a filter and cannot be pushed down to the storage, resulting in 100000 index entries fetched.

Use an immutable function (TO_CHAR on TIMESTAMPTZ AT TIME ZONE 'GMT' )

One solution is to create an immutable function to extract the hours and minutes without depending on the default timezone:

create function GMT_HH24MI(ts timestamptz) returns text as $SQL$
 select to_char(ts at time zone 'GMT','HH24MI')
$SQL$ 
 language sql 
 immutable -- because it operates always on GMT whatever the default timezone is
;

This function can be indexed:

create index on demo ( category, created_at, GMT_HH24MI(created_at) )
;

The query must be rewritten to use the function:

yugabyte=# explain (analyze, costs off, summary off, dist)
select demo.* from demo
 where category=0
 and created_at between '01-01-2025' and '12-31-2025'
 and GMT_HH24MI(created_at)='1615'
;
                                                                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using demo_category_created_at_gmthh24mi_idx on demo (actual time=73.116..73.610 rows=70 loops=1)
   Index Cond: ((category = 0) AND (created_at >= '2025-01-01 00:00:00+00'::timestamp with time zone) AND (created_at <= '2025-12-31 00:00:00+00'::timestamp with time zone) AND (GMT_HH24MI(created_at) = '1615'::text))
   Storage Table Read Requests: 1
   Storage Table Read Execution Time: 1.915 ms
   Storage Table Rows Scanned: 70
   Storage Index Read Requests: 1
   Storage Index Read Execution Time: 70.931 ms
   Storage Index Rows Scanned: 70

Creating an immutable function and adding it to the index key or include clause allows PostgreSQL and YugabyteDB to apply the predicate to the index entries without an Index-Only Scan.

If you use such an immutable function, I recommend that the function's name describes the specific behavior. In this example, my function extracts the time of a particular time zone (GMT). The semantics are different:

The original example with TIMESTAMPTZ and EXTRACT shows records created when it was 15:16 in the time zone of the user who runs the query later, but it may have been a different clock time for the one who created it.
My GMT_HH24MI function shows those created when it was 15:16 UTC.
With a TIMESTAMP without a time zone, EXTRACT shows those created when it was 15:16 in the creation's time zone, which may not match the querying user's time zone.

When specifying 15:16 in the query, which of these three results does the user expect? You cannot know without asking.

🌐 All these considerations originate from client/server times. Today, you don't want your stored data to depend on where the application server runs. The application server and the database's default time zone should be identical and probably UTC. TIMESTAMP or AT TIME ZONE 'GMT' provides better optimization possibilities, as their results do not depend on environment variables but rely on the guarantee that those timezone settings are consistent across all servers and do not change.

💡 Always look at the explain (analyze, buffers) output in PostgreSQL or explain (analyze, dist) in YugabyteDB. In general, you should avoid Rows Removed by Filter on many rows, especially with distributed databases that must fetch them across the network before applying the Filter condition. The number of buffers or rows scanned should not exceed the scan result (actual rows=).

More details in pg_locks for YugabyteDB

Franck Pachot — Sun, 29 Dec 2024 10:11:20 +0000

One benefit of Wait-on-Conflict concurrency control, also known as pessimistic locking, is its simplicity in observation and troubleshooting when it impacts performance, especially when all lock intents are observable.
Let's quickly explore how to use pg_locks in YugabyteDB to examine the blocking and blocked transactions when waiting to acquire a lock.

In the following example, I created a table with three rows. One session deletes them. Before it commits, another session tries to delete the same rows. This is fast, thanks to pessimistic locking, where it waits for the completion of the first one instead of raising an error. For demo purposes, to observe the locks on a small table, I exaggerate the transaction duration by waiting five seconds:

set yb_use_hash_splitting_by_default to off;

create table demo ( id bigserial, value int, primary key(id));
insert into demo (value) select generate_series(1,3);

\timing on
begin transaction;
delete from demo;

-- another session attempts the same delete
\! sleep 5 ; psql -ec '\timing on' -c 'delete from demo' & sleep 5

select locktype, mode, granted
, regexp_replace( ybdetails->>'transactionid'      ,'[-0-9a-f]{32}'        ,'','g') as txid
, regexp_replace( ybdetails->>'blocked_by'       ,'("[-0-9a-f]{32}|"|]|\[)','','g') as blockers
, ybdetails->'keyrangedetails'->>'cols' as cols
from pg_locks
where relation='demo'::regclass
order by blockers desc,txid, mode desc
;

commit;

I queried the pg_lock table, which is inherited from PostgreSQL. YugabyteDB adds more details in a ybdetails document accessible with JSON functions:

yugabyte=# begin transaction;
BEGIN
Time: 18.158 ms
yugabyte=*# delete from demo;
DELETE 3
Time: 21.419 ms
yugabyte=*# \! sleep 5 ; psql -ec '\timing on' -c 'delete from demo' & sleep 5
Timing is on.
delete from demo
yugabyte=*# select locktype, mode, granted
yugabyte-*# , regexp_replace( ybdetails->>'transactionid'      ,'.{32}'        ,'','g') as txid
yugabyte-*# , regexp_replace( ybdetails->>'blocked_by'       ,'(".{32}|"|]|\[)','','g') as blockers
yugabyte-*# , ybdetails->'keyrangedetails'->>'cols' as cols
yugabyte-*# from pg_locks
yugabyte-*# where relation='demo'::regclass
yugabyte-*# order by blockers desc,txid, mode desc
yugabyte-*# ;
 locktype |           mode           | granted | txid | blockers | cols
----------+--------------------------+---------+------+----------+-------
 relation | WEAK_READ,WEAK_WRITE     | t       | 1375 |          |
 row      | STRONG_READ,STRONG_WRITE | t       | 1375 |          | ["1"]
 row      | STRONG_READ,STRONG_WRITE | t       | 1375 |          | ["2"]
 row      | STRONG_READ,STRONG_WRITE | t       | 1375 |          | ["3"]
 relation | WEAK_READ,WEAK_WRITE     | f       | e5cb | 1375     |
 row      | STRONG_READ,STRONG_WRITE | f       | e5cb | 1375     | ["1"]
 row      | STRONG_READ,STRONG_WRITE | f       | e5cb | 1375     | ["2"]
 row      | STRONG_READ,STRONG_WRITE | f       | e5cb | 1375     | ["3"]
(8 rows)

Time: 21.732 ms
yugabyte=*# commit;
COMMIT
Time: 19.304 ms
yugabyte=# DELETE 0
Time: 4979.748 ms (00:04.980)

I can see two transactions. One (e5cb) is waiting for the other (1375) because it encountered conflicting locks.
The delete acquires STRONG_READ and STRONG_WRITE on the deleted rows, which conflicts with other transactions doing the same. A WRITE lock is an exclusive lock, which conflicts with READ, a shared lock, and WRITE, an exclusive lock.
The WEAK locks are there for performance, so a STRONG WRITE at the relation (table) level would not have to consider all row-level locks, but this is not the case here.

In ybdetails, there is enough information to identify which rows conflict. YugabyteDB provides all details because all transaction intents are stored in the provisional records (IntentsDB in the distributed storage). When you query pg_locks, it reads the tablets involved in the transaction from the global transaction table and fetches the relevant information from those tablets (IntentsDB). You should not query this view too often, but you can query it when troubleshooting a blocking situation.

PostgreSQL doesn't have this level of detail because the row locks are stored within the heap table, not separating provisional records like YugabyteDB does with IntentsDB and not organizing them by key as YugabyteDB does with LSM tree. To compare, here is what PostgreSQL displays in the same situation:

postgres=*> select locktype, mode, granted, virtualtransaction , *
from pg_locks
where relation='demo'::regclass
order by virtualtransaction, mode desc
;

 locktype |        mode         | granted | virtualtransaction | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid  |        mode         | granted | fastpath | waitstart
----------+---------------------+---------+--------------------+----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------------+---------+----------+-----------
 relation | RowExclusiveLock    | t       | 11/18              | relation |    16385 |   974858 |      |       |            |               |         |       |          | 11/18              | 1654 | RowExclusiveLock    | t       | t        |
 relation | RowExclusiveLock    | t       | 8/35               | relation |    16385 |   974858 |      |       |            |               |         |       |          | 8/35               | 2363 | RowExclusiveLock    | t       | t        |
 tuple    | AccessExclusiveLock | t       | 8/35               | tuple    |    16385 |   974858 |    0 |     1 |            |               |         |       |          | 8/35               | 2363 | AccessExclusiveLock | t       | f        |
(3 rows)

Time: 183.098 ms

RowExclusiveLock is not a row lock but a table lock. You know this when looking at the lock mode relation. It is an intent lock (the intention to acquire row locks on this table) equivalent to the WEAK locks acquired by YugabyteDB.

In PostgreSQL, The blocker/blocking relationship is not visible from here because there is no information about row-level locks, and this is where the conflict occurs. You can look at pg_stat_activity to see the wait events, but not pg_locks.
A tuple level AccessExclusiveLock shows the currently accessed row (with relation, page, and tuple number), which can provide a clue, but it doesn't list all locks acquired.

YugabyteDB locks are described in:

Transaction isolation levels | YugabyteDB Docs

Learn how YugabyteDB supports two transaction isolation levels Snapshot Isolation and Serializable.

docs.yugabyte.com

YugabyteDB pg_locks is described in:

Get lock information insights with pg_locks | YugabyteDB Docs

Using pg_locks to get insights into lock information specific to YugabyteDB's distributed SQL architecture.

docs.yugabyte.com

A few additional remarks for YugabyteDB:

You may see multiple relation lock types for the same table, because it is distributed and locks are stored per tablet. The details are in ybdetails->>'node' and ybdetails->>'tablet_id'
Lock information is available at the table, keyrange, row, and column levels, but the outputs when querying pg_locks is limited by yb_locks_max_transactions
Short locks are not displayed when querying pg_locks as the goal is to see the blocking ones, this is controlled by yb_locks_min_txn_age
Write operations are batched to avoid network latency, so you see many lock intents for the blocked transaction, even if it was blocked on the first row.

Like PostgreSQL, Oracle Database displays only the table-level locks in V$LOCK. You can know the row from the blocked transaction in V$SESSION, but there is no visible information about row-level locks acquired by the blocking transaction. Other databases store locks in memory rather than storage and can display them, but they do not scale because of lock escalation.
In contrast, YugabyteDB stores lock intents in a scalable and observable way. IntentsDB is a per-tablet LSM tree with its first level in memory. Long transactions can grow by flushing into SST files. YugabyteDB can display all locks acquired by a transaction, which helps troubleshoot conflict issues.