5 ms·

My startup currently does just this 'at scale', which is for us ~150 b2b customers with a total database footprint of ~500 GB. We are using Rails and the Apartm

• gomox 6y ago
  How does the architecture block major refactors or improvements? Are you running a single codebase for all your tenants, albeit with separate schemas for each? Edit: on reading the link you included, it seems like a lot of the problems are on the Rails implementation of the architecture with ActiveRecord and Apartment rather than with the architecture itself.
  • sky_rw 6y ago
    For me it falls into the category of decisions that are easy to make and difficult to un-make. If for whatever reason you decide this was the wrong choice for you, be in tech needs (e.g. rails) or business needs, merging your data and going back into a codebase to add this level of filtering is a massive undertaking.
    • gomox 6y ago
      Indeed, but if you are making a B2B enterprise/SMB SaaS, I think you are most likely to regret the opposite choice [1][2]. A lot of companies run a single instance, multitenant application and have to develop custom sharding functionality down the road when they realize the inevitable: that most joins and caches are only needed on strict subsets of the data that are tenant specific. If you get successful enough in this type of application space, you reach a mature state in which you need to be able to: * Run large dedicated instances for your largest customers, because either their performance or security requirements mandate it. * Share resources among a large number of smaller customers, for efficiency and fault tolerance reasons. You can get there in two ways: * You start with a massive multi tenant application, and you figure out a way to shard it and separate in pieces later. * You start with multiple small applications, and you develop the ability to orchestrate the group, and scale the largest of them. I would argue the latter is more flexible and cost efficient, and requires less technical prowess. [1] https://news.ycombinator.com/item?id=23307172 https://news.ycombinator.com/item?id=23307172 [2] https://news.ycombinator.com/item?id=23307139 https://news.ycombinator.com/item?id=23307139
      • cornishpixels 6y ago
        You can always take a multi-tenant system and convert it into a single-tenant system a lot more easily. First and foremost, you can simply run the full multi-tenant system with only a single tenant, which if nothing else enables progressive development (you can slowly remove those now-unnecessary WHERE clauses, etc).
        • gomox 6y ago
          True, but: In my experience by the time you reach this point you have a lot of operational complexity because you and your team are used to your production cluster being a single behemoth, so chances are it's not easy to stand up a new one or the overhead for doing so is massive (i.e. your production system grew very complex because there is rarely if ever a need to stand up a new one). Additionally, a multi tenant behemoth might be full of assumptions that it's the only system in town therefore making it hard to run a separate instance (i.e. uniqueness constraints on names, IDs, etc).
  • krainboltgreene 6y ago
    Here I'll give you one: If you want to change a property of the database that will give your specific use improved performance, you have no way to transactionally apply that change. Rolling back becomes a problem of operational scale, rolling out as well. What if you need to release some feature, but that feature requires a database feature enabled? Normally you enable it once, in a transaction hopefully, and then roll out your application. With this style you have to wait for N database servers to connect, enable, validate, then go live before you can even attempt the application being deployed, much less if you get it wrong.
    • lonelappde 6y ago
      You roll the feature out incrementally to the users who are ready. You build backwards compatible features. Basic software engineering.
      • sah2ed 6y ago
        > You roll the feature out incrementally to the users who are ready. You build backwards compatible features. Basic software engineering. The parent mentioned having to support ~150 B2B customers, so the effort is amplified x100 — more than 100 individual customers databases have to be coddled as you’ve described, albeit they are stuck with poor tooling to manage changes across their unusual architecture.
        • magicalhippo 6y ago
          While not a web-app, we too have ~200 B2B customers running our application and each one have their own DB. Some self-host, most others are hosted by us. We have written our own tool to upgrade the DB schema, it's nothing fancy, just takes an XML description, compares with the current DB and makes changes. However it ensures the upgrade process is automated. New versions of our application come with a new DB schema, and we also have an auto-updater for our software. We have a small launcher application which allows the users to select a few older versions of our software, so upgrades are almost always safe in the sense that if the user encounters a bug with the new version, they can try in the previous one. If the DB schema upgrade tool fails for some reason, the application upgrade will be held back and we'll get a notification. Combined this allows us to be quite aggressive with pushing out new versions, and to do so without manual intervention. A limitation with this setup is that DB changes have to be backwards compatible. So we do have a few cases of "if this table exists do this, else do that" type logic and similar, but mostly it's not a big deal. For example I recently had to refactor some old table where they had used five columns for some additional references (ref_1, ref_2 etc), into a new table of additional references. To handle this I just made a new view which would return either the refs from the new table, or the data from the columns in case the new table didn't have any data for that record. I then changed the application to use a grid instead of five separate edit controls, and to use this new view. If the user had to use an older version, that version would just write to the five fields and not populate a row in the new table, and the view would then return this data if the user later viewed it in the new version of our application. So the "overhead" of backwards compatibility in this case was just ensuring this would be sufficient and writing that view, so just a few minutes.
