It's used in a not particularly advanced way - bluesky uses repositories per each user for all their public social activity (posts, likes, etc) and it creates a Merkle tree as an index for them and signs that index (this enables stuff like authenticated content addressing and efficient verification).

SQLite simply stores those posts and that signed Merkle tree. The PDS account host server also has another SQLite DB with a list of its accounts.

They have fancier stuff in their appview server and relay server