All we need is an easy explanation of the problem, so here it is.
I m partitioning my problem using a Domain-Driven Design approach. Several databases arise with low or null coupling.
In the long term they will be separate databases on separate hosts, horizontally scaled. However, currently I do not need/want such an expensive deployment. I just plan to run the whole thing on a single PostgreSQL cluster. The development environment is another clear situation where having a single cluster is desirable.
Authors as Chris Richardson recommend using schemas in the near term however, I have found this implies rewriting part of the SQL code when logical replication is used to solve the couplings. I imagine he is using the term schema with MySQL in mind.
Using separate databases seem to be the perfect solution in terms of SQL code invariance but, does this imply a performance penalty on a sole cluster compared to a single database virtually segmented by schemas?
I have read this similar question https://stackoverflow.com/questions/1152405/is-it-better-to-use-multiple-databases-with-one-schema-each-or-one-database-wit/1157008#1157008 but mine is clearly focused on performance penalty. I am clearly biased to segment the problem using databases from a logical point of view.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
If you don’t need to join data from several databases, splitting the data into multiple databases is the cleaner solution, and there should be no performance disadvantages.
Yes. The main problem is that you can’t reuse connections. Which means that you will have more connection, which means that you will use more memory.
OTOH separate DBs means that each DB stats will be smaller.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