Curious about other SQL databases

[isaacdonaldson]

This is great! I've been thinking about queues and Postgres queues recently so this is a happy coincidence! I was wondering what parts of the implementation are specific to Postgres and if there is an appetite to support other DB backends, namely SQLite? I am not certain on how easy this would be, but since you're using sqlx it could be something to look into?

[maxcountryman]

The primary database feature we rely on is FOR UPDATE SKIP LOCKED, which is somewhat specific to Postgres. This allows us to efficiently handle jobs without worrying about the overhead of locking so much.

Could this work in SQLite? Well some version of it, probably. (I'm not sure what's required to make something similar safe and efficient in the context of SQLite.) I don't think there's an exact equivalent for the update clause as Postgres provides.

[isaacdonaldson]

From my understanding (which I am not super confident in) I think MySQL has the FOR UPDATE SKIP LOCKED feature which seems to be equivalent? As for SQLite, I think there are some mechanisms to lock access to the database file, but I think (again, not 100%) that the concurrency model of SQLite makes some of the concurrency things go away at cost of some performance metric. Not an SQLite whiz, but I think the EXCLUSIVE transaction prevents reads to a SQLite database file while there is a write transactions in progress, so you wouldn't don't need the same locking mechanism. Probably worth validating every claim I made though for your own sake 😅.