YugabyteDB’s cost-based optimizer (CBO) is a big deal. Turn it on, keep stats fresh, and your plans get smarter, closer to PostgreSQL parity, without hints or query surgery. The docs recommend enabling CBO and underscore the need for up-to-date statistics (ANALYZE), or the planner can’t do its job.
One hidden gotcha often discovered only in production is that running ANALYZE can increment the per-database catalog version, which in turn invalidates YSQL caches on every TServer. The next connection to each TServer then incurs a “cold start” penalty as metadata is reloaded, especially painful in multi-region setups, where the first wave of lookups may have to cross regions to reach the Master leader.
Why this happens (and why it’s worse across regions)
• YugabyteDB tracks schema/metadata freshness with a per-database catalog version. When that version increases, existing backends must refresh their local catalog caches before executing further work; new connections do that on startup.
• First-touch metadata often comes from the YB-Master leader. If your leader sits in another region, those initial cache fills add latency, exactly when your app is trying to spin up fresh connections. The admin best-practices doc calls out this multi-region cost and recommends preloading to mitigate “first query” slowness.
Reproducing the catalog bump with ANALYZE
yugabyte=# CREATE TABLE t(c INT PRIMARY KEY);
CREATE TABLE
-- Check the catalog version for the current DB
yugabyte=# SELECT d.datname, current_version, last_breaking_version
FROM pg_yb_catalog_version v
JOIN pg_database d ON d.oid = v.db_oid
WHERE d.datname = current_database()
ORDER BY d.datname;
datname | current_version | last_breaking_version
----------+-----------------+-----------------------
yugabyte | 1 | 1
(1 row)
-- Run ANALYZE (updates stats)
yugabyte=# ANALYZE t;
ANALYZE
-- Version increased → TServer caches invalidated for this DB
yugabyte=# SELECT d.datname, current_version, last_breaking_version
FROM pg_yb_catalog_version v
JOIN pg_database d ON d.oid = v.db_oid
WHERE d.datname = current_database()
ORDER BY d.datname;
datname | current_version | last_breaking_version
----------+-----------------+-----------------------
yugabyte | 2 | 1
(1 row)
That increment is what triggers the one-time “longer than usual” connection on every TServer.
Warning from the Field!
I ran into this with a customer and I was caught off guard. They scheduled a daily ANALYZE on every table in a schema (about 20 tables). That meant 20 separate catalog bumps, invalidating TServer caches each time. Immediately after the job, we saw a wave of slow first connections from each app tier as every TServer had to re-warm its caches, amplified by their multi-region topology.
If this sounds familiar, the good news is there’s a clean workaround.
Workaround: keep the stats, skip the bump
YugabyteDB added a one-shot GUC that prevents the next DDL-like statement from incrementing the catalog version:
-- Suppress the next bump
SET yb_make_next_ddl_statement_nonincrementing = on;
-- Refresh stats without a version increment
ANALYZE t;
This capability exists specifically to reduce memory/latency spikes during bursts of DDL-ish operations. It’s in recent releases (v2.20+, v2024.1+, v2025.1+).
Trade-off: existing sessions won’t see the newly gathered stats until they reconnect; new connections will.
Verifying no bump
yugabyte=# SELECT d.datname, current_version, last_breaking_version
yugabyte-# FROM pg_yb_catalog_version v
yugabyte-# JOIN pg_database d ON d.oid = v.db_oid
yugabyte-# WHERE d.datname = current_database()
yugabyte-# ORDER BY d.datname;
datname | current_version | last_breaking_version
----------+-----------------+-----------------------
yugabyte | 2 | 1
(1 row)
yugabyte=# SET yb_make_next_ddl_statement_nonincrementing = on;
SET
yugabyte=# ANALYZE t;
ANALYZE
yugabyte=# SELECT d.datname, current_version, last_breaking_version
yugabyte-# FROM pg_yb_catalog_version v
yugabyte-# JOIN pg_database d ON d.oid = v.db_oid
yugabyte-# WHERE d.datname = current_database()
yugabyte-# ORDER BY d.datname;
datname | current_version | last_breaking_version
----------+-----------------+-----------------------
yugabyte | 2 | 1
(1 row)
Applying it to many tables (one session)
Because the GUC applies to one statement, include it inline per table. With ysqlsh, this pattern is simple:
\set schema my_schema
SELECT format(
'SET yb_make_next_ddl_statement_nonincrementing=on; ANALYZE %I.%I;',
schemaname, tablename
)
FROM pg_tables
WHERE schemaname = :'schema' \gexec
That will run SET … = on; ANALYZE …; for each table… no catalog bumps.
Putting it all together (recommendations)
• Enable CBO (recommended) and keep stats fresh, the CBO relies on them for good plans.
- • Preload catalog caches so new connections aren’t starting cold.
• Avoid surprise invalidations when running routine stats jobs:
◦ Use
SET yb_make_next_ddl_statement_nonincrementing = on; ANALYZE ...;per table (or generate statements with\gexec).◦ New connections benefit immediately; existing ones pick up stats on reconnect.
• If you do bump the version, schedule the job off-peak and consider preloading/connection warm-ups so the “first hit” after invalidation isn’t on your critical path.
The Very Near Future is Bright!
In YugabyteDB today, any DDL operation that increments the catalog version forces all YSQL backend processes to invalidate and completely reload their catalog caches, which can be costly. Issue #23785 proposes enhancing this by allowing backends to refresh their caches incrementally, updating only the entries affected by the DDL rather than reloading everything, thereby improving efficiency and reducing disruption.
Have Fun!
