Hacker News new | ask | show | jobs
by ManBeardPc 822 days ago
Depends on what you want to scale. Memory, CPU and disc scale well. High concurrency can become a problem due to the 1 process per connection architecture. Keeping latency low between your app and db server and understanding how and where locks are used helps. So far I have used and seen it being used in pretty big companies successfully in a single instance (+ standby) setup.
1 comments
denchick 822 days ago
> High concurrency can become a problem due to the 1 process per connection architecture.

If I understand correctly what you mean, then this is no longer a problem. You will simply need to use a connection pool, such as Odyssey or PgBouncer. Even SPQR has its own pool of connections for each shard.

link
ManBeardPc 822 days ago
PgBouncer just pools the connection, but each connection still needs its own process in PostgreSQL itself. Each query blocks the whole process. That limits the amount of queries that can run in parallel/concurrently to the amount of connections. Long-running queries can easily clog up everything. No tool can fix this, you need to be aware of it and consider it in your design.
link
hot_gril 822 days ago
It's not really about the processes. Even if each query ran on a thread within a process, or some form of greenthreading were in use, there are I/O constraints and locking to consider.
link
ManBeardPc 822 days ago
What I talk about is blocking of limited resources. Processes/connections are expensive and therefore you want/have to limit the max amount of them. Each query/transaction requires its own process and blocks it for everyone else until it is done.

I/O or compute constraints are another issue, if your CPU or disc is already saturated you get probably no additional benefit.

But if you wait for something (locks, I/O) the connection/process can't do other things. High latency between app/database and long running transactions can also use up your available processes, even if they don't consume a lot of CPU or I/O or fight for the same locks.

Lock contention is its own problem, but makes the blocking of processes/connections worse.

link
hot_gril 822 days ago
What I meant is, even if processes had no cost, you will probably bottleneck on the I/O. So it doesn't matter a lot how many Postgres connections you have.
link
ManBeardPc 821 days ago
Sure, it’s not the most common problem to experience, just something that’s useful to know once you have more and more clients. It did happen to me, all connections used up for long running transactions (for multiple reasons) and then nothing else was able to run, CPU and I/O nearly idling at <5%, no significant lock contention.
link