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

51–60 of 121 posts

Re: Does anyone run Postgres without PgBouncer?

#51

Post by aobdev »

fabian2k wrote: post #34

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 t…

> 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.

But this is only true if you have no more than a few running instances of your application. So it feels like the cases where you must have Postgres (over an alternative like SQLite) but can't justify PgBouncer are very narrow.

Re: Does anyone run Postgres without PgBouncer?

#53

Post by aobdev »

gkbrk wrote: post #31
aobdev wrote: post #28

Earlier quoted context omitted.

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.

This solves the latency but not the overhead--you'll end up with a full pool of connections for each running instance of your application.

Re: Does anyone run Postgres without PgBouncer?

#54

Post by SoftTalker »

aobdev wrote: post #53
gkbrk wrote: post #31

Earlier quoted context omitted.

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.

This solves the latency but not the overhead--you'll end up with a full pool of connections for each running instance of your application.

If I'm implementing my own connection pool, I'll certainly make the number of connections configurable.

Re: Does anyone run Postgres without PgBouncer?

#55

Post by mike_hearn »

solatic wrote: post #41

Article title is pure click-bait. PgBouncer adds complexity. If you need it, you need it. If you don't need it, then you added complexity for nothing. > since neither IBM nor Oracle is a service that any self-respecting person not part of an enterprise sales cycle would actually use Author needs a serious ego check. There are legitimate engineering reasons to pick IBM Cloud (like if you need to support Z mainframes,…

It's an ironic claim because the Oracle cloud has great support for the Oracle Database, which doesn't require "bouncers" or equivalent. Its support for server-side connection pooling, client side load balancing (SCAN) and horizontal scaling means it offers exactly what the author wants - a single URL that just magically works and scales to any amount of work or connections cheaply.

https://docs.oracle.com/en/database/oracle/oracle-database/2...

Yet he says simultaneously that Postgres hasn't improved in a decade, but also no self-respecting person would use a database that fixes all the problems he identified. Right!

Disclosure: work part time in the Oracle DB group. Things I say here are unvetted, personal opinions.

Re: Does anyone run Postgres without PgBouncer?

#56

Post by mike_hearn »

gkbrk wrote: post #31
aobdev wrote: post #28

Earlier quoted context omitted.

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.

The issue is language ecosystems that don't use client side connection pooling because they're single threaded (node, Python). So scaling up the number of web server threads means scaling the number of Postgres processes, which are expensive.

Re: Does anyone run Postgres without PgBouncer?

#57

Post by atombender »

fabian2k wrote: post #34

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 t…

It's a little more nuanced than that. At my company we run Kubernetes workloads where we have 20-30 or sometimes more pods connecting to a single Postgres instance, each pod handling hundreds of concurrent requests. If each pod hangs on to pooled connections for more than a few seconds, then you end up with quite a few Postgres processes and a lot of memory usage and process churn; we mitigate that by having a relatively short TTL on the pool, so idle ones get reaped relatively quickly. But any idle connection not used by a pod can't be used by a different pod. A connection pooler lets all the pods share more connections to Postgres, reducing in less wastage.

Re: Does anyone run Postgres without PgBouncer?

#58

Post by fabian2k »

aobdev wrote: post #51
fabian2k wrote: post #34

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 t…

> 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. But this is only true if you have no more than a few running instances of your application. So it feels like the cases where you must have Postgres (over an alternative like SQLite) but can't justify PgBouncer are very narrow.

Not everything is a SaaS application. There's a lot of software where you only run one or two application servers.

Re: Does anyone run Postgres without PgBouncer?

#59

Post by Asmod4n »

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…

The main Question is: why do you allow clients to connect to your database server, it should be limited to a server which could actually serve the data in a format the client can just render without any logic client side.
Post reply on HN