Bluesky migrates to single-tenant SQLite

Love SQLite - in general there are many challenges with a schema or database per tenant setup of any kind though. Consider the luxury of row-level security in a shared instance where your migration either works or rolls back. Not now! If you are doing a data migration and failed to account for some unexpected data, now you have people on different schema versions until you figure it out. Now, yes, if you are at sharding scale this may occur anyway, but consider that before you hit that point, a single database is easiest.<p>You will possibly want to combine the data for some reason in the future as well. Or, move ownership of resources atomically.<p>I&#x27;m not opposed to this setup at all and it does have its place. But we are running away from schema-per-tenant setup at warp speed at work. There are so many issues if you don&#x27;t invest in it properly and I don&#x27;t think many are prepared when they initially have the idea.<p>The funny thing is that about a decade ago, the app was born on a SQLite per tenant setup, then it moved to schema per tenant on Postgres, now it&#x27;s finally moving to a single schema with RLS. So, the exact opposite progression.

What do they mean by &quot;Since SQLite does not support concurrent transactions&quot; - it supports them, as long as you don&#x27;t access the .db file through a file share (UNC, or NFS, etc) - <a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;wal.html" rel="nofollow noreferrer">https:&#x2F;&#x2F;www.sqlite.org&#x2F;wal.html</a><p>I&#x27;ve been using this to update&#x2F;read db from multiple threads&#x2F;processes on the same machine. You can also do snapshotting with the sqlite backup API, if you want consistent view, and to not hold on transaction (or copy in-memory).<p>But maybe I&#x27;m missing something here... Also haven&#x27;t touched sqlite in years, so not sure...

Interesting... I like the strategy of having each user be 1:1 with a DB. What would be done for data that needs to be aggregated across users though? If I&#x27;m subscribed to another user and they post, how does my DB get updated with that new post? Or is this meant just for durable data and not feed data (like profile data, which users are followed &#x2F; not followed &#x2F; etc.) and all the interactive stuff happens separately?<p>I like that &quot;connection pooling&quot; is just limiting the number of open handles in a LRU cache. It&#x27;s also interesting because instead of having to manage concurrency at the connection level, it handles it at the tenancy level since each DB connection is single-threaded. You could build up per-DB rate limiting on top of this pretty easily to prevent abuse by a given user.<p>Is there a straightforward way to set up Litestream to handle any arbitrary number of DBs?

Always happy to see more server SQLite&#x2F;Litestream adoption which we&#x27;ve also been using to build our new Apps with.<p>SQLite + Litestream is an even greater choice for tenant databases, that&#x27;s vastly cheaper to replicate&#x2F;backup to S3&#x2F;R2 than expensive cloud managed databases [1] (up to 3900% cheaper vs SQLServer on Azure).<p>[1] <a href="https:&#x2F;&#x2F;docs.servicestack.net&#x2F;ormlite&#x2F;litestream" rel="nofollow noreferrer">https:&#x2F;&#x2F;docs.servicestack.net&#x2F;ormlite&#x2F;litestream</a>

At a previous fintech role the company would store customer accounts as encrypted sqlite3 files on blob storage ... this worked out decently well for our access patterns.

On the surface, this looks like the worst combined with the awful. I hope someone will make a good article with some hard numbers to explain the benefits and analyze the assumed flaws, because this could be something really, fascinating to learn about.

Can someone that knows more about bluesky explain what data is stored in sqlite and not? Because i assume it isnt messages etc between users.

Why sha256 hash the user into to get a two character target directory? Wouldn&#x27;t md5 be much faster and solve the same problem?

Is Bluesky still invite only?

If anyone needs a Bluesky invite, there are three in the About section of my profile page here (note: you will need to prepend &quot;bsky-social-&quot; to the code).

This should make leaving the service rather simple. Download your sqlite file and throw up a simple local-only html front end to the data and you&#x27;re solid.

Will the BGS also be federated, or is that to be the centralized big spider in Bluesky&#x27;s web?

good luck with running updates.

That looks like the PR from hell - 190 files changed, 143 commits? Mostly with names like &quot;tidy&quot; and &quot;wip&quot;<p>Props to whoever actually reviewed that, you are a warrior

This seems like a very misleading title, the Bluesky PDS is the meant-for-selfhosting thing they distribute, not the bluesky service as experienced and used by most of its users.

Slightly related: is Bluesky moderated good enough or do I get lots of rightwing and conspiracy crap like on twitter currently?<p>I‘d really love to have some more civilized hub again that isn’t full of hate and anti-intellectualism.

Cool, but maybe let people actually use your service before everyone forgets what it is?

Anyone interested in joining Bluesky, please grab these. I have extra and I&#x27;ve already invited all my Twitter mutuals I wanted to invite.<p>Edit: I&#x27;m all out now :)

I am curious, does the HN folks know if bluesky is more active than nostr or the mastodon network?

I&#x27;ve got a bunch of invites if folks want them:<p>bsky-social-etdu7-njigu<p>bsky-social-2ktcs-uwoxg<p>bsky-social-6f5nh-36gnq<p>bsky-social-ciwro-3gzk5<p>bsky-social-y4h57-dxh3g

That sounds like centralizing

I sure hope they don’t ever want to change their db structure.<p>Why not use Postgres with RBAC (Row Based Access Control).