Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Does anyone run Postgres without PgBouncer?

brandur.org

Post reply on HN
Print view View original

31–40 of 121 posts

Re: Does anyone run Postgres without PgBouncer?

#31

Post by gkbrk »

aobdev wrote: post #28
LtWorf wrote: post #16

Earlier quoted context omitted.

Me too. I even opened the page and I'm not sure of what's the problem being solved.

Every single connection to Postgres is a new process, which requires a fork and new memory allocation (at least 10MB plus whatever you need for your query). PgBouncer opens a pool of connections and then reuses them each time a client asks for a connection. This reduces latency (no more fork) and overhead (reuse memory).

If it's designed well, your application also opens a pool of connections and re-uses them each time the code needs a DB connection.

Re: Does anyone run Postgres without PgBouncer?

#33

Post by tyre »

matsemann wrote: post #21

Python: absolutely necessary due to the amount of processes and various deployments to run an application once it grows. Java: never felt the need even on quite big apps. As it's much easier to share a connection pool locally, it's not as many single connections across the whole app.

Your high level buckets are languages but the constraints you list are usage patterns.

You can build applications in any language that have many short-lived transactions in single connections or a few huge, blocking one, or anything in between.

The former case is a good one for PGBouncer (it can interleave transactions) and the latter isn’t (it can’t do much about your three minute long BEGIN…COMMIT). Neither has to do with the language.

Re: Does anyone run Postgres without PgBouncer?

#34

Post by fabian2k »

If you have a connection pooler in your application, and the DB is only used for this application, you don't need an external pool like PgBouncer. That is probably a pretty common scenario, and typical web frameworks include a connection pool anyway.

An additional complication is that more aggressive pooling methods have side effects that you must know and prevent in your application. They're not safe to use out of the box.

The need for a connection pool is a side effect of the heavy process-based PostgreSQL connections. And I would suspect that this will change at some point in the not so near future, so that users don't have to think about this part this much.

Re: Does anyone run Postgres without PgBouncer?

#35

Post by ummonk »

petcat wrote: post #23

This question will get more interesting responses if it was qualified as: "Does anyone run Postgres without PgBouncer for non-trivial workloads?" Because, as we can see from the comments so far, lots of people are going to say you don't need it for your blog that gets 10 hits a month. I've personally never heard of anyone not using PGbouncer, or some connection pooling proxy, for reasonably concurrent workloads. PG's…

I feel like there are a lot of use cases where I’d opt for SQLite and a lot of use cases where I’d opt for Postgres + PgBouncer. I’m curious what kinds of features push towards using Postgres alone over SQLite.

Re: Does anyone run Postgres without PgBouncer?

#37

Post by Brotkrumen »

LtWorf wrote: post #16
1over137 wrote: post #8

Yes. I've never even heard of PgBouncer.

Me too. I even opened the page and I'm not sure of what's the problem being solved.

If you handle database requests naively, every request to the database may open its own connection. This is a super simple approach but will exceed the amount of connections the database can or wants to handle concurrently.

One solution is to increase your app complexity and introduce a layer that manages connection pooling or queuing.

Or you can just keep your app naive and simple and put pgbouncer transparently in front of your DB. Even for multiple apps, so instead of every app increasing in complexity, reimplementing connection handling, you just have pgbouncer.

Re: Does anyone run Postgres without PgBouncer?

#38

Post by tyre »

petcat wrote: post #23

This question will get more interesting responses if it was qualified as: "Does anyone run Postgres without PgBouncer for non-trivial workloads?" Because, as we can see from the comments so far, lots of people are going to say you don't need it for your blog that gets 10 hits a month. I've personally never heard of anyone not using PGbouncer, or some connection pooling proxy, for reasonably concurrent workloads. PG's…

Good question.

PGBouncer isn't as useful if you have seriously long-running transactions. It can’t do much of anything with those. Sure, you can give it a pool of 1000 and your Postgres instance a pool of 100, but you’re just moving who is going to say, “sorry, the database can’t handle your request right now.”

It’s not a silver bullet.

If you have more connections than Postgres can handle on the hardware it’s on, but with a bit of buffer it’ll be able to burn them down: great.

If you have long-held connections with many transactions that PGBouncer can interleave: great.

If you have connections whose transactions are longer than a reasonable timeout, well, your optimization princess is in another castle.

Re: Does anyone run Postgres without PgBouncer?

#39

Post by atomicnumber3 »

tyre wrote: post #33
matsemann wrote: post #21

Python: absolutely necessary due to the amount of processes and various deployments to run an application once it grows. Java: never felt the need even on quite big apps. As it's much easier to share a connection pool locally, it's not as many single connections across the whole app.

Your high level buckets are languages but the constraints you list are usage patterns. You can build applications in any language that have many short-lived transactions in single connections or a few huge, blocking one, or anything in between. The former case is a good one for PGBouncer (it can interleave transactions) and the latter isn’t (it can’t do much about your three minute long BEGIN…COMMIT). Neither has to…

They addressed this though? I suspect you are unfamiliar with the GIL in python. The reason for a language distinction is because, as OP says: "As it's much easier to share a connection pool locally".

This may change vaguely soon, but right now you typically scale python apps by starting multiple python processes, while for Java you can just add threads. Python processes can't share a thread pool among all of them, while Java can.

Re: Does anyone run Postgres without PgBouncer?

#40

Post by tyre »

ummonk wrote: post #35
petcat wrote: post #23

This question will get more interesting responses if it was qualified as: "Does anyone run Postgres without PgBouncer for non-trivial workloads?" Because, as we can see from the comments so far, lots of people are going to say you don't need it for your blog that gets 10 hits a month. I've personally never heard of anyone not using PGbouncer, or some connection pooling proxy, for reasonably concurrent workloads. PG's…

I feel like there are a lot of use cases where I’d opt for SQLite and a lot of use cases where I’d opt for Postgres + PgBouncer. I’m curious what kinds of features push towards using Postgres alone over SQLite.

All of my personal apps use Postgres because:

- types are lovely. We love types. SQLite’s default of non-strict typing is, to me, bananas.

- SELECT DISTINCT ON is my ride-or-die

- most importantly, I’m very comfortable in Postgres and the setup cost is basically zero (like SQLite) because Claude does it.

Post reply on HN