• bluejekyll 6y ago
  The link failed to medium for me. Would you mind describing in a little more detail some things? What DB are you using? How mature are your automation tools for managing these DB instances? Do you use a “single” DB server instance with a database defined per customer? Do you require all your customers to have identical schema, and do upgrade them all irrespective of individual customer concerns? Thanks in advance!
• rclayton 6y ago
  I totally get why you are doing this, but I would think DB management and migrations would be really troublesome. Do you have a migration framework to manage migrations across all catalogs?
• pgt 6y ago
  Can confirm, here be dragons. I did a DB per tenant for a local franchise retailer and it was the worst design mistake I ever made, which of course seemed justified at the time (different tax rules, what not), but we never managed to get off it and I spent a significant amount of time working around it, building ETL sync processes to suck everything into one big DB, and so on. Instead of a DB per tenant, or a table per tenant, just add a TenantId column on every table from day 1.
  • developuh 6y ago
    I do both. Have a tenant_id column in every table. This gives me flexibility to either host each client separately or club them together.
• qatanah 6y ago
  Can confirm the experience, I had to work on a company before with similar tech using Django. https://github.com/bernardopires/django-tenant-schemas https://github.com/bernardopires/django-tenant-schemas All I can say, that it was a nightmare to do deployment and migration and some backward incompatibility features restrained us in some ways. The company valued data isolation as a priority rather than ease of developing it. Hard to work with but great for data isolation.
• ryanbrunner 6y ago
  Echoing this as well, I worked for Influitive and was one of the original authours of apartment (sorry!) There are a lot of headaches involved with the "tenant per schema" approach. Certainly it was nice to never have to worry about the "customer is seeing data from another customer" bug (a death knell if you're in enterprisish B2B software), but it added so many problems: - Migrations become a very expensive and time-consuming process, and potentially fraught with errors. Doing continious-deployment style development that involves database schema changes is close to impossible without putting a LOT of effort into having super-safe migrations. - You'll run into weird edge cases due to the fact that you have an absolutely massive schema (since every table you have is multiplied by your number of tenants). We had to patch Rails to get around some column caching it was doing. - Cloud DB hosting often doesn't play nice with this solution. We continually saw weird performance issues on Heroku Postgres, particularly with backup / restores (Heroku now has warnings against this approach in their docs) - It doesn't get you any closer to horizontal scalability, since connecting to a different server is significantly different than connecting to another schema. - It will probably push the need for a dedicated BI / DW environment earlier than you would otherwise need it, due to the inability to analyze data cross-schema. I still think there's maybe an interesting approach using partioning rather than schemas that eliminates a lot of these problems, but apartment probably isn't the library to do it (for starters, migrations would be entirely different if partioning is used over schemas)
  • sitkack 6y ago
    This leads me to believe that everything you mentioned is already subtly broken, it is the new DB/account model that just exposes it. Is there something between the two solutions or pieces that could be modified that collapse the problem? What about going with DB account per app account and using views to limit exposure to data. If user level views are applied before the business logic has access, then the death knell scenario should be avoided. Migrations seem hard regardless, they have to be automated anyway and have a verified rollback/undo mechanism. Code should be multiversion aware if you are doing live upgrades. I am personally a fan of read-only mode while doing the last phase of an ETL upgrade on the backend. Snapshot databases, run a bunch of Hive jobs and throw it back down on a new set of DBs. In the cases you outlined where it was dangerous or fragile, I think those systems were already that way, and that fragility is a system of a different problem. Fix that and lots of other stuff disappears.
• itsthecourier 6y ago
  Some ideas about this: I have seen more than 200 dbs in rds with around 100 tables each working and migrating just fine. The author explicitly says he didn't dig up what the cause of the slow down was. About the rails problem he says activerecord always check all the tables in the schema and create a huge 500MB object with its properties. Apartment wasn't able to come to some solution, but that's a rails specific problem. About the joins, he should have use a reference to current_schema just as he needs to do it to tenant_id in joins About the serialization of IDs, it all depends on the business and whether they need unique series of referable short IDs and whether uuid is acceptable for them About migrations, of course, you need to use migrations with dbs on production. You cannot rely in a dev file to match the current state of production db. Been working with a huge amount of production dbs in my life without issues, just by using it Thinks I don't see mentioned and are important, the need to have a connection pool that allows schema switches, i.e. pg_bouncer About db_migrations, actually this method works. there are some rules floating around about how to do non-blocking of migrations, but let me tell you. At some point you will need them and it's way easier to do it incrementally one schema at the time, than just do it on the 500GB and failing for some records at some of the last GB What about two versions of the db running in production? That should be possible. You should be running v1 and v2 and automatically switching down v1 when no dbs are using it
• elialbert 6y ago
  want to chime in that this matches my experience doing something similar in B2B in django 10 years ago