[BUGS] BUG #14726: Memory consumption of PreparedStatement

The following bug has been logged on the website:

Bug reference:      14726
Logged by:          Daniel Migowski
Email address:      dmigowski@ikoffice.de
PostgreSQL version: 9.5.7
Operating system:   Debian Linux 8.6
Description:

Hello,

This is more a feature request than a bug, but I beliebe it should be placed
on the dev list anyway. I was researching cases for OOMs on our servers
yesterday, and noticed that the server side prepared statements can be real
nasty.

Due to a misconfiguration we had all our statements name-prepared on the
server side, so the query plans became stored. We maxed the number to 256
per connection, and didn't think about it anymore.

Now, there was this loop we created where we called something like

"select * from vw_largebeast where id=n"

without using prepared statement parameters, but the driver created server
side prepared statements anyway. 

The loop contained about 1000 entries, but after about 200 entries the
memory of the server was complety exhausted (small 8GB VM with 6GB free at
time of start).  

I concluded that the query plan of the statement, which I uploaded to despez
would result in 30MB of memory footprint!

https://explain.depesz.com/s/gN2

Besides that fact that I should have developed that better, I have some
questions now:

* How can I determine the memory footprint of prepared statements?
* Wouldn't it be useful if we could give a memory limit for prepared
statements on the server, so that PostgreSQL automatically evicts them if
more are prepared, maybe by using an LRU list? 

PostgreSQL could automatically replan them when they get used again, I
think. Currently we have no way to determine how much memory really is used
in PreparedStatements. Althought the JDBC driver we use does its own memory
based limitation, it is only based on query length, and a "select * from
vw_FatMamma" doesn't honor the real complexity. 



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs