Hacker News Reader

Comment by internetter | Hacker News Reader

Something I've been thinking about is partitioning my SQLite. Instead of storing all user's data in one mega table, what if I made a SQLite database for each user? Provided users never talk to each other, I think this might work?

View on HN

> Instead of storing all user's data in one mega table, what if I made a SQLite database for each user? Provided users never talk to each other, I think this might work?

I'm doing this in a project I'm developing for language learning, except that you have both shared databases for content, and individual databases for view logs, preferences, and so on. What I actually do is open a :memory: database, ATTACH all the appropriate databases. Transactions work just fine, but because the shared database is basically read-only, then there's no write contention because each user is just writing to their own database. Overall it makes queries easier, because you don't even need to include the user (or the language). (Of course, the flip side is that getting stats on all the users and languages is more difficult.)

Currently it's just single server, but it should be possible to read-replicate the content, and actually move the write replica of the study database to a local server. It should also make it straightforward to let people download their own information: just hand them the actual SQLite file.

If I ever grow large enough that I need multiple servers in different geos, I'll write up my experience and post it here.

You can attach to databases dynamically in queries and join across them. I probably wouldn't (in an ordinary data model) do per-user, but I would consider it for different functional areas.

There's a limit on how many databases you can attach to the same connection (SQLITE_LIMIT_ATTACHED), it defaults to 10.

Worth noting that this limit can be raised up to 125 (as I'm sure Simon is aware).

I would say that if one needs to query across more SQLite files than that, it's definitely time for a different data policy.

I believe this is what one of the companies mentioned in the article, Turso, can help you do. a per-tenant database.

If you want to query across users, which you probably want for analytics, that is going to be a massive PITA.

You should probably use a specialized DB for analytics (a.k.a. OLAP DB) anyway. As long as you have an automated way of replicating data from SQLite to your OLAP DB, everything should be fine.

You can slice it and dice it any way you want, really. The constraint is often what data needs to be written within a transaction. You'll have to figure our a way to reliably apply a consistent schema to all these database files somehow and keep track of them.

One of the things I appreciate about SQLite is being able to keep all the schema initialization and upgrades in the application itself, which are then checked into git and can be tested like mad with throw-away copies of the data.

Here's a package in golang I wrote to help with that process:

https://pkg.go.dev/gitlab.com/martyros/sqlutil@v0.0.0-202312...

This setup can work great and even support elements that are shared between users, if you also give those elements their own DB. I’m working on a prototype to support this natively in Prisma.

at least for awhile this is how bluesky/atproto worked. afaik they only ran into issues when the number of users on each server overwhelmed how many files would fit comfortably in a single directory (which is obviously a large number)

https://news.ycombinator.com/item?id=38171322

that's still how it works, we just shard our users across multiple hosts

Sqlite handles many tables, per tenant is more reasonable.

Reply on news.ycombinator.com