Choose Postgres queue technology

https://adriano.fyi/posts/2023-09-24-choose-postgres-queue-technology/

by bo0tzz • 3 years ago

762 362 3 years ago

aduffy• 3 years ago

For several projects I’ve opted for the even dumber approach, that works out of the box with every ORM/Query DSL framework in every language: using a normal table with SELECT FOR UPDATE SKIP LOCKED

https://www.pgcasts.com/episodes/the-skip-locked-feature-in-...

It’s not “web scale” but it easily extends to several thousand background jobs in my experience

matsemann5• 3 years ago

I've done even simpler without locks (as no transaction logic), where I select a row, and then try to update a field about it being taken. If 1 row is affected, it's mine. If 0, someone else did it before me and I select a new row.

I've used this for tasks at big organizations without issue. No need for any special deployments or new infra. Just spin up a few worker threads in your app. Perhaps a thread to reset abandoned tasks. But in three years this never actually happened, as everything was contained in try/catch that would add it back to the queue, and our java app was damn stable.

andrelaszlo2• 3 years ago

I guess you update it with the assigned worker id, where the "taken by" field is currently null? Does it mean that workers have persistent identities, something like an index? How do you deal with workers being replaced, scaled down, etc?

Just curious. We maintained a custom background processing system for years but recently replaced it with off the shelf stuff, so I'm really interested in how others are doing similar stuff.

bushbaba• 3 years ago

You could even use a timestamp for handling what if this task was never finished by the worker who locked the row.

mbb70• 3 years ago

I've done the same with MongoDB with findOneAndModify, simple and solid

klysm7• 3 years ago

With what transaction isolation level?

samus2• 3 years ago

I recently got introduced to this system at work, and also built a new job using it. It works fine, but since I had to implement work stealing to deal with abandoned jobs in a timely manner, I wouldn't dare to use it for actions that absolutely must not happen twice.

valzam2• 3 years ago

The reason why you want to use skip locked is so that Postgres can automatically skip rows that are being concurrently accessed for updating the "status". You are right, if you update a "status" field you don't really need to worry about advisory locks and skipping rows that are locked but it still helps with performance if you have a decent amount of concurrent consumers polling the table.

refibrillator• 3 years ago

PSA: This is a read-modify-write pattern, thus it is not safe under concurrency unless a transaction isolation level of SERIALIZABLE is specified, or some locking mechanism is used (select for update etc).

ricardobeat• 3 years ago

That's what's in the article.

orangepanda• 3 years ago

As I understand, with SKIP LOCKED rows would no longer be processed in-order?

riku_iki2• 3 years ago

article says he also uses "order by" clause, but I am wondering if it will severely limit throughput since all messages will need to be sorted on each lookup, but this probably can be solved by introducing index.

klysm7• 3 years ago

Depends on how many consumers you have. If you need order guarantees, then something like the outbox pattern is probably a better fit.

vore4• 3 years ago

Yes, but if you're going through the queue with multiple workers in parallel, you lose ordering guarantees anyway.

qaq2• 3 years ago

batch inserts process tasks in batches and it is pretty much webscale

somsak2• 3 years ago

Fourth paragraph of the post:

Applied to job records, this feature enables simple queue processing queries, e.g. SELECT * FROM jobs ORDER BY created_at FOR UPDATE SKIP LOCKED LIMIT 1.

surprisetalk2• 3 years ago

I recently published a manifesto and code snippets for exactly this in Postgres!

  delete from task
  where task_id in
  ( select task_id
    from task
    order by random() -- use tablesample for better performance
    for update
    skip locked
    limit 1
  )
  returning task_id, task_type, params::jsonb as params

[1] https://taylor.town/pg-task

thom2• 3 years ago

Presumably it's okay that this loses work if your task runner has an error?

adatta02• 3 years ago

This is more or less how graphile, https://github.com/graphile/worker is implemented.

Klonoar• 3 years ago

I’ve used this for a queue with millions of items and some indexes. It “just works”.

johnthescott• 3 years ago

skip lock works well on many Ks/sec message queues.

boruto2• 3 years ago

Not if you have groups in those thousands and want to maintain order on those groups

Karrot_Kream3• 3 years ago

In my experience, a queue system is the worst thing to find out isn't scaling properly because once you find out your queue system can't architecturally scale, there's no easy fix to avoid data loss. You talk about "several thousand background jobs" but generally, queues are measured in terms of Little's Law [1] for which you need to be talking about rates; according to Little's Law namely average task enqueue rate per second and average task duration per second. Raw numbers don't mean that much.

In the beginning you can do a naive UPDATE ... SET, which locks way too much. While you can make your locking more efficient, doing UPDATE with SELECT subqueries for dequeues and SELECT FOR UPDATE SKIP LOCKED, eventually your dequeue queries will throttle each other's locks and your queue will grind to a halt. You can try to disable enqueues at that point to give your DB more breathing room but you'll have data loss on lost enqueues and it'll mostly be your dequeues locking each other out.

You can try very quickly to shard out your task tables to avoid locking and that may work but it's brittle to roll out across multiple workers and can result in data loss. You can of course drop a random subset of tasks but this will cause data loss. Any of these options is not only highly stressful in a production scenario but also very hard to recover from without a ground-up rearchitecture.

Is this kind of a nightmare production scenario really worth choosing Boring Technology? Maybe if you have a handful of customers and are confident you'll be working at tens of tasks per second forever. Having been in the hot seat for one of these I will always choose a real queue technology over a database when possible.

[1] https://en.wikipedia.org/wiki/Little%27s_law

mlyle5• 3 years ago

and are confident you'll be working at tens of tasks per second forever.

It's more like a few thousand per second, and enqueues win, not dequeues like you say... on very small hardware without tuning. If you're at tens of tasks per second, you have a whole lot of breathing room: don't build for 100x current requirements.

https://chbussler.medium.com/implementing-queues-in-postgres...

eventually your dequeue queries will throttle each other's locks a

This doesn't really make sense to me. To me, the main problem seems to be that you end up with having a lot of snapshots around.

asdfaoeu2• 3 years ago

You are going to have the same scaling issues with your datastore. I don't really understand why you say that your dequeue queries will throttle each others locks and grind it to a half? Isn't that the whole point of SKIP LOCKED?

klauserc2• 3 years ago

If you find yourself in that situation, migrating to a more performant queuing solution is not that much of a leap. You already have an overall system architecture that scales well (async processing with a queue).

_Ideally_ the queuing technology is abstracted from the job-submitters/job-runners anyway. It's a bit more work if multiple services are just writing to the queue table directly.

I agree that the _moment_ the system comes to a screeching halt is definitely not fun.

boruto2• 3 years ago

Skip locked is useful till you have to maintain order for a group of messages with some "group_id", so that set of related messages are sent one after the other.

Then you probably have to write complicated queries or use partitions in some sort.

Or Just stick to one thread polling the messages.

theK• 3 years ago

Ditto.

Also, postgres partial indexes can be quite helpful in situations where you want to persist and query intermediate job lifecycle state and don't want multiple rows or tables to track one type of job queue

mvdtnz• 3 years ago

How is this "an even dumber approach"? It's literally the one thing this article is advocating for. Did you read it?

andrelaszlo2• 3 years ago

One issue with Redis as a queue backend seems to be that persistence is quite expensive, at least for managed Redis instances. Using PG seems like it could be much cheaper, especially if you already have an instance with room to spare.

I thought it was an interesting article, and I'd love to hear more from people using PG for queues in production (my intuition would say you'd get a lot of table bloat and/or vacuum latency, but I haven't tested it myself), but when it comes to the conclusion - "choosing boring technology should be one’s default choice" - I can't think of anything more boring (in a good sense, mostly) than Sidekiq + Redis for a Rails app.

pqdbr2• 3 years ago

Same here. Sidekiq + Rails in a Rails app is a powerhouse, simple and reliable, but I do worry about losing the queue in Redis. It would be great to have that in Postgres as well.

jarofgreen• 3 years ago

I’d love to see more neoq-like libraries for languages other than Go.

Python has Celery, but maybe the author is looking for more choice between brokers. https://docs.celeryq.dev/en/stable/index.html

welder3• 3 years ago

Celery is crap. Full of bugs. https://wakatime.com/blog/56-building-a-background-task-queu...

nicoburns• 3 years ago

For running queues on Postgres with Node.js backend(s), I highly recommend https://github.com/timgit/pg-boss. I'm sure it has it scale limits. But if you're one of the 90% of the apps that never needs any kind of scale that a modern server can't easily handle then it's fantastic. You get transactional queueing of jobs, and it automatically handles syncing across multiple job processing servers using Postgres locks.

pphysch2• 3 years ago

Another point is that task queue technology is highly fungible. There's nothing stopping you from starting with cron, adding in Postgres/Redis, then graduating to Kafka or something as need arises. And running all three in parallel, with different jobs in each. I would be surprised if the average Kafka shop didn't also have a bunch of random cron jobs doing things that could be implemented on Kafka or vice versa.

At some point you may want to refactor things to reduce tech debt, but it really is a "and" rather than "or" decision.

Scubabear68• 3 years ago

I agree with all of this except for the part about “cron”. Cron jobs in my experience quickly become hard to manage and effectively invisible over time.

Use almost anything else to manage job scheduling….

dharmab• 3 years ago

I'm not sure if they literally mean crond, or something vaguely cron-like but easier to manage like systemd timers.

levkk• 3 years ago

Running this exact implementation with 47M jobs processed and counting. SKIP LOCKED is great for VACUUM, and having durable storage with indexes make otherwise expensive patterns like delayed jobs, retries, status updates, "at least once", etc. really easy to implement.

dlisboa• 3 years ago

Do you have some idea of how many jobs per minute or hour do you have? Just want to compare with what we have on Redis at work.

Do you also have any idea on the concurrency? How many workers you have pulling from Postgres.

I’ve used this approach before (ages ago) when Redis wasn’t even a thing, though not at high throughout requirements.

