Hacker News Reader

Comment by sidcool | Hacker News Reader

If a Database needs to support more than 500 connections at a given time (due to scale), what's the way around?

What exactly do you want to scale? But in the end the answer is likely "connection pooling", either integrated in the application or with a dedicated pooler like PgBouncer which this article is about.

For Postgres a good way to scale is to just use a bigger server. You can get a lot of very fast storage and lots of CPU cores inside a single server today. And if that isn't enough you're far, far into territory where you can't give generic answers and it depends a lot on your specific use case and what you do with that database.

I mean let's say I have to support 10000 updates per second and 100000 reads per second. Surely 500 connection pool won't suffice.

I don't think 10K updates per second are really that difficult for the db (Unless you have locking issues).

But at that point your reads should be probably be sent to read-only replica's. So you write to a master but all your read-heavy apps and queries run against replicas.

Each of those reads may only be 1ms, which would translate into only 100 connections needed.

So the workload you describe — without more detail — may in fact be just fine with that connection count.

But plenty of people also run Postgres with well above 500 max connections.

I think you would look at

1. "Server level connection pooling" in the article (all clients share one pgbouncer instance) and

2. "Statement mode" if you can live without transactions (connection is re-pooled after every statement) otherwise "Transaction Mode" (connection is repooled upon transaction commit or rollback).

If you require 500 instances of your app to scale, how do you get away with just a single DB instance?

By having relatively simple queries. You can push 50k queries per second on a 64 CPU Postgres machine.

This is real world experience from a moderately complex application.

It all depends on your workload. Those 500 instances may be make calls to other services, services, for example, or burning CPU in various ways.

simple queries, grouping writes, minimizing updates, caching, connection pooling

Reply on news.ycombinator.com