YSQL relies heavily on PostgreSQL-compatible system catalogs to store metadata about tables, columns, indexes, constraints, functions, statistics, schemas, users, and much more.
All of these catalog tables live under the pg_catalog schema. YugabyteDB builds on PostgreSQL’s catalog and also adds YugabyteDB-specific catalog objects.
As your database evolves, the catalogs evolve with it.
So occasionally it can be useful to answer two simple questions:
- ● Which YSQL catalog tables contain the most data?
- ● Which catalog indexes represent the most indexed data?
Let’s build a couple of reusable functions to find out. 🔎
First, What Are We Measuring?
PostgreSQL provides the handy pg_column_size() function, which reports the number of bytes used to represent an individual data value.
We can also pass an entire row to it:
SELECT pg_column_size(t)
FROM pg_catalog.pg_class AS t;
Summing that value across the rows of a catalog table gives us a useful measure of its logical data size.
That distinction — logical — is important.
Show the Logical Size of Every Catalog Table
We could manually list every catalog table and build a giant UNION ALL.
But there is a much easier way.
YugabyteDB exposes the list of catalog tables through:
SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'pg_catalog';
That’s also the method documented by YugabyteDB for enumerating its system catalog tables.
So let’s make the function discover them automatically.
get_yb_catalog_table_sizes()
CREATE OR REPLACE FUNCTION get_yb_catalog_table_sizes()
RETURNS TABLE (
table_name text,
row_count bigint,
logical_bytes bigint,
logical_size text
)
LANGUAGE plpgsql
AS $$
DECLARE
r record;
BEGIN
FOR r IN
SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'pg_catalog'
LOOP
RETURN QUERY EXECUTE format(
'SELECT %L::text,
COUNT(*)::bigint,
COALESCE(SUM(pg_column_size(t)), 0)::bigint,
pg_size_pretty(
COALESCE(SUM(pg_column_size(t)), 0)::bigint
)
FROM pg_catalog.%I AS t',
r.tablename,
r.tablename
);
END LOOP;
END;
$$;
A couple of nice things about this version:
- ● No catalog table names are hard-coded.
- ● YugabyteDB-specific
pg_yb_*tables are picked up automatically. - ● New catalog tables added by future releases are automatically included.
- ● You get both the raw byte count and a human-readable size.
- ● You also get the number of rows in each catalog.
Run it:
SELECT *
FROM get_yb_catalog_table_sizes()
ORDER BY logical_bytes DESC;
Or, if you only want a quick Top 10:
SELECT
table_name,
row_count,
logical_size
FROM get_yb_catalog_table_sizes()
ORDER BY logical_bytes DESC
LIMIT 10;
In one test database, the largest catalog tables looked like this:
.table_name | logical_size
-----------------+-------------
pg_rewrite | 3025 kB
pg_proc | 803 kB
pg_attribute | 521 kB
pg_statistic | 274 kB
pg_description | 263 kB
pg_type | 126 kB
pg_depend | 112 kB
pg_operator | 106 kB
pg_collation | 102 kB
pg_class | 77 kB
Those values will naturally vary based on the YugabyteDB version and the objects defined in the database. The test that led to this Tip showed the same general ranking, with pg_rewrite, pg_proc, and pg_attribute at the top.
Now Let’s Look at Catalog Indexes
Tables are only half the story.
The catalog tables themselves have indexes such as:
pg_class_oid_index
pg_attribute_relid_attnam_index
pg_proc_proname_args_nsp_index
pg_type_typname_nsp_index
PostgreSQL/YSQL describes those indexes using the pg_index catalog.
Among other things, pg_index.indkey identifies the table columns represented by an index. It contains both index key columns and INCLUDE columns; indnkeyatts tells us how many are actual key columns. A zero in indkey identifies an expression rather than a simple column reference.
That gives us enough information to construct another function.
Show the Logical Size of Catalog Index Payloads
Because you can’t simply:
SELECT *
FROM pg_class_oid_index;
we’ll use pg_index and pg_attribute to determine which underlying table columns are represented by each index.
Here is the refined function:
get_yb_catalog_index_sizes()
CREATE OR REPLACE FUNCTION get_yb_catalog_index_sizes()
RETURNS TABLE (
index_name text,
base_table text,
key_columns text,
include_columns text,
is_unique boolean,
logical_bytes bigint,
logical_size text
)
LANGUAGE plpgsql
AS $$
DECLARE
r record;
BEGIN
FOR r IN
SELECT
n.nspname AS schema_name,
tbl.relname AS table_name,
idx.relname AS index_name,
i.indisunique AS is_unique,
string_agg(
format('%I', a.attname),
', ' ORDER BY k.ord
) FILTER (
WHERE k.ord <= i.indnkeyatts
) AS key_columns,
string_agg(
format('%I', a.attname),
', ' ORDER BY k.ord
) FILTER (
WHERE k.ord > i.indnkeyatts
) AS include_columns,
string_agg(
format(
'COALESCE(pg_column_size(%I), 0)',
a.attname
),
' + ' ORDER BY k.ord
) AS size_expr
FROM pg_catalog.pg_index AS i
JOIN pg_catalog.pg_class AS tbl
ON tbl.oid = i.indrelid
JOIN pg_catalog.pg_class AS idx
ON idx.oid = i.indexrelid
JOIN pg_catalog.pg_namespace AS n
ON n.oid = tbl.relnamespace
CROSS JOIN LATERAL
unnest(
string_to_array(i.indkey::text, ' ')::int2[]
) WITH ORDINALITY AS k(attnum, ord)
JOIN pg_catalog.pg_attribute AS a
ON a.attrelid = tbl.oid
AND a.attnum = k.attnum
WHERE n.nspname = 'pg_catalog'
AND i.indisvalid
AND i.indislive
AND i.indexprs IS NULL
AND i.indpred IS NULL
AND k.attnum > 0
GROUP BY
n.nspname,
tbl.relname,
idx.relname,
i.indisunique,
i.indnkeyatts
LOOP
RETURN QUERY EXECUTE format(
'SELECT %L::text,
%L::text,
%L::text,
%L::text,
%L::boolean,
bytes,
pg_size_pretty(bytes)
FROM (
SELECT COALESCE(SUM(%s), 0)::bigint AS bytes
FROM %I.%I
) AS s',
r.index_name,
r.table_name,
r.key_columns,
COALESCE(r.include_columns, ''),
r.is_unique,
r.size_expr,
r.schema_name,
r.table_name
);
END LOOP;
END;
$$;
Run it:
SELECT *
FROM get_yb_catalog_index_sizes()
ORDER BY logical_bytes DESC;
Or just grab the biggest indexes:
SELECT
index_name,
base_table,
key_columns,
include_columns,
logical_size
FROM get_yb_catalog_index_sizes()
ORDER BY logical_bytes DESC
LIMIT 10;
From the database used during testing:
. index_name | base_table | logical_size
-------------------------------------------+----------------+-------------
pg_proc_proname_args_nsp_index | pg_proc | 323 kB
pg_attribute_relid_attnam_index | pg_attribute | 246 kB
pg_operator_oprname_l_r_n_index | pg_operator | 59 kB
pg_collation_name_enc_nsp_index | pg_collation | 56 kB
pg_description_o_c_o_index | pg_description | 52 kB
pg_type_typname_nsp_index | pg_type | 48 kB
pg_depend_reference_index | pg_depend | 27 kB
pg_depend_depender_index | pg_depend | 27 kB
pg_class_relname_nsp_index | pg_class | 26 kB
pg_attribute_relid_attnum_index | pg_attribute | 22 kB
In one test database, pg_proc_proname_args_nsp_index and pg_attribute_relid_attnam_index had the largest logical index payloads.
pg_index.indkey contains both key columns and non-key INCLUDE columns. Since included columns are stored with the index entry, this function includes both when calculating the logical payload. key_columns and include_columns so you can see exactly what is being counted. PostgreSQL/YSQL documents INCLUDE columns as values stored in the index but not used as search keys or for uniqueness semantics.
Verify One Index Manually
Let’s use pg_class_oid_index as a simple example.
Its indexed value is the oid column from pg_class.
We can calculate that logical payload directly:
SELECT
pg_size_pretty(
SUM(pg_column_size(oid))
) AS logical_size
FROM pg_catalog.pg_class;
Result from the test:
.logical_size
--------------
1564 bytes
Now look for the same index in our function:
SELECT
index_name,
base_table,
key_columns,
logical_size
FROM get_yb_catalog_index_sizes()
WHERE index_name = 'pg_class_oid_index';
The function returned the following result for pg_class_oid_index:
pg_class_oid_index | pg_class | 1564 bytes
That matches the manual pg_column_size(oid) calculation above, which gives us a nice sanity check that the function is measuring the expected indexed column values.
Why Skip Expression and Partial Indexes?
You may have noticed these conditions:
AND i.indexprs IS NULL
AND i.indpred IS NULL
They’re intentional.
An expression index could contain something like:
LOWER(column_name)
rather than the raw value of a single table column.
PostgreSQL/YSQL represents an expression column as a zero entry in pg_index.indkey. indexprs contains the corresponding expression tree. Likewise, indpred contains the predicate for a partial index.
For a partial index, only some rows of the base table are represented in the index.
Simply summing the underlying columns for every table row would therefore give us the wrong answer.
One More Important YugabyteDB Detail
Secondary indexes in YugabyteDB are themselves distributed objects. YugabyteDB implements them similarly to tables: they are sharded into tablets and distributed across nodes.
Primary-key indexes are different.
YugabyteDB documents that primary-key indexes are implemented in the main table itself rather than as a separate index structure.
So even though PostgreSQL’s catalogs describe indexes uniformly, you should not interpret the logical value returned by this Tip as:
- Bytes physically consumed by a separate DocDB index
Think of it instead as:
- Logical PostgreSQL payload represented by the index definition
That’s exactly what we want for comparing objects and monitoring catalog growth.
What About pg_relation_size() and pg_table_size()?
This isn’t the main point of this Tip, but it is worth mentioning.
PostgreSQL/YSQL also provides functions such as:
pg_relation_size()
pg_table_size()
pg_indexes_size()
pg_total_relation_size()
Those functions are intended to report physical relation storage.
During the testing that led to this Tip, some YugabyteDB system catalog relations did not provide useful physical size estimates. For example:
SELECT pg_relation_size(
'pg_catalog.pg_class_oid_index'::regclass
);
returned:
.pg_relation_size
------------------
0
Similarly, attempts to retrieve sizes for the catalog tables generated notices stating that tablet disk-size estimates were unavailable.
That’s another reason pg_column_size() is useful here: we’re intentionally calculating a logical catalog baseline rather than depending on a physical relation estimate.
A Few Handy Queries
Top 10 Catalog Tables
SELECT
table_name,
row_count,
logical_size
FROM get_yb_catalog_table_sizes()
ORDER BY logical_bytes DESC
LIMIT 10;
Top 10 Catalog Indexes
SELECT
index_name,
base_table,
key_columns,
logical_size
FROM get_yb_catalog_index_sizes()
ORDER BY logical_bytes DESC
LIMIT 10;
Find All Indexes for a Specific Catalog
SELECT *
FROM get_yb_catalog_index_sizes()
WHERE base_table = 'pg_class'
ORDER BY logical_bytes DESC;
Find Unique Catalog Indexes
SELECT *
FROM get_yb_catalog_index_sizes()
WHERE is_unique
ORDER BY logical_bytes DESC;
Look for Empty Catalog Tables
SELECT *
FROM get_yb_catalog_table_sizes()
WHERE row_count = 0
ORDER BY table_name;
When Is This Useful?
Remember: These Queries Perform Real Work
The functions actually scan the catalog rows and evaluate:
pg_column_size(...)
for those rows.
The catalogs are normally small enough that this is perfectly reasonable for troubleshooting, but this is still actual SQL work.
Clean Up
If you created the functions only for troubleshooting:
DROP FUNCTION get_yb_catalog_table_sizes();
DROP FUNCTION get_yb_catalog_index_sizes();
Final Takeaway
YSQL’s PostgreSQL-compatible system catalogs make it possible to inspect a tremendous amount of database metadata.
With pg_catalog.pg_tables, pg_index, pg_attribute, pg_column_size(), and a little dynamic SQL, we can also answer two useful questions:
- ● Which catalog tables contain the most logical data?
- ● Which catalog indexes represent the most logical indexed data?
Run:
SELECT *
FROM get_yb_catalog_table_sizes()
ORDER BY logical_bytes DESC;
and:
SELECT *
FROM get_yb_catalog_index_sizes()
ORDER BY logical_bytes DESC;
No hard-coded list of catalog tables.
No giant UNION ALL.
And now both the tables and their indexes are part of the picture. 🔎
Have Fun!
My friend and I have an annual tradition: a trip to the local pop-up Halloween store. 🎃
This year they moved into a much bigger space, which apparently means much bigger ways to test my self-control.
Case in point: this life-size Xenomorph from Alien. 👽
I would have loved to bring him home… but I’m pretty sure my wife would have shot me out of the airlock instead of the alien. 😂