There is an alternative solution: account-specific set of tables. Just add account id to table names like 'id_12345_posts' and 'id_12345_invoices'. To scale, shard them by account ID.
Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
141–150 of 264 posts
Re: Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
#142Earlier quoted context omitted.
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.
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 ro…
You roll the feature out incrementally to the users who are ready. You build backwards compatible features. Basic software engineering.
Re: Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
#143Earlier quoted context omitted.
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).
> You can always take a multi-tenant system and convert it into a single-tenant system a lot more easily. This is not true if your primary keys are int or bigint. It's also not true if you have any sort of unique indexes that are scoped to a table.
I think you are talking about problems going from multiple single-tenant systems to a single multi-tenant system. You parent is talking about the opposite.
Re: Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
#144Earlier quoted context omitted.
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.
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 ro…
I’m not a database architect, but can’t you just add the column or whatever as a nullable field in one migration, update the application to write to the column, and then make the field non-nullable in a further migration?
As I say I’m no expert but this is what I’ve done previously.
Re: Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
#145Earlier quoted context omitted.
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 ro…
I’m not a database architect, but can’t you just add the column or whatever as a nullable field in one migration, update the application to write to the column, and then make the field non-nullable in a further migration? As I say I’m no expert but this is what I’ve done previously.
You can, but a transactional database migration is much nicer, predictable and less prone to bugs.
Re: Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
#146In my case this worked out pretty well. Other than data separation and ease of scaling database per-customer (they might have different behavior of read/write operations), they other benefit was that we could place customer's database in any jurisdiction, which for some enterprise customers appeared an important point, regulations wise...
Re: Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
#147Earlier quoted context omitted.
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).
> You can always take a multi-tenant system and convert it into a single-tenant system a lot more easily. This is not true if your primary keys are int or bigint. It's also not true if you have any sort of unique indexes that are scoped to a table.
How is it not? Uniqueness would cease to matter between different tenants. They're unique by virtue of using a different database.
Re: Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
#148My memory might be a bit shaky on this, but I am pretty sure that Facebook was running "one solr instance per user" in the early days and that (Apache) Cassandra was developed from this idea.
Generally any large social network will need to follow the 1 database per account strategy to some extent because of the tricky many-to-many relationship that groups/users have with other groups/users- this creates a cartesian product that is too large to traverse in reasonable time with one large database.
This of course leads to a world of pain, and shouldn't be attempted unless there is really no other way to make it work.
Re: Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
#149Earlier quoted context omitted.
> You can always take a multi-tenant system and convert it into a single-tenant system a lot more easily. This is not true if your primary keys are int or bigint. It's also not true if you have any sort of unique indexes that are scoped to a table.
I think you are talking about problems going from multiple single-tenant systems to a single multi-tenant system. You parent is talking about the opposite.
No.
For example, HubSpot runs a multitenant system. URLs look like:
https://app.hubspot.com/section/$ACCOUNT_ID/etc/etc
In the simple, YAGNI implementation of this, when you create a new HubSpot account, most likely that will insert a new row into the accounts table, and the auto generated ID of that row will be your account ID. Therefore you need uniqueness to be enforced at that level.
If you want to start running a separate copy of the system, you need to refactor the system to move that sequence out of the database so that two different customers running on different clusters don't end up with the same account ID. This is just an example, but there are many problems like this that are caused by the assumption that the production system is a single unique system.
Re: Ask HN: Has anybody shipped a web app at scale with 1 DB per account?
#150Earlier quoted context omitted.
> You can always take a multi-tenant system and convert it into a single-tenant system a lot more easily. This is not true if your primary keys are int or bigint. It's also not true if you have any sort of unique indexes that are scoped to a table.
How is it not? Uniqueness would cease to matter between different tenants. They're unique by virtue of using a different database.
See my sibling comment with the Hubspot example. Even though the system might work internally, other things will break if you start having duplicate account IDs because other systems don't think of the account ID as a cluster-local identifier, but as a global one.