1. What it is about This investigation began as a performance comparison for different memory allocators. However, during benchmarking, I discovered unexpected effects deserving a more detailed explanation. I hope you find these findings both interesting and useful. Imagine you need to set up a MySQL database server. Every detail is planned: the operating system, … Continued

The post Stored Procedures memory consumption in Percona Server for MySQL appeared first on Percona.

Percona Server for MongoDB 8.3 is available today as a Technical Preview. It is not for production. It is for your lab, your staging cluster, and your benchmark harness – and for sharing with us what works and what does not. Especially if this version is your segue to leverage upcoming full-text and vector search … Continued

The post Percona Server for MongoDB 8.3 Technical Preview Is Now Available appeared first on Percona.

Aaron Bertrand wants you to consider using partitioned tables and the sliding window pattern to help archive old data. That’s a great idea. In fact, I’d like to do that at my own job. I have a truly humungous log table (Terabytes) and its clustered index is already on CreatedDate so it’s a good candidate […]

SQL is quite flexible, making it easy to write a single query that works in two situations: one without a filtering parameter and one with a parameterized filter. For example, I came across a benchmark comparing MongoDB and PostgreSQL handling pagination effectively—by avoiding OFFSET and instead using the last value to fetch the next set of results. The first page typically uses only ORDER BY and LIMIT, while subsequent pages add a WHERE condition to continue from the last value returned.

In the MongoDB version of this benchmark, the filter is handled within the application, which leads to two separate queries for these scenarios.

export async function getOrders(cursor) {
  const match = cursor ? { timestamp: { $gt: new Date(cursor) } } : {};
  const rows = await orders
    .aggregate([
      { $match: match },
      { $sort: { timestamp: 1 } },
      { $limit: PAGE_SIZE },
    ])

The PostgreSQL version uses a single prepared statement. SQL is such a powerful language that it often feels tempting to write it this way:

SELECT *
     FROM orders
     WHERE $1::timestamptz IS NULL OR "timestamp" > $1
     ORDER BY "timestamp" ASC
     LIMIT ${PAGE_SIZE}

If $1 is NULL, the predicate evaluates to true for every row, effectively disabling the filter and returning the first rows in timestamp order without applying any filter. When $1 has a value, it filters the results using that specific value, enabling a more targeted search.

However, expressing both use cases in a single generic predicate may result in an execution plan that is suboptimal for some parameter value.

I gave it a try:

drop table if exists orders;

create table orders (
  order_id text primary key,
  "timestamp" timestamptz not null
);

create index idx_orders_timestamp
  on orders("timestamp");

insert into orders
select
    'ORD-'||g,
    '2025-01-01'::timestamptz + g * interval '1 minute'
from generate_series(1, 5000000) as g;

analyze orders;

prepare getorders(timestamptz, int) as
select * from orders
     where $1::timestamptz is null or "timestamp" > $1
     order by "timestamp" asc limit $2
;

explain (analyze, buffers, settings, costs off)
 execute getorders(date'2026-01-01'::timestamptz, 10);

Here is the execution plan:

                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit (actual time=0.028..0.031 rows=10.00 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using idx_orders_timestamp on orders (actual time=0.027..0.029 rows=10.00 loops=1)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
         Index Searches: 1
         Buffers: shared hit=4
 Planning Time: 0.117 ms
 Execution Time: 0.042 ms

This looks great because the planner was able to evaluate date'2026-01-01'::timestamptz is null during planning and simplify the predicate. The index scan is efficient, reading just about rows=10.00 entries.

Let's see what happens when PostgreSQL uses a generic plan instead of a custom one:

set plan_cache_mode to force_generic_plan;

explain (analyze, buffers, settings, costs off)
 execute getorders(date'2026-01-01'::timestamptz, 10);

It's still an Index Scan, but without an Index Cond to narrow the scan range - the predicate is an after-scan Filter:

                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Limit (actual time=71.945..71.948 rows=10.00 loops=1)
   Buffers: shared hit=4786
   ->  Index Scan using idx_orders_timestamp on orders (actual time=71.943..71.945 rows=10.00 loops=1)
         Filter: (($1 IS NULL) OR ("timestamp" > $1))
         Rows Removed by Filter: 525600
         Index Searches: 1
         Buffers: shared hit=4786
 Settings: plan_cache_mode = 'force_generic_plan'
 Planning Time: 0.030 ms
 Execution Time: 71.971 ms

Since the generic plan must work for all possible values of $1, PostgreSQL cannot transform the predicate into an Index Cond on "timestamp". As a result, it reads 525600 unnecessary index entries, which are then removed by the filter.

While the default setting plan_cache_mode = 'auto' often works well, it relies on a heuristic: PostgreSQL executes the statement a few times with custom plans, then compares the estimated cost of a generic plan with the average estimated cost of the custom plans. The decision is based on estimates rather than actual execution times, which means it may occasionally select a generic plan even when a custom plan would perform better for a particular workload.

One alternative is to rewrite the predicate so that PostgreSQL can always generate an index condition.

reset plan_cache_mode;

prepare getorders_better(timestamptz, int) as
select * from orders
     where timestamp > coalesce($1, '-infinity'::timestamptz)
     order by "timestamp" asc limit $2
;

With the new query, there is always an Index Cond. The main difference is whether the scan range starts at -infinity or at the supplied parameter value:

postgres=# explain execute getorders_better(null::timestamptz, 10)
;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..174194.43 rows=5000000 width=19)
         Index Cond: ("timestamp" > '-infinity'::timestamp with time zone)
(3 rows)

postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.78 rows=10 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..155802.94 rows=4471972 width=19)
         Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
