Monitoring YugabyteDB Catalog Table Sizes

YugabyteDB delivers the best of both worlds: horizontal scalability and global distribution, powered by a PostgreSQL-compatible query layer (YSQL). But even in this modern distributed world, the foundational metadata stored in PostgreSQL’s pg_catalog system tables is still at the heart of every query, DDL operation, and schema change.

Today’s tip explores why monitoring catalog table sizes still matters in YugabyteDB, how to do it easily with a reusable function, and which tables to watch for signals of stress in a metadata-heavy environment.

YugabyteDB leverages the PostgreSQL catalog system in its YSQL API, storing metadata for:

Tables, columns, indexes
Functions and constraints
Grants, schemas, types, and more

In certain scenarios, especially multi-tenant applications, dynamic schema generation, or heavily partitioned data models, these catalog tables can grow quite large.

Risks of Unchecked Catalog Growth

Slower DDL performance (e.g., creating/dropping tables or indexes)
Increased planning time due to metadata lookup overhead
Unexpected performance dips in complex schemas or automation-heavy setups
Schema complexity bottlenecks as catalog lookups scale

While YugabyteDB uses distributed storage to scale out, catalog table overhead still exists on the per-node level and can grow silently in metadata-heavy apps.

To proactively monitor catalog table sizes in YugabyteDB, here’s a lightweight, PostgreSQL-compatible function that scans all pg_ tables in pg_catalog and computes an estimated in-memory footprint per table:

				
					CREATE OR REPLACE FUNCTION get_pg_catalog_table_sizes()
RETURNS TABLE(table_name TEXT, total_size BIGINT)
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
    sql TEXT;
BEGIN
    FOR r IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'pg_catalog'
          AND tablename LIKE 'pg_%'
    LOOP
        sql := format(
            'SELECT COALESCE(SUM(pg_column_size(t.*)), 0) FROM pg_catalog.%I t',
            r.tablename
        );
        EXECUTE sql INTO total_size;
        table_name := r.tablename;
        RETURN NEXT;
    END LOOP;
END $$;

Example:

The following query will return a list of internal catalog tables with their approximate total row size, helping you identify unexpected growth before it turns into a performance issue.

				
					yugabyte=# SELECT * FROM get_pg_catalog_table_sizes() ORDER BY total_size DESC;
          table_name          | total_size
------------------------------+------------
 pg_rewrite                   |    2550794
 pg_proc                      |     591219
 pg_depend                    |     385382
 pg_attribute                 |     371862
 pg_statistic                 |     233683
 pg_collation                 |     197597
 pg_operator                  |     110600
 pg_type                      |      65441
 pg_class                     |      65132
 pg_amop                      |      60357
 pg_description               |      43776
 pg_amproc                    |      28493
 pg_opclass                   |      22689
 pg_index                     |      21102
 pg_opfamily                  |      16521
 pg_init_privs                |      14415
 pg_aggregate                 |      13557
 pg_ts_config_map             |      12157
 pg_cast                      |      10393
 pg_ts_dict                   |       2367
 pg_ts_config                 |       1725
 pg_constraint                |       1670
 pg_authid                    |       1437
 pg_database                  |       1370
 pg_pltemplate                |       1271
 pg_am                        |        805
 pg_shdepend                  |        634
 pg_ts_template               |        537
 pg_namespace                 |        535
 pg_language                  |        461
 pg_range                     |        285
 pg_shdescription             |        277
 pg_yb_catalog_version        |        237
 pg_extension                 |        222
 pg_tablespace                |        197
 pg_yb_logical_client_version |        197
 pg_ts_parser                 |        117
 pg_auth_members              |        108
 pg_yb_migration              |         93
 pg_subscription_rel          |          0
 pg_transform                 |          0
 pg_trigger                   |          0
 pg_user_mapping              |          0
 pg_yb_profile                |          0
 pg_yb_role_profile           |          0
 pg_default_acl               |          0
 pg_yb_tablegroup             |          0
 pg_enum                      |          0
 pg_event_trigger             |          0
 pg_foreign_data_wrapper      |          0
 pg_foreign_server            |          0
 pg_foreign_table             |          0
 pg_db_role_setting           |          0
 pg_inherits                  |          0
 pg_largeobject               |          0
 pg_largeobject_metadata      |          0
 pg_conversion                |          0
 pg_partitioned_table         |          0
 pg_policy                    |          0
 pg_publication               |          0
 pg_publication_rel           |          0
 pg_replication_origin        |          0
 pg_seclabel                  |          0
 pg_sequence                  |          0
 pg_shseclabel                |          0
 pg_attrdef                   |          0
 pg_statistic_ext             |          0
 pg_subscription              |          0
(68 rows)

Key Catalog Tables to Watch

Not all catalog tables grow equally. Focus on these high-signal tables:

Table	Purpose	When to worry
`pg_class`	Tables and indexes	Rapid table creation, partitioning
`pg_attribute`	Columns per table	Wide tables, evolving schemas
`pg_proc`	User-defined functions	Heavy PL/pgSQL usage
`pg_constraint`	PK, FK, checks	Constraint-heavy workloads
`pg_namespace`	Schemas	Multi-tenant designs
`pg_statistic`	Planner stats	Workloads with frequent `ANALYZE`

These tables act as bellwethers, growing faster in systems under schema stress or automation-driven DDL operations.

Have Fun!