PostgreSQL crumbles even when a moderate number of clients connect to it. This happens because of how it handles each connection. Let’s understand this in a bit more detail.
When a new client connects, PostgreSQL (postmaster process) spawns a new backend process (not thread) to handle the session. This backend process is responsible for starting a session, and maintaining it until the client disconnects or the connection is lost.
As the number of clients increases, the server has to spawn more and more backend processes. Each of these processes needs to cache catalog tables, prepared statements, intermediate query results, and other data; leading to a higher memory consumption.
This is why, when we spin up PostgreSQL we always keep it behind a connection pooling manager, like pg_bouncer. This manager establishes and manages a pool of connections to the database. When a client request comes in, the manager assigns an available connection, instead of creating a new one.
This connection reuse makes it easy for us to handle a large number of clients over fewer database connections. By the way, I have a deep-dive into how PostgreSQL does connection management, if you are interested in knowing the internal workings and details,
give it a watch - youtu.be/o7qLKfILuD8
Fun fact: there is a massive proposal for moving PostgreSQL to a multi-threaded model instead of a multi-process model.
⚡ I keep writing and sharing my practical experience and learnings every day, so if you resonate then follow along. I keep it no fluff.