Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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:
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:
Sometimes, one or the other is not the correct call to begin with. Often, a dedicated system information function or a cast to an object identifier type can simplify or solve the task. Examples:
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.
