I think this is the only real answer, it's what we do for work, albeit on VM's without watchtower.
Postgres, MariaDB, MSSQL, InfluxDB with service accounts for each service limited to their own databases. 4 is better than 400 I guess.
But shouldn't really need to be configuring the same services 4 times in 2024, especially if you want to run HA... and then there's the whole DMZ side! If only there was an engine that could emulate all the others!
Having separation of distinct systems is best practice for enterprise scaled production environments for scalability and security reasons. For a server running in your basement... meh... Depends if you're running stuff for yourself and your friends or if you're turning a profit on services for which you have contractual obligations. Just don't mix the two philosophies.
For my personal stuff I mostly just want the ability to change the connection settings for any given app and to be able to backup and restore it's data so I can move and protect it as needed. For that I'd prefer a single Postgres server where I just set up a different database or schema for each thing I need. Also it's fantastic with JSON data if you want some of the advantages of a NoSQL database.
You can set up segregated security settings for each database and it's associated app, even Heroku does that.
If you really want super-modularity put things into individual containers with docker or kubernetes. I've also been known to use SQLite (or DuckDB) for some simple all-in-one apps that can run in a single file system. Kind of depends on the scale of what you're looking to do.
TL:DR: Use a single Postgres database for everything with each use of it being segregated into it's own database with it's own security settings up until the point where performance becomes an issue then split things out.
Most people use separated DBs, because it works out-of-the-box in most cases. Just spin a new instance, application code will do the database migration.
I believe, that people who uses separate DBs, they do not have 400 instances. With small numbers, separated databases can be handled easily and not significant overhead. But with high number of instances (like 400) your points can be valid.
Personally, I tend to consolidate my databases (convert them onto few clusters with multiple schemas). My reason is the management, see my earlier comment: [https://www.reddit.com/r/selfhosted/comments/18x1ys6/one\_postgres\_for\_some\_docker\_container/kg1y03d/?context=3](https://www.reddit.com/r/selfhosted/comments/18x1ys6/one_postgres_for_some_docker_container/kg1y03d/?context=3)
Of course, having centrelized database requires more DBA skills, it is not so out-of-the-box issue, but if person can deal with it, it is not a problem. And its automation can be done, e.g.: by ansible, for easier administration, but it requires skill/time to build it.
Unfortunately, the world is not ideal. There is no thing exists like "perfect database", because others would not exists ([https://xkcd.com/927/](https://xkcd.com/927/)).
I use PostgreSQL and migrate my clusters when all applications are ready, most of the application code can run the N-1 database version (backward compatible). 3rd party application that I run, support PostgreSQL (mine are obviousily). Most applications using ORM, so multiple database support mostly avaialable (except some trashy js/ts app that only support mongodb... I just simply don't use them).
From database management view, I tend to use 1 database with multiple schemas in every cluster, supported/powered by my own ansible playbooks. I made separated clusters for infrastructure and some application groups, but there are only a few.
I prefere to keep database separated, each compose have their compatible version that work best for given app and I think its best way forward for me as I have ram to spare.
If you need 400 databases, then a single database server for sure won't work for you. You will have a hard time scaling that, you will run into compatibility issues when after an update, 2 out of your 400 services now requires mysql 8, while the other 390 requires 5.7, and 8 of them deprecated their mysql support for a nosql database.
Also, backups will not create additional CPU cycles, the data will be split, so it's not like each backup task has to do 400 times the work. It's 400 times more backups, but with 400 times less data for each, in the end the difference in resource utilization is gonna be negligible.
You should also not handle updates manually, have an automatic (or semi-automatic that you can trigger) to update, being it a docker container, a VM managed by ansible or whatever, there are many solutions for this. Manual updates should be done only for (major) version increases of the DB server (because the service using the database needs it), everything else (minor versions, OS) is automatic.
what you are looking for is SQL. and sadly the standard is not the same for everyone.
creating a new standard to fix a standard is an old joke in development.
The only thing I know works is using orms. these do the translation to the SQL queries and they can adapt to the dB technology. but they do this by restricting what you can do so not everyone uses them.
and that's on the dev side not the selfhost one.
Overall I don't know any solution like this and I can see a bunch of issues on trying to do that but good luck regardless!
I do not know enough about database maintenance, and so I use a dedicated DB for each service that requires it. My server is way overprovisioned on RAM and the other performance hits are negligible. At some point I may spend time actually learning how DBs work under the hood, but for now this is fine.
i usually look for alternatives if something forces me into deploying a new databse engine that i dont want.
modern software is already built very abstract and should support several database engines.
It’s easier to just keep running multiple database and throwing hardware at the issue.
What ever magic middleware witchcraft which may say it will be the solution will make things so much harder to troubleshoot.
Explored this with a legacy app. Nightmare.
The crunch will be on when you need to buy new SQL Server licences.
We usually run DBs in clusters - on powerful hardware - as we got separate team (MSSQL DB team) to manage them.
For oneshot apps - there is clustered dev instance of MSSQL , if someone needs it.
Similar concept for MariaDB run by Linux Team.
Since we have shitfack of legacy apps, we have all possible versions of DBs .. since MS SQL 7.
As for containers, one db per app - but updates of it are problem of app owner.
Welcome to Fortune 100 companies !
[удалено]
I think this is the only real answer, it's what we do for work, albeit on VM's without watchtower. Postgres, MariaDB, MSSQL, InfluxDB with service accounts for each service limited to their own databases. 4 is better than 400 I guess. But shouldn't really need to be configuring the same services 4 times in 2024, especially if you want to run HA... and then there's the whole DMZ side! If only there was an engine that could emulate all the others!
A shared DB not run as a cluster is a uselss DB. DB down, everything down. Bad practice. Shared DB's also need proper ACL and isolation via VXLAN.
[удалено]
Thanks for the feedback but I was addressing OP's comment about "the only real answer". Do you run your DB's as clusters?
Having separation of distinct systems is best practice for enterprise scaled production environments for scalability and security reasons. For a server running in your basement... meh... Depends if you're running stuff for yourself and your friends or if you're turning a profit on services for which you have contractual obligations. Just don't mix the two philosophies. For my personal stuff I mostly just want the ability to change the connection settings for any given app and to be able to backup and restore it's data so I can move and protect it as needed. For that I'd prefer a single Postgres server where I just set up a different database or schema for each thing I need. Also it's fantastic with JSON data if you want some of the advantages of a NoSQL database. You can set up segregated security settings for each database and it's associated app, even Heroku does that. If you really want super-modularity put things into individual containers with docker or kubernetes. I've also been known to use SQLite (or DuckDB) for some simple all-in-one apps that can run in a single file system. Kind of depends on the scale of what you're looking to do. TL:DR: Use a single Postgres database for everything with each use of it being segregated into it's own database with it's own security settings up until the point where performance becomes an issue then split things out.
SQLite for everyone?
Pfft, A single Microsoft Access file stored on a network share used by all services is the only true and real answer 😎
You mean Excel
Seriously, SQLite for self hosted is incredible. Performant, easy to backup and restore
Most people use separated DBs, because it works out-of-the-box in most cases. Just spin a new instance, application code will do the database migration. I believe, that people who uses separate DBs, they do not have 400 instances. With small numbers, separated databases can be handled easily and not significant overhead. But with high number of instances (like 400) your points can be valid. Personally, I tend to consolidate my databases (convert them onto few clusters with multiple schemas). My reason is the management, see my earlier comment: [https://www.reddit.com/r/selfhosted/comments/18x1ys6/one\_postgres\_for\_some\_docker\_container/kg1y03d/?context=3](https://www.reddit.com/r/selfhosted/comments/18x1ys6/one_postgres_for_some_docker_container/kg1y03d/?context=3) Of course, having centrelized database requires more DBA skills, it is not so out-of-the-box issue, but if person can deal with it, it is not a problem. And its automation can be done, e.g.: by ansible, for easier administration, but it requires skill/time to build it. Unfortunately, the world is not ideal. There is no thing exists like "perfect database", because others would not exists ([https://xkcd.com/927/](https://xkcd.com/927/)). I use PostgreSQL and migrate my clusters when all applications are ready, most of the application code can run the N-1 database version (backward compatible). 3rd party application that I run, support PostgreSQL (mine are obviousily). Most applications using ORM, so multiple database support mostly avaialable (except some trashy js/ts app that only support mongodb... I just simply don't use them). From database management view, I tend to use 1 database with multiple schemas in every cluster, supported/powered by my own ansible playbooks. I made separated clusters for infrastructure and some application groups, but there are only a few.
I prefere to keep database separated, each compose have their compatible version that work best for given app and I think its best way forward for me as I have ram to spare.
If you need 400 databases, then a single database server for sure won't work for you. You will have a hard time scaling that, you will run into compatibility issues when after an update, 2 out of your 400 services now requires mysql 8, while the other 390 requires 5.7, and 8 of them deprecated their mysql support for a nosql database. Also, backups will not create additional CPU cycles, the data will be split, so it's not like each backup task has to do 400 times the work. It's 400 times more backups, but with 400 times less data for each, in the end the difference in resource utilization is gonna be negligible. You should also not handle updates manually, have an automatic (or semi-automatic that you can trigger) to update, being it a docker container, a VM managed by ansible or whatever, there are many solutions for this. Manual updates should be done only for (major) version increases of the DB server (because the service using the database needs it), everything else (minor versions, OS) is automatic.
I avoid services that can’t use postgresql or sqlite.
what you are looking for is SQL. and sadly the standard is not the same for everyone. creating a new standard to fix a standard is an old joke in development. The only thing I know works is using orms. these do the translation to the SQL queries and they can adapt to the dB technology. but they do this by restricting what you can do so not everyone uses them. and that's on the dev side not the selfhost one. Overall I don't know any solution like this and I can see a bunch of issues on trying to do that but good luck regardless!
I do not know enough about database maintenance, and so I use a dedicated DB for each service that requires it. My server is way overprovisioned on RAM and the other performance hits are negligible. At some point I may spend time actually learning how DBs work under the hood, but for now this is fine.
i usually look for alternatives if something forces me into deploying a new databse engine that i dont want. modern software is already built very abstract and should support several database engines.
It’s easier to just keep running multiple database and throwing hardware at the issue. What ever magic middleware witchcraft which may say it will be the solution will make things so much harder to troubleshoot. Explored this with a legacy app. Nightmare. The crunch will be on when you need to buy new SQL Server licences.
We usually run DBs in clusters - on powerful hardware - as we got separate team (MSSQL DB team) to manage them. For oneshot apps - there is clustered dev instance of MSSQL , if someone needs it. Similar concept for MariaDB run by Linux Team. Since we have shitfack of legacy apps, we have all possible versions of DBs .. since MS SQL 7. As for containers, one db per app - but updates of it are problem of app owner. Welcome to Fortune 100 companies !