CODEWITHSUNDEEP
postgresqlamazon-web-servicesexpressmulti-tenantknex.js
jawn
jawn

Reputation: 1029

Multi-tenant DB in one DB using Postgres & AWS

I am a full stack software developer who has been itching to create my own software both for passive income and for career growth. The more I get into the weeds, the more I appreciate/respect DevOps and the infrastructure side of applications in general. I'm trying to build this app in a way that it is both scalable (up to a a hundred/thousand customers) and is not costly to start. The stack is angular/node leveraging postgres with aws infrastructure.

Based on the research I've done some approaches are: 1 db, table per customer (cheap/not scalable) 1 db, schema per customer (cheaper/more scalable) n dbs, db per customer (expensive, infinitely scalable)

I would like to go with option 2, or if there is another hybrid option out there. Could someone explain to me how option 2 would work? I plan on hosting the app on and ec2 instance and then the db possibly in aurora aws with each customer having replicated tables but within 1 db. Does anyone have any suggestions on how this can be implemented. Any input would be great.

Thanks in advance!

Upvotes: 0

Views: 516

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246818

I wonder how you arrive at your estimates for "cheap" and "scalable".

Having a single table for the data of all 1000 customers can work quite well. It all depends on the amount of data, the queries you run and the indexes you create. The speed of an index scan does not depends much on the size of the table.

Using a database per customer would scale just as well as having a schema per customer, that is, it won't scale well beyond a certain number of users. It is difficult to name a concrete number here, but 1000 might still work ok.

My verdict with respect to "cheap" and "scalable" is:

  • both suggested solutions are equally cheap or expensive

  • a single table will scale better with the number of customers

If scalability is a concern, I would look into sharding. Find a rule to distribute your customers equally across a number of different databases. In each database you use a single table for all customers on that shard. If the different databases can reside on different machines, you can scale almost without limits.

Upvotes: 1

Related Questions