Diagnosing a Persistent pg_range Catalog Read in YugabyteDB

Jim Knicely

When tuning YSQL queries with EXPLAIN (ANALYZE, DIST), you will often notice that the first execution performs several catalog reads while later executions perform fewer… or ideally none.

That is normal. The PostgreSQL backend attached to your connection gradually fills its local catalog cache with metadata about tables, columns, indexes, operators, functions, and data types.

However, some queries settle at exactly one catalog read and never reach zero:

				
					Planning Time: 0.283 ms
Execution Time: 0.864 ms
Catalog Read Requests: 1
Catalog Read Execution Time: 0.516 ms

Running the query repeatedly does not help. Reusing the same connection does not help. Even preloading the relevant catalog table may not eliminate it.

When tracing shows that the remaining lookup targets pg_range, the problem may not be an ordinary cold-cache lookup. It may be a catalog lookup that returned “not found” and could not be retained in the backend’s negative catalog cache.

Version note: The behavior described in this tip was observed on YugabyteDB 2024.2.7.1. Catalog-cache behavior can change between releases, so reproduce the test on the exact YugabyteDB version used by your application.

What Is a Catalog Read?

Before YSQL can plan a query, it may need metadata describing the objects used by that query.

For example, the planner may need to determine:

  • ● Which columns exist in a table.
  • ● Which indexes are available.
  • ● Which function implements an expression.
  • ● Which operator applies to a pair of data types.
  • ● Whether a data type is a range type.
  • ● Which tables belong to a partition hierarchy.

This information is stored in PostgreSQL system catalogs such as:

				
					pg_class
pg_attribute
pg_type
pg_proc
pg_operator
pg_range
pg_inherits
pg_amop

In YugabyteDB, an uncached catalog lookup can require distributed work. This is particularly noticeable in multi-region clusters when the YB-Master leader is far from the PostgreSQL backend. YugabyteDB recommends connection pooling and catalog preloading to reduce ordinary cold-cache latency.

Why Catalog Reads Usually Disappear

Each YSQL connection is handled by a PostgreSQL backend process. That backend maintains its own in-memory catalog cache.

The first time the backend needs a particular catalog entry, it reads the entry and places it in memory. Later planning operations can reuse the cached result.

A typical pattern looks like this:

				
					First execution:
Catalog Read Requests: 14

Second execution:
Catalog Read Requests: 2

Third execution:
Catalog Read Requests: 0

This is why testing with the same connection is important. Opening a new ysqlsh connection also creates a new backend with a new catalog cache.

Important: Zero catalog reads is an excellent goal, but it is not a universal guarantee for every query and every YugabyteDB release. Some catalog searches return no row, and not every catalog supports caching those negative results.

What the Heck Is a Negative Cache?

A normal, or positive, cache remembers something that exists:

				
					Question:
Which catalog row describes this function?

Answer:
Here is the row.

Cached result:
The function exists, and this is its metadata.

A negative cache remembers that something does not exist:

				
					Question:
Is this data type registered as a range type?

Answer:
No matching pg_range row exists.

Cached result:
We already checked. This is not a range type.

Without negative caching, the backend remembers successful searches but forgets unsuccessful searches.

The next time the planner asks the same question, it must perform the lookup again—even though the answer is still “not found.”

Think of it like repeatedly searching for a name in a directory:

				
					Lookup 1:
Search for Josh Baskin.
Found him.
Remember the result.

Lookup 2:
Search for Billy Kopecky.
No result.
Forget that the search happened.

Lookup 3:
Search for Billy Kopecky again.
No result.
Forget again.

Lookup 4:
Search for Billy Kopecky again.

A negative cache would remember:

				
					Billy Kopecky is not in this directory.
Do not search again unless the directory changes.
Cache result Catalog search What the backend remembers
Positive A matching catalog row exists The catalog row and its metadata
Negative No matching catalog row exists The fact that the requested object does not exist

What Is pg_range?

The pg_range system catalog stores metadata about PostgreSQL range data types. A type such as int4range, daterange, or tsrange has an entry in pg_range.

A normal scalar type such as text, integer, or a user-defined domain generally does not have its own pg_range entry.

Therefore, a planner operation may effectively ask:

				
					Is this type a range type?

