Skip to main content

Correct multitenancy design

  • May 28, 2014
  • 4 replies
  • 21 views
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

    4 replies

    Adrian_Oprea_1
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • May 29, 2014
    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
    Forum|alt.badge.img+1
    • Adrian_Oprea_1
    • Participating Frequently
    • May 29, 2014
    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
    Forum|alt.badge.img
    • Evan_H
    • Participating Frequently
    • May 30, 2014
    No limit on schemas.. 
    https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/SystemLimits.htm?Highl...

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement