Is there a performance penalty on using databases instead of schemas in a single PostgreSQL cluster?

Question

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. https://microservices.io/patterns/data/database-per-service.html

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.

Does this answer your question? Example where schema are better than multiple databases — mustaccio, Jun 13 '22 at 12:54
Thanks but, as I said, they will end up as separate dbs on separate hosts. Schemas ate not an option — coterobarros, Jun 13 '22 at 12:58

score 3 · Accepted Answer · answered Jun 13 '22 at 15:46

3

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.

answered Jun 13 '22 at 15:46

Laurenz Albe

51,298
4
39
69

Thank you Laurenz. I suppose that no extra PG cluster processes are launched for every database, right? – coterobarros Jun 13 '22 at 15:53
1

Right. They also share the same cache. – Laurenz Albe Jun 13 '22 at 19:51

score 1 · Answer 2 · answered Jun 13 '22 at 15:42

1

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.

answered Jun 13 '22 at 15:42

eijeze

111
1

1

That need not be a problem. Each database will receive fewer requests, so you can use several smaller connection pools. – Laurenz Albe Jun 13 '22 at 15:44
Thank you. I'm ok with that (having more connections). I was expecting something more painful, as having a bunch of separate processes per database or something like that. – coterobarros Jun 13 '22 at 15:52
Sure. You will have separate processes per database. For each connection, as always. – eijeze Jun 13 '22 at 15:52
Yes, that is reasonable. – coterobarros Jun 13 '22 at 15:55

Is there a performance penalty on using databases instead of schemas in a single PostgreSQL cluster?

2 Answers2