For a scalar type, the correct result is:

				
					No matching pg_range row exists.

That “not found” result is exactly the kind of result that requires negative caching if the lookup is to be avoided during the next planning cycle.

Do not confuse the two uses of “range”: The pg_range catalog describes range data types. It does not store the boundaries of range-partitioned tables. A query against a range-partitioned table can read pg_range, but the catalog lookup is generally related to data-type resolution… not the table’s partition dates.

A Representative Test Environment

The following setup mirrors the important characteristics of the original workload:

  • ● A range-partitioned table hierarchy.
  • ● An intermediate table that is both a partition and a partitioned table.
  • ● A case-insensitive username.
  • ● A user-defined domain.
  • ● An expression involving lower().

The exact trigger can vary by query shape and YugabyteDB release, so treat this as a representative test rather than a guarantee that every version will reproduce the same catalog count.

				
					CREATE EXTENSION IF NOT EXISTS citext;

CREATE DOMAIN public.audit_code_type AS integer;

CREATE TABLE public.audit_logs (
    audit_log_identifier bigint,
    audit_code_identifier public.audit_code_type,
    cardholder_username citext,
    cid text,
    bank_id uuid,
    activity_timestamp timestamp without time zone,
    PRIMARY KEY (
        audit_log_identifier,
        activity_timestamp
    )
) PARTITION BY RANGE (activity_timestamp);

Create an intermediate partition that is itself partitioned:

				
					CREATE TABLE public.audit_logs_p2026_01
PARTITION OF public.audit_logs
FOR VALUES FROM ('2026-01-01') TO ('2026-03-01')
PARTITION BY RANGE (activity_timestamp);

Create the leaf partitions:

				
					CREATE TABLE public.audit_logs_p2026_01_part1
PARTITION OF public.audit_logs_p2026_01
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE public.audit_logs_p2026_01_part2
PARTITION OF public.audit_logs_p2026_01
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Create an expression index on the intermediate partition:

				
					CREATE INDEX audit_logs_username_lower_idx
ON public.audit_logs_p2026_01 (
    lower(cardholder_username)
);

Add a test row:

				
					INSERT INTO public.audit_logs (
    audit_log_identifier,
    audit_code_identifier,
    cardholder_username,
    cid,
    bank_id,
    activity_timestamp
)
VALUES (
    1,
    100,
    'MALICIOUSNERD',
    'AAAA6331001',
    '7c98fad1-ff87-4125-b5e9-4294730ac760',
    '2026-01-15 12:00:00'
);
Step 1: Run the Query Repeatedly

Run the query against the intermediate partition:

				
					EXPLAIN (ANALYZE, DIST)
SELECT
    audit_log_identifier,
    audit_code_identifier,
    cardholder_username
FROM public.audit_logs_p2026_01
WHERE lower(cardholder_username) = lower('MALICIOUSNERD')
  AND cid = 'AAAA6331001'
  AND bank_id = '7c98fad1-ff87-4125-b5e9-4294730ac760'
  AND activity_timestamp >= '2026-01-01'
  AND activity_timestamp <  '2026-03-01';

Run the same statement several times using the same connection.

After the normal cache-warming reads disappear, you may continue to see output similar to:

				
					Planning Time: 0.283 ms
Execution Time: 0.864 ms
Catalog Read Requests: 1
Catalog Read Execution Time: 0.516 ms

The important symptom is not merely that the query performs one catalog read. The important symptom is that it performs the same single catalog read during every planning cycle.

Step 2: Identify the Catalog Being Read

Enable catalog-cache event logging for the current session:

				
					SET yb_debug_log_catcache_events = 1;
SET client_min_messages = LOG;

Run the query again.

The output may include a message similar to:

				
					LOG:  Catalog cache miss on cache with id ...
Target rel: pg_range
Search keys: ...

This confirms that the remaining catalog operation is associated with pg_range.

Diagnostic tip: Catalog-cache logging parameters differ between YugabyteDB release lines. Use the logging procedure appropriate for your server version, and remember that catalog behavior is controlled by the server… not by the version displayed by a newer ysqlsh client.

Why Preloading pg_range Might Not Fix It

YugabyteDB supports adding catalog tables such as pg_range to the list of catalogs preloaded when a backend starts or refreshes its cache:

				
					--ysql_catalog_preload_additional_table_list=pg_range

The official flag documentation even uses pg_range,pg_proc as an example.

Preloading can load the rows that currently exist in pg_range. However, it does not necessarily create every possible negative cache entry.

For example, preloading can remember:

				
					daterange exists
tsrange exists
int4range exists

It cannot practically precompute every question that might later be asked:

				
					Is audit_code_type a range type?
Is citext a range type?
Is some_future_extension_type a range type?

Therefore, preloading the table can reduce positive catalog misses without eliminating an unsupported negative lookup.

This is why repeatedly warming the connection or preloading pg_range may still leave exactly one catalog read.

The Relevant YugabyteDB Issue

YugabyteDB issue #29019 is titled:

				
					[YSQL] Allow negative catalog caching for
pg_operator, pg_range, pg_amop

The issue explains that negative caching for these syscaches depends on confirming that the corresponding DDL operations correctly increment the YugabyteDB catalog version. It is labeled high priority and includes a 2024.2 Backport Required label. As of July 30, 2026, the issue remains open and shows no linked pull request.

A related issue involving repeated pg_amop misses recommends prepared statements as a mitigation because they avoid replanning the query on every execution.

Mitigation 1: Use a Prepared Statement

The persistent catalog read occurs during query parsing, analysis, or planning… not while scanning the application table.

A prepared statement can reuse a previously generated execution plan instead of planning the statement again for every execution.

YugabyteDB recommends prepared statements where possible so the database can reuse query plans and avoid repeated parsing.

Force the use of a generic plan for this test:

				
					SET plan_cache_mode = force_generic_plan;

Prepare the statement:

				
					PREPARE audit_lookup (
    citext,
    text,
    uuid,
    timestamp,
    timestamp
) AS
SELECT
    audit_log_identifier,
    audit_code_identifier,
    cardholder_username
FROM public.audit_logs_p2026_01
WHERE lower(cardholder_username) = lower($1)
  AND cid = $2
  AND bank_id = $3
  AND activity_timestamp >= $4
  AND activity_timestamp <  $5;

Execute it once to create and warm the generic plan:

				
					EXECUTE audit_lookup(
    'MALICIOUSNERD',
    'AAAA6331001',
    '7c98fad1-ff87-4125-b5e9-4294730ac760',
    '2026-01-01',
    '2026-03-01'
);

Now inspect a subsequent execution:

				
					EXPLAIN (ANALYZE, DIST)
EXECUTE audit_lookup(
    'MALICIOUSNERD',
    'AAAA6331001',
    '7c98fad1-ff87-4125-b5e9-4294730ac760',
    '2026-01-01',
    '2026-03-01'
);

The catalog read may now disappear because the backend is reusing the generic plan rather than repeating the planner path that checks pg_range.

Important prepared-statement detail: PREPARE parses, analyzes, and rewrites the statement, but the reusable generic plan is normally created when the prepared statement is executed. Therefore, test at least two executions before concluding that the mitigation did not work.

PostgreSQL can use either custom or generic plans for parameterized prepared statements. force_generic_plan avoids repeated planning, but a generic plan may be less efficient when the best plan depends heavily on the supplied values. Always compare the complete execution plans and runtimes before enabling it broadly.

To clean up the test:

				
					DEALLOCATE audit_lookup;

RESET plan_cache_mode;

Mitigation 2: Simplify the Expression

The original query applies lower() to a citext column:

Because citext already provides case-insensitive comparison behavior, the expression may be replaceable with a direct comparison:

				
					cardholder_username = $1::citext

Alternatively, an application can store normalized lowercase values in a regular text column:

				
					cardholder_username = $1

This may avoid the particular function and type-resolution path that triggers the catalog lookup.

However, query rewrites can change:

  • ● Index eligibility.
  • ● Operator selection.
  • ● Collation behavior.
  • ● Application semantics.
  • ● Existing data requirements.

Treat this as a testable alternative… not a guaranteed fix.

Practical recommendation: Before changing the application, run both versions with EXPLAIN (ANALYZE, DIST). Confirm that the rewritten predicate preserves the expected results, uses the intended index, and actually removes the recurring catalog lookup.

Mitigation 3: Reuse Connections

