10 ms·

Django and SaaS

38 points by neeleshs 16y ago

callmeed 16y ago
"As in a typical multi-tenant architecture, one database per customer." Maybe I've misunderstood what "multi-tenant" means for the last 5 years, but I always thought of it as "multiple customers on a single installation/instance and using a single database" ... as in, every customer has some sort of account model other models are scoped to the account. I'm sure there are valid reasons for having a unique db per customer (we do it in one product) but it seems easier to maintain/backup a single one.
- snprbob86 16y ago
  If your data is easily and completely partitioned by customer, it may make sense to do so. It's as easy a partitioning scheme you can come up with. It gives you the power to do staged roll outs and other flexibility in deployment, as well as security isolation that makes your life easier and some customers may prefer.
niels 16y ago
I'm confused... why would they ever come up with a solution like this?
- mattchew 16y ago
  I assume by "like this" you mean a separate db for each customer. The reason is probably a perceived improvement in security. I've worked in systems where they've done this for security reasons. I believe Joel Spolsky did it for this reason when he was betaing a release of product (FogBugz?). It certainly does jack up the maintenance overhead. I wouldn't design a system this way myself. I'm really curious if this is in fact a common practice out there in the wide world.
  - neeleshs 16y ago
    "It certainly does jack up the maintenance overhead." A bit of automation would solve this problem,as far as I can see it. "I'm really curious if this is in fact a common practice out there in the wide world." It is. I've worked for companies which do this, and have scaled with Oracle for several tens of thousands of customers (=databases). Also, complex reports that require hand-coded SQL becomes that much simpler. (Compared to having all customers' data in a single database)
- tomjen3 16y ago
  It makes it dead simple, among other things, to export the companies data should the company wish that.
  - endlessvoid94 16y ago
    I mean, any other export would just be the result of some query. And it doesn't have to be a complicated query, either. Just "WHERE customer_id=5" or whatever. Or am I missing something?
    - neeleshs 16y ago
      There are lot of cases where the query will indeed be complicated. Reports, for example. I have seen SQL queries reports which are quite complex, making sure that such SQL is always qualified with WHERE customer_id=5 is a maintenance nightmare. There is also the customer perception thing, as petethomas said in this thread.
- matclayton 16y ago
  Scaling, it provides cheap, quick sharding from Day 1. If your db is overloaded, just move some to a second machine. FB used a similar approach with mysql and isolated university databases in the early days, I believe.
idan 16y ago
FWIW, I'm no expert but... I don't really see much of a security benefit to "one DB per customer". The actual data will be stored on the same disks, the queries issued to through the same DB processes. This seems like a huge amount of overhead for very little realized gain.
- edanm 16y ago
  snprbob86's point from another comment makes sense: it gives you more control. For example, doing staged rollouts of different features for different customers, etc. I'm not saying it's worth the headaches, but there are advantages.
  - snprbob86 16y ago
    Also consider behind the firewall installs or premium dedicated hosting. One of the nice things about enterprise software is that you can charge for things like this. If you have a large enough customer, even if you are hosting, you can simply forward to them your bill for a dedicated server.
- yesimahuman 16y ago
  Poorly crafted and tested SQL queries or django ORM calls accidentally exposing customer information? Shouldn't happen, but it very well could.
ezmobius 16y ago
This approach will not scale. In fact vie seen it personally with multiple of my hostingnclients fall over after even 5000 databases. There has to be a file per table with innodb usually and this approach will work until younhave a few thousand customersmand then you will feel the pain of refactoring this intoms true multi tenant db system
- jseifer 16y ago
  Have you seen similar problems with Postgres? That's what the author is using.
