Once upon a time you'd have to move on from SQLite if you wanted to be able to survive node failure w/o data loss, but with stuff like Litestream and Verneuil out there it's a much smaller gap now. Postgres still does rich data types (JSON, GIS, probablistic structures) better.

SQLite is always going to be faster for the same simple, single-writer/local-readers-only usage patterns b/c there's no network overhead. Postgres comes into its own with more write contention, or if you want access to the richer datatypes + extension ecosystem.

You can still do an index lookup in the persistent event store if you pre-generate the SHA for each key and store it alongside the original message. Or just do a sequential scan if your message queue is small.

Would the "discovery key" model as used in Dat perhaps be of more use w.r.t. privacy? I.e., instead of asking for pubkeys directly you ask for the SHA of the pubkey. Then the relay can only figure out which keys you're interested in when they actually see a message from that key.