Exploring Catalog Tables vs. Extension Views in YugabyteDB

Jim Knicely

In PostgreSQL-compatible databases like YugabyteDB, the system catalogs are packed with metadata about tables, functions, privileges, and more. But not all system objects are created equal, some are core catalog tables, while others are views installed by extensions like pg_stat_statements.

Understanding this distinction can help you better inspect, monitor, and manage your distributed database.

In this tip, we’ll explore how to programmatically identify:

  • • Built-in catalog tables (like pg_class, pg_type, pg_trigger)

  • • Views added by extensions (like pg_stat_statements)

  • • System views from information_schema

  • • User-defined tables

And more importantly, we’ll discuss how these behave in a distributed YugabyteDB cluster, because not all objects behave the same way across nodes.

System Catalog Overview in YugabyteDB

YugabyteDB uses a PostgreSQL-compatible YSQL layer, which includes many of the same catalog tables and system views you’d find in PostgreSQL, plus some Yugabyte-specific enhancements under the hood.

Key schemas:

  • • pg_catalog: Core system catalogs and functions

  • • information_schema: Standard SQL views on metadata

  • • public (and others): Where user-created tables typically live

Global vs. Local Behavior in a Distributed Cluster

This is where things get interesting.

Catalog tables (e.g., pg_class, pg_type, pg_trigger)

  • • These are global across the cluster.

  • • No matter which node you query, these system catalogs will return consistent and identical metadata.

  • • Examples: definitions of tables, columns, types, indexes, triggers, etc.

Extension views (e.g., pg_stat_statements)

This distinction matters when monitoring performance, diagnosing query behavior, or analyzing activity across your distributed YugabyteDB environment.

One Query to Rule Them All

Use this query to list all user tables, catalog tables, information schema views, and extension views, and see which extension (if any) owns them:

				
					SELECT
  c.relname AS object_name,
  n.nspname AS schema_name,
  CASE
    WHEN c.relkind = 'r' AND n.nspname = 'pg_catalog' THEN 'catalog_table'
    WHEN c.relkind = 'v' AND n.nspname = 'pg_catalog' AND e.extname IS NULL THEN 'system_view'
    WHEN c.relkind = 'v' AND e.extname IS NOT NULL THEN 'extension_view'
    WHEN c.relkind = 'v' AND n.nspname = 'information_schema' THEN 'information_schema_view'
    WHEN c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema') THEN 'user_table'
    WHEN c.relkind = 'v' AND n.nspname NOT IN ('pg_catalog', 'information_schema') THEN 'user_view'
    ELSE 'uncategorized'
  END AS object_type,
  e.extname AS extension_name
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_depend d ON d.objid = c.oid AND d.deptype = 'e'
LEFT JOIN pg_extension e ON e.oid = d.refobjid
WHERE c.relkind IN ('r', 'v')  -- tables and views only
ORDER BY object_type, schema_name, object_name;

Example: Top 5 from each object_type:

				
					yugabyte=# WITH objects AS (
yugabyte(#   SELECT
yugabyte(#     c.relname AS object_name,
yugabyte(#     n.nspname AS schema_name,
yugabyte(#     CASE
yugabyte(#       WHEN c.relkind = 'r' AND n.nspname = 'pg_catalog' THEN 'catalog_table'
yugabyte(#       WHEN c.relkind = 'v' AND n.nspname = 'pg_catalog' AND e.extname IS NULL THEN 'system_view'
yugabyte(#       WHEN c.relkind = 'v' AND e.extname IS NOT NULL THEN 'extension_view'
yugabyte(#       WHEN c.relkind = 'v' AND n.nspname = 'information_schema' THEN 'information_schema_view'
yugabyte(#       WHEN c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema') THEN 'user_table'
yugabyte(#       WHEN c.relkind = 'v' AND n.nspname NOT IN ('pg_catalog', 'information_schema') THEN 'user_view'
yugabyte(#       ELSE 'uncategorized'
yugabyte(#     END AS object_type,
yugabyte(#     e.extname AS extension_name
yugabyte(#   FROM pg_class c
yugabyte(#   JOIN pg_namespace n ON c.relnamespace = n.oid
yugabyte(#   LEFT JOIN pg_depend d ON d.objid = c.oid AND d.deptype = 'e'
yugabyte(#   LEFT JOIN pg_extension e ON e.oid = d.refobjid
yugabyte(#   WHERE c.relkind IN ('r', 'v')
yugabyte(# ),
yugabyte-# ranked AS (
yugabyte(#   SELECT *,
yugabyte(#          ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY object_name) AS rn
yugabyte(#   FROM objects
yugabyte(# )
yugabyte-# SELECT object_name, schema_name, object_type, extension_name
yugabyte-# FROM ranked
yugabyte-# WHERE rn <= 5
yugabyte-# ORDER BY object_type, object_name;
           object_name           |    schema_name     |       object_type       |   extension_name
---------------------------------+--------------------+-------------------------+--------------------
 pg_aggregate                    | pg_catalog         | catalog_table           |
 pg_am                           | pg_catalog         | catalog_table           |
 pg_amop                         | pg_catalog         | catalog_table           |
 pg_amproc                       | pg_catalog         | catalog_table           |
 pg_attrdef                      | pg_catalog         | catalog_table           |
 pg_stat_statements              | pg_catalog         | extension_view          | pg_stat_statements
 pg_stat_statements_info         | pg_catalog         | extension_view          | pg_stat_statements
 _pg_foreign_data_wrappers       | information_schema | information_schema_view |
 _pg_foreign_servers             | information_schema | information_schema_view |
 _pg_foreign_table_columns       | information_schema | information_schema_view |
 _pg_foreign_tables              | information_schema | information_schema_view |
 _pg_user_mappings               | information_schema | information_schema_view |
 pg_available_extension_versions | pg_catalog         | system_view             |
 pg_available_extensions         | pg_catalog         | system_view             |
 pg_backend_memory_contexts      | pg_catalog         | system_view             |
 pg_config                       | pg_catalog         | system_view             |
 pg_cursors                      | pg_catalog         | system_view             |
 sql_features                    | information_schema | uncategorized           |
 sql_implementation_info         | information_schema | uncategorized           |
 sql_parts                       | information_schema | uncategorized           |
 sql_sizing                      | information_schema | uncategorized           |
(21 rows)
Wrapping Up

Understanding the difference between catalog tables, extension views, and their behavior in a distributed YugabyteDB cluster is key to accurate schema introspection, performance monitoring, and debugging.

📘 Catalog tables give you consistent metadata across all nodes.
📊 Extension views like pg_stat_statements are local and must be queried per node for full visibility.

Whether you’re writing tools, building backup scripts, or optimizing queries, knowing which is which will help you do it right.

Have Fun!

My wife and I stopped by the local library to pick up a book, and while waiting, I spotted this beautiful Howard Miller grandfather clock. It looked just like the one in my grandmother’s living room when I was a kid. The moment it struck 5 PM, I was instantly transported back... minus the plastic-covered furniture and the smell of freshly baked cookies!