4 ms·

I was always taught (by extensive reading on SO) that implementing a queue system on top of PGSQL is bad. So, for small (but critical nonetheless) use cases, d

I was always taught (by extensive reading on SO) that implementing a queue system on top of PGSQL is bad. So, for small (but critical nonetheless) use cases, do any of you use your own custom built queues on top of a database?

00000000005 5y ago
There’s people who think using a database as a queue is bad, but if you keep pressing then for tangible reasons, it usually turns out to be emotional …… they just don’t like the idea. Next time someone says it’s a bad idea, keep pressing for real answers, not emotion.
- bokohut 5y ago
  There is a lot of ignorance around all topics but in technology it seems to be more pronounced if only because I have personal and professional interest. I felt inclined to support 10x0five in his statement as my own experiences are identical as it relates to storing files in a database. There were, and likely still are, those that proclaim over and over to never store serially linked chunked data in a database, aka files, yet after building and proving a model in production long before today's tech that stance has seemed to change. The benefits far outweigh the costs when having everything in a database which is comparable in today's uptake of putting everything under version control. Fear not what others judge about your ideas as this should just be more motivation to "press for real answers" proving yourself correct.
onlywicked 5y ago
I don't myself but DigitalOcean had there queue system to create VMs built over MySQL. I couldn't find the original video describing their architecture. (It's private now, I guess).
bfelbo 5y ago
We’ve implemented two types of queues using Postgres tables in production and it’s been working nicely. Happy to answer questions about our usage if it’s useful :)
- kumarvvr 5y ago
  How scalable do you think your system is? Can it handle 10k req/sec? My usage is much lower than that but I want to target that number.
  - bfelbo 5y ago
    We're currently keeping it as simple as possible. We just use a table and this query that runs every second: "DELETE FROM job_queue RETURNING type, info;" Works great and reliably so far. I think our current volume is on the order of 100-1000 req/sec. We'll likely switch to graphile-worker when we need more performance, but we're all about avoiding premature optimization. That library has been benchmarked to handle 10k req/sec: https://github.com/graphile/worker#performance https://github.com/graphile/worker#performance
  - pritambarhate 5y ago
    PgSQL table based job queues should be able to handle that kind of load, but to support it you will need very high IOPS based disk. If you have NVME SSD paired with decent CPU, it should be able to manage this. This is mainly because PgSQL based queue is durable by default and based on how you implement your inserts, every new message will need a disk based commit.
- kumarvvr 5y ago
  Would also love to hear or read about your implementation, the general architecture and failsafe constructs. Thank you.

00000000005 5y ago

There’s people who think using a database as a queue is bad, but if you keep pressing then for tangible reasons, it usually turns out to be emotional …… they just don’t like the idea. Next time someone says it’s a bad idea, keep pressing for real answers, not emotion.

bokohut 5y ago
There is a lot of ignorance around all topics but in technology it seems to be more pronounced if only because I have personal and professional interest. I felt inclined to support 10x0five in his statement as my own experiences are identical as it relates to storing files in a database. There were, and likely still are, those that proclaim over and over to never store serially linked chunked data in a database, aka files, yet after building and proving a model in production long before today's tech that stance has seemed to change. The benefits far outweigh the costs when having everything in a database which is comparable in today's uptake of putting everything under version control. Fear not what others judge about your ideas as this should just be more motivation to "press for real answers" proving yourself correct.

onlywicked 5y ago

I don't myself but DigitalOcean had there queue system to create VMs built over MySQL. I couldn't find the original video describing their architecture. (It's private now, I guess).

bfelbo 5y ago

We’ve implemented two types of queues using Postgres tables in production and it’s been working nicely. Happy to answer questions about our usage if it’s useful :)

kumarvvr 5y ago
How scalable do you think your system is? Can it handle 10k req/sec? My usage is much lower than that but I want to target that number.
- bfelbo 5y ago
  We're currently keeping it as simple as possible. We just use a table and this query that runs every second: "DELETE FROM job_queue RETURNING type, info;" Works great and reliably so far. I think our current volume is on the order of 100-1000 req/sec. We'll likely switch to graphile-worker when we need more performance, but we're all about avoiding premature optimization. That library has been benchmarked to handle 10k req/sec: https://github.com/graphile/worker#performance https://github.com/graphile/worker#performance
- pritambarhate 5y ago
  PgSQL table based job queues should be able to handle that kind of load, but to support it you will need very high IOPS based disk. If you have NVME SSD paired with decent CPU, it should be able to manage this. This is mainly because PgSQL based queue is durable by default and based on how you implement your inserts, every new message will need a disk based commit.
kumarvvr 5y ago
Would also love to hear or read about your implementation, the general architecture and failsafe constructs. Thank you.