One of the more prominent uses of sqlite in Firefox is the 'places' db where your history and bookmarks are stored. If you go to History->Show All History, then select a few thousand entries and press delete, you can lock firefox up for several minutes, tens of minutes even.
I don't think this is necessarily damning of sqlite though, I'm pretty sure Firefox is not quite doing things right in some cases. I can delete thousands of rows in sqlite in mere milliseconds, so something else is going on. A shitty schema maybe? I'm not sure.
This is almost always a case of a programming language loop that implicitly does..
begin transaction
delete from history where id = @id
commit
.. on every iteration. Which is asking the storage engine to write the new data and make sure it's flushed to disk between each deleted entry. No wonder it's slow. You can get massively better just by hoisting the transaction begin and commit to outside the loop, and even better if you can drop the loop altogether and specify range of entries to delete directly.
Hot damn, opened 8 years ago? That report describes the behavior I experience to a T, it's definitely the same bug.
Is the this 'Library' window a dead component? Besides this nearly decade-old bug, it still doesn't get themed like the rest of firefox either; dark mode doesn't effect it.
It might be that they are deleting each entry in its own transaction. A transaction causes file system level sync and with the millisecond latency of common hard drives it causes transactions to have a millisecond overhead. Multiply this by a few thousand and you get the latency you are talking about.
I've made this mistake myself in a project of mine and after tracking down the bad performance to this issue ended up adding a layer that emits explicit transaction begin/end statements after N operations.
Edit: See the bug thread linked in the sibling comment. The transaction latency seems to be a component, but other issues remain.
I don't think this is necessarily damning of sqlite though, I'm pretty sure Firefox is not quite doing things right in some cases. I can delete thousands of rows in sqlite in mere milliseconds, so something else is going on. A shitty schema maybe? I'm not sure.