Lior,
* Ben Zeev, Lior (lior.ben-zeev@hp.com) wrote:
> Does each PostgreSQL process allocating in its own memory (Not shared memory) a cache of all the database catalog
whichit access during the SQL execution?
PG will look up and cache the catalog information regarding all of the
relations involved, yes. In *accessing* those relations, PG will pull
needed blocks into shared buffers. PG will use backend-local memory to
process through the data (generally on a per-tuple basis).
> I mean does each process holds all the catalog indexes data which it accessed, all the catalog index statistics etc'
accessed
Catalog information (eg: information in pg_class) is kept, but the
*data* will only be pulled through shared buffers and then processed.
Anything in shared buffers (eg: the data in the tables or indexes) will
be cleaned up as new blocks are needed which push out old ones.
> If yes is there a way to avoid this behavior?
Catalog information is only cached- if the information isn't being used
then it should get purged out in favor of new data which is needed. Can
you explain a bit more exactly what the issue is..?
> (I asked Josh Berkus from PGExperts and he said that each process holds memory for sorts, hashes, temp tables,
vaccum,etc')
Correct, most backend local usage of memory is for running queries and
doing what is required in those queries. Regarding temp tables, you can
control how much memory is used for those with the temp_buffers
parameter.
Thanks,
Stephen