I’ve managed a system with millions of users and tens of billions of rows, and I always dreamed of DB per user. Generally, ~1% of users were active at a given t

I’ve managed a system with millions of users and tens of billions of rows, and I always dreamed of DB per user. Generally, ~1% of users were active at a given time, but a lot of resources were used for the 99% who were offline (eg, indexes in memory where 99% of the data wouldn’t be needed). Learned a few tricks. If this is the problem you're trying to solve, some tips below. Start by optimizing your indexes. Ensure customer_id is in every index and is the first item. This allows the query to immediately filter to only rows for this customer. Ensure all queries include a customer_id (should be doing this anyway in multi-tenant environment). Even single row lookups by primary key can be really sped up this way; once the index becomes bigger than memory it has to be paged in/out. However with this approach the entire sub-tree of the index for “hot” users can remain in memory without paging, increasing cache hit rate, speeding up queries, and reducing IO. The above is generally enough. However, some consumer apps have a ton of data per user and relatively low revenue per user. In this case there’s one more big trick to keep DB costs down: cluster the whole table by customer_id. Like the index problem, the row data can be inefficient. If your disk layout randomly places rows into pages, chances are there’s only one row for a given client on a given page. If you need to evaluate 1000 rows to do a a query, you’ll have to read close to 1000 pages, and IO is slow/expensive. You’ll use a ton of memory caching pages where very few of the rows are for hot/active users Note: this problem only really matters if your rows are small and you can fit many rows per page. To fix, cluster the table by customer_id. On MySQL+InnoDB this is easy (https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.h...). On Postgres there’s a CLUSTER command but it’s one time, locking and it will take forever; MySQL is the easier solution (and I really prefer Postgres). Lots of the NoSQL DBs allow you to control the physical layout. Once setup, your cache hit rates will go way up, total IO will go way down, and you'll see a pretty good speedup. As always, benchmark a lot for your use case before implementing (but don't forget to benchmark the hot/cold use cases). PS — I remember a post a few days ago about a company running a distributed SQLite (many DBs). Looked really interesting but can’t find the link. For smaller scales than millions of users, look into partitions.