What is faster: pg_catalog or information_schema?

I understand the high-level differences between pg_catalog and information_schema.

But in many cases, a tool could choose to use any of them. And I'm wondering if there are performance reasons why we should prefer one or the other. Are queries expected to be faster on one of them? Do they work in the same way in terms of locks?

Typically, pg_catalog is faster.

The true source of information in Postgres are the catalog tables in pg_catalog. Views in the information_schema are based on those. Sometimes those views are rather convoluted to comply with the SQL standard.

For every query targeting an information schema view, there is a faster alternative based on pg_catalog tables directly, cutting out the middleman.

See:

• Get column names and data types of a query, table or view

For simple queries it won't matter much. But if you repeat the query with high frequency, or for more complex cases it can be substantial. More often than not, the information schema views do a lot of work you didn't ask for. Compare these two queries:

EXPLAIN ANALYZE
SELECT * FROM pg_catalog.pg_attribute;

EXPLAIN ANALYZE
SELECT * FROM information_schema.columns;

db<>fiddle here

1 ms vs 200ms (depends on the number of columns in the DB, of course). That's the price of complying to a standard - which is also the main reason to use the information schema at all: queries are stable across major Postgres versions (but core columns of catalog tables hardly ever change, either) and (in theory) portable to other RDBMS. (But writing "portable" code is tricky business and I would not try unless I absolutely must.)

Related:

• Case insensitive column names in a postgresql trigger function

Sometimes, neither is the best option to begin with. There are many dedicated system information functions, or a cast to an object identifier type can simplify or solve the task. Examples:

• How to check if a table exists in a given schema

• Retrieving all PK and FK

As for locks: you typically don't have to worry about that in Postgres. Thanks to the MVCC model, readers don't block writers and vice versa. If at all, the information_schema is more of a problem as it typically pulls in more catalog tables than necessary.

StackExchange Database Administrators Q#302587, answer score: 6