Find YugabyteDB Syscache IDs for yb_neg_catcache_ids

YSQL relies heavily on PostgreSQL’s system catalog cache, commonly called the catcache, to avoid repeatedly reading metadata from system catalog tables such as pg_class, pg_type, pg_operator, and pg_attribute.

The cache normally stores catalog rows that were successfully found. However, PostgreSQL can also cache a failed lookup… an assertion that no catalog row exists for a particular search key. This is known as a negative cache entry.

A negative cache entry can improve performance when an application repeatedly checks for an object that does not exist. Instead of performing the same catalog lookup every time, the backend can immediately return the cached “not found” result.

YugabyteDB supports negative catalog caching too, but it enables the behavior more selectively. The yb_neg_catcache_ids YSQL configuration parameter can be used to enable negative caching for additional catalog caches.

💡 Tip at a Glance: The values accepted by yb_neg_catcache_ids are not catalog table OIDs. They are the zero-based integer positions of entries in the YugabyteDB SysCacheIdentifier enum.

Why Does YugabyteDB Restrict Negative Caching?

In PostgreSQL, system catalog invalidation messages ensure that cached catalog information is discarded when catalog rows change.

YugabyteDB must coordinate catalog changes across multiple YSQL backends and potentially multiple nodes. Catalog invalidation therefore depends on YugabyteDB’s catalog-version infrastructure in addition to PostgreSQL’s local caching behavior.

A stale positive entry can return outdated metadata. A stale negative entry can be even more confusing because it can incorrectly report that a newly created object does not exist.

For this reason, YugabyteDB checks whether negative entries are safe for a particular catalog cache. The source code implements this decision in YbAllowNegativeCacheEntries().

The yb_neg_catcache_ids parameter adds selected cache IDs to the set of caches that may store negative entries

This makes yb_neg_catcache_ids a targeted optimization rather than a setting that should be enabled indiscriminately.

Syscache IDs Are Not Catalog OIDs

One of the easiest mistakes is to confuse a catalog table’s PostgreSQL OID with its syscache ID.

For example, pg_operator has its own catalog relation OID. However, that OID has no direct relationship to the values used by yb_neg_catcache_ids.

The cache IDs come from the following enum:

				
					src/postgres/src/include/utils/syscache.h

The enum begins with:

				
					enum SysCacheIdentifier
{
    AGGFNOID = 0,
    AMNAME,
    AMOID,
    ...
};

Because only the first entry is explicitly assigned a value, each subsequent entry is incremented automatically.

The syscache ID is therefore the entry’s zero-based position in the enum.

The YugabyteDB source also states that the order of these identifiers must match the cacheinfo[] array in syscache.c.

Starting on March 11, 2025, newly added cache IDs are appended rather than inserted alphabetically. This helps preserve existing integer values across releases and YSQL upgrades.

For pg_operator, the relevant entries are:

Syscache ID	Syscache Name	Catalog Table
37	`OPERNAMENSP`	`pg_operator`
38	`OPEROID`	`pg_operator`

These values are derived from their positions in SysCacheIdentifier, not from the OID of pg_operator.

Automate the Lookup

Manually counting enum entries is tedious and error-prone. It is also better to inspect the source for the exact YugabyteDB release you are running.

The following shell script:

● Resolves GitHub’s designated latest YugabyteDB release by default.
● Accepts a full release, build number, release prefix, tag, or branch.
● Reads syscache.h to determine each syscache name and ID.
● Reads syscache.c to map each ID to its catalog table.
● Filters the results by catalog table when requested.
● Supports aligned text or CSV output.
● Stops rather than returning potentially incorrect results when the two parsed arrays do not match.

Create the Script

Save the following as syscache_lookup.sh:

				
					#!/usr/bin/env bash