devoutsalsa• 3 years ago

I'm sure Redis is much faster than an RDBMS w/ all the ACID features turned on. The biggest concern I always have with Redis is simply overwhelming the in-memory storage limits when someone wants to do process a large number of good-sized messages at an inconvenient time. #tradeoffs

dilyevsky• 3 years ago

I’ve seen it used for up to 1000 jobs per second with concurrency of 3-12

cle• 3 years ago

I recently did the same thing but without LISTEN/NOTIFY and with a partial index. Pushed about 700 jobs/sec with 1000 workers (via pgbouncer).

xdanger• 3 years ago

I do enjoy using https://github.com/graphile/worker for my postgresql queuing needs. Very scalable, the next release 0.14 even more so, and easy to use.

mildavw• 3 years ago

For Rails apps, you can do this using the ActiveJob interface via

https://github.com/bensheldon/good_job

Had it in production for about a quarter and it’s worked well.

paulddraper6• 3 years ago

USE. ADVISORY. LOCKS.

Do not use SKIP LOCKED unless it is a toy/low throughout.

Row locks require transactions and disk writes.

Advisory locks require neither. (However, you do have to stay inside the configurable memory budget.)

ukd12• 3 years ago

Maybe it's changed in the last year or so, but from benchmarking and writing / running queue software for Postgres - SKIP LOCKED was/is significantly faster. Is that different for MySQL?

mikeocool• 3 years ago

Pretty common advice for scaling Postgres is to deploy pgbouncer in transaction mode in front of it to handle connection pooling.

Advisory locks don’t work in this setup (and will start behaving in strange ways if you do try to use them.) Something to consider if you go this route.

ris• 3 years ago

Transaction-scoped advisory locks are very much a thing too.

paulddraper6• 3 years ago

Depends. That has more to do with how your're scaling application servers.

sorentwo• 3 years ago

To do anything safe and interesting you’ll need transactions. Using SKIP LOCKED won’t be your bottleneck, your application will. Job queues are about side effects and the rest of your application needs to keep up.

Oban is able to run over 1m jobs a minute, and the ultimate bottleneck is throttling in application code to prevent thrashing the database: https://getoban.pro/articles/one-million-jobs-a-minute-with-...

paulddraper6• 3 years ago

That's true.

However in the empty poll case, you can avoid a transaction.

klysm7• 3 years ago

Not all use cases are high throughput. That’s not what makes it a toy

paulddraper6• 3 years ago

"Toy/low throughput" = "Toy or low throughout"

5id• 3 years ago

One of the biggest benefits imo of using Postgres as your application queue, is that any async work you schedule benefits from transactionality.

That is, say you have a relatively complex backend mutation that needs to schedule some async work (eg sending an email after signup). With a Postgres queue, if you insert the job to send the email and then in a later part of the transaction, something fails and the transaction rollbacks, the email is never queued to be sent.

matsemann5• 3 years ago

Good point. We alleviate that a bit by scheduling our queue adds to not run until after commit. But then we still have some unsafety, and if connection to rabbit is down we're in trouble.

theptip• 3 years ago

Worth being clear that bridging to another non-idempotent system necessarily requires you to pick at-least-once or at-most-once semantics. So for emails, if you fail awaiting confirmation of your email you still need to pick between failing your transaction and potentially duplicating the email, or continuing and potentially dropping it.

The big advantage is for code paths which async modify your DB; these can be done fully transactionally with exactly-once semantics since the Job consumption and DB update are in the same transaction.

skybrian2• 3 years ago

Email might never arrive, though. The only way to know they got it is to have them follow a link to confirm.

laurencerowe• 3 years ago

One of the biggest benefits imo of using Postgres as your application queue, is that any async work you schedule benefits from transactionality.

This is a really important point. I often end up using a combination of Postgres and SQS since SQS makes it easy to autoscale the job processing cluster.

In Postgres I have a transaction log table that includes columns for triggered events and the pg_current_xact_id() for the transaction. (You can also use the built in xmin of the row but then you have to worry about transaction wrap around.) Inserting into this row triggers a NOTIFY.

A background process runs in a loop. Selects all rows in the transaction table with a transaction id between the last run's xmin and the current pg_snapshot_xmin(pg_current_snapshot()). Maps those events to jobs and submits them to SQS. Records the xmin. LISTEN's to await the next NOTIFY.

__jem• 3 years ago

I'm not sure this is really an issue with transactionality as a single request can obviously be split up into multiple transactions, but rather that even if you correctly flag the email as pending/errored, you either need to process these manually, or have some other kind of background task that looks for them, at which point why not just process them asynchronously.

ryanbrunner• 3 years ago

Another benefit of this is that you're guaranteed that the transaction is completed before the job is picked up. With redis-backed queues (or really anything else), you very quickly run into the situation where your queue executes a job depending on a database record existing prior to the transaction being committed (and the fix for this is usually awkward / complex code).

vb-84483• 3 years ago

With a Postgres queue, if you insert the job to send the email and then in a later part of the transaction, something fails and the transaction rollbacks, the email is never queued to be sent.

An option could be use a second connection and a separate transaction to insert data in the queue table.

robertlagrant• 3 years ago

I agree - having to tell a database that something was processed, and fire off a message into RabbitMQ, say, is never 100% transactional. This would be my top reason to use this approach.

With a Postgres queue, if you insert the job to send the email and then in a later part of the transaction, something fails and the transaction rollbacks, the email is never queued to be sent.

This is true - definitely worth isolating what should be totally separate database code into different transactions. On the other hand, if your user is not created in the DB, you might not want your signup email. Just depends on the situation.

skybrian2• 3 years ago

It seems like listen/notify doesn't play well with a serverless architecture. Would it make sense for Postgres to make a web request when there's work in the queue? Is that a thing?

jpgvm• 3 years ago

Few things.

1. The main downside to using PostgreSQL as a pub/sub bus with LISTEN/NOTIFY is that LISTEN is a session feature, making it incompatible with statement level connection pooling.

2. If you are going to do this use advisory locks [0]. Other forms of explicit locking put more pressure on the database while advisory locks are deliberately very lightweight.

My favorite example implementation is que [1] which is ported to several languages.

[0] https://www.postgresql.org/docs/current/explicit-locking.htm...

[1] https://github.com/que-rb/que

MrPowerGamerBR• 3 years ago

One reason that makes me dislike NOTIFY/LISTEN is that issues with it are hard to diagnose.

Recently I had to stop using it because after a while all NOTIFY/LISTENS would stop working, and only a database restart would fix the issue https://dba.stackexchange.com/questions/325104/error-could-n...

runeks5• 3 years ago

At my previous company, they switched from using NOTIFY/LISTEN for Postgres notifications to a custom solution built on top of logical replication. As I understand it, part of the reason was reliability. I never touched that part of the code, but I believe the idea was to subscribe to logical replication updates and send out notifications based on these.

teaearlgraycold• 3 years ago

Is there a Node version of Que?

andrewstuart6• 3 years ago

MS SQL server, Postgres and MySQL all support SKIP LOCKED, which means they are all suitable for running queues.

I built a complete implementation in Python designed to work the same as SQS but be more simple:

https://github.com/starqueue/starqueue

Alternatively if you just want to quickly hack something into your application, here is a complete solution in one Python function with retries (ask ChatGPT to tell you what the table structure is):

    import psycopg2
    import psycopg2.extras
    import random
    
    db_params = {
        'database': 'jobs',
        'user': 'jobsuser',
        'password': 'superSecret',
        'host': '127.0.0.1',
        'port': '5432',
    }
    
    conn = psycopg2.connect(**db_params)
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    
    
    def do_some_work(job_data):
        if random.choice([True, False]):
            print('do_some_work FAILED')
            raise Exception
        else:
            print('do_some_work SUCCESS')
    
    def process_job():
        sql = """DELETE FROM message_queue
        WHERE id = (
            SELECT id
            FROM message_queue
            WHERE status = 'new'
            ORDER BY created ASC
            FOR UPDATE SKIP LOCKED
            LIMIT 1
        )
        RETURNING *;
        """
        cur.execute(sql)
        queue_item = cur.fetchone()
        print('message_queue says to process job id: ', queue_item['target_id'])
        sql = """SELECT * FROM jobs WHERE id =%s AND status='new_waiting' AND attempts <= 3 FOR UPDATE;"""
        cur.execute(sql, (queue_item['target_id'],))
        job_data = cur.fetchone()
        if job_data:
            try:
                do_some_work(job_data)
                sql = """UPDATE jobs SET status = 'complete' WHERE id =%s;"""
                cur.execute(sql, (queue_item['target_id'],))
            except Exception as e:
                sql = """UPDATE jobs SET status = 'failed', attempts = attempts + 1 WHERE id =%s;"""
                # if we want the job to run again, insert a new item to the message queue with this job id
                cur.execute(sql, (queue_item['target_id'],))
            else:
                print('no job found, did not get job id: ', queue_item['target_id'])
                conn.commit()
    
    process_job()
    cur.close()
    conn.close()

scraplab• 3 years ago

Yep, we process hundreds of thousands and sometimes a few million jobs daily inside Postgres, using Oban in Elixir.

Having transactional semantics around background jobs is incredibly convenient for things like scheduling email only if the transaction is successful, and so on.

You do need to do a little bit of autovacuum tuning, but once sorted it’s been great for us.

kissgyorgy• 3 years ago

Here is a Python example how to use it: https://gist.github.com/kissgyorgy/beccba1291de962702ea9c237...

evil-olive• 3 years ago

there's an important dimension of scalability that I think gets overlooked in a lot of these discussions about database-as-a-queue vs queue-system-as-a-queue:

are you queuing jobs, or are you queuing messages?

that's a fuzzy distinction, so somewhat equivalently, what's the expected time it takes for a worker to process a given queue item?

