27

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.

Improve this question

3 Answers 3

Reset to default
42

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

10

Disaster recovery - all services vs individual

You can dump and restore one database at a time. You can dump and restore one schema at a time. You can also dump schemas that match a pattern.

Security between services

I presume you mean isolation between databases and isolation between schemas. The isolation between databases is stronger and more "natural" for developers concerned with "ease of use". For example, if you use one database per service, every developer can just use the public schema for all development. This might seem "easier" than adding schemas to the search path, or "easier" than using schema.object when programming.

It depends in part on how you manage privileges for the roles you use for development, and on how you manage privileges in each database or schema. You can change default privileges.

Performance

I don't see a measurable difference. YMMV.

Improve this answer

Comments

0

I recommend going with separate databases, for 2 reasons:

  1. Service independence

    Since your services don't share data, it looks that they are microservices.

    Amazon has an article on this:

    each microservice has its own data storage. In microservices architectures, data independence is more important than reusability

    A shared database breaks independence and violates the core idea why we separate services in the first place.

  2. Ease of deployment and change

    AWS RDS will manage databases for you. You can easily add new services, remove an unnecessary one, restore after a failed upgrade etc. If you go with schemas, you're on your own.

As for other criteria that you mention, I think they are covered by the aforementioned two:

  • Database backups are managed by AWS too, so it doesn't matter 1 or 100 backups
  • Performance could be tuned in any architecture, but it's easier to do in separate independent environments
  • Separate databases increase the surface for attack but decrease the damage per breach. I'd say the security is the same and is not a factor here

The only sane reason to go with a single database that I see could be costs.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.