A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.
Answer from kquinn on Stack OverflowI work as a web developer and have experience with both Oracle and SQL Server but I've started to look into PostgreSQL, to begin with for personal projects but I'll hopefully be able to move the company at least partially over from Oracle to PostgreSQL in the future.
I've set up a dedicated machine running PostgreSQL and so far I'm really liking what I'm seeing and reading. My only stumble block so far is more of a question of best practice and is basically when to use databases and when to use schemas. As far as I can tell they can both be used to solve many of the same task, i.e. setting a tablespace and namespacing objects. The only difference I've seen so far is that roles are separate between databases. So how do you choose when to create a new database and when to just use a new schema?
For my personal projects I'm leaning towards just using schemas since I'd prefer to have roles be able to access multiple schemas. Is there any good reasons to instead use databases?
Oh, and does the default database "postgres" have any special meaning(like the template databases), or should I just create my schemas in it and not bother creating a new database?
Is it better to use multiple databases with one schema each, or one database with multiple schemas? - Stack Overflow
Example where schema are better than multiple databases - Database Administrators Stack Exchange
Can someone help me understand schemas and how they relate to databases?
Is it possible to compare two databases?
Videos
A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.
I would recommend against accepted answer - multiple databases instead of multiple schemas for this set of reasons:
- If you are running microservices, you want to enforce the inability to join between your "schemas", so the data is not entangled and developers won't end up joining other microservice's schema and wonder why when other team makes a change their stuff no longer works.
- You can later migrate to a separate database machine if your load requires with ease.
- If you need to have a high-availability and/or replication set up, it's better to have separate databases completely independent of each other. You cannot replicate one schema only compared to the whole database.