Shabel here again, and oh, today's writeup isn't about my usual football rants and hypes. My recent adventures on YouTube led me to a video by @fireship where he talked about supporting pub/sub in PostgreSQL. I got curious and surprised, so I began my quest of finding out how feasible this is, given certain complications around delivery guarantees in message-queue-based systems. What I found along the way ended up being the reason behind me building SGQueue.
SKIP LOCKED and LISTEN/NOTIFY to the rescue
The two core Postgres features at the heart of SGQueue are SKIP LOCKED and LISTEN/NOTIFY. Double-processing in a task processing system is such a common phenomenon due to different workers trying to process the same message without knowledge of whether the task has been processed already or not. For simple systems with a single worker, this may seem unlikely due to non-concurrent task access. For highly concurrent systems, however, workers actively try to retrieve and process tasks at their convenience, hence the need for ensuring that a task isn't acquired by multiple workers. A very common solution to this would be using row locks whenever a task is retrieved from the database table. While locking a row will solve our problem here, other workers will be stuck waiting for the initial worker to release the lock it holds. Since the locked task is of no use to us now, it's best that we skip locked rows and proceed to other rows (tasks) in line for us to process.
The beauty of SQL databases is that we can use an inbuilt mechanism to skip all locked tasks when they are being updated by using the SKIP LOCKED clause. Here's a simple demo:
SELECT * FROM messages
WHERE status = 'pending'
ORDER BY created_at ASC
FOR UPDATE SKIP LOCKED
LIMIT 1;With the snippet above, a worker simply has to run the query to retrieve a non-processed task. When multiple consumers execute this query:
- Each consumer gets a different message
- No consumer blocks another
- Messages are processed in order
- No messages are lost or processed twice
Now on to the second mechanism. LISTEN/NOTIFY is a Postgres-proprietary solution that allows users to trigger a notification with a simple SQL query to all subscribers of a specified channel. Yeah, you heard it right lol, PostgreSQL for everything. Not so fast though, the gotcha here is that payloads sent in notifications can't exceed 8kb (roughly 8,000 characters), so the plan is to use the notify feature purely as a signal to the consumer/worker that a new task is ready for processing, rather than sending the entire payload in the notification. This is still on my TODO list though. Right now, SGQueue falls back to a simple poll every second to pick up new messages, and LISTEN/NOTIFY will slot in as the reactive layer on top of that once it's built.
Here's a simple notification demo. Listen on the messages_channel channel:
LISTEN messages_channelThen send a message to subscribers using NOTIFY:
NOTIFY messages_channel, 'new task available';Still around? You may want to check out SGQueue whenever you need a message queue in your Postgres-based application without introducing additional services such as RabbitMQ, Redis, or Kafka. The ACID properties of SQL databases also guarantee that messages are durable, even in the event of a system failure.
Till my next football writeup drops, don't hesitate to check out the implementation here. SGQueue on GitHub.