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?

CodePudding user response：

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

CodePudding user response：

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...