8000 PostgreSQL clusters on one host?

Coming back to the last post: 8000 databases in one PostgreSQL cluster?. As we’ve seen this is doable but comes with a lot of pain points. Another solution for this kind of requirement would be to host 8000 PostgreSQL clusters on one host. Again, I am not saying this is something you should do, and again, this is not how it finally will be implemented. This is more about if you can do it, and what is means for operating such a setup. Eight thousand is a huge number, no matter if it is about this number of databases in a single cluster, or this number of clusters on a single node.

The default PostgreSQL configuration on Linux comes with 128MB of shared_buffers. Again, if we do the math, that would be 1’024’000MB for 8000 clusters on a single host. Surprisingly I do not have such a machine available right now. We’ll use a AWS EC2 t3a.xlarge which comes with 4vCPUs and 16GB of memory. If we reduce shared_buffers to 128kB (which is the minimum), we come to 1024000kB, which is around 8GB of shared_buffers for all clusters. This should be doable, not counting any other memory related parameters.

The next bit we need to calculate is the required disk space. A fresh initialized PGDATA for PostgreSQL 13.1 consumes 29MB. For 8000 clusters we will therefore need 312’000MB of disk space. Se, here we go:

Initializing 8000 clusters will take some time, but the procedure is quite simple. We need a different directory for each PGDATA and a different port for each cluster. In addition we need to reduce shared_buffers to 128kB and then start up all the clusters. First, lets create all the data directories and check if we run into any issues with this already:

If you want to monitor the progress, you might do something like this:

That takes quite some time. How many space or we consuming?

Not far from what we calculated above. Before we try to start all the clusters there is another bit we need to think about. If we startup a PostgreSQL 13.1 instance, how many processes do we get by default?

That’s eight per cluster, multiplied by 8000, that makes 64’000 processes. In the default configuration on Debian we have these limits:

This will for sure not work, as we can easily try:

This will go fine until around 1500 postmasters have been started:

… so we are clearly hit some limits here. Lets increase the maximum number of open files and processes limits for the postgres user (-1 means unlimited, only hard limits here):

After restarting the session we can check the actual value of the hard limits:

This means we should be able to create an unlimited amount of user processes, and we should be able to open 1048576 files (this seems to be the maximum on Debian). As these are the hard limits we need to increase the soft limits as well:

Lets check if that is sufficient for starting the 8000 clusters:

No, we’ll still hit limits (again around 1500 clusters):

Lets try to use a larger instance type: c5.9xlarge: This one comes with 36vCPU and 72GiB of memory. Will that work?

We can go a bit further and come to approx. 1740 running cluster, but this is far from 8000:

If we increase even more and go to c5a.24xlarge we’ll come to (96vCPUs and 192GiB of memory):

This will give us around 4096 clusters, which is a bit more than half the 8000 we wanted. But this also means that there is another limit we’re hitting. Four thousand clusters will consume 32000 processes, and we’re not close to the limit we’ve set above. What is it then? Increasing the number of vCPUs clearly gave us more running clusters, so there must be a relation between the number of vCPUs and the number of processes we can start. There is a kernel parameter which limits the maximum number of processes one can start on a Linux system:

We’re already higher than the default, which is 32768 and this value is dependent on the number of CPUs (more information here). This is actually not a real limit, but once that number is reached, the counter starts from 1 again and if no numbers are left, no additional process can be started. If we scale down our instance to c5.large (4vCPUs and 8GiB of memory) we’ll again get the default:

Although we should not hit the limit of 98304 lets try to increase that and do another run. This time we’ll also have a look at the memory usage:

In a second session monitor memory usage:

… and it still fails with a bit more than 4000 clusters. The issue is not the overall memory consumption, we still have plenty of memory left. The answer is in the PostgreSQL startup log:

So we need to increase shmmni for enough shared memory segments being available. Next try:

Next limit hit, now we come to around 4600 clusters, which is better, but still not the 8000. This time we are hitting the number of open files:

But even if we increase the system-wide limit we’ll run into the same issue because we can not raise the user defined limit by more than 1048576:

The reason is in the sshd_config:

PAM is responsible for setting the user limits. Lets skip that by removing this line and restarting sshd:

Once we restarted the ssh session let’s try again:

Next issue, we are not able to login anymore. The way to do it is to disable the line which does the enforcing of the limits:

After restarting sshd lets try again:

.. and it still fails:

The next test is to check if we are hitting a user/session, or a system wide limit. For that we will start the first 4000 clusters with the postgres user we already have. For the remaining 4000 clusters we’ll create another user, change $PGDATA to be owned by that user, and then try to start them. If that works, we can at least start 8000 clusters using two separate OS users:

First 4000 clusters:

Lets check if we can start another 4000 using the other user:

Which leads to the same issue once we reach around 4600 clusters. At this point I decided to stop this experiment as the conclusion is clear: Don’t do it, for obvious reasons:

• You’ll run into all kinds of limits
• Managing 8000 clusters on one host is not doable, you’ll for sure do a mistake one time and then one or may clusters are damaged
• Only looking at the number of open files and the number of processes should already tell you, again: Don’t do it
• Many, many other reasons (backup/restore/patching/upgrades)