(3 rows)

postgres=# set plan_cache_mode to force_generic_plan;
SET

postgres=# explain execute getorders_better(null::timestamptz, 10)
;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5807.41 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58070.11 rows=1666667 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..5807.41 rows=166667 width=19)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..58070.11 rows=1666667 width=19)
         Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)

This revised query is effective because the Index Scan is optimal for pagination with order by "timestamp" asc limit $2, even when a null parameter is used. However, this isn't always the case, as a generic plan must estimate selectivity without knowing the actual parameter values. In some situations, this can lead to poor cardinality estimates and suboptimal plan choices.

I used prepared statements because they are the standard way to execute parameterized queries while avoiding SQL injection. However, you might face the same problem if you construct a custom query using a hardcoded value while keeping the generic predicate. For instance, you could include your parameters in a WITH clause's common table expression.

postgres=# explain
with param as ( select
 date'2026-01-01'::timestamptz as p1
)
select * from orders, param
     where p1::timestamptz is null or "timestamp" > p1
     order by "timestamp" asc limit 10
;

                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..0.90 rows=10 width=27)
   ->  Index Scan using idx_orders_timestamp on orders  (cost=0.43..210363.45 rows=4467609 width=27)
         Filter: ((('2026-01-01'::date)::timestamp with time zone IS NULL) OR ("timestamp" > ('2026-01-01'::date)::timestamp with time zone))
(3 rows)

Since ((('2026-01-01'::date)::timestamp with time zone IS NULL) is provably false, the OR expression could theoretically be simplified to a single predicate. However, PostgreSQL currently does not perform this simplification. The optimizer source code explicitly notes that OR branches proven to be always false could theoretically be removed, but this optimization is not currently implemented:

Currently, when processing OR expressions, we only return true when all of the OR branches are always false. This could perhaps be expanded to remove OR branches that are provably false. This may be a useful thing to do as it could result in the OR being left with a single arg. That's useful as it would allow the OR condition to be replaced with its single argument which may allow use of an index for faster filtering on the remaining condition.

This situation mirrors what happens when a prepared statement is executed using a generic plan. When using generic predicates, such as p1::timestamptz is null or "timestamp" > p1, which contain branches that might be false depending on parameter values, the planner cannot safely eliminate those branches to get a sargable condition. The same workaround applies: if you understand the specific parameter characteristics, write a more targeted query to enable index usage, maybe one per scenario.

For workloads where parameter values can lead to radically different optimal plans, relying on plan_cache_mode = auto may not always produce the desired result. In such cases, forcing custom plans with plan_cache_mode = force_custom_plan can be a simple way to guarantee that the optimizer considers the actual parameter values on every execution.

The final example shows that PostgreSQL's failure to convert OR expressions into indexable predicates extends beyond generic plans. Even when the value is known at planning time via a single-row CTE and not a parameter, PostgreSQL treats the OR expression as a filter instead of simplifying it. This indicates a broader optimizer limitation in OR-clause simplification that you should know and address when writing the SQL query.

I've always been amazed by how Oracle Database handles updates to a unique column—performing set-based operations that don't violate the unique constraint, yet when executed row by row, it temporarily permits duplicates.

SQL> create table franck ( val int unique );

Table created.

SQL> insert into franck values (-1) , (1) ;

2 rows created.

SQL> select val from franck;

       VAL
----------
        -1
         1

SQL> update franck set val=-val;

2 rows updated.

SQL> select val from franck;

       VAL
----------
         1
        -1

From a SQL perspective, this is expected behavior, but not all databases support it without raising an error:

• Db2, SQL Server, and Oracle handle it without error.
• PostgreSQL raises ERROR: duplicate key value violates unique constraint "franck_val_key", DETAIL: Key (val)=(1) already exists. This works with a deferred constraint.
• MySQL or MariaDB raise Duplicate entry '1' for key 'franck.val'
• SQLite raises { "code": "SQLITE_CONSTRAINT_UNIQUE" }
• MongoDB raises E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 }

