Verify pg_type Negative Catalog Caching in YugabyteDB

YSQL uses PostgreSQL’s system catalog caches to avoid repeatedly reading metadata from the distributed system catalog.

But there is another useful optimization that is easy to overlook: negative catalog caching.

A negative cache entry remembers that a catalog lookup did not find an object.

That can be particularly important with pg_type, because type resolution may probe the catalog for types that don’t exist. Without effective caching, repeated lookups can result in unnecessary catalog reads.

So how can you verify that negative caching for pg_type is actually doing its job?

Let’s build a very small reproducible test. 🔎

📘 New to YSQL Catalog Caching?

Before diving into negative cache entries, you may want to read:

👉 Understanding YSQL Catalog Caching in YugabyteDB

What Is a Negative Catalog Cache Entry?

Normally, we think of a cache as remembering something that exists.

For example:

type "integer"

↓

found in pg_type

↓

cache the result

A negative cache remembers the opposite:

type "something_that_does_not_exist"

↓

not found in pg_type

↓

remember that result

If YSQL needs to perform the same lookup again, it can avoid repeating unnecessary distributed catalog work.

The important part is that creating a negative cache entry and performing a remote catalog read are not the same thing.

We’ll see that shortly.

Find the `pg_type` Syscache IDs

Negative catalog caching is controlled by catalog cache IDs.

For YugabyteDB 2025.2.6 used in this example, pg_type maps to syscache IDs 79 (TYPENAMENSP) and 80

These IDs are version-specific.

⚠️ Don’t Blindly Copy Syscache IDs Between Versions

Syscache IDs are internal identifiers and should be verified for the YugabyteDB version you’re running.

I have another Tip that shows how to determine the correct IDs directly from the YugabyteDB source:

👉 Find YugabyteDB Syscache IDs for yb_neg_catcache_ids

For this example we’ll therefore use:

				
					SET yb_neg_catcache_ids = '79,80';

Start With Negative Caching Enabled

Two settings are important when enabling negative catalog caching safely:

● yb_enable_negative_catcache_entries
● yb_always_increment_catalog_version_on_ddl

📘 YugabyteDB Support Guide

For the complete supported procedure for enabling negative catalog caching on YugabyteDB 2025.2, including the required flag ordering, rolling-restart sequence, validation steps, and important limitations, see:

👉 How to Enable Negative Caching on 2025.2

These should be configured cluster-wide, rather than enabled only inside the test session. That was an important detail uncovered during the testing.

For example, here’s what happens when you try to enable yb_enable_negative_catcache_entries at the session level:

				
					yugabyte=# SET yb_enable_negative_catcache_entries=on;
ERROR:  parameter "yb_enable_negative_catcache_entries" cannot be changed now

For a local yugabyted test:

				
					./yugabyted start \
  --tserver_flags="ysql_pg_conf_csv={yb_enable_negative_catcache_entries=on,yb_always_increment_catalog_version_on_ddl=on}" \
  > start.log

Verify them:

				
					./ysqlsh -c "
SELECT name, setting
FROM pg_settings
WHERE name IN (
    'yb_enable_negative_catcache_entries',
    'yb_always_increment_catalog_version_on_ddl'
);"

You should see:

				
					.                   name                    | setting
--------------------------------------------+---------
 yb_always_increment_catalog_version_on_ddl | on
 yb_enable_negative_catcache_entries        | on

The Simple `pg_type` Repro

We don’t need a complicated application query to demonstrate the behavior.

This is enough:

				
					SELECT TRIM('hello world') AS trimmed_string;

Run it twice in the same session while enabling catalog-cache debugging:

				
					ysqlsh -h 127.0.0.1 -d yugabyte 2>&1 << 'EOF' \
| egrep "=== FIRST RUN|=== SECOND RUN|added neg entry|Catalog Read Requests|Planning Time"

SET yb_neg_catcache_ids = '79,80';
SET yb_debug_log_catcache_events = 1;
SET backtrace_functions = 'SearchCatCacheMiss';
SET client_min_messages = LOG;

\echo '=== FIRST RUN (Cold Session) ==='

EXPLAIN (ANALYZE, DIST)
SELECT TRIM('hello world') AS trimmed_string;

\echo '=== SECOND RUN (Warm Session) ==='

EXPLAIN (ANALYZE, DIST)
SELECT TRIM('hello world') AS trimmed_string;

EOF

Without additional catalog preloading, the test produced:

				
					=== FIRST RUN (Cold Session) ===
LOG:  SearchCatCache(pg_type): added neg entry in cache id 79 bucket 53
 Planning Time: 0.157 ms
 Catalog Read Requests: 7

=== SECOND RUN (Warm Session) ===
 Planning Time: 0.013 ms
 Catalog Read Requests: 0

The negative entry is working.

After the first execution, the repeated query no longer requires catalog reads.

But notice the first execution still required:

				
					Catalog Read Requests: 7

Can we do better?

Yep. 🙂

Preload Additional Catalog Information

YugabyteDB provides the YB-TServer flag:

ysql_catalog_preload_additional_tables

When enabled, PostgreSQL backend processes preload additional catalog information at connection creation and catalog-cache refresh time. YugabyteDB documents this as a way to reduce first-query latency, with the tradeoff of additional backend memory consumption.

Restart with:

				
					./yugabyted stop

./yugabyted start \
  --tserver_flags="ysql_pg_conf_csv={yb_enable_negative_catcache_entries=on,yb_always_increment_catalog_version_on_ddl=on},ysql_catalog_preload_additional_tables=true" \
  > start.log

Verify that YSQL really started:

				
					./ysqlsh -h 127.0.0.1 -Atc "SELECT 1"