Connection pooling does not directly fix an unsupported negative lookup. The backend may still repeat the single pg_range search whenever it creates a new plan.

However, pooling remains valuable because it prevents all the other ordinary catalog entries from being repeatedly loaded by short-lived connections.

Without pooling:

				
					New connection
Cold catalog cache
Multiple catalog reads
Connection closes

New connection
Cold catalog cache again
Multiple catalog reads
Connection closes

With pooling:

				
					Existing backend
Mostly warm catalog cache
Possibly one remaining negative lookup
Backend is reused

This can be especially important in a multi-region deployment, where catalog-cache warming may involve latency to a remote YB-Master leader.

Mitigation 4: Evaluate an Upgrade… but Verify the Exact Release

YugabyteDB 2025.1 introduced configuration work related to customizing negative catalog caching. However, that does not mean every catalog supports negative caching in every 2025.1 or later patch.

In particular, pg_range, pg_operator, and pg_amop remain explicitly named in the open enhancement request.

Therefore, do not assume that upgrading to an arbitrary 2025.x release automatically removes this lookup.

Use this process instead:
  • 1. Reproduce the query on the current version.
  • 2. Capture the catalog-cache log showing pg_range.
  • 3. Test the same query on the proposed target version.
  • 4. Run it repeatedly on the same connection.
  • 5. Confirm whether Catalog Read Requests reaches 0.
  • 6. Confirm that the query plan itself has not regressed.

Mitigation 5: Accept the Single Catalog Read

Sometimes the safest immediate response is to document and accept the lookup.

A stable single pg_range lookup is metadata work. The lookup itself does not scan the rows in the application table, so increasing the number of audit records should not proportionally increase the cost of this particular catalog operation.

However, the total impact still depends on:

  • ● How often the query is planned.
  • ● Whether the application uses prepared statements.
  • ● Connection churn.
  • ● Distance from the YB-Master leader.
  • ● Current master load.
  • ● Number of application sessions executing the query.
  • ● Whether many different queries encounter the same limitation.

One catalog read taking less than a millisecond in a local region may be harmless. The same planning lookup can become much more visible when the PostgreSQL backend is in a region with tens of milliseconds of round-trip latency to the catalog leader.

Choosing the Right Response

Situation Recommended response
Query runs frequently with different values Test a prepared statement and generic-plan reuse
Application opens many short-lived connections Add or improve connection pooling
lower() is applied to a citext column Test direct citext equality or normalized storage
One lookup has negligible local latency Document and accept the predictable planning cost
Remote-region planning adds noticeable latency Prioritize prepared statements, connection reuse, and version testing
An upgrade is already planned Reproduce on the exact target patch; do not rely only on major-version release notes

Cleanup (Demo)

Remove the test objects when finished:

				
					DROP TABLE public.audit_logs CASCADE;

DROP DOMAIN public.audit_code_type;

DROP EXTENSION citext;

Final Takeaway

A query that remains at exactly one catalog read after repeated executions is not necessarily suffering from an ordinary cold cache.

When catalog-cache logging identifies pg_range, the planner may be repeatedly checking whether a data type is a range type. If no matching row exists and that negative result cannot be cached, the backend performs the same lookup during every new planning cycle.

The most important points are:

  • ● pg_range describes range data types, not range-partition boundaries.
  • ● A negative cache remembers that a catalog search returned no row.
  • ● Preloading existing pg_range rows may not eliminate missing-row searches.
  • ● Prepared statements can avoid the lookup by reusing a generic plan.
  • ● Connection pooling reduces other catalog-cache warming costs.
  • ● Query simplification may avoid the triggering type-resolution path.
  • ● Do not assume that every 2025.x or later release fixes the behavior.
  • ● A stable single catalog read can be accepted when its measured impact is small.

The goal is not to chase a zero simply because zero looks cleaner. The goal is to identify what the remaining catalog read is doing, measure its actual latency, and choose the mitigation that makes sense for the workload.

Last night, my best friend and I saw Nate Bargatze’s Big Dumb Eyes World Tour at PPG Paints Arena in Pittsburgh!

What a great show! At the end of the night, Nate walked right past us, giving everyone high-fives. 

After seeing that signature wide-eyed look up close, I finally understood the name of the tour! 😂👏