Connection pools and LIFO

[cooljacob204]

Hey All,

I'm have an issue where I need a larger amount of connections for burst traffic but for everyday traffic I need a small amount. So my pool size is larger then it needs to be for normal traffic.

Because timed_queue uses a queue (so FIFO) I have an issue where all these connections stay open and are rotated through during queries. The related postgres connection processes slowly consume more and more memory for what I presume is caching reasons and our instance slowly creeps up in memory until it runs low on memory.

My ideal solution would be to reap idle connections via postgresql's idle_session_timeout and the connection_validator plugin. However after some research I expect that no connection will really goes idle all that long due to the connection pool's queue. So ideally there would be a stack option, so last in first out which I think would work well for this use case.

For now I plan to use the connection_expiration extension to refresh my connections and relieve memory. But to me that isn't ideal, I think a few long lived connections that handle the majority of requests would be great while being able to clean up burst connections via idle_session_timeout.

I know a lot of people use PG bouncer for this but we are trying to avoid it for now as that adds a lot of subtle complications.

I see threaded connection pool has a stack option but it's not documented at the top of the class and I'm worried about it's potential deprecation with the default being moved to timed queue.

Curious for thoughts / opinions / suggestions on this.

[jeremyevans]

You can use the stack option for the threaded connection pool. It's not planned for removal any time soon. That with connection_validator and idle_session_timeout may get you what you want.

Another possible option is using Database#disconnect after the burst. This only disconnects connections no longer in use.

The reason Sequel uses FIFO and not LIFO for connections is LIFO makes it more likely for connections to go unused, hit timeouts, be disconnected, and then fail when usage is actually attempted. You can protect against this with the connection_validator extension, but it imposes some overhead.

There has been discussion in Ruby core about introducing a Thread::Stack object that operates similarly to Thread::Queue but uses LIFO instead of FIFO. If a future Ruby version adds it, I plan to support it as an option.