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

111–120 of 121 posts

Re: Does anyone run Postgres without PgBouncer?

#111

Post by frollogaston »

colechristensen wrote: post #82
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've personally never heard of anyone not using PGbouncer, or some connection pooling proxy, There is plenty of space for large systems which need a database but don't have a large number of clients. It's a matter of the scale of your data vs. the scale of your readers and writers.

If you do have a large number of clients, PgBouncer only means you have a single shared pool of connections rather than each replica having its own smaller pool. You already have a load-balancer for the web clients, so the latter is maybe fine. Of course this doesn't work if you have more replicas than available DB connections.

Re: Does anyone run Postgres without PgBouncer?

#112

Post by conradludgate »

00117 wrote: post #98
conradludgate wrote: post #19

(I work on the postgres proxy layer at Neon) PgBouncer is entirely optional and it's not always the right choice. If you have a classical app (non serverless) and you can maintain a connection pool from your app, then I recommend avoiding pgbouncer. The benefits of pgbouncer mostly come from irregular client connections (too many, too much churn). If you don't have that problem, go direct to postgres. I'm exploring r…

Can you add more on why Pgbouncer is limited in a multi-tenant environment?

pgbouncer runs as a single threaded service, so it ends up getting bottlenecked by some heavy clients on typical server CPUs with low clock speeds.

Sharding/peering pgbouncer works, but it's not perfect.

More significantly for us is that Neon has millions of databases in each region, and thousands get created/destroyed every hour. Managing the pgbouncer config and shared connection pools will be extremely tricky to balance. We have a bunch of logic for this in the proxy I maintain, but not in pgbouncer.

The main intent is to incorporate a pooler directly into our existing proxy service to avoid needing double proxy services. We just need to find the right pooler implementation (and one that works in async Rust)

Re: Does anyone run Postgres without PgBouncer?

#114

Post by aobdev »

LtWorf wrote: post #97
mike_hearn wrote: post #56

Earlier quoted context omitted.

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.

Python has threads and connection pools work fine on async workers as well.

Yes but they can’t be shared. If you size your pool to hold 10 connections (because asyncio can handle that and more), then deploy with uvicorn —-workers 4 (which should match the number of cores on your app server), and then deploy to 3 app servers (for redundancy) then you’ve now got 120 open connections to Postgres. Run anything more than a trivial query and you’re easily at gigabytes of ram.

Re: Does anyone run Postgres without PgBouncer?

#115

Post by frollogaston »

aobdev wrote: post #114
LtWorf wrote: post #97

Earlier quoted context omitted.

Python has threads and connection pools work fine on async workers as well.

Yes but they can’t be shared. If you size your pool to hold 10 connections (because asyncio can handle that and more), then deploy with uvicorn —-workers 4 (which should match the number of cores on your app server), and then deploy to 3 app servers (for redundancy) then you’ve now got 120 open connections to Postgres. Run anything more than a trivial query and you’re easily at gigabytes of ram.

120 connections is likely fine. If it's not, you could do only 5 connections per worker. This is more of a problem if you have uneven load on the workers though.

Re: Does anyone run Postgres without PgBouncer?

#117

Post by aobdev »

frollogaston wrote: post #115
aobdev wrote: post #114

Earlier quoted context omitted.

Yes but they can’t be shared. If you size your pool to hold 10 connections (because asyncio can handle that and more), then deploy with uvicorn —-workers 4 (which should match the number of cores on your app server), and then deploy to 3 app servers (for redundancy) then you’ve now got 120 open connections to Postgres. Run anything more than a trivial query and you’re easily at gigabytes of ram.

120 connections is likely fine. If it's not, you could do only 5 connections per worker. This is more of a problem if you have uneven load on the workers though.

I’m not claiming it’s not fine, but it is a surprising consideration for a relatively small deployment. You have to start planning around Postgres’ architecture for anything larger, hence the solution in PgBouncer.

Re: Does anyone run Postgres without PgBouncer?

#118

Post by mike_hearn »

btown wrote: post #110
mike_hearn wrote: post #104

Earlier quoted context omitted.

Licensing in the cloud is like all cloud services, cost scales with use. I think you're talking about on prem usage which isn't relevant to most new users.

Does this apply if using cloud environments other than Oracle's own though? (Which is a far cry from on-prem, and relevant to perhaps a vast majority of businesses.) Documents like https://www.oracle.com/a/ocom/docs/cloud-licensing-070579.pd... suggest that licensing for these environments is complicated at best, with the onus of reporting lying on the customer not the cloud provider.

Yes. That doc is for the case where you run everything yourself and just use cloud VMs; not an option for most modern cloud only database.

Pay as you go offerings can be found here:

https://learn.microsoft.com/en-us/azure/oracle/oracle-db/ora...

https://docs.aws.amazon.com/odb/latest/UserGuide/what-is-odb...

Re: Does anyone run Postgres without PgBouncer?

#119

Post by gkbrk »

aobdev wrote: post #114
LtWorf wrote: post #97

Earlier quoted context omitted.

Python has threads and connection pools work fine on async workers as well.

Yes but they can’t be shared. If you size your pool to hold 10 connections (because asyncio can handle that and more), then deploy with uvicorn —-workers 4 (which should match the number of cores on your app server), and then deploy to 3 app servers (for redundancy) then you’ve now got 120 open connections to Postgres. Run anything more than a trivial query and you’re easily at gigabytes of ram.

This is more app instances than 99% of deployments. Most apps and websites run a single server.

Re: Does anyone run Postgres without PgBouncer?

#120

Post by vladsanchez »

bart3r wrote: post #91

Am i the only one who still uses Pgpool ?

Nope, I'd take pgPool any day mostly for its load balancing abilities, not found on pgBouncer. I've never understood public fascination and favor of pgBouncer over pgPool despite this major omission. Perhaps it's because their unfamiliarity about its features and benefits.

Thanks for being the only pgPool comment ;)

Post reply on HN