chrismsnz 16y ago
Seems like row-level permissions seems to be a better paradigm to make this work. Last I checked, Django had some support for it - recently improved in their 1.2 release. Here's a Django Advent post, published while they were ramping up for the 1.2 release about row-level/object permissions: http://djangoadvent.com/1.2/object-permissions/ http://djangoadvent.com/1.2/object-permissions/
StavrosK 16y ago
This solution seems overcomplicated. It might work fine, I've never tried it, but I can't imagine databases (even postgres) being optimized for such things. The way we did it at http://historio.us http://historio.us was to just have a foreign key to each user (this can be easily extended to groups of users, i.e. companies) and just performed a lookup of the related fields depending on the domain. This has worked very well so far, it's very fast, scalable and has given us zero problems. Since all the tables are the same, I imagine it should work very well for the poster, with an appropriate index, too...
petethomas 16y ago
The main advantage of one-database-per-business-customer isn't isolating customer data, it's the ability to serve business customers who are willing to pay for heavy customizations and the extra support those entail, while not polluting your core offering through the introduction of attributes, features, etc. for which other customers will have no use. For a lot of B2B web applications aimed at serving "bigger" companies, customer adoption can still hinge on scotch-sipping and rounds of golf more than well-placed sign-up buttons or great viral marketing campaigns. It's been argued of late that maybe this part of the industry is becoming more "web-like"[1], but in any case it's not very web-like yet, when it comes to sealing deals. Many times the companies who were convinced by your talented sales team to buy your product eventually demand that the product be tailored to their (sometimes very company-specific) needs. If you want to keep the customer happy, or maybe just keep the customer, you have to keep customizing. One could make the argument that the most talented sales teams are the ones that sign deals with customers who are willing to "stay on core", but over time as your customers' own businesses evolve, the challenge grows for everyone involved. [1] http://techcrunch.com/2010/07/25/enterprise-software-is-sexy-again/ http://techcrunch.com/2010/07/25/enterprise-software-is-sexy...
- danieldon 16y ago
  it's the ability to serve business customers who are willing to pay for heavy customizations and the extra support those entail You'd need to have flags in the app itself, which would likely turn into an unmaintainable monstrosity. If you have a handful of large clients, are heavily customizing the app for each one, are using a modern framework like Django or Rails and a DVCS, the obvious maintainable approach is to have full individual installs, forks for each install and a modular application that pushes the customizations into pluggable components as much as possible.
  - pilif 16y ago
    Having a shared core and a lot of hooks for customization is the road we have taken for our application. I've written a lengthy post about it just last friday: http://news.ycombinator.com/item?id=1557218 http://news.ycombinator.com/item?id=1557218
danieldon 16y ago
The 1 DB/customer approach is usually a bad idea. Strict data security requirements are one of the only situations this approach makes sense compared to the traditional single DB approaches or individual installs.
ibejoeb 16y ago
No! Schemas! I don't usually go on tirades like this, but this is what I do for a living. You'll want to use schemas vs. databases. Not only is it significantly more convenient (i.e., you'll administer 1 database instead of n), it will let you scale. For instance, a postgres database has a pool of shared memory that it uses for query plans, caching, etc., and you'll be able to optimize the use of it across all of your clients, rather than one by one. Related is the impact on connection resources. A connection to a database may be used for multiple schemas, so you'll be able to make much better use of those. Suppose you have 1000 clients, which each typically have 1-2 users on your application when it's typically in use. In the multiple database config, you'll need one connection pool per database, each pool having at least 1 open connection, so that under no load you're still maintaining 1000 connections. With the schemas approach, you can maximize the throughput and balance the connections proportionally to your active users rather than the number of customers you have. Oh, one more biggie: remember the shared buffers? Those are typically per-session (connection). So if you frequently run "select price from widgets where category=?", you'll want to parse, rewrite, plan, compile, and cache that as infrequently as possible, so keep those connections and sessions open as long as possible so you get the benefit of prepared statements. You will see load go down if you do this, from reduced CPU for parsing and plan generation, and reduced system wait since you won't be churning your shared buffers as much.
- neeleshs 16y ago
  Very helpful comment. Indeed, schemas are the way to go. Too many benefits against creating a database per customer to ignore.
- phugoid 16y ago
  Could you please clarify what you mean by "schemas"? My understanding is that a schema is an abstract definition of what your database fields/relations are. So that's too vague for me. Adding a customer_id field to every single table in the database would technically fit the definition of "schema", but I suspect you mean something else altogether. Please elaborate.
  - neeleshs 16y ago
neeleshs 16y ago
Based on ibejoeb's comments, there is an updated entry at http://tidbids.posterous.com/ http://tidbids.posterous.com/ which uses one schema per user. Thanks ibejoeb!

38 points by neeleshs 16y ago

"As in a typical multi-tenant architecture, one database per customer." Maybe I've misunderstood what "multi-tenant" means for the last 5 years, but I always thought of it as "multiple customers on a single installation/instance and using a single database" ... as in, every customer has some sort of account model other models are scoped to the account. I'm sure there are valid reasons for having a unique db per customer (we do it in one product) but it seems easier to maintain/backup a single one.

snprbob86 16y ago
If your data is easily and completely partitioned by customer, it may make sense to do so. It's as easy a partitioning scheme you can come up with. It gives you the power to do staged roll outs and other flexibility in deployment, as well as security isolation that makes your life easier and some customers may prefer.

niels 16y ago

I'm confused... why would they ever come up with a solution like this?

mattchew 16y ago
I assume by "like this" you mean a separate db for each customer. The reason is probably a perceived improvement in security. I've worked in systems where they've done this for security reasons. I believe Joel Spolsky did it for this reason when he was betaing a release of product (FogBugz?). It certainly does jack up the maintenance overhead. I wouldn't design a system this way myself. I'm really curious if this is in fact a common practice out there in the wide world.
- neeleshs 16y ago
  "It certainly does jack up the maintenance overhead." A bit of automation would solve this problem,as far as I can see it. "I'm really curious if this is in fact a common practice out there in the wide world." It is. I've worked for companies which do this, and have scaled with Oracle for several tens of thousands of customers (=databases). Also, complex reports that require hand-coded SQL becomes that much simpler. (Compared to having all customers' data in a single database)
