This statement is executed as a prepared statement, but column "current" is not treated as a bound variable.
This is ok when current is a boolean, a most two prepared statements will be used. But when it is a timestamp, a different prepared statement will be used (and saved into postgresql) for each request.

More generally, only a few columns can be bound variables. This leads to lots of useless prepared statement into database, especially, if you have conditions on timestamp or subquery.

Futhermore, the limit size of StatementPool is 1000. With postgresql this is very high due to the fact that prepared statements are not shared between connections : the prepared statement cache is per connection / backend. This leads quite fast to a very high memory usage on the postgresql side.

How did we find this :
Our application used to work well with Rails 3.1 and 3.2. After upgrading to Rails 4, we experienced a memory leak on the db side : after about 1 hour of running, where we could observe the db memory grow and grow, the database ended up by returning OUT OF MEMORY errors. We finally figured out it was the number of prepared statements that exploded and was bloating the backend prepared statements cache. It seems to come from the fact that Rails 4 makes a wider use of prepared statements. While this is a great idea (thanks @tenderlove to bring that is Rails), it can very be problematic if there is no way to bind variables all variables properly.

If you have timestamp into some queries and if by no chance those queries are big, memory consumption of postgresql server increases drastically as well. In our case, we have 30 postgresql connections, with Rails 4, due to prepared statements, the memory used by the database server moves from 500 KB to more than 3 GB in about 1 hour, leading to out of memory errors.
Here is a stackoverflow post where we explain the symptoms http://bit.ly/1hcG1ba

I noticed we don't have any docs on this config for statement_limit. I'm working on a docrails commit to add it to the guides http://guides.rubyonrails.org/configuring.html. Is there a cannonical location for config docs inside of AR? Maybe we could lower the default limit, i can try to pull in some advice from our postgres team. Even if all of the prepared statements weren't one off-s it seems 1000 legitimate prepared statements would also bring down their postgres.

This comment has been minimized.

Apologies if this is the wrong place to ask this, but I'm curious about this as we were just bitten by this issue. It seems like the use cases of where(user_id: user.id).where("created_at > ?", Time.now) for example would be quite common, and for that to unexpectedly start eating up memory on the DB server to the point of bringing it down seems like something that perhaps shouldn't be "on by default"?

This comment has been minimized.

I recently ran into this issue as well. Like @lsimoneau's example, ActiveRecord is not binding date parameters into a general prepared statement and is instead creating a new prepared statement with the date present as sql. An example from our app (Rails 4.2.5.1, Ruby 2.2.2):

design.campaigns.where('end_time > ?', Time.zone.now).where(cancelled: nil) winds up creating the querySELECT "campaigns".* FROM "campaigns" WHERE "campaigns"."design_id" = $1 AND (end_time > '2016-03-24 03:51:32.48373') AND "campaigns"."cancelled" IS NULL This was pulled straight from pg_stat_activity in Postgres 9.3.

I'm not sure if that is the expected behavior when binding a date in an ActiveRecord query, but in our application this was generating a new prepared statement for every lookup. It generated so many, in fact, that our database was running out of memory as it's prepared statement cache was being filled up with date-specific prepared statements.

We fixed our issue at the application layer, using Time.zone.end_of_day as the cut off so we're not duplicating prepared statements. Still, this seems like something that needs attention.

This comment has been minimized.

Any idea if you get the same result in Rails 5? Lots of improvements have gone into Rails5 (i.e. master) that didn't make it into 4.2. If you can reproduce the behavior in Rails 5 I think it's worth opening a new issue for this.

This comment has been minimized.

@ghiculescu Any idea if you get the same result in Rails 5 RC2 ? Lots of improvements have gone into Rails5 that didn't make it into 4.2. If you can reproduce the behavior in Rails 5 I think it's worth opening a new issue for this.

This comment has been minimized.

All our sites use pgbouncer, without it we eat up way too many DB
connections (and PG gets very very weird if you have too many connections
going - 50 is fine, 10000 is a disaster)

If we were to eliminated pgbouncer, I still would have some concerns around
memory, especially since we have around 10-80 active connection going to pg
going at any point in time for one site. This is cause of threads and
sidekiq (each web has a light weight background job thread).

Clearly the more connections you have the higher risk there is that you
have a "prepared statement" stored that is never going to be used and just
use up memory.

To add to that in some conditions preparing can lead to enormous amounts of
pain especially if the amount of data the prepared statement returns highly
depends on the params sent to it.

This comment has been minimized.

This comment has been minimized.

edited

Thanks so much for this thread, we also experienced this bug in production.

Instead of completely disable prepared statement, we decided to try statement_limit: 200 and it worked flawlessly! We're going to keep playing with the value to find the perfect balance, but so far so good!

This comment has been minimized.

edited

We upgraded our app from rails 4.0.2 to 5.1 (a loooong process by the way) and enabled back the prepared statements.

We are definitely not having any issues with them anymore.

Memory increases (and immediately drops) on each deploy, since connections are closed and re opened. But after that, the memory stays pretty much stable.

Before the upgrade the memory would be consumed in like 4 days. (we have 1 db server, 3 app servers with 4 puma workers with 4 threads and 20 sidekiq workers on each, thats 108 connections to the db that can store up to 1000 queries each)