#
# syscache_lookup.sh
#
# Looks up YugabyteDB system catalog cache IDs (SysCacheIdentifier values)
# used by yb_neg_catcache_ids. The script parses these files directly from
# the YugabyteDB GitHub repository for a selected release, tag, or branch:
#
#   src/postgres/src/include/utils/syscache.h
#   src/postgres/src/backend/utils/cache/syscache.c
#
# Usage:
#   ./syscache_lookup.sh [-v REF] [-t TABLE] [-c] [-h]
#
# Options:
#   -v, --version REF   Release, release prefix, tag, or branch. Examples:
#                       2026.1.0.0, 2026.1.0.0-b118, 2025.2.4,
#                       v2024.2.10.0, master.
#                       Defaults to GitHub's designated latest release.
#   -t, --table TABLE   Show only caches for this catalog table. Examples:
#                       pg_operator or pg_catalog.pg_operator.
#                       Omit this option to show every cache.
#   -c, --csv           Output CSV instead of an aligned table.
#   -h, --help          Show this help.
#
# Optional:
#   GITHUB_TOKEN        GitHub token used to avoid the lower anonymous
#                       GitHub API rate limit.
#
# Requires: Bash 4+, curl, awk, sed, grep, sort -V

set -euo pipefail

REPO="yugabyte/yugabyte-db"
RAW_BASE="https://raw.githubusercontent.com/${REPO}"
API_BASE="https://api.github.com/repos/${REPO}"

VERSION_INPUT=""
TABLE_FILTER=""
CSV=0

usage() {
    cat <<'USAGE'
Usage:
  ./syscache_lookup.sh [-v REF] [-t TABLE] [-c] [-h]

Options:
  -v, --version REF   Release, release prefix, tag, or branch. Examples:
                      2026.1.0.0, 2026.1.0.0-b118, 2025.2.4,
                      v2024.2.10.0, master.
                      Defaults to GitHub's designated latest release.
  -t, --table TABLE   Show only caches for this catalog table. Examples:
                      pg_operator or pg_catalog.pg_operator.
                      Omit this option to show every cache.
  -c, --csv           Output CSV instead of an aligned table.
  -h, --help          Show this help.
USAGE
}

die() {
    echo "ERROR: $*" >&2
    exit 1
}

require_command() {
    command -v "$1" >/dev/null 2>&1 \
        || die "Required command not found: $1"
}

for command_name in curl awk sed grep sort; do
    require_command "$command_name"
done

while [[ $# -gt 0 ]]; do
    case "$1" in
        -v|--version)
            [[ $# -ge 2 ]] || die "$1 requires a value"
            VERSION_INPUT="$2"
            shift 2
            ;;
        -t|--table)
            [[ $# -ge 2 ]] || die "$1 requires a value"
            TABLE_FILTER="$2"
            shift 2
            ;;
        -c|--csv)
            CSV=1
            shift
            ;;
        -h|--help)
            usage
            exit 0
            ;;
        *)
            echo "Unknown argument: $1" >&2
            usage >&2
            exit 1
            ;;
    esac
done

api_get() {
    local url="$1"
    local -a headers=(
        -H "Accept: application/vnd.github+json"
        -H "X-GitHub-Api-Version: 2022-11-28"
        -H "User-Agent: syscache_lookup.sh"
    )

    if [[ -n "${GITHUB_TOKEN:-}" ]]; then
        headers+=(
            -H "Authorization: Bearer ${GITHUB_TOKEN}"
        )
    fi

    curl -fsSL \
        --retry 3 \
        --connect-timeout 10 \
        "${headers[@]}" \
        "$url"
}

raw_get() {
    local url="$1"

    curl -fsSL \
        --retry 3 \
        --connect-timeout 10 \
        -H "User-Agent: syscache_lookup.sh" \
        "$url"
}

extract_release_tags() {
    grep -oE \
        '"tag_name"[[:space:]]*:[[:space:]]*"[^"]+"' \
        | sed -E 's/.*"([^"]+)"$/\1/'
}

extract_ref_tags() {
    grep -oE \
        '"ref"[[:space:]]*:[[:space:]]*"refs\/tags\/[^"]+"' \
        | sed -E 's#.*"refs/tags/([^"]+)"$#\1#'
}

resolve_series_release() {
    local series="$1"
    local series_pattern
    local pattern
    local response
    local tag

    # Convert:
    #   2025.2   -> 2025[.]2
    #   2025.2.4 -> 2025[.]2[.]4
    #
    # Using [.] avoids awk and shell escaping issues.
    series_pattern=${series//./[.]}

    if [[ "$series" =~ ^[0-9]+\.[0-9]+$ ]]; then
        # Example:
        #   2025.2 -> v2025.2.<patch>.<hotfix>
        pattern="^v${series_pattern}[.][0-9]+[.][0-9]+$"
    else
        # Example:
        #   2025.2.4 -> v2025.2.4.<hotfix>
        pattern="^v${series_pattern}[.][0-9]+$"
    fi

    response=$(
        api_get \
            "${API_BASE}/git/matching-refs/tags/v${series}."
    ) || die \
        "GitHub API request failed while resolving '$series'"

    tag=$(
        printf '%s\n' "$response" \
            | extract_ref_tags \
            | grep -E "$pattern" \
            | sort -V \
            | tail -n 1 \
            || true
    )

    [[ -n "$tag" ]] || return 1

    printf '%s\n' "$tag"
}

resolve_ref() {
    local requested="$1"
    local response
    local tag
    local series

    if [[ -z "$requested" ]]; then
        response=$(
            api_get "${API_BASE}/releases/latest"
        ) || die \
            "GitHub API request failed while resolving the latest release"

        tag=$(
            printf '%s\n' "$response" \
                | extract_release_tags \
                | sed -n '1p' \
                || true
        )

        [[ -n "$tag" ]] \
            || die \
                "Could not resolve GitHub's latest YugabyteDB release"

        printf '%s\n' "$tag"
        return 0
    fi

    # Normalize a full YugabyteDB release or build number to its Git tag.
    #
    # Examples:
    #   2025.2.4.1      -> v2025.2.4.1
    #   v2025.2.4.1     -> v2025.2.4.1
    #   2026.1.0.0-b118 -> v2026.1.0.0
    if [[ "$requested" =~ ^v?([0-9]+\.[0-9]+\.[0-9]+\.[0-9]+)(-b[0-9]+)?$ ]]
    then
        printf 'v%s\n' "${BASH_REMATCH[1]}"
        return 0
    fi

    # Resolve a release prefix to the highest matching clean release tag.
    #
    # Examples:
    #   2025.2   -> highest v2025.2.x.y tag
    #   2025.2.4 -> highest v2025.2.4.x tag
    if [[ "$requested" =~ ^v?([0-9]+\.[0-9]+(\.[0-9]+)?)$ ]]
    then
        series="${BASH_REMATCH[1]}"

        tag=$(resolve_series_release "$series") \
            || die \
                "Could not find a release matching '$requested'"

        printf '%s\n' "$tag"
        return 0
    fi

    # Otherwise, use the value as a literal Git ref, such as master,
    # another branch, or a custom tag.
    printf '%s\n' "$requested"
}

REF=$(resolve_ref "$VERSION_INPUT")

echo "Using YugabyteDB source ref: $REF" >&2

HDR_URL="${RAW_BASE}/${REF}/src/postgres/src/include/utils/syscache.h"
SRC_URL="${RAW_BASE}/${REF}/src/postgres/src/backend/utils/cache/syscache.c"

HDR=$(raw_get "$HDR_URL") \
    || die \
        "Failed to fetch $HDR_URL (invalid or unavailable ref?)"

SRC=$(raw_get "$SRC_URL") \
    || die \
        "Failed to fetch $SRC_URL (invalid or unavailable ref?)"

# Parse enum SysCacheIdentifier from syscache.h.
#
# AGGFNOID starts at zero and the remaining entries auto-increment, so the
# zero-based array position is the syscache ID.
mapfile -t NAMES < <(
    printf '%s\n' "$HDR" \
        | awk '
            /enum[[:space:]]+SysCacheIdentifier/ {
                in_enum = 1
                sub(/^.*\{/, "")
            }

            in_enum &&
            /#define[[:space:]]+SysCacheSize/ {
                exit
            }

            in_enum {
                print
            }
        ' \
        | sed -E '
            s#/\*.*\*/# #g
            s,//.*$,,
            s/[{},]//g
            s/[[:space:]]*=[[:space:]]*[0-9]+[[:space:]]*$//
            s/^[[:space:]]+//
            s/[[:space:]]+$//
        ' \
        | grep -E '^[A-Z][A-Z0-9_]*$'
)

# Parse YugabyteDB's parallel cache-ID-to-catalog-table mapping
# from syscache.c.
mapfile -t TABLES < <(
    printf '%s\n' "$SRC" \
        | awk '
            /static[[:space:]]+YbCatalogCacheTable[[:space:]]+yb_catalog_cache_tables\[\]/ {
                in_map = 1
                next
            }

            in_map &&
            /^[[:space:]]*};/ {
                exit
            }

            in_map {
                print
            }
        ' \
        | sed -E '
            s#/\*.*\*/# #g
            s,//.*$,,
            s/^[[:space:]]+//
            s/[[:space:]]*,?[[:space:]]*$//
            s/^YbCatalogCacheTable_//
        ' \
        | grep -E '^pg_[a-z0-9_]+$'
)

[[ ${#NAMES[@]} -gt 0 ]] \
    || die \
        "Could not parse SysCacheIdentifier from $HDR_URL"

[[ ${#TABLES[@]} -gt 0 ]] \
    || die \
        "Could not parse yb_catalog_cache_tables[] from $SRC_URL"


if [[ ${#NAMES[@]} -ne ${#TABLES[@]} ]]; then
    die \
        "Parsed ${#NAMES[@]} cache names but ${#TABLES[@]} table mappings; refusing potentially misaligned output"
fi

if [[ -n "$TABLE_FILTER" ]]; then
    TABLE_FILTER=${TABLE_FILTER,,}
    TABLE_FILTER=${TABLE_FILTER#pg_catalog.}
fi

MATCHES=0

if [[ $CSV -eq 1 ]]; then
    echo "id,name,table"
else
    printf '%-4s %-30s %s\n' \
        "ID" \
        "NAME" \
        "TABLE"
fi

for i in "${!NAMES[@]}"; do
    name=${NAMES[$i]}
    table=${TABLES[$i]}

    if [[ -n "$TABLE_FILTER" &&
          "$table" != "$TABLE_FILTER" ]]
    then
        continue
    fi

    MATCHES=$((MATCHES + 1))

    if [[ $CSV -eq 1 ]]; then
        printf '%s,%s,%s\n' \
            "$i" \
            "$name" \
            "$table"
    else
        printf '%-4s %-30s %s\n' \
            "$i" \
            "$name" \
            "$table"
    fi
done

if [[ -n "$TABLE_FILTER" && $MATCHES -eq 0 ]]; then
    die \
        "No caches found for table '$TABLE_FILTER' in $REF"
fi

Make the script executable:

				
					chmod +x syscache_lookup.sh

Look Up All Syscache IDs

Run the script without any parameters to use GitHub’s designated latest YugabyteDB release and display every cache:

				
					./syscache_lookup.sh

Example output:

				
					Using YugabyteDB source ref: v2026.1.0.0
ID   NAME                           TABLE
0    AGGFNOID                       pg_aggregate
1    AMNAME                         pg_am
2    AMOID                          pg_am
3    AMOPOPID                       pg_amop
4    AMOPSTRATEGY                   pg_amop
...

Look Up a Specific Catalog Table

To display only the caches associated with pg_operator, run:

				
					./syscache_lookup.sh --table pg_operator

The schema-qualified name is also accepted:

				
					./syscache_lookup.sh --table pg_catalog.pg_operator

Example output:

				
					Using YugabyteDB source ref: v2026.1.0.0
ID   NAME                           TABLE
37   OPERNAMENSP                    pg_operator
38   OPEROID                        pg_operator

Look Up a Specific YugabyteDB Release

Specify a full release tag:

				
					./syscache_lookup.sh \
    --version v2025.2.4.1 \
    --table pg_operator

The leading v is optional:

				
					./syscache_lookup.sh \
    --version 2025.2.4.1 \
    --table pg_operator

A complete YugabyteDB build number is also accepted. The build suffix is removed when resolving the Git tag:

				
					./syscache_lookup.sh \
    --version 2026.1.0.0-b118 \
    --table pg_operator

You can also specify a release prefix. The script selects the most recently published matching release:

				
					./syscache_lookup.sh \
    --version 2025.2 \
    --table pg_operator

Or:

				
					./syscache_lookup.sh \
    --version 2024.2.8 \
    --table pg_enum

Inspect the Master Branch

To inspect the current development branch instead of a released build:

				
					./syscache_lookup.sh \
    --version master \
    --table pg_operator

⚠️ Version Matters: Use the release that matches your running YugabyteDB cluster. Although YugabyteDB now tries to preserve existing syscache integer values, newer releases may append additional caches. Do not assume that a table generated from master exactly represents an older installed release.

Generate CSV Output

Use --csv or -c to generate output that can be redirected into a file:

				
					./syscache_lookup.sh \
    --version 2025.2 \
    --csv \
    > syscache_ids.csv

Filter the CSV by catalog table:

				
					./syscache_lookup.sh \
    --version 2025.2 \
    --table pg_operator \
    --csv

Example:

				
					id,name,table
37,OPERNAMENSP,pg_operator
38,OPEROID,pg_operator

Configure `yb_neg_catcache_ids`

After identifying the required IDs, provide them as a comma-separated string.

For the two pg_operator caches:

				
					SET yb_neg_catcache_ids = '37,38';

This setting allows the selected caches to retain failed lookups as negative entries.

Repeated searches using the same cache keys may then return the stored “not found” result instead of performing another catalog lookup.

🚨 Use With Care: Enabling the wrong cache ID can create stale negative results if catalog changes affecting that cache do not reliably cause the required catalog invalidation. Test the change against the exact YugabyteDB version and DDL patterns used by your workload. This setting should be treated as a targeted performance optimization… not as a general-purpose list of every catalog cache.

Minimal Catalog Preloading Compatibility

The reported case involved negative caching for the pg_enum caches.

A backend preloaded only system catalog rows but treated the partially loaded cache as complete. As a result, lookups for user-created enum values could incorrectly return “not found.”

The fix was included in:

Release Series	First Fixed Release
YugabyteDB 2024.2	`2024.2.8.0`
YugabyteDB 2025.2	`2025.2.1.0`

Before combining yb_neg_catcache_ids with minimal catalog cache preloading, verify that the cluster includes this fix.

When Can Negative Caching Help?

Negative catalog caching is most useful when a workload repeatedly performs the same unsuccessful metadata lookup.

Possible examples include:

● An ORM repeatedly checking whether an optional type or relation exists.
● Dynamically generated SQL repeatedly resolving an operator combination that is not present.
● Application initialization code checking for optional database objects.
● Prepared statements repeatedly encountering the same missing catalog entry.
● Frameworks or migration tools repeatedly probing system catalogs.

The benefit depends on whether the workload is actually generating repeated misses against the same cache and search keys.

Enabling negative caching for a catalog that rarely misses is unlikely to provide a meaningful improvement.

How the Script Determines the IDs

The script combines two pieces of YugabyteDB source code.

First, it reads SysCacheIdentifier from:

				
					src/postgres/src/include/utils/syscache.h

This determines:

				
					ID → syscache name

For example:

				
					37 → OPERNAMENSP
38 → OPEROID

Second, it reads yb_catalog_cache_tables[] from:

				
					src/postgres/src/backend/utils/cache/syscache.c

That parallel array determines:

				
					syscache ID → catalog table

Combining the two arrays produces:

				
					37 → OPERNAMENSP → pg_operator
38 → OPEROID     → pg_operator

The script requires the number of cache names to exactly match the number of catalog-table mappings.

If they do not match, it exits rather than displaying potentially misaligned IDs.

Final Takeaway

The values used by yb_neg_catcache_ids are syscache enum ordinals, not PostgreSQL catalog table OIDs.

Instead of manually counting entries in SysCacheIdentifier, use the script to inspect the exact source for your YugabyteDB release:

				
					./syscache_lookup.sh \
    --version 2025.2 \
    --table pg_operator

Then apply only the IDs that have been evaluated for your workload and catalog invalidation behavior:

				
					SET yb_neg_catcache_ids = '37,38';

Negative caching can eliminate repeated catalog work for frequently repeated misses, but an incorrectly cached “not found” result can create metadata correctness problems.

Use the setting selectively, test it carefully, and always verify the behavior on the exact YugabyteDB release running in your environment.

Have Fun!