PostgreSQL consuming large amount of memory for persistent connection

Question

I have a C++ application which is making use of PostgreSQL 8.3 on Windows. We use the libpq interface.

We have a multi-threaded app where each thread opens a connection and keeps using without PQFinish it.

We notice that for each query (especially the SELECT statements) postgres.exe memory consumption would go up. It goes up as high as 1.3 GB. Eventually, postgres.exe crashes and forces our program to create a new connection.

Has anyone experienced this problem before?

EDIT: shared_buffer is currently set to be 128MB in our conf. file.

EDIT2: a workaround that we have in place right now is to call PQfinish for every transaction. But then, this slows down our processing a bit since establishing a connection every time is quite slow.

Answer 1

The memory usage is not necessarily a problem. PostgreSQL uses shared memory for some caching, and this memory does not count towards the size of the process memory usage until it's actually used. The more you use the process, the larger parts of the shared buffers will be active in it's address space.

If you have a large value for *shared_buffers*, this will happen. If you have it too large, the process can run out of address space and crash, yes.