Idle postgres processes taking up lots of memory

What kind of information are these processes holding on to? I would expect long-running, idle postgres processes to have similar memory usage to brand new, idle ones.

There are actually quite a few things that Postgres will cache in local memory once it has loaded them:

• relcache (relation descriptors)
• catcache (system catalog entries)
• compiled trees for plpgsql functions

For most use cases, all of these add up to a negligible amount. The key here was heavy usage of schemas and the effect on the relcache. This database contains ~500 schemas, each with the same ~90 tables. To Postgres, even though the schemas are all the same, this works out to 45,000 tables (500*90).

Each request cached some of the tables' relation descriptors in memory (most often in a different schema than the request before it), gradually filling up the relcache. Unfortunately, Postgres does not offer a way to limit the size of these caches, as the overhead would probably be counterproductive for most use cases.

Possible solutions:

• Reconnect after a certain number of requests
• Add more memory
• Connection pooling to put a ceiling on the number of postgres connections using pgpool-II or PgBouncer

Thanks to Tom Lane and Merlin Moncure for help with this over the Postgres mailing lists.