Question: Connections and sessions
Do idle Postgres connections use memory?
Answered in the first paragraph. Last updated .
Yes, and the amount is neither small nor fixed. Each session is a separate process with private memory, and the caches it fills while working are kept for the life of the session rather than returned when the query ends. A connection that ran one enormous statement this morning can still be holding what that statement needed tonight, while showing as idle and doing nothing at all.
What the private memory is actually made of
Most of it is catalog caching. A backend remembers the definitions of the tables, indexes, functions and types it has touched, and that cache only grows. A schema with thousands of objects, or a partitioned table whose partitions are visited over time, produces backends that are each individually reasonable and collectively enormous.
Sorting and hashing memory behaves differently: it is released when the query finishes, but released to the process, not necessarily back to the operating system. So resident size tends to ratchet up to the high-water mark of the worst query that session ever ran. That is the connection between the working memory setting and a memory graph that never comes down.
Temporary tables and the cached plans of prepared statements are the other two contributors, and both are per session by definition.
From PostgreSQL 14 there is a view for this rather than guesswork, along with a way to ask another session to dump its own breakdown to the log, which is how to read a backend’s memory from SQL. The shape of it changed again in 18, so check which you are on before copying a query from anywhere.
What to do about it
Recycle connections on a schedule. Almost every pool can cap the maximum lifetime or the number of statements a connection serves before it is closed and replaced, and that is the only reliable way to hand the memory back. Setting it to something like an hour costs one fork per connection per hour and removes the ratchet entirely.
Keep the number of long-lived backends small and deliberate, which is the other half of what a pool is for. Hundreds of idle sessions are not free even though each one looks harmless, and the total is what the machine sees. How many connections Postgres can handle covers choosing that number, and connection pooling and PgBouncer covers getting there without changing the application.