tomjen3 16y ago
It makes it dead simple, among other things, to export the companies data should the company wish that.
- endlessvoid94 16y ago
  I mean, any other export would just be the result of some query. And it doesn't have to be a complicated query, either. Just "WHERE customer_id=5" or whatever. Or am I missing something?
  - neeleshs 16y ago
    There are lot of cases where the query will indeed be complicated. Reports, for example. I have seen SQL queries reports which are quite complex, making sure that such SQL is always qualified with WHERE customer_id=5 is a maintenance nightmare. There is also the customer perception thing, as petethomas said in this thread.
matclayton 16y ago
Scaling, it provides cheap, quick sharding from Day 1. If your db is overloaded, just move some to a second machine. FB used a similar approach with mysql and isolated university databases in the early days, I believe.

idan 16y ago

FWIW, I'm no expert but... I don't really see much of a security benefit to "one DB per customer". The actual data will be stored on the same disks, the queries issued to through the same DB processes. This seems like a huge amount of overhead for very little realized gain.

edanm 16y ago
snprbob86's point from another comment makes sense: it gives you more control. For example, doing staged rollouts of different features for different customers, etc. I'm not saying it's worth the headaches, but there are advantages.
- snprbob86 16y ago
  Also consider behind the firewall installs or premium dedicated hosting. One of the nice things about enterprise software is that you can charge for things like this. If you have a large enough customer, even if you are hosting, you can simply forward to them your bill for a dedicated server.
yesimahuman 16y ago
Poorly crafted and tested SQL queries or django ORM calls accidentally exposing customer information? Shouldn't happen, but it very well could.

ezmobius 16y ago

This approach will not scale. In fact vie seen it personally with multiple of my hostingnclients fall over after even 5000 databases. There has to be a file per table with innodb usually and this approach will work until younhave a few thousand customersmand then you will feel the pain of refactoring this intoms true multi tenant db system

jseifer 16y ago
Have you seen similar problems with Postgres? That's what the author is using.

chrismsnz 16y ago

Seems like row-level permissions seems to be a better paradigm to make this work. Last I checked, Django had some support for it - recently improved in their 1.2 release. Here's a Django Advent post, published while they were ramping up for the 1.2 release about row-level/object permissions: http://djangoadvent.com/1.2/object-permissions/ http://djangoadvent.com/1.2/object-permissions/

StavrosK 16y ago

