Devious SQL: Message Queuing Using Native PostgreSQL

Re:Devious SQL: Message Queuing Using Native PostgreSQL by 00000000005 on Friday January 28, 2022 @08:04PM (#30122675)

You don’t even need a database to implement an effective queueing mechanism.

Linux file move is atomic, which means file system based queues are perfectly viable. Just save one message per file. Move the file to a different directory when it changes state.

I built a prototype queue around this mechanism. Performance was bound to the disks ability to create small files, around 30,000 messages per second.

This sort of performance rivals some of much more sophisticated and complex queuing systems, but has zero configuration.

[ Parent | on HN ]

Re:Devious SQL: Message Queuing Using Native PostgreSQL by eloff on Friday January 28, 2022 @08:47PM (#30122938)

It has complex failure modes though, unless you're not at all concerned about what happens if the program crashes, power goes out, file gets corrupted, etc.

It's surprisingly difficult to write something that stores changing data on disk correctly and durably.

[ Parent | on HN ]

Re:Devious SQL: Message Queuing Using Native PostgreSQL by 00000000005 on Friday January 28, 2022 @10:41PM (#30123561)

Databases have the same problems when disks fail, power goes out etc.

[ Parent | on HN ]

Re:Devious SQL: Message Queuing Using Native PostgreSQL by eloff on Saturday January 29, 2022 @11:48AM (#30127815)

If you mean they have to solve the same problems with very careful design and programming, yes. It's non trivial. Even Postrgres had a correctness issue around fsync for nearly twenty years.

If you roll your own, that's very hard to get right. I wouldn't have confidence in such a system that I've built myself.

[ Parent | on HN ]

Re:Devious SQL: Message Queuing Using Native PostgreSQL by RedShift1 on Saturday January 29, 2022 @01:51AM (#30124356)

Would you mind sharing the code? How do you know subscribers have messages waiting for them, inotify or polling or something else?

[ Parent | on HN ]

Re:Devious SQL: Message Queuing Using Native PostgreSQL by kumarvvr on Friday January 28, 2022 @09:35PM (#30123236)

This mechanism has multiple points of failure, and too many disk writes. Batching up writes saves the hardware.

[ Parent | on HN ]

Re:Devious SQL: Message Queuing Using Native PostgreSQL by 00000000005 on Friday January 28, 2022 @10:40PM (#30123555)

Batching up writes means greater risk if data loss compared to immediate writes.

[ Parent | on HN ]