Question 90451 in PostgreSQL Question posted in
The official documentation can be found here.

saas with one database per client – Postgresql

I am designing a basic ERP (nodejs/express/postgresql-vue3/quasar), in which several businesses of different clients will be managed, some of these clients have several businesses with some branches, I should implement a server/database instance per customer or should I look to load balance and scale a single database in the future?

Tags:

2

Answers


  1. 0

    That is database tenancy aproach. Here is nice article on that.

    Personally, would recommend schema multi-tenancy for start (one client per schema) as it is basic ERP and it’s easier to manage and maintain single DB, and you can add specific changes for some clients on table design if needed

    You can use set search_path on pg connection for each client to direct queries to specific schema

    Login or Signup to reply.
  2. 0

    PostGreSQL has not be designed for VLDB, so you must evaluate the final volume for 3 to 5 years.
    If this volume will be over 300 Gb, it is preferable to split your customers into one database each.
    If this volume will be under, you can use SQL schemas.

    Beware of the number of files… PG create many file for each table… If there is too much files this will need a high consumption of resources. In this case, it will be necessary to split your system over many PG clusters…

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search