0

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?

1
  • 1
    If you look closely at the information_schema you'll see that most of the objects in there appear to be views against the pg_catalog tables so I would expect similar performance with queries directly against the pg_catalog to win out when there are any performance differences.
    – gsiems
    Nov 13, 2021 at 22:10

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.