3 ms·
Postgres. Doing ~ 70k messages/second average. Nothing huge but don’t need anything dedicated yet.
by wordofx 1y ago
Postgres. Doing ~ 70k messages/second average. Nothing huge but don’t need anything dedicated yet.
- lawn 1y agoI'm curious on how people use Postgres as a message queue. Do you rely on libraries or do you run a custom implementation?
- padjo 1y agoYou can go an awfully long way with just SELECT … FOR UPDATE … SKIP LOCKED
- Spivak 1y agoI've never found a satisfying way to not hold the lock for the full duration of the task that is resilient to workers potentially dying. And postgres isn't happy holding a bunch of locks like that. You end up having to register and track workers with health checks and a cleanup job to prune old workers so you can give jobs exclusivity for a time.
- user928347483 1y agoIf you go that route you could spawn a parent process , the work is done in a child process ?
- wordofx 1y agoI use a visibility timeout. So when selecting a message that isn’t started. It also looks for in progress ones that have been going longer than the timeout. The update sets status, start time, and attempt counter. If attempt counter equals 3 when the update happens, it sets the message to failed. The return looks at the stats sees failed and raises a notification. Then if it’s a fix like correcting data or something I just reset the state to have it reprocess. Never needed to track workers or cleanup jobs etc.
- nick0garvey 1y agoHold the lock and write a row with timestamp at the time you read. That row indicates you are the one processing the data and no one else should. When reading, abort the read if someone else wrote that row first. When you are finished processing, hold the lock and update the row you added before to indicate processing is complete. The timestamp can be used to timeout the request.
- digianarchist 1y agopgmq https://github.com/pgmq/pgmq https://github.com/pgmq/pgmq
- ericaska 1y agoWe also use Postgres but we don't have many jobs. It's usually 10-20 squedule that creates hourly-monthly jobs and they are mostly independent. Currently a custom made solution but we are going to update it to use skip locked and use Notify/Listen + interval to handle jobs. There is a really good video about it on YouTube called: "Queues in PostgreSQL Citus Con."
- wordofx 1y agoJust select for update skipped locked. Table is partitioned to keep unprocessed small.
- j45 1y agoBuilt right in using a group of pg functions, or also with a library, or also with a python based tool that happens to use pg for the queue.
- iamcalledrob 1y agoCurious what kind of hardware you're using for that 70K/s?
- wordofx 1y agoIt’s an r8g instance in aws. Can’t remember the size but I think it’s over provisioned because it’s at like 20% utilisation and only spikes to 50.
- aynyc 1y agoWhat’s your batch size?