Re:PostgreSQL Schema Design
by nothrabannosir on Sunday March 29, 2020 @02:14PM
(#22721212)
But there's a middle layer between the database instance and the schema, which is also called a "database" (great naming /s). The "foo" in postgres://u:p@host/foo . What's that? So you have database host / instance, database "...", and schema (not to be confused with the schema being the column and view etc definition...!).
In the PostgreSQL world, the "database instance" is called a "cluster". A cluster contains multiple databases (and the definitions of roles and memberships and which roles can access which databases); a database contains multiple schemas; and a schema is where your tables/views/functions and other database objects live.
In your phrasing, a database (really a database INSTANCE) is similar to a whole server itself, and carries with it overhead such as memory allocation, etc.
When you put multiple schemas in a single instance, the resources allocated to the database instance can be shared, whereas if you have every app in its own instance, you can’t share things like working memory between them because they are in separate processes.