Correct multitenancy design

Michael_Spector

Hello!

I wonder what would be the correct way do design a multi-tenant system for hundreds (maybe thousands) of customers. As far as I can see there are two ways:

1. Have customer_id in every table. Add "WHERE customer_id=..." to every query.
2. Have multiple schemas - one per customer.

What would be advantages and pitfalls of these methods?

Thanks!
Michael

Adrian_Oprea_1

I  think this depends on the characteristics of your data .
But i would go for multiple schema approach!
 Why ?

•  easy to customize
•  easy isolation
•  easy backup and recover 

Adrian_Oprea_1

I  think this depends on the characteristics of your data .
But i would go for multiple schema approach!
 Why ?

•  easy to customize
•  easy isolation
•  easy backup and recover 

Michael_Spector

Hello Adrian,

Thanks for your answer! Do you know what are resource limitations of the second method, like what number of schemas can be created on one node/whole database?

Thanks,
Michael

Evan_H

No limit on schemas.. 
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemLimits.htm?Highl...