This solution seems overcomplicated. It might work fine, I've never tried it, but I can't imagine databases (even postgres) being optimized for such things. The way we did it at http://historio.us http://historio.us was to just have a foreign key to each user (this can be easily extended to groups of users, i.e. companies) and just performed a lookup of the related fields depending on the domain. This has worked very well so far, it's very fast, scalable and has given us zero problems. Since all the tables are the same, I imagine it should work very well for the poster, with an appropriate index, too...

petethomas 16y ago

The main advantage of one-database-per-business-customer isn't isolating customer data, it's the ability to serve business customers who are willing to pay for heavy customizations and the extra support those entail, while not polluting your core offering through the introduction of attributes, features, etc. for which other customers will have no use. For a lot of B2B web applications aimed at serving "bigger" companies, customer adoption can still hinge on scotch-sipping and rounds of golf more than well-placed sign-up buttons or great viral marketing campaigns. It's been argued of late that maybe this part of the industry is becoming more "web-like"[1], but in any case it's not very web-like yet, when it comes to sealing deals. Many times the companies who were convinced by your talented sales team to buy your product eventually demand that the product be tailored to their (sometimes very company-specific) needs. If you want to keep the customer happy, or maybe just keep the customer, you have to keep customizing. One could make the argument that the most talented sales teams are the ones that sign deals with customers who are willing to "stay on core", but over time as your customers' own businesses evolve, the challenge grows for everyone involved. [1] http://techcrunch.com/2010/07/25/enterprise-software-is-sexy-again/ http://techcrunch.com/2010/07/25/enterprise-software-is-sexy...

danieldon 16y ago
it's the ability to serve business customers who are willing to pay for heavy customizations and the extra support those entail You'd need to have flags in the app itself, which would likely turn into an unmaintainable monstrosity. If you have a handful of large clients, are heavily customizing the app for each one, are using a modern framework like Django or Rails and a DVCS, the obvious maintainable approach is to have full individual installs, forks for each install and a modular application that pushes the customizations into pluggable components as much as possible.
- pilif 16y ago
  Having a shared core and a lot of hooks for customization is the road we have taken for our application. I've written a lengthy post about it just last friday: http://news.ycombinator.com/item?id=1557218 http://news.ycombinator.com/item?id=1557218

danieldon 16y ago

The 1 DB/customer approach is usually a bad idea. Strict data security requirements are one of the only situations this approach makes sense compared to the traditional single DB approaches or individual installs.

ibejoeb 16y ago

No! Schemas! I don't usually go on tirades like this, but this is what I do for a living. You'll want to use schemas vs. databases. Not only is it significantly more convenient (i.e., you'll administer 1 database instead of n), it will let you scale. For instance, a postgres database has a pool of shared memory that it uses for query plans, caching, etc., and you'll be able to optimize the use of it across all of your clients, rather than one by one. Related is the impact on connection resources. A connection to a database may be used for multiple schemas, so you'll be able to make much better use of those. Suppose you have 1000 clients, which each typically have 1-2 users on your application when it's typically in use. In the multiple database config, you'll need one connection pool per database, each pool having at least 1 open connection, so that under no load you're still maintaining 1000 connections. With the schemas approach, you can maximize the throughput and balance the connections proportionally to your active users rather than the number of customers you have. Oh, one more biggie: remember the shared buffers? Those are typically per-session (connection). So if you frequently run "select price from widgets where category=?", you'll want to parse, rewrite, plan, compile, and cache that as infrequently as possible, so keep those connections and sessions open as long as possible so you get the benefit of prepared statements. You will see load go down if you do this, from reduced CPU for parsing and plan generation, and reduced system wait since you won't be churning your shared buffers as much.

neeleshs 16y ago
Very helpful comment. Indeed, schemas are the way to go. Too many benefits against creating a database per customer to ignore.
phugoid 16y ago
Could you please clarify what you mean by "schemas"? My understanding is that a schema is an abstract definition of what your database fields/relations are. So that's too vague for me. Adding a customer_id field to every single table in the database would technically fit the definition of "schema", but I suspect you mean something else altogether. Please elaborate.
- neeleshs 16y ago

neeleshs 16y ago

Based on ibejoeb's comments, there is an updated entry at http://tidbids.posterous.com/ http://tidbids.posterous.com/ which uses one schema per user. Thanks ibejoeb!