at one end, an item on the queue may take several seconds to a minute or longer to process. at the other end, an item might take only a few milliseconds to process. in that latter case, it's often useful to do micro-batching, where a single worker pulls 100 or 1000 items off the queue at once, and processes them as a batch (such as by writing them to a separate datastore)

the "larger" the items are (in terms of wall-clock processing time, not necessarily in terms of size in bytes of the serialized item payload) the more effective the database-as-a-queue solution is, in my experience.

as queue items get smaller / shorter to process, and start to feel more like "messages" rather than discrete "jobs", that's when I tend to reach for a queue system over a database.

for example, there's a RabbitMQ blog post [0] on cluster sizing where their recommendations start at 1000 messages/second. that same message volume on a database-as-a-queue would require, generally speaking, 3000 write transactions per second (if we assume one transaction to enqueue the message, one for a worker to claim it, and one for a worker to mark it as complete / delete it).

can Postgres and other relational databases be scaled & tuned to handle that write volume? yes, absolutely. however, how much write volume are you expecting from your queue workload, compared to the write volume from its "normal database" workload? [1]

I think that ends up being a useful heuristic when deciding whether or not to use a database-as-a-queue - will you have a relational database with a "side gig" of acting like a queue, or will you have a relational database that in terms of data volume is primarily acting like a queue, with "normal database" work relegated to "side gig" status?

0: https://blog.rabbitmq.com/posts/2020/06/cluster-sizing-and-o...

1: there's also a Postgres-specific consideration here where a lot of very short-lived "queue item" database rows can put excessive pressure on the autovacuum system.

doctor_eval3• 3 years ago

I’ve used PG as a message queue, actually it was used as a transactional front end to Kafka; we’d push messages to a PG table during a transaction, which would then be snarfed up to Kafka by a separate process after the transaction completed.

I’ve seen very high transaction rates from this arrangement, more than 20k messages/second.

ukd12• 3 years ago

