@ArtOfCode: For the (IMO rare) instances where we need to query cross-database, there are facilities to allow that.
How big of a trouble do you have in mind? Just a matter of repeated/multiple queries vs just one (unified), or something more limiting?
Unfortunately it appears that “schema” is a bit of an overloaded word and means something different in MySQL - but, assuming it carries the same meaning in Postgres as it does in SQL Server: a schema is analogous to a namespace in an application, while a database is similar to an application instance itself.
I prefer to have separate databases where application instances are entirely (or almost entirely, save for very few corner cases) independent; while reserving schema separation for concepts that pertain to each application instance (like we do with namespaces at the application level).
The advantages of one DB per site are that backup and general maintainance operations can be much faster (and follow different schedules), and taking down specific clusters without affecting the entire “network” is possible. In terms of runtime performance, it very much depends on the engine and I’m not familiar enough with Postgres to make any authoritative assertions, but in general, smaller DB sizes allows more optimal working of internal query optimizers, optimizing memory usage and also CPU usage to some degree, and it’s sometimes the case, though not always, that replication works better too. Optimal working doesn’t necessarily translate to better, perceptible performance, but it most certainly translates to cost savings.
Some reading material:
- https://stackoverflow.com/questions/5323740/difference-between-database-and-schema
- https://stackoverflow.com/questions/28951786/postgresql-multiple-database-vs-multiple-schemas
- https://www.postgresql.org/docs/8.1/ddl-schemas.html
The bold part: Not entirely sure about that, as it’s not the approach I’ve primarily had in mind… each site would, as I envision them, be a separate web application that runs on its own context. This differs from the current SE software topology (see Discord) but for it’s own, relevant set of reasons. Anyway, this right here reminds me we gotta talk, myself and devs, about this specifically. Will ping contributors over the chat.
No, unfortunately it’s much, much earlier than that, believe me. Some of our communities should one day hit impressive db size stats, and when the day comes we’ll be thankful we didn’t make the wrong decision to support that scenario.
[Edit] @manassehkatz, I’ll have to get back to your message later - tonight.