Skip to main content

How to Enable Negative Caching on 2025.2

Eugene Kim
Updated

At present, negative caching is disabled, which results in specific queries performing RPCs to the master process every time they are executed. This article describes how to enable negative caching, which is supported starting on YugabyteDB version 2025.2.2 and later. This guide also covers how to disable the catalog version incrementing on 2026.1.

Environment

  • YugabyteDB - 2025.2.2 and later (required for negative caching support)
  • YugabyteDB - 2026.1 and later (only for the autoflag demotion step, when disabling the catalog version incrementing for all DDLs)

Issue

Under normal operating conditions, executing a query repeatedly in the same connection will do some requests to the master process in the first execution, but starting from the second execution, all catalog data should be cached, so it should not do any catalog requests.

For certain queries, the results will not be cached, so every execution will do one or more requests to master. This is the problem we are fixing in this guide.

To check if this is the right problem, open a connection and run the same query twice with EXPLAIN (ANALYZE, DIST). If the second run is still performing catalog read requests, then there is a negative caching issue, which is addressed in this article.

Diagnosis

Scenario 1: OK

$ bin/ysqlsh
yugabyte=# \q  -- Make sure to open + close a connection first to ensure there is no preloading when testing

$ bin/ysqlsh
yugabyte=# EXPLAIN (ANALYZE, DIST) SELECT
yugabyte-#   unnest(
yugabyte(#     ARRAY[
yugabyte(#       10,
yugabyte(#       20,
yugabyte(#       30
yugabyte(#     ]
yugabyte(#   ) AS value;
 Catalog Read Ops: 12 -- First execution is expected to have >0 catalog reads

yugabyte=# EXPLAIN (ANALYZE, DIST) SELECT
yugabyte-#   unnest(
yugabyte(#     ARRAY[
yugabyte(#       10,
yugabyte(#       20,
yugabyte(#       30
yugabyte(#     ]
yugabyte(#   ) AS value;
 Catalog Read Ops: 0 -- OK

Scenario 2: Negative caching issue

$ bin/ysqlsh
yugabyte=# \q  -- Make sure to open + close a connection first to ensure there is no preloading when testing

$ bin/ysqlsh
yugabyte=# EXPLAIN (ANALYZE, DIST)
yugabyte-# SELECT *
yugabyte-# FROM unnest(
yugabyte(#   ARRAY[1,2,3]::int[],
yugabyte(#   ARRAY['a','b','c']::text[]
yugabyte(# );
 Catalog Read Ops: 18 -- First execution is expected to have >0 catalog reads

yugabyte=# EXPLAIN (ANALYZE, DIST)
yugabyte-# SELECT *
yugabyte-# FROM unnest(
yugabyte(#   ARRAY[1,2,3]::int[],
yugabyte(#   ARRAY['a','b','c']::text[]
yugabyte(# );
 Catalog Read Ops: 1 -- Negative caching issue!

Resolution

Overview

There are two possible methods for enabling negative caching. Option 1 is recommended since it is simpler, but Option 2 is effective if the user would like to enable the flags without performing any cluster restarts.

Option 1: Double Rolling Restart

The issue can be addressed by enabling negative caching (see "Limitations" for tradeoffs). There are two flags that need to be enabled in two successive rolling restarts. The first step is to enable catalog version incrementing on all DDLs, then we can enable negative caching.

Important: The exact sequence must be followed, otherwise correctness issues may result.

  1. First, set yb_always_increment_catalog_version_on_ddl=true in ysql_pg_conf_csv and restart all tservers. Once all tservers have restarted with the new flag, proceed to step 2.
  2. Next, set yb_enable_negative_catcache_entries=true in ysql_pg_conf_csv and restart all tservers. This enables negative caching across the entire cluster.

Note: enabling yb_always_increment_catalog_version_on_ddl is required in order to use yb_enable_negative_catcache_entries. Using yb_enable_negative_catcache_entries without enabling yb_always_increment_catalog_version_on_ddl can lead to unexpected errors.

Option 2: Live

The following method can be used if the desire is to enable negative caching without performing any rolling restarts.

Important: This method is more complex than the double rolling restart method described earlier.

  1. Set yb_always_increment_catalog_version_on_ddl=true in ysql_pg_conf_csv and apply the change using a non-restart gflags update.
  2. Wait for the GUC to propagate to all nodes. Verify that this has happened by querying Postgres (ysql).

Important: Do not advance to the next step until all nodes return on:

yugabyte=# SHOW yb_always_increment_catalog_version_on_ddl;
 yb_always_increment_catalog_version_on_ddl 
--------------------------------------------
 on
(1 row)
  1. Once the flag has been propagated to all nodes, get the current timestamp. The timestamp will be used as the fence timestamp, waiting until all transactions that started before this timestamp have completed before proceeding.
yugabyte=# SELECT now() AS t_fence;
            t_fence            
-------------------------------
 2026-04-21 20:47:55.825731+00
(1 row)
  1. Wait for all transactions that started before t_fence to complete. Wait until this query returns zero rows on every node before advancing to the next step by running the following query:
SELECT pid, datname, usename, application_name,
       state, xact_start, query_start, backend_start
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND backend_type = 'client backend'
  AND state IS DISTINCT FROM 'idle'
  AND (xact_start IS NULL OR xact_start <= TIMESTAMPTZ '<t_fence>');
  1. Set yb_enable_negative_catcache_entries=true in ysql_pg_conf_csv (do not perform a restart afterwards).

Disable Negative Caching and the Catalog Version Increment

As with the enablement case, there are two possible methods for disabling the flags. Option 1 is recommended since it is simpler, but Option 2 is effective if the user would like to disable the flags without doing any cluster restarts.

NOTE: If the goal is to disable the catalog version increment for all DDLs on 2026.1+, then an additional step is required, see the "Autoflag Demotion" section of this article.

Option 1: Rollback With a Double Restart

The simple way to rollback the changes is to follow the steps in Method 1 in reverse:

  1. First, set yb_enable_negative_catcache_entries=false in ysql_pg_conf_csv and restart all tservers. This disables negative caching across the entire cluster. Once all tservers have restarted with the new flag, proceed to step 2.
  2. Then, set yb_always_increment_catalog_version_on_ddl=false in ysql_pg_conf_csv and restart all tservers again.

Option 2: Live Rollback

The steps to roll back these flags without any restarts are the same as Method 2, but with the flags swapped:

  1. Set yb_enable_negative_catcache_entries=false in ysql_pg_conf_csv (do not restart tservers afterwards).
  2. Wait for the GUC to propagate to all nodes. Verify that this has happened by querying PG. Do not advance to the next step until all nodes return off:
yugabyte=# SHOW yb_enable_negative_catcache_entries;
 yb_enable_negative_catcache_entries 
-------------------------------------
 off
(1 row)
  1. Once the flag has been propagated to all nodes, get the current timestamp. This timestamp will be used as the fence timestamp, waiting until all transactions that started before this timestamp have completed before moving on.
yugabyte=# SELECT now() AS t_fence;
            t_fence            
-------------------------------
 2026-04-21 20:47:55.825731+00
(1 row)
  1. Wait for all transactions that started before t_fence to complete. Wait until this query returns zero rows on every node before advancing to the next step (substitute the t_fence returned in step 3):
SELECT pid, datname, usename, application_name,
       state, xact_start, query_start, backend_start
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND backend_type = 'client backend'
  AND state IS DISTINCT FROM 'idle'
  AND (xact_start IS NULL OR xact_start <= TIMESTAMPTZ '<t_fence>');
  1. Set yb_always_increment_catalog_version_on_ddl=false in ysql_pg_conf_csv (do not perform a restart afterwards).

Autoflag Demotion

If the goal is to disable the catalog version increment for all DDLs on 2026.1+, the user must demote the autoflag ysql_yb_test_make_all_ddl_statements_incrementing:

$ MA=10.128.48.50:7100,10.128.48.51:7100,10.128.48.52:7100
$ ~/tserver/bin/yb-admin -master_addresses $MA -certs_dir_name ~/yugabyte-tls-config \
  demote_single_auto_flag yb-tserver ysql_yb_test_make_all_ddl_statements_incrementing
WARNING: Demotion of AutoFlags is dangerous and can lead to silent corruptions and data loss!Are you sure you want to demote the flag 'ysql_yb_test_make_all_ddl_statements_incrementing' belonging to process 'yb-tserver'? (y/N)?y
AutoFlag ysql_yb_test_make_all_ddl_statements_incrementing from process yb-tserver was successfully demoted
New config version: 2

Limitations

Incrementing the catalog version on all DDLs may cause more DDL errors. In particular, if previously the application/user was performing concurrent DDLs that did not increment the catalog version (e.g. CREATE TABLE), these CREATE statements will now fail due to conflict, when previously they would have succeeded.

A list of most of the DDLs that are affected (previously did not increment the catalog version, but will increment the catalog version when yb_always_increment_catalog_version_on_ddl is set to true):

  • CREATE TABLE
  • CREATE DATABASE
  • CREATE VIEW
  • CREATE OPERATOR
  • CREATE AGGREGATE
  • CREATE TYPE
  • CREATE TEXT SEARCH ...
  • CREATE COLLATION
  • COMMENT
  • CREATE PROFILE
  • TRUNCATE (when yb_enable_alter_table_rewrite=false)
  • CREATE ROLE (when not referencing other roles)
  • CREATE TABLE AS
  • CREATE SEQUENCE
  • DISCARD
  • ALTER ROLE WITH PASSWORD

 

SUPPORT-1097

Related to

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk