Gracefully Scaling to 10k PostgreSQL Connections for $35/mo, Part Two | Hacker News Reader

Gracefully Scaling to 10k PostgreSQL Connections for $35/mo, Part Two

articles.fti.bi

141 pointsAlexAtFTI18 comments

Connection management in Postgres is definitely one of its sore spots. Each connection even an idle one consumes roughly 10 MB of overhead and introduces additional coordination overhead on queries. If you're running at any large scale production you want pgbouncer in place which can help manage the idle connections so they're not a hit to your system. Hopefully one day this improves directly in core.

> Each connection even an idle one consumes roughly 10 MB of overhead

Citation needed :P

Unless you're using a large shared_buffers setting without configuring huge pages, you should see smaller overhead. Obviously depends on what you're doing in each connection - if you consider accessing 10k tables in each session part of the connection overhead, sure, it'll be bigger.

> introduces additional coordination overhead on queries.

It's not really the established connection that matters. The number of in-progress transactions matters considerably (a snapshot is O(#active-transactions)). There's some influence of the maximum number of possible connections.

Production example: per-process memory usage at ChartMogul is >70MB because we use a huge number of tables with partitioning.

> Citation needed :P

There are plenty of citations out there, but Craig wrote most of them.

pgbouncer or pgpool (of the extensions I searched) do this. Which ones are more of a challenge? I mean I have read on some forum that PGPOOL -II would just shutdown and stop or misbehave. Any such experiences or gotchas with pgbouncer?

We use pgbouncer with pools of thousands of concurrent connections. It does what it says on the tin.

We have experimented with both at scale and found PgBouncer to be a lot more stable and reliable. We haven't seen gotcha failures, or any failures really, in the last few years. It's also refreshingly simple to automate and scale, although there are shortcomings in the configuration design that ends up requiring some cleverness.

"although there are shortcomings in the configuration design that ends up requiring some cleverness."

Like?

Namely, it doesn't support configuration via CLI or env vars, which means you have no option but to generate a file to be loaded in. To be fair, that's kind of a newer pattern. Also, multi-DB PgBouncer is a little more insecure than it leads on to be by default, along with a few other things you learn quickly as you handle larger and more complex use cases. It's not bad, just not as good as it could be in my opinion.

Sorry if this is a stupid question, but don't most libraries do connection pooling on their own? Why is something like pgbouncer necessary?

Most libraries do not handle connection pooling on their own. They simply handle opening and closing a connection per query. Hence the write up on pgbouncer being a (relatively) transparent, drop-in solution to handling this when a library does not.

well transaction pooling is good for most databases.

consider having 10 upstream connections per transaction pool and 10000s of connections to applications. pgbouncer can handle that and every application would only be assigned to a real database if a transaction/query happens and after that the upstream connection would be deassigned.

I'm glad that pretty much the whole thing is 'use pgbouncer.'

This was my exact situation at a previous position. We needed up to 20k connections to a single pgsql master. Even the most monster postgres server falls over around ~600 connections (up to 1k depending on usage).

Using a pgbouncer-per-webserver we easily got to 20k.

I will say this was with 9.3 though, things may have changed on that front. Nowadays I use pgsql primarily for analytics, so there's only ever 20-30 connections tops, albeit doing quite heavy querying.

Correct me if I am wrong in understanding this "Using a pgbouncer-per-webserver we easily got to 20k.", but does this mean say you have 5 web-servers, each web-server has an instance of pgbouncer installed? or you have 5 web-servers with 5 separate pgbouncer servers (1:1 mapping between webserver and pgbouncer) while all pgbouncer server transact with a powerful, single master Postgres.

Postgres connection pooling is pretty much necessary in almost all cases, except when doing pretty low level connection management yourself.

Besides the old stalwart pgBouncer, Yandex recently released Odessey as well https://github.com/yandex/odyssey

Thanks for sharing..

Part 1 in case anyone missed out on it (like me)

https://medium.com/futuretech-industries/ten-thousand-high-a...

This is great! Have run up some very expensive bills trying to scale postgres. Eager to try this out.

Reply on news.ycombinator.com