Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgresql Multiple Database VS Multiple Schemas

We are in the process of building a cluster for our hosted services at work, the final product will be used to host multiple separate services. We are in the middle of deciding on how we want to setup our databases. We are running a postgresql database server which all services in the cluster will use. The debate right now is whether to give each service its own schema in a single database or to give each service its own database.

We just aren't sure which is the better solution for us. None of our services have a common structure and data does not need to be shared. What we are more concerned about is ease of use.

Here's what we care most about, we are really hoping for an objective vs opinion based answer.

Backups

Disaster recovery - all services vs individual

Security between services

Performance

For some additional information, the cluster is hosted within AWS with our database being an RDS instance.

like image 763
Colin Murphy Avatar asked Mar 09 '15 21:03

Colin Murphy


People also ask

What is difference between schema and database in Postgres?

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators.

Is it better to have one database or multiple databases?

Single database to backup, less maintainance. You don't need to manage multiple connections. Multiple databases can break the chance to perform atomic transactions, a feature I would never throw away. You avoid synchronizing two or more databases to avoid integrity problems.

Can Postgres have multiple databases?

PostgreSQL can have multiple databases in each instance. Each database can have multiple schemas. Each schema can have multiple tables.

Should you use multiple databases?

Within systematic reviews, when searching for relevant references, it is advisable to use multiple databases. However, searching databases is laborious and time-consuming, as syntax of search strategies are database specific.


1 Answers

This is what PostgreSQL official docs says:

Databases are physically separated and access control is managed at the connection level. If one PostgreSQL server instance is to house projects or users that should be separate and for the most part unaware of each other, it is therefore recommendable to put them into separate databases. If the projects or users are interrelated and should be able to use each other's resources they should be put in the same database, but possibly into separate schemas. Schemas are a purely logical structure and who can access what is managed by the privilege system.

Source: http://www.postgresql.org/docs/8.0/static/managing-databases.html

like image 156
sunny Avatar answered Sep 20 '22 08:09

sunny