<story><title>Ask HN: Has anybody shipped a web app at scale with 1 DB per account?</title><text>A common way of deploying a web application database at scale is to setup a MySQL or Postgres server, create one table for all customers, and have an account_id or owner_if field and let the application code handle security. This makes it easier to run database migrations and upgrade code per customer all at once.&lt;p&gt;I’m curious if anybody has taken the approach of provisioning one database per account? This means you’d have to run migrations per account and keep track of all the migration versions and statuses somewhere. Additionally, if an application has custom fields or columns, the differences would have to be tracked somehow and name space collisions managed.&lt;p&gt;Has anybody done this? Particularly with Rails? What kinda of tools or processes did you learn when you did it? Would you do it again? What are some interesting trade offs between the two approaches?</text></story><parent_chain><item><author>sky_rw</author><text>My startup currently does just this &amp;#x27;at scale&amp;#x27;, which is for us ~150 b2b customers with a total database footprint of ~500 GB. We are using Rails and the Apartment gem to do mutli-tenancy via unique databases per account with a single master database holding some top-level tables.&lt;p&gt;This architecture decisions is one of my biggest regrets, and we are currently in the process of rebuilding into a single database model.&lt;p&gt;FWIW, this process has worked well for what it was originally intended to do. Data-security has a nice db level stopgap and we can keep customer data nicely isolated. It&amp;#x27;s nice for extracting all data from a single customer if we have extended debugging work or unique data modeling work. It saves a lot of application layer logic and code. I&amp;#x27;m sure for the most part it makes the system slightly faster.&lt;p&gt;However as we have grown this has become a huge headache. It is blocking major feature refactors and improvements. It restricts our data flexibility a lot. Operationally there are some killers. Data migrations take a long time, and if they fail you are left with multiple databases in different states and no clear sense of where the break occurred.&lt;p&gt;Lastly, if you use the Apartment gem, you are at the mercy of a poorly supported library that has deep ties into ActiveRecord. The company behind it abandoned this approach as described here: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;influitive.io&amp;#x2F;our-multi-tenancy-journey-with-postgres-schemas-and-apartment-6ecda151a21f&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;influitive.io&amp;#x2F;our-multi-tenancy-journey-with-postgre...&lt;/a&gt;&lt;p&gt;Happy to expand on this if anybody is interested. It&amp;#x27;s currently a cause of major frustration in my life.</text></item></parent_chain><comment><author>ryanbrunner</author><text>Echoing this as well, I worked for Influitive and was one of the original authours of apartment (sorry!)&lt;p&gt;There are a lot of headaches involved with the &amp;quot;tenant per schema&amp;quot; approach. Certainly it was nice to never have to worry about the &amp;quot;customer is seeing data from another customer&amp;quot; bug (a death knell if you&amp;#x27;re in enterprisish B2B software), but it added so many problems:&lt;p&gt;- 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.&lt;p&gt;- You&amp;#x27;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.&lt;p&gt;- Cloud DB hosting often doesn&amp;#x27;t play nice with this solution. We continually saw weird performance issues on Heroku Postgres, particularly with backup &amp;#x2F; restores (Heroku now has warnings against this approach in their docs)&lt;p&gt;- It doesn&amp;#x27;t get you any closer to horizontal scalability, since connecting to a different server is significantly different than connecting to another schema.&lt;p&gt;- It will probably push the need for a dedicated BI &amp;#x2F; DW environment earlier than you would otherwise need it, due to the inability to analyze data cross-schema.&lt;p&gt;I still think there&amp;#x27;s maybe an interesting approach using partioning rather than schemas that eliminates a lot of these problems, but apartment probably isn&amp;#x27;t the library to do it (for starters, migrations would be entirely different if partioning is used over schemas)</text></comment>
<story><title>Ask HN: Has anybody shipped a web app at scale with 1 DB per account?</title><text>A common way of deploying a web application database at scale is to setup a MySQL or Postgres server, create one table for all customers, and have an account_id or owner_if field and let the application code handle security. This makes it easier to run database migrations and upgrade code per customer all at once.&lt;p&gt;I’m curious if anybody has taken the approach of provisioning one database per account? This means you’d have to run migrations per account and keep track of all the migration versions and statuses somewhere. Additionally, if an application has custom fields or columns, the differences would have to be tracked somehow and name space collisions managed.&lt;p&gt;Has anybody done this? Particularly with Rails? What kinda of tools or processes did you learn when you did it? Would you do it again? What are some interesting trade offs between the two approaches?</text></story><parent_chain><item><author>sky_rw</author><text>My startup currently does just this &amp;#x27;at scale&amp;#x27;, which is for us ~150 b2b customers with a total database footprint of ~500 GB. We are using Rails and the Apartment gem to do mutli-tenancy via unique databases per account with a single master database holding some top-level tables.&lt;p&gt;This architecture decisions is one of my biggest regrets, and we are currently in the process of rebuilding into a single database model.&lt;p&gt;FWIW, this process has worked well for what it was originally intended to do. Data-security has a nice db level stopgap and we can keep customer data nicely isolated. It&amp;#x27;s nice for extracting all data from a single customer if we have extended debugging work or unique data modeling work. It saves a lot of application layer logic and code. I&amp;#x27;m sure for the most part it makes the system slightly faster.&lt;p&gt;However as we have grown this has become a huge headache. It is blocking major feature refactors and improvements. It restricts our data flexibility a lot. Operationally there are some killers. Data migrations take a long time, and if they fail you are left with multiple databases in different states and no clear sense of where the break occurred.&lt;p&gt;Lastly, if you use the Apartment gem, you are at the mercy of a poorly supported library that has deep ties into ActiveRecord. The company behind it abandoned this approach as described here: &lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;influitive.io&amp;#x2F;our-multi-tenancy-journey-with-postgres-schemas-and-apartment-6ecda151a21f&quot; rel=&quot;nofollow&quot;&gt;https:&amp;#x2F;&amp;#x2F;influitive.io&amp;#x2F;our-multi-tenancy-journey-with-postgre...&lt;/a&gt;&lt;p&gt;Happy to expand on this if anybody is interested. It&amp;#x27;s currently a cause of major frustration in my life.</text></item></parent_chain><comment><author>pgt</author><text>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.&lt;p&gt;Instead of a DB per tenant, or a table per tenant, just add a TenantId column on every table from day 1.</text></comment>