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

101–110 of 121 posts

Re: Does anyone run Postgres without PgBouncer?

#101

Post by tredre3 »

pocksuppet wrote: post #67
tyre wrote: post #33

Earlier quoted context omitted.

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…

Languages have affordances. At the extreme ends are PHP, and Java or Go. PHP runs a separate logical process on every request which can't share resources with any other request. Almost everyone using Go is writing a long-running server process because that's how the libraries are designed. Almost everyone using Java is writing a long-running process or a module for one, because Java startup times are obscene. Java al…

> PHP runs a separate logical process on every request which can't share resources with any other request.

PHP allows persistent connections to postgres that will be reused by subsequent requests. But of course the connection isn't shared by concurrent requests (unless you use Swoole), so it doesn't solve the process-per-connection on postgres' side, it merely saves some overhead.

Re: Does anyone run Postgres without PgBouncer?

#102

Post by mike_hearn »

pocksuppet wrote: post #89
mike_hearn wrote: post #78

Earlier quoted context omitted.

Multi-threaded processes are used because that's the most efficient model, hardware wise. Tasks can maximally share resources. It's not to do with library design or startup time.

The hardware doesn't know anything about processes. The most efficient model, hardware-wise, is to disable virtual memory and never do a context switch, but we don't see anyone programming like that, except for the crazy SDN folks who are doing software packet forwarding at hundred-gigabit rates.

Well, a server running a single process is fairly close to that. Switching to kernel mode doesn't require a full context switch and nor does switching between threads.

Re: Does anyone run Postgres without PgBouncer?

#103

Post by mike_hearn »

paulryanrogers wrote: post #79
mike_hearn wrote: post #55

Earlier quoted context omitted.

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/databas…

Oracle licensing audits scare off plenty of folks. At a small SaaS we were put off by the difficulty getting OracleDB's dev edition installed and working at all. While MySQL (then independent) and Pg were bare bones in comparison, they were very quick to get started, covered what was needed, and no risk of price spikes or time consuming audits. When pain points were encountered with MySQL and Pg there were plenty of…

I believe you, but these days it's just a container launch to do local dev, no different to postgres. In the cloud there are no license audits. Those are only for on-prem setups where licensing servers aren't acceptable.

If you don't need the features of a commercial database then they're not worth the cost indeed.

Re: Does anyone run Postgres without PgBouncer?

#104

Post by mike_hearn »

sgarland wrote: post #96
mike_hearn wrote: post #75

Earlier quoted context omitted.

Yeah, that's a common sentiment. I regularly see people describe features commercial RDBMS' have shipped for years as if they were unmapped frontiers in computer science. Awareness of their capabilities is very low; I was once there too and remember being taken by surprise when I realized how far ahead of open source they truly are. IMO startups can get edge by exploiting this information asymmetry. Spending a bit mo…

I truly wish that Oracle wasn't so aggressively hostile WRT licensing enforcement, because its DB capabilities are insane. I've never operated it, but all the docs I've read are legitimately impressive. I support getting paid for your product, but charging per-core/thread is frankly absurd, and charging for cores that aren't even being used by the product is beyond the pale.

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.

Re: Does anyone run Postgres without PgBouncer?

#107

Post by moooo99 »

mike_hearn wrote: post #76
moooo99 wrote: post #61

Earlier quoted context omitted.

I do not think it is a mystery why people favor Postgres over OracleDB, is it?

Depends what you mean by people. Outside of the startup space you'll find commercial RDBMS everywhere, especially OracleDB. Not many are running banks or hospitals on Postgres.

> Not many are running banks or hospitals on Postgres.

Certainly not. But the key here is that not many are running banks or hospitals in the first place.

I work at a big company, we run on SAP as the big commercial DBMS, but also have some oracle instances. By number by far the most widespread commercial RDBMS seems to be SQL Server

Re: Does anyone run Postgres without PgBouncer?

#109

Post by frollogaston »

benji-york wrote: post #99
frollogaston wrote: post #50

Earlier quoted context omitted.

Yes, I've done a variety of typical, nontrivial workloads on Postgres for about a decade and have never used PgBouncer. Though I can imagine use cases where it'd make sense.

I believe it would add color to the discussion if you enumerated a few of the details of those workloads.

1. Typical backend with a DB, serving either a web/mobile app or other services within a company. Each job had a pool, so there's a steady number of connections to the DB that didn't exceed what it can support, with some headroom for monitoring, cronjobs, and emergency access. Xacts were usually short. Was able to handle high QPS, and when it hit the limits, it was something on the DB rather than backend CPU starvation, so adding more backends and thus connections wouldn't have helped. Even though some of these were on Heroku which has a rather low DB connection limit.

2. Data pipeline that used Postgres queries as sort of a map-reduce. Not ideal but I think not that uncommon. Each machine had a local DB with mostly temp tables, plus there were some shared DBs. Each running stage in the pipeline needed one connection per CPU core because queries were sharded that way to utilize all cores.

3. Another data pipeline that used batch workers that did their bookkeeping in a DB. This was infrequent enough access that I had each one opening a connection right before using it then closing it after. PgBouncer would make sense there, but we were fine even if every worker opened a connection at the same time. That's partially because many of those workers were GPU instances, so there weren't terribly many of them.

I'm actually wondering who is in situation #1 and needs PgBouncer, and why exactly. The scenario I have in my head is you're doing heavy CPU work directly in your web workers, and thus you need more workers than you have DB connections available, which seems like it's more monolithic than it should be.

Re: Does anyone run Postgres without PgBouncer?

#110

Post by btown »

mike_hearn wrote: post #104
sgarland wrote: post #96

Earlier quoted context omitted.

I truly wish that Oracle wasn't so aggressively hostile WRT licensing enforcement, because its DB capabilities are insane. I've never operated it, but all the docs I've read are legitimately impressive. I support getting paid for your product, but charging per-core/thread is frankly absurd, and charging for cores that aren't even being used by the product is beyond the pale.

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.

Post reply on HN