top | item 23728006

(no title)

oomkiller | 5 years ago

I really look forward to seeing what can be done with Postgres's pluggable storage backends that were recently added. It seems that some of the issues with treating a table as a queue could be mitigated with special storage backends designed for such a job.

discuss

sradman|5 years ago

PostgreSQL and Oracle were always a good starting point for a queueing system since they support MVCC, a necessity for the hot rows at the head of the queue. DB2, Sybase, and Microsoft started with hierarchical locking, but all three have added optional MVCC in the form of Snapshot or Cursor Stability isolation levels. I’m not sure what functionality is missing that requires a new storage engine.

anarazel|5 years ago

FWIW, you already can use postgres' logical decoding / change data capture to make queuing more efficient. Depending on what you need.

If it's the type of queue that various consumers need to see in their entirety, then you can just use pg_logical_emit_message(transactional bool, prefix text, payload data/bytea) to emit messages , which logical decoding consumers then see either in time order (transactional = false) or in commit order (transactional = true).

If it's more the the job type of queue where exactly one subscriber is allowed to see a message it's a bit more complicated, but using logical decoding will probably still be more efficient than querying a queue table with ORDER BY etc.

Being able to do queue submissions as part of a transaction (i.e. queue submissions will only be visible after commit) can be really useful to integrate with external systems.

xyzzy_plugh|5 years ago

You could use logical replication for queuing but there are a lot of footguns. It's far from a general purpose queue. For a handful of consumers, fine, but you'll have trouble scaling this to hundreds or thousands of consumers, which other queues solve for handily.

ianlevesque|5 years ago

I agree but MySQL has had pluggable backends for over ten years and I haven’t seen a viable one for this yet.

gregw2|5 years ago

mysql did get a couple nice columnar storage engines until Oracle killed the business