2

I am trying to figure out if Postgres' relation and catalog cache are per connection or per server? I did find that plan caches are per connection. It would make sense for the cache to exist for as long as the server is running, but I can't find any documentation on it.

Improve this question

2 Answers 2

Reset to default
1

Is catalog and relation cache kept in shared memory or in session memory?

Both.

All relations in PostgreSQL are cached in shared memory up to the limit configured by shared_buffers parameter. This includes also system catalog (pg_class, pg_attribute, etc).

When a session is running, it builds its own internal cache in private memory for many things - including query plans, name-oid mappings etc.

On query level you can review caching effect with EXPLAIN command - see this answer.

On global level you can inspect contents of shared buffers using a standard extension named pg_buffercache.

Also, if we speak about caching you can't ignore the O/S filesystem caching machinery. See this excellent presentation.

Improve this answer
2

Those caches are per connection. They are not held in shared memory.

Improve this answer
5
  • It is not possible to share it among connections? Is there any config param?
    – Hadi
    Mar 24, 2019 at 19:15
  • No, no server config to allow that. You can use a connection pooler, which will allow virtual connections to share a real connection, so can get sharing that way.
    – jjanes
    Mar 24, 2019 at 20:21
  • @jjanes But if pg_class lives in shared buffers then we can say it's cached, right?
    – filiprem
    Mar 26, 2019 at 9:34
  • But pg_class is not the catalog cache. It is part of the catalog itself. I guess you could say that whatever pages of it happen to be shared_buffers are cached, but they are just copies of disk pages, not in their parsed form. I was taking "catalog cache" to refer to the specific thing from the source, e.g. src/include/utils/catcache.h
    – jjanes
    Mar 26, 2019 at 12:42
  • @jjanes yup the question was not very clear.... but I assumed the generic meaning of "cache" term, which can mean both shmem AND private set. The code manifested in catcache.h also (indirectly) uses shmem...
    – filiprem
    Mar 29, 2019 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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