YSQL catalog cache preloading can reduce the catalog reads performed when a new PostgreSQL backend processes its first queries.
However, on older YugabyteDB releases, you may encounter a confusing situation: you explicitly add pg_operator to the catalog preload list, but the first query on every new connection still performs a catalog read against pg_operator.
Is the preload flag being ignored?
Not exactly.
The rows from pg_operator may already be preloaded, but the query is using a different type of PostgreSQL catalog cache lookup… one that older YugabyteDB versions cannot satisfy from the individual preloaded rows.
This tip reproduces the behavior on YugabyteDB 2024.2.7.1 and explains the difference between individual catalog tuple entries and catalog cache list entries.
ysqlsh client and the YugabyteDB server. The enhancement discussed in this tip was added in YugabyteDB 2025.2.4.0 and is also included in YugabyteDB 2026.1.
The Test Environment
This demonstration uses yugabyted to create and manage a local single-node YugabyteDB cluster. Using yugabyted keeps the setup simple and makes it easy to restart the cluster with different catalog preload flags during the test.
Check the YugabyteDB server version:
./yugabyted version
Example output:
----------------------------------------------------------------------
| Version |
----------------------------------------------------------------------
| Version : 2024.2.7.1-b1 |
| Build Time : 30 Dec 2025 19:05:31 UTC |
| Build Hash : 13a0684aa7a7a0cb1d4fb9f1e8e67a67acc7f912 |
----------------------------------------------------------------------
For a clean test, destroy and restart the local cluster:
./yugabyted destroy > destroy.log
./yugabyted start > start.log
Create a convenient alias for ysqlsh:
y='/root/yugabyte-2024.2.7.1/bin/ysqlsh'
Connect to YSQL:
y
The client and server versions are shown separately:
ysqlsh (15.12-YB-2026.1.0.0-b0, server 11.2-YB-2024.2.7.1-b0)
Type "help" for help.
yugabyte=#
The server version, 2024.2.7.1, is what matters for this test.
Step 1: Create the Test Objects
Create two tablespaces:
CREATE TABLESPACE test1_tablespace WITH (
replica_placement='{
"num_replicas": 1,
"placement_blocks": [
{
"cloud": "cloud1",
"region": "datacenter1",
"zone": "rack1",
"min_num_replicas": 1
}
]
}'
);
CREATE TABLESPACE test2_tablespace WITH (
replica_placement='{
"num_replicas": 1,
"placement_blocks": [
{
"cloud": "cloud1",
"region": "datacenter1",
"zone": "rack1",
"min_num_replicas": 1
}
]
}'
);
Create a list-partitioned table:
CREATE TABLE test (
c1 INT,
c2 INT,
PRIMARY KEY (c1, c2)
)
PARTITION BY LIST (c2);
Create two partitions:
CREATE TABLE test1
PARTITION OF test
FOR VALUES IN (1);
CREATE TABLE test2
PARTITION OF test
FOR VALUES IN (2);
Create an index on each partition:
CREATE INDEX test1_idx
ON test1 (c2)
INCLUDE (c1)
TABLESPACE test1_tablespace;
CREATE INDEX test2_idx
ON test2 (c2)
INCLUDE (c1)
TABLESPACE test2_tablespace;
Exit ysqlsh:
\q
Step 2: Check Which Catalog Tables Are Read
The following command runs the test query on a new YSQL connection and returns the distinct catalog tables accessed:
DEFAULT=""
y 2>&1 <<'SQL' \
| grep "Target rel:" \
| awk '{print $3}' \
| sort -u \
| awk -v re="$DEFAULT" 're=="" || $0 !~ re' \
| paste -sd, -
SET yb_debug_log_catcache_events = 1;
SET yb_debug_report_error_stacktrace = 1;
SET client_min_messages = LOG;
EXPLAIN (ANALYZE, DIST)
SELECT *
FROM test
WHERE c2 = 1;
SQL
The debug settings above are appropriate for YugabyteDB 2024.2.
Newer YugabyteDB releases use:
SET yb_debug_log_catcache_events = 1;
SET backtrace_functions = 'SearchCatCacheMiss';
SET client_min_messages = LOG;
Step 3: Run Without Additional Catalog Preloading
Run the catalog read test (the command from step 2).
Example result:
pg_amop,pg_class,pg_operator,pg_tablespace
Run it again using another new connection.
Example result:
pg_am,pg_amop,pg_amproc,pg_attribute,pg_class,pg_index,pg_namespace,pg_opclass,pg_operator,pg_partitioned_table,pg_proc,pg_tablespace,pg_type
Each invocation starts a new ysqlsh session. Each session creates a new PostgreSQL backend with its own process-local catalog cache.
The test is therefore measuring the first-query behavior of a new backend, not repeatedly executing the query through the same warmed backend.
Step 4: Enable the Default Additional Preload List
Stop the cluster:
./yugabyted stop > stop.log
Restart it with additional catalog preloading enabled:
./yugabyted start \
--tserver_flags="ysql_catalog_preload_additional_tables=true" \
--master_flags="ysql_catalog_preload_additional_tables=true" \
> start.log
Run the test again:
DEFAULT=""
y 2>&1 <<'SQL' \
| grep "Target rel:" \
| awk '{print $3}' \
| sort -u \
| awk -v re="$DEFAULT" 're=="" || $0 !~ re' \
| paste -sd, -
SET yb_debug_log_catcache_events = 1;
SET yb_debug_report_error_stacktrace = 1;
SET client_min_messages = LOG;
EXPLAIN (ANALYZE, DIST)
SELECT *
FROM test
WHERE c2 = 1;
SQL
Example result:
pg_amop,pg_operator
The default additional preload list eliminated most of the catalog reads, but pg_amop and pg_operator remain.
Step 5: Explicitly Preload pg_amop and pg_operator
Stop the cluster again:
./yugabyted stop > stop.log
Restart it and explicitly add both tables to the catalog preload list:
./yugabyted start \
--tserver_flags="ysql_catalog_preload_additional_tables=true,ysql_catalog_preload_additional_table_list={pg_amop,pg_operator}" \
--master_flags="ysql_catalog_preload_additional_tables=true,ysql_catalog_preload_additional_table_list={pg_amop,pg_operator}" \
> start.log
Run the catalog read test again:
DEFAULT=""
y 2>&1 <<'SQL' \
| grep "Target rel:" \
| awk '{print $3}' \
| sort -u \
| awk -v re="$DEFAULT" 're=="" || $0 !~ re' \
| paste -sd, -
SET yb_debug_log_catcache_events = 1;
SET yb_debug_report_error_stacktrace = 1;
SET client_min_messages = LOG;
EXPLAIN (ANALYZE, DIST)
SELECT *
FROM test
WHERE c2 = 1;
SQL
The result is surprising:
pg_operator
Why?
Look at the Full Cache Miss
Run the query without filtering the debug output:
y \
-c "SET yb_debug_log_catcache_events = 1;" \
-c "SET yb_debug_report_error_stacktrace = 1;" \
-c "SET client_min_messages = LOG;" \
-c "EXPLAIN (ANALYZE, DIST) SELECT * FROM test WHERE c2 = 1;"
The relevant portion of the output is:
LOG: Catalog cache list miss on cache with id: 37:
Target rel: pg_operator (oid: 2617), index oid: 2689
Search keys: =, 23, 23
@ SearchCatCacheList
@ OpernameGetOprid
@ oper
@ make_op
@ transformSelectStmt
The execution plan also reports one catalog read:
Catalog Read Requests: 1
Catalog Read Execution Time: 2.602 ms
The most important words in the log are:
Catalog cache list miss
This is not an ordinary single-row catalog cache miss.
Tuple Cache Entries Versus List Cache Entries
PostgreSQL’s catalog cache can store different forms of cached information.
Individual tuple entries
A normal catalog cache lookup searches for one specific catalog row using a complete cache key.
Conceptually:
Find the pg_operator row whose OID is 96.
The result is stored as an individual catalog cache tuple, commonly called a CatCTup.
The ysql_catalog_preload_additional_table_list setting preloads these individual tuple entries for the selected catalog tables.
Catalog list entries
Some PostgreSQL operations need a set of matching rows rather than one exact row.
For example, while resolving:
c2 = 1
the parser must identify the appropriate = operator for the supplied operand types.
That lookup follows this path:
OpernameGetOprid
-> SearchCatCacheList
The resulting collection is stored as a catalog cache list, commonly called a CatCList.
Conceptually, the lookup asks:
Find the candidate "=" operators matching these operand types.
That list is a separate cache object from the individual preloaded pg_operator rows.
What Do the Search Keys Mean?
The debug output shows:
Search keys: =, 23, 23
These keys represent:
The parser is effectively trying to resolve:
integer = integer
Why Preloading pg_operator Was Not Enough
On YugabyteDB 2024.2.7.1, preloading pg_operator loads its individual catalog tuples into the backend cache.
However, it does not construct every possible CatCList that PostgreSQL may later request.
When SearchCatCacheList asks for the operator list identified by:
=, 23, 23
the backend does not yet have an entry proving that the requested list is complete.
Even though the individual pg_operator rows are already present, the older code path still performs a catalog read to construct the list.
Why Did pg_amop Disappear?
Certain frequently used list-cache paths already had specialized optimizations that prepopulated common list entries.
These included some lookups involving catalogs such as:
pg_proc
pg_amop
That is why adding pg_amop to the preload list can eliminate its catalog read while pg_operator remains.
Creating equivalent special cases for every possible pg_operator lookup would be difficult. Operators can be searched using different combinations of:
- ● Operator name
- ● Left operand type
- ● Right operand type
- ● Namespace and search path rules
That could require many separate prebuilt lists.
YugabyteDB Issue #30868
This behavior was addressed by YugabyteDB issue #30868 – YSQL: Compute catalog cache list entries from preloaded entries.
The enhancement introduced a more general solution.
When a catalog cache is known to be fully loaded, YugabyteDB can satisfy a list-cache miss by walking the individual tuples already present in the backend’s local cache.
Instead of doing this:
SearchCatCacheList
-> Read pg_operator from the YB-Master
newer versions can do this:
SearchCatCacheList
-> Scan the already-preloaded pg_operator tuples
-> Build the CatCList locally
The implementation checks that:
The catalog cache is fully loaded
AND
The number of cached tuples is below the configured limit
Version Availability
Does pg_operator Cache at All on Older Releases?
Yes.
The list lookup is cached after it is built in that PostgreSQL backend.
The key detail is that every execution of the shell test creates a new ysqlsh connection and therefore a new backend process.
On YugabyteDB 2024.2.7.1, the sequence is:
The catalog read is not necessarily performed for every query. It is generally performed once for that list lookup in each new backend, unless the cache entry is later invalidated.
Test Two Queries in the Same Backend
To demonstrate the difference, run the query twice through the same ysqlsh connection:
y 2>&1 <<'SQL'
\echo 'First execution'
EXPLAIN (ANALYZE, DIST)
SELECT *
FROM test
WHERE c2 = 1;
\echo 'Second execution in the same backend'
EXPLAIN (ANALYZE, DIST)
SELECT *
FROM test
WHERE c2 = 1;
SQL
Example output:
First execution
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..5.11 rows=10 width=8) (actual time=0.604..0.604 rows=0 loops=1)
-> Index Only Scan using test2_idx on test1 (cost=0.00..5.06 rows=10 width=8) (actual time=0.603..0.603 rows=0 loops=1)
Index Cond: (c2 = 1)
Heap Fetches: 0
Storage Index Read Requests: 1
Storage Index Read Execution Time: 0.250 ms
Planning Time: 0.501 ms
Execution Time: 0.714 ms
Storage Read Requests: 1
Storage Read Execution Time: 0.250 ms
Storage Rows Scanned: 0
Catalog Read Requests: 1
Catalog Read Execution Time: 0.867 ms
Catalog Write Requests: 0
Storage Write Requests: 0
Storage Flush Requests: 0
Storage Execution Time: 1.117 ms
Peak Memory Usage: 16 kB
(18 rows)
Second execution in the same backend
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..5.11 rows=10 width=8) (actual time=0.399..0.399 rows=0 loops=1)
-> Index Only Scan using test2_idx on test1 (cost=0.00..5.06 rows=10 width=8) (actual time=0.399..0.399 rows=0 loops=1)
Index Cond: (c2 = 1)
Heap Fetches: 0
Storage Index Read Requests: 1
Storage Index Read Execution Time: 0.354 ms
Planning Time: 0.066 ms
Execution Time: 0.419 ms
Storage Read Requests: 1
Storage Read Execution Time: 0.354 ms
Storage Rows Scanned: 0
Catalog Read Requests: 0
Catalog Write Requests: 0
Storage Write Requests: 0
Storage Flush Requests: 0
Storage Execution Time: 0.354 ms
Peak Memory Usage: 8 kB
(17 rows)
Note that in he first execution, we see:
Catalog Read Requests: 1
While in the second execution, we observe:
Catalog Read Requests: 0
The second execution in the same backend can reuse the resulting list, provided the entry has not been invalidated.
When Is This Most Noticeable?
The extra first-query catalog lookup is most noticeable in environments with:
- ● High connection churn
- ● Short-lived database sessions
- ● Applications without connection pooling
- ● Serverless or job-based workloads that frequently reconnect
- ● Multi-region deployments where catalog RPC latency is higher
- ● Large numbers of new backends starting at the same time
Connection pooling reduces this cost because applications reuse warmed PostgreSQL backend processes instead of continuously creating new ones.
What Should You Do?
On YugabyteDB 2024.2.7.1
Adding pg_operator to the preload list still preloads the table’s individual tuple entries, but it cannot eliminate the first SearchCatCacheList read in every new backend.
The practical options are:
- 1. Use connection pooling so warmed PostgreSQL backends are reused.
- 2. Accept the one-time list-cache lookup on each new backend.
- 3. Upgrade to a YugabyteDB release that contains the issue #30868 enhancement.
On YugabyteDB 2025.2.4.0 or Later
Preload the required catalog tables:
--tserver_flags="ysql_catalog_preload_additional_tables=true,ysql_catalog_preload_additional_table_list={pg_amop,pg_operator}"
When the pg_operator cache is fully loaded and remains below yb_catcache_list_from_preloaded_limit, YugabyteDB can build the requested list locally without an additional catalog read.
Final Takeaway
Preloading a PostgreSQL catalog table does not necessarily mean that every possible catalog cache object derived from that table has already been created.
On YugabyteDB 2024.2.7.1:
pg_operator tuples are preloaded
but:
pg_operator CatCList entries are not
The predicate:
WHERE c2 = 1
uses SearchCatCacheList while resolving the = operator.
Because older YugabyteDB versions cannot build that list from the individual preloaded tuples, the first lookup in each new backend still performs a catalog read.
YugabyteDB issue #30868 closes that gap by allowing eligible catalog cache lists to be constructed from catalog tuples that are already preloaded in memory.
So pg_operator was not refusing to cache… it was waiting for a different kind of cache entry.
Resources
Have Fun!