db.franck.createIndex({ val: 1 }, { unique: true });
db.franck.insertMany([ { val: -1 }, { val: 1 } ]);
db.franck.updateMany({},[ { $set: { val: {$multiply:[ "$val",-1 ]} } } ]);
MongoServerError: Plan executor error during update :: caused by :: E11000 duplicate key error collection: test.franck index: val_1 dup key: { val: 1 }

This is surprising because Oracle unique indexes store the indexed columns as the B-tree key and the ROWID as the associated data. Non-unique indexes add the ROWID to the physical key and are required for a deferrable unique constraint to allow temporary duplication before the end of the transaction. So how do non-deferrable unique indexes allow duplication during a single update statement? In this simple example, I would expect:

1. The initial index entries are: (-1): row #1 and (1): row #2
2. Updating the first row deletes the first entry (-1): row #1 and adds one with (1): row #1
3. Updating the second row deletes the corresponding entry (1): row #2 and adds one with (-1): row #2
4. The final non-deleted index entries are: (-1): row #2 and (1): row #1

Even if the final state is valid, this execution would raise a duplicate key error at step 2 because two entries have the same value: (1): row #1 and (1): row #2. So Oracle does something smarter.

Let's look at the internals with another example: five rows with values from 1 to 5, and an update that increments them by one should create a temporary violation until the last row is updated.

Investigation with block dumps

My goal is to observe how a unique B-tree index evolves as Oracle executes a multi-row update that temporarily creates duplicate values.

The table contains five rows with values from 1 to 5, and a row-level trigger pauses for five seconds before each row update. This gives me enough time to dump the index leaf block and capture intermediate states in a second session.

I create the table:

drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 5');
create unique index FRANCK on FRANCK ( VAL );
create or replace trigger FRANCK_SLEEP 
 before update on FRANCK 
 for each row begin dbms_session.sleep(5); end;
/
show errors

Here are my table's rows in the order they are scanned:

SQL> column "DUMP(VAL,16)" format a18
SQL> column "DUMP(ROWID,16)" format a39
SQL> select val, dump(val,16), dump(rowid,16)
     from FRANCK 
;

       VAL DUMP(VAL,16)       DUMP(ROWID,16)
---------- ------------------ ---------------------------------------
         1 Typ=2 Len=2: c1,2  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,0
         2 Typ=2 Len=2: c1,3  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,1
         3 Typ=2 Len=2: c1,4  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,2
         4 Typ=2 Len=2: c1,5  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,3
         5 Typ=2 Len=2: c1,6  Typ=69 Len=10: 0,1,3a,83,0,40,87,69,0,4

I've displayed the value in hexadecimal, as well as the ROWID because the index entries are key -> ROWID and I'll inspect them with a hexadecimal dump.

I locate the index block and my session's trace file:

alter session set tracefile_identifier = 'franck';

column fileno    new_value fileno
column block     new_value block
column tracefile new_value tracefile

select header_file as fileno ,
       header_block+1 as block
   from dba_segments
    where segment_type='INDEX'
      and segment_name='FRANCK'
      and owner=sys_context('USERENV', 'CURRENT_SCHEMA')
;

select value as tracefile
 from v$diag_info
 where name = 'Default Trace File'
;

Each time I want to dump the block and read the entries, I run the following in the session where the variables were defined:

alter system checkpoint;
alter system dump datafile &fileno block █
host tail -20 &tracefile | egrep "^row|^col"

I'll dump the block multiple times while another session runs update FRANCK set VAL = VAL + 1;, showing:

• the index entry (row# in the dump)
• with the 6-byte ROWID (data in the dump)
• and the key (col 0 in the dump).

Initial state

The index leaf block before the update contains one entry per table row:

row#0[8021] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

The six-byte data:(6) field is the ROWID stored in the unique index entry. The indexed value is stored in col 0 as it is a single-column index.

To help with the hexadecimal values, I've put them in a table to show the table rows and their index entries, with values in hexadecimal and decimal:

Running the update

I run the update in another session, and thanks to the trigger, it waits 5 seconds between each updated row:

update FRANCK set val = val + 1
;

The update starts the scan and processes the first table row, where the value 1 (c1 02) is incremented to 2 (c1 03).

Oracle first locates the index entry for the old value, acquires an exclusive lock on the row (the lock: 2 references ITL slot 2, whose transaction information identifies the modifying transaction), and marks it as deleted (-D- flag):

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

At this intermediate point, the old entry has been deleted, and no entry references the row containing the new value:

The next dump is interesting. Oracle does not add another entry for value 2 (c1 03). Instead, it updates the existing entry #1, which already has the right key c1 03 but for a different row. The old ROWID of entry #1 is replaced:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

There are now two index entries for rowid 69 00 00, one deleted and one current:

Notice that there is no longer any entry for rowid 69 00 01 because Oracle has changed the ROWID stored in the existing key for value 2 (c1 03) entry from 69 00 01 to 69 00 00. This is why there's no duplicate key situation: all keys in the index are unique.

If the update stops after modifying only the first row, it can create duplicate key issues because the first two rows share the same value. It can also cause logical corruption since the second row lacks an index entry. One possible approach is for Oracle to track these uniqueness conflicts as violation counts during statement processing and confirm that none persist at the end. This strategy would enable Oracle to proceed, expecting that further updates will restore consistency.

The update then continues with the next table row. Rowid 69 00 01 changes from 2 to 3.

I guess that Oracle attempts to locate the entry with key value 2 (c1 03) pointing to 69 00 01 for deletion. However, it finds the key but associated with a different ROWID. Since there's nothing to delete, it leaves it as-is but decreases the violation counter, which then returns to zero, because the current update removes the value that was a duplicate.

As we have seen before, Oracle updates the index entry for the new value c1 04 = 3 to point to rowid 69 00 01:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 0, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 06

Currently, there is an index entry for 69 00 01 with the value 3 (c1 04), but no entry exists for row 69 00 02, so continuing with my guess, the violation counter is incremented.

With this pattern, the ROWID associated with each key shifts one row earlier. The same pattern applies to keys 4 and 5. The violation counter may be decremented and then incremented again, remaining at 1.

After processing the row containing the value 4 (c1 05), the key with value 5 (c1 06) points to rowid 69 00 03. The violation counter remains at 1.

Once the row with value 5 (c1 06) is processed, Oracle encounters a situation where no existing key is reused. According to my guess, the violation counter decreases because the previous value no longer matches the current row. The new key value is 6 (c1 07), which did not previously exist. Only then is a new index entry created:

row#0[8021] flag: ---D---, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 02

row#1[8010] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 00
col 0; len 2; (2): c1 03

row#2[7999] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 01
col 0; len 2; (2): c1 04

row#3[7988] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 02
col 0; len 2; (2): c1 05

row#4[7977] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 03
col 0; len 2; (2): c1 06

row#5[7966] flag: -------, lock: 2, len=11
data:(6): 00 40 87 69 00 04
col 0; len 2; (2): c1 07

The final state is:

All rows and index entries are consistent, and the violation counter is back to zero. The index structure I examined never had duplicate keys in the block dumps. Oracle maintains key uniqueness by reusing entries and updating their stored ROWIDs. The method Oracle uses to monitor unresolved uniqueness conflicts until the statement finishes isn't clear from these dumps, but a violation counter appears to be a possible implementation.

UNIQUE vs non-UNIQUE index

Without grasping these internals, one might assume a UNIQUE index isn't necessary for a UNIQUE constraint. However, now we see the performance benefit of this update pattern: it modifies existing index entries rather than deleting the old value and inserting the new one. I tested this with one million rows:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create unique index FRANCK on FRANCK ( VAL );
update FRANCK set val = val + 1 ;
analyze index FRANCK validate structure;
select height, br_rows, lf_rows, del_lf_rows, br_blks, lf_blks
 from index_stats
;

The index statistics display branch and leaf blocks along with entries. Following the update statement that affected all rows, the unique index only added a single deleted leaf entry (DEL_LF_ROWS):

    HEIGHT    BR_ROWS    LF_ROWS DEL_LF_ROWS    BR_BLKS    LF_BLKS
---------- ---------- ---------- ----------- ---------- ----------
         3       2220    1000001           1          5       2221

I tested the same process using a non-unique index:

set linesize 200 pagesize 1000
drop table FRANCK purge;
create table FRANCK as select rownum as VAL from xmltable('1 to 1000000');
create index FRANCK on FRANCK ( VAL );
update FRANCK set val 
                                        by Franck Pachot