I maintain QueueClassic (https://github.com/QueueClassic/queue_classic) for Rails/Ruby folks; which is basically what you're talking about - a queuing system for Postgres. A bonus reason, and why I originally wanted this was the ability to use transactions fully - i.e. I can start one, do some stuff, add a job in to the queue (to send an email), .....and either commit, or roll back - avoiding sending the email. If you use resque, I found sometimes either you can't see the record (still doing other stuff and it's not committed), or it's not there (rollback) - so either way you had to deal with it.

QC (and equivs) use the same db, and same connection, so same transaction. Saves quite a bit of cruft.

andrewstuart6• 3 years ago

You don't even need a database to make a message queue. The Linux file system makes a perfectly good basis for a message queue since file moves are atomic.

My guess is that many people are implementing queuing mechanisms just for sending email.

You can see how this works in Arnie SMTP buffer server, a super simple queue just for emails, no database at all, just the file system.

https://github.com/bootrino/arniesmtpbufferserver

doctor_eval3• 3 years ago

This is true, and I’ve worked on systems that use this, but it’s a lot more work than just a rename.

I’d recommend that, if you have a Postgres database already, definitely use that instead. Your queues will be transactional and they will get backed up when the rest of your database does.

andrewstuart6• 3 years ago

> but it’s a lot more work than just a rename

Such as?

repiret• 3 years ago

That’s a key property leveraged in the Maildir mailbox format.

andrewstuart6• 3 years ago

It was learning about this that led to me understanding file systems make perfectly acceptable queues.

whartung• 3 years ago

For those using simple SELECTs, what kind of WHERE clause are you using that works well with lots of qualified pending messages and (somewhat) guarantees the most appropriate (oldest?) message?

rubenfiszel• 3 years ago

We use exactly this for windmill (OSS Retool alternative + modern airflow) and run benchmarks everyday. On a modest github CI instance where one windmill worker and postgres run as containers, our benchmarks run at 1200jobs/s. Workers can be added and it will scale gracefully up to 5000jobs/s. We are exploring using Citus to cross the barrier of 5000j/s on our multi-tenant instance.

https://github.com/windmill-labs/windmill/tree/benchmarks

dools• 3 years ago

I'm always surprised that when I see people talk about queues I never see anyone mention beanstalkd. I've been using it for basically everything for 10 years and it's solid as a rock, incredibly simple and requires basically no maintenance. It Just Works™

arthurbrown• 3 years ago

I've used beanstalkd for personal projects previously with a similar experience to you.

For one such project, the message 'priority' feature was a life saver and a feature that is not super common in competing solutions.

mixmastamyk4• 3 years ago

Yes, I liked it when I encountered it a few years back. Lately been using redis and rq on a recent project since we're already using redis for caching. Is there much difference other than that?

jjice• 3 years ago

I've implemented queues with tables in RDBMSs a few times and it's always great and usually all you need. Worried about future scale? Make a class to wrapper the queue with a decent interface and swap it for RabbitMQ or whatever you want down the road. Implementation stays opaque and you have an easy upgrade path later on.

tiagod• 3 years ago

Temporal, which AFAIK was made by the Uber Cadence team, which was also involved in SQS, uses postgres as a backend.

I used it for a web automation system for an accounting client (automatically read files from a network share, lookup the clients on a database, submit the documents to government websites, using headless browsers, and put the resulting files in the directory). It allows for completely effortless deterministic programs that call workers that run the non deterministic code, with built in configurable retries (react to certain exception type, exponential back off) so you can write code that works almost like there were no issues with api connections, filesystem, etc.

This code has been running for 5 or more years, with barely any maintenance, with 0 issues so far. It keeps everything in postgres, so even full reboots and crashes have no impact, it will just move the work back to the queue and it will run when there's an available worker.

leoqa• 3 years ago

Temporal is a pretty complicated system. It has sharding built in, stores the entire activity history and runs multiple queues for timers and events. I’m a big fan (worked at Uber) but it’s definitely not just postgres with a few indices.

simonw• 3 years ago

One of my favourite pieces of writing about worker queues is this by Brandur Leach:

Transactionally Staged Job Drains in Postgres - https://brandur.org/job-drain

It's about the challenge of matching up transactions with queues - where you want a queue to be populated reliably if a transaction completes, and also reliably NOT be populated if it doesn't.

Brandur's pattern is to have an outgoing queue in a database table that gets updated as part of that transaction, and can then be separately drained to whatever queue system you like.

vhiremath42• 3 years ago

We used postgres for some of our queues back when we were at ~10 msg/s. It scaled quite a bit, but, honestly, setting up SQS or some other queue stack in AWS, GCP, or Azure is so simple and purpose built for the task (with DL queues and the like built in), I don’t know why you wouldn’t just go that route and not have to worry about that system shitting the bed and affecting the rest of the DB’s health.

It seems foolish. I am a big fan of “use the dumbest tool”, but sometimes engineers take it too far and you’re left with the dumbest tool with caveats that don’t seem worth it given the mainstream alternative is relatively cheap and simple.

bicijay• 3 years ago

Transactions, data consistency. This is the answer that you will not find in SQS.

mjb2• 3 years ago

You can't do better than At Least Once if you're having side effects outside the database, so it's not clear that SQS's weaker semantics have any practical effect.

rtpg• 3 years ago

What I've settled on is "store most job state in the DB, use task queues just to poke workers into working on the jobs".

Storing the job state in the DB means you can query state nicely. It's not going to exactly show the state of things but it's helpful for working through a live incident (especially when most job queues just delete records as work is processed).

And if you make all the background tasks idempotent anyways then you're almost always safe with running a thing like "send a job to the task queue to handle this job".

If you rely _just_ on message queues, there are a lot of times where you can have performance issues, yet have a lot of trouble knowing what's going on (for example, rabbitMQ might tell you the size of your queues, but offer little-to-no inspection of the data inside them).

vhiremath42• 3 years ago

Ultimately you have to figure out the separation of concerns of the job state and other core state. Ranging from “all state stored in message and will never become out of sync” to “no state stored in message and will never become out of sync”. In between you have “some state stored in db and some in message” and what I’ve found to be useful is keeping stuff in the db that needs to have high end state integrity (or as you said just making sure jobs are cancellable/idempotent).

Tangible example:

We have a video transcoder queue. The state of the video model in our db can change as the video is being finalized in various ways. The transcoder generates thumbnails and assets from the video and also updates its state in the db. So we store job information in the message about what thumbnails we want to generate and the video ID but nothing else. This allows us to look up the video row, see if the same media was already transcoded from the video (and cancel the job), and, if not, run the job and update the video row.

Also (and I know you’re not saying this), but I’ve never understood the argument that keeping queues in Postgres leads to higher data integrity via transaction guarantees. The job is still running on another process outside of the db. The only time this could be true is if the job itself mostly updates state in the db, in which case it’s the small minority of queued workloads (with the majority needing to do non-db compute work).

Rapzid2• 3 years ago

I don’t know why you wouldn’t just go that route and not have to worry about that system shitting the bed

Because different software has different requirements. Not having an external service requirement other than Postgres might be a feature of an on-prem/b2b appliance.

Because some software may be projected never outgrow the capabilities of Postgres, and if it does moving to another service can be made very easy.

Because you want a transitional job system and the simplicity of doing it in Postgres.

MattIPv42• 3 years ago

We process around 1 million events a day using a queue like this in Postgres, and have processed over 400 million events since the system this is used in went live. Only issue we've had was slow queries due to the table size, as we keep an archive of all the events processed, but some scheduled vacuums every so often kept that under control.

djbusby• 3 years ago

Active Queue table and then archive jobs to a JobDone table? I do that. Queue table is small but archive goes back many months

MattIPv42• 3 years ago

We just have a single table, with a column indicating if the job has been taken by a worker or not. Probably could get a bit more performance out of it by splitting into two tables, but it works as it is for now.

paulddraper6• 3 years ago

In modern PG you can use partitioned table for a similar effect.

andrewstuart6• 3 years ago

Partial indexes might help.

emilsedgh• 3 years ago

Exactly. A partial index should make things fly here.

runeks5• 3 years ago

Also: an ordered index that matches the ordering clause in your job-grabbing query. This is useful if you have lots of pending jobs.

encoderer• 3 years ago

This has its place. I would gladly use Postgres queues in places where I use redis queues today; but I would not consider replacing my SQS queues with Postgres.

Ozzie_osman• 3 years ago

One thing I love about Kafka is... It's just an append-only log, and a client is essentially just holding an offset. This is conceptually very simple to reason about. It's also persistent and pretty fault-tolerant (you can just go back and read any offset).

Unfortunately, Kafka carries with it enough complexity (due to the distributed nature) that it ends up not being worth it for most use-cases.

Personally I'd love something similar that's easier to operate. You'd probably be able to handle hundreds (if not thousands) of events per second on a single node, and without distributed complexity it'd be really nice.

And yes, in theory you could still use postgres for this (and just never delete rows). And maybe that's the answer.

valzam2• 3 years ago

Considering that you have a native "offset" (auto incrementing id) and the ability to partition by date I would say postgres is a great candidate for a simple Kafka replacement. It will also be significantly simpler to set up consumers if you don't really need to whole consumer group, partition etc. functionality.

maxbond2• 3 years ago

Unfortunately `serial` is not sufficient on it's own for that use case. If you observe the values 1, 2, and 4, you can't actually conclude whether 3 exists or not. That transaction may have failed after incrementing the serial, meaning 3 does not exist, or it may be an ongoing transaction which hasn't yet committed, meaning 3 exists but is not yet visible to you.

So if you update your offset to 4 before the transaction for 3 commits, you'll lose 3 forever (unless you control for this, eg by periodically detecting & requeueing orphaned jobs, or by using a strictly serial/gap free integer rather than the built in auto incrementing type).

chalcolithic• 3 years ago

I used to hope that FasterLog would eventually be mature enough

what-no-tests2• 3 years ago

Really unimpressed with so many people thinking it's OK to shoehorn their database as a message queue.

Use the right tool for the job.

beachy• 3 years ago

Our industry is full of shysters pushing their own technology. Time and again, it turns out that a RDBMS will handle that job just fine. That's really the premise of this article.

So please, go on and back up your bold statements with some specifics. Why specifically is it not OK to use a database as a message queue?

what-no-tests2• 3 years ago

No time at the moment to break it all down, but here's a previous discussion thread on a similar topic.

https://news.ycombinator.com/item?id=27483003

dangoodmanUT• 3 years ago

It's interesting that Temporal is not addressed in this

mianos4• 3 years ago

Skype used postgres as queue with a small plugin to process all their CDR many years ago. I have no idea if it used these days but it was 'web scale', 10 years ago. Just working, while people on the internet argued about using a database as a queue is an anti-pattern.

Having transactions is quite handy.

https://wiki.postgresql.org/wiki/SkyTools

I did a few talks on this at Sydpy as I used it at work quite a bit. It's handy when you already have postgresql running well and supported.

This said, I'd use a dedicated queue these days. Anything but RabbitMQ.

reubenbond• 3 years ago

Anything but RabbitMQ.

Would you mind elaborating on this? I'd be happy for others to chime in with their experiences/opinions, too.

mianos4• 3 years ago

The software works excellently in a development environment and performs well when running as a single instance. However, I encountered issues when scaling it up for high availability in a clustered setup. The system would fail inconsistently, with two masters consuming messages simultaneously, which wasn't ideal for my use case. Eventually, I switched to Kafka and haven't revisited the original solution since.

It's worth noting that these issues might have been due to my improper configuration. Nevertheless, if the configuration process is fraught with pitfalls, that's problematic in itself. I've had these experiences more than once.

Additionally, I found a critical race condition in the Python library, rendering it practically unusable for me. I submitted a bug report with a minimal example demonstrating the issue. I considered fixing it myself, but since using RabbitMQ wasn't crucial for my project, I switched to ZeroMQ, which didn't require a broker. The issue was acknowledged and fixed about a year later. At the time, I had to assume that nobody else was using the Python bindings.

Three years ago, I worked on a project that used the software for a Celery queue. Messages would occasionally go missing, although this could have been a configuration issue on our part. Ultimately, we replaced it with a Redis queue (not the best practice, I admit) and didn't look back. This was for a lower-availability use case where a single instance of Redis sufficed.

phamilton4• 3 years ago

I can share our experience with RabbitMQ/SQS/Sidekiq. Our two major issues have been around the retry mechanism and resource bottlenecks.

The key retry problem is "What happens when a worker crashes?".

RabbitMQ solves this problem by tying "unacknowledged messages" to a tcp connection. If the connection dies, the in-flight messages are made available to other connections. This is a decent approach, but we hit a lot of issues with bugs in our code that would fail to acknowledge a message and the message would get stuck until that handler cycled. They've improved this over the past year or so with consumer timeouts, but we've already moved on.

The second problem we hit with RabbitMQ was that it uses one-erlang-process-per-queue and we found that big bursts of traffic could saturate a single CPU. There are ways to use sharded queues or re-architect to use dynamically created queues but the complexity led us towards SQS.

Sidekiq solves "What happens when a worker crashes?" by just not solving it. In the free version, those jobs are just lost. In Sidekiq Pro there are features that provide some guarantees that the jobs will not be lost, but no guarantees about when they will be processed (nor where they will be processed). Simply put, some worker sometime will see the orphaned job and decide to give it another shot. It's not super common, but it is worse in containerized environments where memory limits can trigger the OOM killer and cause a worker to die immediately.

The other issue with Sidekiq has been a general lack of hard constraints around resources. A single event thread in redis means that when things go sideways it breaks everything. We've had errant jobs enqueued with 100MB of json and seen it jam things up badly when Sidekiq tries to parse that with a lua script (on the event thread). While it's obvious that 100MB is too big to shove into a queue, mistakes happen and tools that limit the blast radius add a lot of value.

We've been leaning heavily on SQS the past few years and it is indeed Simple. It blocks us from doing even marginally dumb things (max message size of 256gb). The visibility timeout approach for handling crashing workers is easy to reason about. DLQ tooling has finally improved so you can redrive through standard aws tools. There are some gaps we struggle with (e.g. firing callbacks when a set of messages are fully processed) but sometimes simple tools force you to simplify things on your end and that ends up being a good thing.

boyter• 3 years ago

I used RabbitMQ for a while and nothing but problems.

Admittedly I probably shouldn't have used it the way I did. I dumped many millions of tasks into it, then fanned out processes pulling from that queue that took a variable amount of time to run. Some ran in seconds, some hours.

I had picked RabbitMQ because I wanted that queue to be durable and resist workers dying, or being restarted. However long lived tasks like this is not really what it was designed for (in my opinion). I kept running into issues where it would take a long time to restart, and stop answering connections and need a restart to continue. I ended up having to write monitoring code to check for this and handle it to have it be slightly reliable.

Im sure it works well for smaller short lived messages, but considering the issues I bumped into I would be hesitant to try it. Id probably reach to redis first with wrappers allowing me to swap out to any other queue as required first.

justinclift• 3 years ago

Anything but RabbitMQ.

Bad experiences? If so, was it the tech itself or surrounding stuff? (aka maybe docs, maybe community, etc)

lmm5• 3 years ago

Skype used postgres as queue with a small plugin to process all their CDR many years ago. I have no idea if it used these days but it was 'web scale', 10 years ago. Just working, while people on the internet argued about using a database as a queue is an anti-pattern.

It works great until it doesn't, and the way it breaks puts you in a state that's very difficult to recover from. And if your excuse for using a database as a queue was that you were already running a database, that cuts both ways: congratulations, your queue mess has now brought down your primary datastore too.

dventimi2• 3 years ago

I wonder if it would be possible to have more than just one PostgreSQL database.

hn_throwaway_993• 3 years ago

congratulations, your queue mess has now brought down your primary datastore too.

Just don't put your queue tables/logic in the same DB instance as your datastore. There are still a lot of benefits to using the same tech even if you have segregated instances.

mianos4• 3 years ago

It works great until it doesn't, and the way it breaks puts you in a state that's very difficult to recover from.

This is similar to saying, 'if I mess up all the tables in one database I wreck the rest'. Just my opinion, but this is not actually a thing in databases.

Maybe compromised the performance of one database due to another loading things up? I think database are developed with this as an important consideration. I can't say I have seen this, but admit it's a possibility.

Also, if you run one postgres, you won't have of an issue running another if you have the experience in production.

hn_throwaway_993• 3 years ago

This said, I'd use a dedicated queue these days.

I agree, primary reason being that if you're in the cloud (thus this applies to a lot of people but obviously not everyone), all the cloud providers have extremely easy to use, and cheap, hosted queueing tech. Even if you're worried about vendor lockin, queueing primitives are so small (basically push and pop), that it's relatively easy to write things in a way so it would be easy to migrate if necessary.

klysm7• 3 years ago

Is it cheap if you already using Postgres though?

pyrophane• 3 years ago

I don't have a problem with this approach at all, but I would argue that for many use cases a redis backed job library like Celery or Sidekiq might be even easier, especially if you are on a cloud provider that offers managed redis.

jgavris• 3 years ago

I wrote a hacky version of SKIP LOCKED using advisory locks and a recursive CTE before it was released for a job queue. It worked splendidly, along with the transactional semantics of a proper database. I’m surprised more systems don’t realize they need it.

codeadict• 3 years ago

This is exactly what the Oban https://getoban.pro/ Elixir library uses and combining postgres plus actors for queues scales pretty great for 90% of the needs out there. I have used it at my last few jobs at pretty decent scale and would take it over 10 years using Celery to manage queues + supervisord, setting up RabbitMQ or Redis. Its so simple you only need Elixir and Postgres and not 3 or 4 infrastructure pieces to manage a queue.

liminal• 3 years ago

My main issue with pretty much all queue approaches is that they don't work across platforms. They are built for one technology stack, be it Python/NodeJS/etc. This is fine if you've only got one stack, but in a microservices world it doesn't work where jobs can span multiple systems. You might be able to find some abandoned library that supports that queue tech on the other platforms you need, but now you've basically become a queue tech maintainer.

jerrygenser• 3 years ago

There are queues like sqs and google pubsub where there is already official clients written for most popular languages. And they also have http API so you can use it in any language environment that can make http requests.

mrkeen• 3 years ago

My main issue with pretty much all queue approaches is that they don't work across platforms.

What technology stack are you working in which doesn't support postgres?

but in a microservices world it doesn't work where jobs can span multiple systems

The point of the queue system is to be able to span said microservices. You can have an OCaml service picking up from one queue, processing, then writing into another queue. That queue could then be processed by a TypeScript service.

Ericson2314• 3 years ago

Even more fun than notify/listen is using the write-ahead log! :D

Xeoncross• 3 years ago

As an industry, we’ve become absolutely obsessed with “scale”.

I wish the industry was even half as concerned with efficiency as it was with scale. Bitcoin? Electron? 5MB web pages? 5/10/25GB downloads to run 20yr old CD-sized games on modern software?

menacingly• 3 years ago

I've been thinking a lot lately about how much of tech, and life more broadly, is ruined by the pursuit of scale.

Taking on problems you don't (and will never) have because some vanishingly small minority has experienced them is nuts. Over-engineering is as incorrect as under-engineering. The correctly sized, correctly complicated answer is what we're after.

xpe4• 3 years ago

Over-engineering is as incorrect as under-engineering.

I know what you mean. The catch is figuring out 'the correctly sized' answer. Uncertainty is a huge factor here.

I think a lot of projects that are called 'over-engineered' have the benefit of hindsight. At least some of such projects were rationally designed based on what people knew at the time. There is often significant uncertainty. When engineering resources are hard to acquire, the powers-that-be often want to 'get it right' the first time. Translated: over-engineer it, because we don't know when we'll get resources for the next iteration.

I suppose my point is this: the next time somebody finds some 'over-engineered' code, it can be insightful to learn about the project history. (Example: some projects have overly optimistic prediction of usage. This perhaps suggests that pessimism is one antidote to over-engineering.)

dikei• 3 years ago

IIRC, LISTEN/NOTIFY needs to pin a PostgreSQL connection to the client, so you won't be able to use transaction-level pooling with it.

rockwotj2• 3 years ago

I used a separate dedicated connection for listen that wasn’t in the pool. Notify is fine to be apart of a normal txn

LennyWhiteJr2• 3 years ago

In my Amazon team, we use PostgreSQL as a queue using skip-locked to implement transactional outbox pattern for our database inserts. People commenting 'just use a queue' are totally missing the need for transactional consistency. I agree with the author, it's an amazing tool and scales quite well.

rockwotj2• 3 years ago

All of shortwave.com is built on this concept. The super powerful bit here that is the mentioned is that enqueuing jobs is transactional with other normal transactions, so you don’t have to architect around using a different system for the queue and the rest of your data.

TalktoCrystal• 3 years ago

I think this may help. A control plane for PostGre and Kafka and Pulsar. https://github.com/apecloud/kubeblocks/blob/main/docs/releas...

donatj4• 3 years ago

We have our own queue, because it was easy, fun and has been exceedingly reliable above all else. Far moreso than other things we had tried. Cough Gearman cough SQS cough

One endpoint accepts work to a named queue, writes it to a file in an XFS directory. Another locks a mutex, moves the file to an in progress directory and unlocks the mutex before passing the content to the reader. A third and final endpoint deletes the in progress job file. There is a configurable timeout, after which they end up at a dead letter box. I am simplifying only a little bit. It's a couple hundred lines of Go.

The way this is set up means a message will only ever be handed to one worker. That simplifies things a lot. The workers ask for work when they want it, rather than being constantly listening.

It took a little tuning but we process a couple billion events a day this way and it's been basically zero maintenance for almost 10 years. The wizards in devops even figured out a way to autoscale it.

amerine• 3 years ago

Sounds neat. Do you have the go code anywhere for folks to poke at?

donatj4• 3 years ago

Nah, afraid not. I wish.

We always wanted to open source it, but we got bought out by a big and very IP protective company before we got the chance.

mr-karan• 3 years ago

The workers ask for work when they want it, rather than being constantly listening

Can you elaborate more on this? How do the workers know when they have to process a new job?

Also, am I right in assuming this is typically a single node setup only, as all the files are mounted on a non "share-able" XFS disk?

donatj4• 3 years ago

They ask for work after they finish the previous job (or jobs, they can ask for more than one). Each worker is a single process built just for one task.

If there's no work for them there's a small timeout and they ask for more. Simple loop. It's all part of a library we built for building workers. For better or worse, it's all done over http.

You are right, though, it is one XFS volume per queue instance.

We just run multiple instances (EC2) on a load balancer. Each instance of the queue gets it's own set of workers though so the workers know the right server to report done to.

We want a way to have a single pool of workers, rather than a pool per queue instance, and have them talk to the load balancer rather than directly, but we haven't come up with a reasonable way to do that.

Karrot_Kream3• 3 years ago

I'm curious what throughput are you moving? How many tasks per second on average and how long does each task take to be serviced, on average?

matja• 3 years ago

I'd just be aware that XFS has no data journaling, only metadata.

winrid• 3 years ago

What if a task fails/crashes?

hknmtt• 3 years ago

Why files though, and why move them into different directories? You said billions a day. With files, the physical drive must be taking a beating. Not to mention potential issues with directory file limitations(based on OS and file system). Why not use some kvdb?

lijok2• 3 years ago

As I understand (correct me if I'm wrong, it's been forever since I've worked with filesystems) - file renames are very cheap as the actual data does not get moved, simply a journal gets updated

lijok2• 3 years ago

Built very similar but on S3. Jobs have statuses, land in /jobs, indexed by status at /indexes-jobs/PENDING, etc. Scheduler polls for jobs in PENDING index, acquire lock, pass job to processor, change its status to COMPLETE or DEAD.

300~ LOC or so and fairly easy to test. Wouldn't take that approach every time, but definitely worth it when you're aiming for a simple architecture.

xpe4• 3 years ago

I hope to disimbue anyone of the notion that Postgres is an inferior queue technology.

I offer this correction to the author: it is "disabuse" not "disimbue".

mlyle5• 3 years ago

Disimbue is also a word with an appropriate meaning-- even if it's archaic (and no longer appears in most dictionaries) and pretentious.

xpe4• 3 years ago

No, I won't believe it until I see evidence.

Did you find it in a credible source? Which one(s)?

I've found nothing credible in Merriam Webster, Etymology Online, nor _any_ other I've searched. There is at least one low-quality ad-serving site that credits ChatGPT with a definition.

I'm happy to be enlightened.

zacksiri5• 3 years ago

During my tenure as CTO at a fintech company I built a banking engine using postgres backed queue system using Elixir / Phoenix. It's still in use today. The company processed large volumes of transactions and we were able to do things in real-time in terms of payments. Our system reached a point where I realized that we can scale almost infinitely just using a 2 tier architecture (Elixir / Phoenix / Oban and PostgreSQL)

The industry standard for real-time was anything under 30s end-to-end. We hit many scaling limits as our platform grew. We worked on improving the system instead of looking at postgresql as a problem. We measured and measured and postgresql was never the problem when it came to scaling. Most of the problems that existed was mostly due to design decisions in the system or some technical debt that needed to be solved. When we solved those problems the system flew.

There was one incident where our PostgreSQL instance CPU usage went up to 100% during peak hours and would cause problems in the system. We wondered why, there was a lot of debate around the topic, PostgreSQL doesn't scale, we need a larger instance, or move to a dedicated queue system etc...

I didn't believe the hypothesis that PostgreSQL didn't scale. So I conducted a performance audit on the system myself I discovered one of the engineers forgot to add an index on one of the columns on a major table with > 60m records. There was a lot of back and forth between engineers that discussed whether we should add the index. I simply conducted a small experiment and PG analyze clearly showed a missing index in one of the key tables. After we added a single line of code and deployed the index, during peak hours DB CPU usage would not even exceed 20% we had to scale down our DB to save some $$.

PostgreSQL backed queue system does work.

I've been building distributed systems in large and small scales for over a decade. Before building systems with Elixir / Phoenix I also used to work with Rails where the default is as mentioned in this article is you just adopt Redis. While Redis does have additional overhead when it comes to management of the infrastructure, it also worked well.

Having been through both I'd say a simple PostgreSQL backed solution is the clear winner when it came to not having to manage a 3rd dependency when you already have a complex system. There was no 3rd or 4th piece of the puzzle to theorize hypothesize or experiment with. You just had 2 pieces it is either your application or your database that's the problem. That simplicity saved us a lot of time for debate and doing experiments also squashed any dogmatic values and opinions that just didn't hold any water.

SanderNL• 3 years ago

Interesting how the immediate reaction is “postgres does not scale” when there is a single table lacking an index.

This also tells how important competence and knowledge of the system is. People that came in new and didn’t know the system like you do probably lacked the confidence/skills to just “get in” like that.

zacksiri5• 3 years ago

Yeah, I think though what happened in this scenario probably happens a lot everywhere else also. In my entire career, this type of scenario is very typical. Lots of Meetings / discussions, standups and talking uselessly without jumping in face to face with the actual problem. Things get in the way of the science and facts. Which is why it's important to remove fear, think from first principles and break things down and get your hands dirty.

ansc• 3 years ago

There was a lot of back and forth between engineers that discussed whether we should add the index.

Jeez. What was the idea behind not adding? Disk space I presume?

zacksiri5• 3 years ago

There was a fear that having to create an index on a table that large would take a long time, and I think some of it was also ego "I intentionally didn't add it in, because so and so reason". This was why I dug in and did my thing, debunk all the fear / opinions / rationalization. Sometimes you just gotta be able to tell people they're wrong supported with empirical evidence. That's how the team will grow. There is just no need to dance around facts. I remember having to tell the team, "taking a long time to run an index is no reason to avoid creating the index".

ezekiel682• 3 years ago

> I simply conducted a small experiment and PG analyze clearly showed a missing index in one of the key tables.

Based on this sentence, I interpreted that part as representing that the engineers did not believe the missing index was causing the problem (until the experiment was run).

mixmastamyk4• 3 years ago

Nice. How did you split the data and queue records? Tables, dbs, partitions, etc?

zacksiri5• 3 years ago

The job queue had it's own table. It's basically whatever was the default of the job queue library we were using.

jasongi• 3 years ago

I’m not against using Postgres for this. But I am against the rolling your own distributed task queue. It always seems like a simple task but snowballs in complexity. Any gains you get simplifying your stack will be wiped out by the fact that things like Celery (for example) don’t support using Postgres as a broker so now you have to do your own DIY Celery instead of say, just using Celery with the SQS broker (which… since we’ve established scale isn’t being considered here, SQS costs shouldn’t be an issue either).

Anyone know if there are Celery or Celery-like tools that support Postgres as a broker?

As a side-note, if you want a simple no-frills task scheduler ap-scheduler is a dead simple option. It’s even more limited than the solution described in OP (you can only run one worker so it’s not distributed at all) but often it is all you need especially for toy projects.

welder3• 3 years ago

I’m not against using Postgres for this. But I am against the rolling your own distributed task queue.

Good thing I didn't listen to your advice... my DIY background task queue saved my website when Celery couldn't scale. Why are you against rolling your own task queue besides it seeming complicated?

https://wakatime.com/blog/56-building-a-background-task-queu...

Rapzid2• 3 years ago

Not sure this particular article brings anything new to the table, but it's nice to spread the word.

Been meaning to build an Orleans stream provider for Postgres.. I believe that's the main missing component that would allow everything to "JustWork" with Postgres until you outgrow it.

AlexITC• 3 years ago

I have been involved in a few projects using postgres-bakend queues for a few years, scale hasn't been a problem so far.

On the other hand, I have done a few experiments with postgres LISTEN/NOTIFY, while the feature seems nice at first glance, I concluded that it wasn't worth it for our use cases, maybe it is different in other languages but in the JVM, you have to allocate 1-thread for polling these results, which also keeps a connection busy.

What I ended up doing is leveraging akka-stream to stream the queue data directly from the db, which makes it simple to define throttling rules, this is super simple and effective.

debuggerpk• 3 years ago

on a sidenote, what is the theme on this blog?

s17n• 3 years ago

I don't usually downvote posts, but this article is just garbage - a rant about "the cargo cult of scale" but no actual arguments as to why Postgres is better than redis or sqs or anything else. The main reason that people don't use postgres for this kind of thing isn't some kind of misguided obsession with scalability, it's because postgres is way more complicated to deploy / manage and harder to use for application developers than any of the other options.

qaq2• 3 years ago

Yep but in many apps postgres is already there so redis or sqs has to be managed in addition to postgres.

mlyle5• 3 years ago

The argument is stated-- you shouldn't adopt too many technologies. The author merely says that if you're running postgres already, you should seriously consider it for your queue. They also point out that many of the arguments against psql-as-queue are flawed.

figassis2• 3 years ago

The way I implement my queues (usually as part of my monolith application) is as go routines. Each instance of the app launches with a unique id, and also a role. It can be a worker or the app itself. So when the app generates a queue item, it simply adds it to a table as pending. A worker will then, via transaction, update a set of items to add its instance id as well as an expiration for this lock. If that succeeds, no other worker will pull a queue item with a non null id or with an id different that it’s instance id that is not expired. Worker can then start processing and update item status accordingly. If it crashes, another worker will just repeat the process after the lock expires.

The code that does this is maybe 100 lines at most. It’s very effective especially if you deploy your app in kubernetes where you can expect instances to be ephemeral. It’s one of the components of my apps that has never needed any updates since I first wrote it circa 2017.

perfmode• 3 years ago

How are workers notified of new work? Or do they poll and sleep?

figassis2• 3 years ago

They poll. It's easy however to add pub/sub via redis. Queues are categorized in frequency. So for example a notification queue might process every minute or 5 minutes, a reconciliation queue might run every 12 hours, or have a fixed time, like daily at 2am and 6pm, etc. Each queue runs on its own goroutine on its onwn schedule, and every worker runs all queues. I can also adjust how many items a worker can pull each time so queues do not get backed up. Say there is some criteria an account needs to meet to be eligible for some feature. If that criteria requires some expensive db queries, I can run a daily job that pulls 100 accounts each time to check that they meet the criteria. But if the instances can process more, I can configure it to pull 1000 accounts. Or I can add 9 workers and each pulls 100 accounts.

Usually what I do is pull the records that were updated least recently (as in they should be ahead of the queue). So if a previous worker locked the oldest X records, the second worker will pull the next batch bc the condition will exclude the previously updated (locked) records. There's a lot of flexibility you can add with just these controls: schedule, frequency, batch size, number of workers.

ezekiel682• 3 years ago

I hate to point out something uncomfortable, but this guy's combative writing style and penchant for abstract art reminds me of the articles by a certain infamous creator of the ReiserFS file system for linux.

foreigner2• 3 years ago

I'm in the market for a Postgres-backed queue system with client libraries in NodeJS _and_ Python. Clients in both languages need to be able to read and write from the queue. Can anybody suggest one?

chuckhend• 3 years ago

PGMQ does not require a client library, https://github.com/tembo-io/pgmq so long as your language of choice can run SQL. All the functions live in Postgres, and you just call them with SQL statement. Very similar feel and semantics to SQS.

foreigner2• 3 years ago

Thank you!

fasteo• 3 years ago

Over the years I have used beanstalkd for all things queue. Working flawlessly every single time.

This is an output for our oldest instance (legacy system running Ubuntu 12)

  Trying 127.0.0.1...
  Connected to localhost.
  Escape character is '^]'.
  stats
  OK 952
  ---
  current-jobs-urgent: 0
  current-jobs-ready: 765
  current-jobs-reserved: 2
  current-jobs-delayed: 946
  current-jobs-buried: 0
  cmd-put: 1188640739
  cmd-peek: 2718986
  cmd-peek-ready: 5052
  cmd-peek-delayed: 797
  cmd-peek-buried: 797
  cmd-reserve: 0
  cmd-reserve-with-timeout: 3245006799
  cmd-delete: 1188639093
  cmd-release: 43276760
  cmd-use: 1137988211
  cmd-watch: 166122
  cmd-ignore: 166077
  cmd-bury: 0
  cmd-kick: 1
  cmd-touch: 0
  cmd-stats: 1260
  cmd-stats-job: 43134828
  cmd-stats-tube: 53942209
  cmd-list-tubes: 3251625
  cmd-list-tube-used: 0
  cmd-list-tubes-watched: 0
  cmd-pause-tube: 988
  job-timeouts: 14084
  total-jobs: 1188640739
  max-job-size: 1048576
  current-tubes: 44
  current-connections: 63
  current-producers: 20
  current-workers: 47
  current-waiting: 41
  total-connections: 14996583
  pid: 3959
  version: 1.4.6
  rusage-utime: 170303.331293
  rusage-stime: 399435.543161
  uptime: 321658179
  binlog-oldest-index: 90539
  binlog-current-index: 90983
  binlog-max-size: 10485760

alexbezhan• 3 years ago

I'm using SQLite as my main database. Is it a bad idea to build queues on top of SQLite?

dunno7456• 3 years ago

andrewstuart6• 3 years ago

SKIP LOCKED guarantees that a row can only be read once. This is critical for queueing systems.

SQLite does not support SKIP LOCKED.

winternewt• 3 years ago

The official JDBC driver for PostgreSQL provides no way for the database to push events to the client. So you need a dedicated connection to continuously poll the database to see if there are any events available. This seems half-baked and does not give me the warm and fuzzy feeling I crave when making architectural choices. Not to mention, it causes undesirable latency in event delivery unless I flood the database with queries.

afhammad• 3 years ago

There are a few mentions of Oban [1] here. Most people don't realise that Oban in fact uses SKIP LOCKED [2] as well.

Oban's been great, especially if you pay for Web UI and Pro for the extra features [3]

The main issue we've noticed though is that due to its simple fetching mechanism using locks, jobs aren't distributed evenly across your workers due to the greedy `SELECT...LIMIT X` [2]

If you have long running and/or resource intensive jobs, this can be problematic. Lets say you have 3 workers with a local limit of 10 per node. If there are only 10 jobs in the queue, the first node to fetch available jobs will grab and lock all 10, with the other 2 nodes sitting idle.

[1] https://github.com/sorentwo/oban [2] https://github.com/sorentwo/oban/blob/main/lib/oban/engines/... [3] https://getoban.pro/#feature-comparison

ritzaco2• 3 years ago

I often see the "engineers copy FAANG infrastructure because they want to be cool, even though their needs are completely different" take as a kind of attack on engineers.

But I think a lot of it is also about knowledge and documentation. If I want to copy FAANG or another startup, and set up an infinitely scalable queue-based architecture, I can find dozens of high quality guides, tutorials, white papers etc, showing me exactly how to do it. Yes maintenance is higher, but I can get set up with redis, SQS, any of the 'scalable' solutions within a few hours of copy-pasting commands and code and configuration from a reputable source.

If I want to use NOTIFY in postgres? I googled "SQLALchemy notify listen postgres" and I find a few unanswered stackoverflow questions and a github gist that has some code but no context.

I would honestly love to use this approach for a side project, but I don't have 2-3 days to figure it out on my own. The direct choice for me might seem to be

* simple, but not scalable (ie just use postgres)

* complex, but scalable (ie redis, sqs, whatever)

and then it's a tradeoff, and the argument goes that I am blinded by cool tech and FAANG and I'm choosing complex but scalable, even though I don't need scalable.

But taking into account guides and other resources, the choice for me is actually

* complex and not scalable (this, because I don't know how to implement it and I can't predict what pitfalls I might face if I try)

* simple and scalable (what everyone actually does)

and that makes the engineer's choice to follow faang look a lot more reasonable.

natmaka3• 3 years ago

Another point is: you don't need scalable now, but may (or even hope) to need it later, and you know that when you will need it you probably won't have time to invest into migrating this component.

Also: you may think that you may one day want to be hired by a FAANG.

mkl952• 3 years ago

How relevant is it to be hired by a FAANG? I have some experience with "web scale" systems, but I tend to reject FAANG recruiters because Leetcode makes me want to become an apple farmer (no pun).

tmpX7dMeXU• 3 years ago

Yeah. Just keep it to side-projects only. Anyone practicing resume-driven development on my team will be (and I’m exaggerating here) shown the door.

runeks5• 3 years ago

If your first point holds, then all app components should be “scalable” from the beginning, because you may not have time to make it so later.

And that’s terrible advice, of course. You very likely will have time to scale things up (customer count almost never increase dramatically from one day to the next), and even if you don’t you’ll most likely never deliver a useable product if all components need to be “scalable” from the beginning.

evantbyrne• 3 years ago

Hard disagree. Some things are difficult to change later on, others not so much, and you can't do everything for v1. The product has to launch at some point. Your choice of queue is one of the things you'll be able to change. Don't complicate things unless you've run the numbers and know you'll need to. A lot of very large companies do just fine with using relational databases as queues.

_jal• 3 years ago

Building things you don't need in hopes that you'll need them because things you aren't spending time on will grow to demand them is like hiring an investment manager when you're in debt.

zozbot234• 3 years ago

If I want to use NOTIFY in postgres?

The nice thing about "boring" tech like Postgres is that it has great documentation. So just peruse https://www.postgresql.org/docs/current/sql-notify.html . No need for google-fu.

ritzaco2• 3 years ago

Python, Flask, SQLAlchemy, and Postgres all have great documentation individually, but if I am building an application at the intersection often a guide on exactly how to join them all up is much faster than using each individually and trying to figure out the interactions in four places.

AWS white papers and engineering blogs tend to give me everything I need in one place, and I don't think there are any for apps built with NOTIFY.

ranting-moth• 3 years ago

Yes maintenance is higher

This is what kills you if you're a small startup. Of course it gives you a lot too. But if you're belly up then it doesn't matter.

Of course go for whatever solution gives you the most benefits while not distracting you too much from your main goal.

I've seen a startup where devs spent around 80% of the time fighting their tools and infrastructure. They had a 3 month runway and today there's a massive hole at the end of that runway. I still shudder form just the thought of it.

PH95VuimJjqBqy2• 3 years ago

This line stood out to me too as very dismissive of something that can absolutely bring you to a standstill.

It's not a small thing and it's not something you should be dismissing out of hand.

valenterry4• 3 years ago

Scalability comes at a price. Unless you need it, it makes you less flexible. And that is exactly what you don't want to be as a startup.

For instance, if you use postgres with a low load, it is almost trivial to migrate schemas, add new constraints, do analytics etc.

If you use SQS, Cassandra, whatever, then you now get scalability/availability but it becomes much more time-consuming to change things if you figure out that your original design doesn't work. Say the business comes and says "please add constraint X. All users of type foo must never combined value bar at the same time."

It is possible to implemented that without postgres, but it is not easy or simple, especially if you need to make changes.

Therefore, my take is that you either use postgres to stay flexible or you use both postgres and something else on top of it when you know that you won't have to change things. Of course this means additional infrastructure/maintenance overhead.

In the end it's always a trade-off, you just need to know when to trade which thing off against what.

Lutger2• 3 years ago

This is all true, important and often misunderstood, but beside the point made to which you reply.

There's a (sort of) objective trade-off to be made, but another dimension is how familiar you are with the solution and/or how quick can you implement it using documentation and examples.

If you happen to know exactly how to create a horizontally scalable microservice based hairball with nodejs, then maybe you are quicker with that than with some traditional django monolith using a nicely normalized sql database (or whatever).

In a startup, you are often always squeezed for time, so making the objectively right tradeoff for your context is usually secondary to the simple question of 'when can you ship?' If the scalable-yet-inflexible is what stack overflow abundantly recommends and documents, maybe this is quicker to get done now, whatever the consequences are on the longer run.

gazpacho2• 3 years ago

Scalability comes at a price. Unless you need it, it makes you less flexible. And that is exactly what you don't want to be as a startup.

This is a valid comment. I’ve chosen Postgres in the past for the features, not the performance. For example guaranteed at most once delivery (via row locks) and filtering of jobs based on attributes (it’s a database after all).

edanm• 3 years ago

That's a great point, that often people misunderstand.

It's even worse than you say though. As someone who has used neither Postgres or Redis for queueing, how am I supposed to know what is the "simple" solution here and if it really solves my problem?

Almost everyone uses solution X. A few people are saying "no, just use solution Y, it's obviously enough and far simpler". Even if it is far simpler, how am I supposed to know whether there are some hidden gotchas here?

Much safer to bet on technology that is proven to work, given that large amounts of people are using it in production for this purpose.

acaloiar4• 3 years ago

If I want to use NOTIFY in postgres? I googled "SQLALchemy notify listen postgres" and I find a few unanswered stackoverflow questions and a github gist that has some code but no context.

Author here. I would say that my post is less targeted at someone like you (application developer, presumably) and more targeted at library developers.

I don't think it's ideal for everyone to be implementing bespoke, Postgres-backend (or any other queue for that matter) background job workers in their applications. There's a lot of nuance and implementation details to get wrong with background jobs, and for that reason I think background work should generally be done by more comprehensive, dedicated libraries or frameworks.

If every Rails application didn't have Sidekiq/Active Jobs and instead had bespoke background worker implementations, Rails applications would likely have a much less rosy reputation on account of their unreliability.

JohnBooty2• 3 years ago

Thank you for writing this.

I love the article's point, and I tend to feel that the "chasing the cargo cult of 'scale'" is maybe the biggest problem I see in development teams today. It is certainly the biggest problem that I rarely hear anybody talking about.

    Author here. I would say that my post is less 
    targeted at someone like you (application developer, 
    presumably) and more targeted at library developers.

I think the article might benefit from clarification on this point.

Reading the HN comments, I see that I'm not the only person who came away with a misunderstanding there.

Again, I 100% love the overall point.

JohnBooty2• 3 years ago

I agree with you:

    but I can get set up with redis, SQS, any of the 
    'scalable' solutions within a few hours of copy-pasting 
    commands and code and configuration from a reputable 
    source

    [...] and that makes the engineer's choice to follow faang 
    look a lot more reasonable.

I also agree with the linked article's overall point, but I think the specific "job queue" example from the article is actually a bad example because:

- "rolling your own" job queue is not rocket science but is nontrivial and easy to get wrong w.r.t. locking etc.

- the argument against taking additional dependencies is that now you have one more tool to master, understand, and manage. but my experience is that job queues like Sidekiq are not a significant overhead in terms of developer burden.

PH95VuimJjqBqy2• 3 years ago

and that makes the engineer's choice to follow faang look a lot more reasonable.

Your argument is that going with FAANG level designs saves time?

And the crux of your argument is that you're able to find a guide online?

I strongly suspect you don't have a healthy respect for complexity.

polyrand• 3 years ago

Some time ago, I wrote a queue using SQLite[0]. Instead of SKIP LOCKED, you can use RETURNING to lock-and-read a message and ensure only one worker is going to pick it up:

  UPDATE ... SET status = 'locked' ... RETURNING message_id

Or you can just use an IMMEDIATE transaction, SELECT the next message ID to retrieve, and UPDATE the row.

On top of that, if you want to be extra safe, you can do:

  UPDATE Queue SET status = 'locked' WHERE status = 'ready' AND message_id = '....'

To make sure you that the message you are trying to retrieve hasn't been locked already by another worker.

[0] https://github.com/litements/litequeue/

[1] https://github.com/litements/litequeue/blob/3fece7aa9e9a31e4...

sgarland6• 3 years ago

I just want to commend OP - if they’re here - for choosing an int64 for job IDs, and MD5 for hashing the payload in Neoq, the job library linked [0] from the article.

Especially given the emphasis on YAGNI, you don’t need a UUID primary key, and all of its problems they bring for B+trees (that thing RDBMS is built on), nor do you need the collision resistance of SHA256 - the odds of you creating a dupe job hash with MD5 are vanishingly small.

As to the actual topic, it’s fine IFF you carefully monitor for accumulating dead tuples, and adjust auto-vacuum for that table as necessary. While not something you’d run into at the start, at a modest scale you may start to see issues. May. You may also opt to switch to Redis or something else before that point anyway.

EDIT: if you choose ULID, UUIDv7, or some other k-sortable key, the problem isn’t nearly as bad, but you still don’t need it in this situation. Save yourself 8 bytes per key.

[0] https://github.com/acaloiaro/neoq

acaloiar4• 3 years ago

Thanks! I'd like to believe that those decisions were incredibly thoughtful, but they're largely Bob Rossian happy accidents.

wholesomepotato• 3 years ago

md5? blake3 is both faster and cryptographically secure, and not a novelty at this point.

gazpacho2• 3 years ago

I feel like one of the problems with using Postgres as a queue is that it’s hard to get started. There’s a lot you need to know. Getting started with something like Pub/Sun on GCP is much easier for many developers.

I’ve experimented with making this easier via libraries that provide high-level APIs for using Postgres as a queue and manage the schemas, listen/notify, etc for you: https://github.com/adriangb/pgjobq

KronisLV• 3 years ago

For example, this Hacker News comment stated that using Postgres this way is “hacky” and the commenter received no pushback. I found the comment to be load of BS and straw man arguments. This thinking seems to be “the prevailing wisdom” of the industry – if you want to talk about queue technology in public, it better not be a relational database.

I don't think that there's anything wrong with using a database as a queue, however, I think that there probably could have been better ways to get across the idea, rather than just dismissing an honest opinion as BS. I don't necessarily agree with all of what was said there, but at the same time I can see why those arguments would be reasonable: https://news.ycombinator.com/item?id=20022572

For example:

Because it is hacky from the perspective of a distributed system architecture. It's coupling 2 components that probably ought not be coupled because it's perceived as "convenient" to do so. The idea that your system's control and data planes are tightly coupled is a dangerous one if your system grows quickly.

To me, this makes perfect sense, if you're using the same database instance for the typical RDBMS use case AND also for the queue. Then again, that could be avoided by having separate database instances/clusters and treating those as separate services: prod-app-database and prod-queue-database.

That said, using something like RabbitMQ or another specialized queue solution might also have the additional benefit of bunches of tutorials and libraries, as well as other resources available, which is pretty much the case whenever you have a well known and a more niche technology, even when the latter might be in some ways better! After all, there is a reason why many would use Sidekiq, resque, rq, Hangfire, asynq and other libraries that were mentioned and already have lots of content around them.

Though whether the inherent complexity of the system or the complexity of your code that's needed to integrate with it is more important, is probably highly situational.

oooyay• 3 years ago

A couple counter points:

1. Many queueing solutions struggle with at most once delivery. I've used a queue I built on top of RDBMS to solve this before. Ironically, when I was a junior we used a key/value store to lock certain messages for at most once delivery, which doubly compounds my next point.

2. A queue is more infrastructure to manage, and hard to troubleshoot infrastructure at that.

3. Many queueing softwares struggle with deserialization into native types which makes the code on either end unnecessarily obtuse. Using RDBMS queues makes your serialization as good as your drivers.

ekidd2• 3 years ago

I've built three distributed job systems at this point. A handy rule of thumb which I have promoted for years is "build for 10x your current scale."

If you need to handle 70 requests/second, design for 700. If you need to handle 20 servers running batch jobs, design for 200 servers. If you're in a startup that grows 100% per year, you'll be at 8x scale in 3 years. So you'll have time to rewrite as you grow!

Out of three job systems I built, the first one tried to avoid SQL for "scalability." Then we hit a bunch of edge cases that needed transactional integrity, and we were in a world of pain.

My two more recent distributed job systems use PostgreSQL as a coordinator. They're literally built around SELECT FOR UPDATE SKIP LOCKED. One of them routinely controls 350 workers and the other does elaborate prioritization for thousands of jobs. Both of them will continue to run just fine until they're earning millions of dollars a year—for example, the one controlling 350 workers should scale to about 2,000 CPUs with a little work.

Hyperscale technologies are fairly cheap right up until you discover you need transactions. At that point, faking transactional semantics on top of an eventually consistent data store becomes an engineering nightmare.

So sit down, and do the math. If your company was earning $100 million/year, how big would your distributed system need to be? Can you easily get a PostgreSQL instance big enough to handle that load? (Or could you just shard per client?) If so, strongly consider using PostgreSQL. It makes a hundred things trivial.

nerpderp822• 3 years ago

Design (and test) for 10x your current scale, build for what you need now. And the system has to be able to handle peak loads, and if you don't know what those are build in a safety margin or a way to shed or defer work if you need to.

Everything is a tradeoff, optimize for the things that need optimizing, and determining what those are is the hallmark of a good engineer.

insanitybit2• 3 years ago

I've found the same. You should understand what your 10x / 100x growth solution would look like (assuming that that's relevant - obviously if you have no intent to hit that scale, don't bother). Build your system to handle your 1-1.5x, maybe 10x scale, and make sure you're not blocking the 10-100x solutions by doing so.

bunderbunder2• 3 years ago

Similar experience here. Multiple times, I've pushed an SQL-based queue a couple orders of magnitude past the scale where others say SQL craps out and a distributed solution is an absolute requirement. And the SQL solution is typically simpler, requires fewer compute resources, and easier to support in production.

But, to make it work, you've got to know the database well enough to know that things like SELECT FOR UPDATE SKIP LOCKED exist in the first place. Which is a kind of knowledge that's getting quite rare these days, because more and more engineers grow up never having known a world where they aren't walled off from their DBMS's true capabilities by a heavyweight ORM.

runeks5• 3 years ago

Multiple times, I've pushed an SQL-based queue a couple orders of magnitude past the scale where others say SQL craps out and a distributed solution is an absolute requirement.

What about availability, though? The distributed solution is also useful to avoid downtime in case of single node failure.

Is there an off-the-shelf solution that lets me do that with Postgres? I know the newest version (16) just added active-active replication, but I wouldn’t know how to use that to achieve resilience.

merb2• 3 years ago

you can even built a advisory lock queue in Postgres, which is way slower but has some benefits.

pas• 3 years ago

slower to build or slower to run?

aaroninsf• 3 years ago

"faking transactional semantics on top of an eventually consistent data store becomes an engineering nightmare"

PREACH

If this is news to you, you just justified a week's worth of browsing HN on the clock reading this statement. Scribble it on a scrap of paper and keep it taped to your ATM card.

n_time• 3 years ago

Could it be people are choosing over-engineered solutions because AWS has bad documentation and that's what the solutions architects tell them to do?

Could it be because microservices and so-called "server-less" have been sold as cost-saving measures that increase the business' flexibility and decrease capital investment?

When... in reality a single deployed Docker container is way more manageable than a distributed system constructed with "lambdas" and requires fewer engineers in the long run?

What I'm trying to say is that FAANG cargo-culting is only part of why developers choose to build solutions that scale larger than they need. Another large part is the cloud development ecosystem writ-large and the consulting culture that has built up around it.

vb-84483• 3 years ago

Did you ever hear about "no one got fired because he bought IBM/Oracle/Microsoft" ?

It's the same exact thing: most of the people do what is considered a "best practice" just to avoid any type of annoyance when something will break.

welder3• 3 years ago

When I wrote my own background task queue I looked at Postgres, because it was already in use in the stack. Postgres would work for a simple queue, but supporting queue priorities, delayed/eta tasks, and broadcast tasks was too complicated. I decided on Redis, and it's scaled very well over the last year:

https://github.com/wakatime/wakaq

We currently process ~20 million tasks per day, and I don't have to worry about running VACUUM on my queue ;)

jack_squat• 3 years ago

I've done the Postgres skip locked thing at least three times and I'm currently doing it, but IMO it is actually more maintenance and overhead, not less -- at least when compared with the queues made available by the major cloud providers. Compared with Pubsub or SQS you need to handle,

* Metrics, monitoring, alarming on depth, message age

* Autoscaling on your custom metrics

* Managing retries, dead lettering, backoff

* Managing the DB workload: it's update-heavy and may easily be more intensive than the rest of your app. You may need to repeatedly scale your tiny, startup-scale DB, causing customer disruptions, because of your homemade queue.

The arguments for it are either avoidance of lock-in, or the hand-wavy one in this article/many comments: "we're a small startup, we need to minimize complexity, and make things as simple as possible, maintenance will kill us!".

Lock-in makes sense (though other queue alternatives should still be considered), but the latter argument gets used to justify all kinds of harebrained, superficially-simple but actually-complicated engineering schemes. I generally put the PG skip locked approach in that bucket, particularly when the alternative on hand is Pubsub or SQS. If it's between a Postgres table and ActiveMQ I might feel more conflicted.

5Qn8mNbc2FNCiVV• 3 years ago

That's what Temporal is for

insanitybit2• 3 years ago

Something that always bothers me about "Use Postgres as a queue" (something I would suggest, even) is that there are shockingly few people publishing numbers around this. How am I supposed to know what scale I can handle with this solution? I've seen so, so few benchmarks, and maybe no recent benchmarks - a problem since performance has significantly changed across versions.

nijave2• 3 years ago

If you're on a cloud provider, I'd say just use their offering. For small/medium amounts of messages (single digit millions a day) the cost will be trivial.

A lot of frameworks already have queue/job libraries with adapters (so you're not really locked in) and cloud providers are highly scalable and fault tolerant.

It seems silly to try to build into Postgres something that is already cheap and readily available unless you find yourself in a situation where standing up additional infra is hard (embedded, certain on premise)

vb-84483• 3 years ago

If you're on a cloud provider, I'd say just use their offering. For small/medium amounts of messages (single digit millions a day) the cost will be trivial.

It's a good compromise but not suitable for every use case.

The thing I really don't like is that you need to be connected to the cloud even for local development and test.

afandian• 3 years ago

You don’t. SQS works fine in Localstack.

bblommers• 3 years ago

In addition to Localstack, Moto can help speedup the dev/test cycle as well.

If you're architecture is on AWS, you're most likely using either one of those solutions.

nijave2• 3 years ago

AWS SDK can stub responses. In general, I usually recommend creating local/in memory implementations of infrastructure so you don't end up running a gigantic stack locally. You can write tests against the real integration that selectively run against a real environment and write contract tests that cover the real implementation and your local stubs (or maybe even interfaces is strongly typed languages are sufficient).

Some languages/frameworks have this built in (a lot of ORMs do this out of the box). A lot of frameworks also have facilities for conditionally wiring in different implementations based on runtime config.

ComodoHacker• 3 years ago

Postgres queue tech is a thing of beauty, but far from mainstream.

A small nitpick. FOR UPDATE SKIP LOCKED was mainstream (in a sense) way before it was copied from Oracle to Postgres. It's used under the hood in most non-trivial Oracle deployments. Like for replication or refreshing materialized views in parallel.

snicker7• 3 years ago

I wonder how this is is actually simpler than an a managed queue, say SQS.

HeavyStorm• 3 years ago

Terrible idea. Using a database for queues means using a file format that's not optimized for this, so, unless you don't delete the rows (effectively having a always-growing table) you'll run into performance issues. Also the index for a processed flag will cause contention.

I'm certain you can work around those issues, but why if you can use a proper queue?

Choose Postgres queue technology

Related Stories