Then confirm the TServer flag:

				
					curl -s http://127.0.0.1:9000/varz?raw \
  | grep ysql_catalog_preload_additional_tables

You should see:

				
					--ysql_catalog_preload_additional_tables=true

The flag requires a restart, according to the current YugabyteDB configuration documentation.

🔎 Want More Control Over Catalog Preloading?

Rather than guessing which catalog tables matter for a workload, you can identify them directly.

👉 Identifying YSQL Catalog Tables to Preload Without Grepping Logs Across Nodes

Run the Test Again

Use the exact same test:

				
					ysqlsh -h 127.0.0.1 -d yugabyte 2>&1 << 'EOF' \
| egrep "=== FIRST RUN|=== SECOND RUN|added neg entry|Catalog Read Requests|Planning Time"

SET yb_neg_catcache_ids = '79,80';
SET yb_debug_log_catcache_events = 1;
SET backtrace_functions = 'SearchCatCacheMiss';
SET client_min_messages = LOG;

\echo '=== FIRST RUN (Cold Session) ==='

EXPLAIN (ANALYZE, DIST)
SELECT TRIM('hello world') AS trimmed_string;

\echo '=== SECOND RUN (Warm Session) ==='

EXPLAIN (ANALYZE, DIST)
SELECT TRIM('hello world') AS trimmed_string;

EOF

Now look at the result:

				
					=== FIRST RUN (Cold Session) ===
LOG:  SearchCatCache(pg_type): added neg entry in cache id 79 bucket 181
 Planning Time: 0.082 ms
 Catalog Read Requests: 0

=== SECOND RUN (Warm Session) ===
 Planning Time: 0.019 ms
 Catalog Read Requests: 0

That’s the result we’re looking for. 🎯

The test produced zero catalog read requests even on the first execution after enabling the negative-caching settings together with additional catalog preloading.

Wait… Why Does It Still Say `added neg entry`?

This is the subtle — and important — part.

The output still contains:

				
					LOG:  SearchCatCache(pg_type): added neg entry in cache id 79 bucket 181

At first glance, that looks like a cache miss.

It isn’t necessarily one.

💡 This Is the Key

Seeing added neg entry means YSQL created a negative catalog-cache entry.

It does not necessarily mean YSQL performed a distributed catalog lookup or sent an RPC to the YB-Master.

What really matters: Catalog Read Requests: 0

That tells us the lookup was resolved without a remote catalog read.

That distinction was explicitly confirmed during the investigation: Catalog Read Requests: 0 means the lookup did not miss locally and go to the master, even though the debug log shows a new negative entry being created.

In other words:

Creating a negative cache entry

≠

Performing a remote catalog read

That’s the part I would pay attention to when validating this optimization.

Comparing the Results

Here’s the progression from the simple repro:

Configuration	First Run Catalog Reads	Second Run Catalog Reads
Negative caching configured	7	0
Negative caching + additional catalog preload	0 ✅	0 ✅

The first configuration already gives us the expected steady-state behavior.

The second takes it one step further by avoiding catalog read requests on the first execution of this repro as well.

Don’t Confuse a Log Message With an RPC

When troubleshooting catalog caching, debug logs are extremely useful.

But don’t stop at:

				
					SearchCatCache(pg_type): added neg entry

Also inspect:

				
					Catalog Read Requests

from:

				
					EXPLAIN (ANALYZE, DIST)

For this particular test:

added neg entry

Catalog Read Requests: 0

is a good result.

The negative entry was created, but no remote catalog read was necessary.

🔗 Related Catalog-Read Tip

If a catalog table continues to appear in catalog-read diagnostics, this related Tip walks through a deeper investigation using pg_range.

👉 Diagnosing a Persistent pg_range Catalog Read in YugabyteDB

A Simple Validation Checklist

When validating pg_type negative catalog caching, I would check these four things:

Status	Validation Step
✅	Identify the correct `pg_type` syscache IDs for your YugabyteDB version
✅	Configure negative caching and catalog-version handling cluster-wide
✅	Enable additional catalog preloading when first-query catalog reads matter
✅	Verify with `EXPLAIN (ANALYZE, DIST)` that `Catalog Read Requests` reaches 0

And remember:

				
					"added neg entry"

by itself is not evidence of a remote catalog read.

Final Takeaway

Negative catalog caching can eliminate repeated metadata lookups when YSQL searches pg_type for something that doesn’t exist.

But when you’re validating the behavior, don’t judge it solely by the debug log.

This:

				
					LOG: SearchCatCache(pg_type): added neg entry ...

may still appear.

The important measurement is:

				
					Catalog Read Requests: 0

With the appropriate negative-caching configuration and additional catalog preloading, the simple repro showed zero catalog read requests on both the first and second execution.

That’s how you know the pg_type negative lookup is being handled locally. 🔥

🙏 Special Thanks

A special thanks to Kai Franz, Sr. Software Engineer at YugabyteDB, for helping with the testing and diagnosis behind this Tip.

Kai also helped clarify an important detail in these examples: seeing added neg entry does not necessarily mean YSQL performed a remote catalog read. The key validation metric is whether Catalog Read Requests remains at 0.

Have Fun!

Apparently, moving to Dallas comes with a few requirements… and Shiner Bock is one of them. 🍺🤠

While in Dallas last week, we had an amazing “work” dinner at Terry Black’s BBQ. I decided to try a Shiner Bock… a beer I’d somehow never heard of before. My Dallas-based work colleague informed me that I’d better get used to it since I’m moving there soon. Apparently, it’s practically a requirement at backyard BBQs in Texas.

Looks like my Dallas orientation has officially begun. 😂🍻