What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
blog.2ndquadrant.com
What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
1–10 of 82 posts
Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
#2Why use a database as a queue? It is known this doesn't scale well nor work particularly well. If you need big scale, you can get something like RabbitMQ or Kafka. If you want to avoid server setup, use SQS or Cloud Pub/Sub. If you want it to be lighter weight, use Redis.
This is kind of like an article talking about how most people use a hammer to put in screws wrong. Which is cool, and good for learning why using a hammer to put in screws is a bad idea. But the outcome of this all should be "Use a proper tool for the job", not "try to come up with a neat trick to make this work in the wrong system".
Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
#3Why use a database as a queue? It is known this doesn't scale well nor work particularly well. If you need big scale, you can get something like RabbitMQ or Kafka. If you want to avoid server setup, use SQS or Cloud Pub/Sub. If you want it to be lighter weight, use Redis. This is kind of like an article talking about how most people use a hammer to put in screws wrong. Which is cool, and good for learning why using a…
> Why use a database as a queue?
To avoid running a second system.
Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
#4Why use a database as a queue? It is known this doesn't scale well nor work particularly well. If you need big scale, you can get something like RabbitMQ or Kafka. If you want to avoid server setup, use SQS or Cloud Pub/Sub. If you want it to be lighter weight, use Redis. This is kind of like an article talking about how most people use a hammer to put in screws wrong. Which is cool, and good for learning why using a…
> Why use a database as a queue?
Already have a central, configured and monitored server and need "just a small queue" for something. This is not per se a bad decision. For the same reason it doesn't have to be a bad idea to cache things in the main database, instead of using a dedicated cache like Redis.
Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
#5Why use a database as a queue? It is known this doesn't scale well nor work particularly well. If you need big scale, you can get something like RabbitMQ or Kafka. If you want to avoid server setup, use SQS or Cloud Pub/Sub. If you want it to be lighter weight, use Redis. This is kind of like an article talking about how most people use a hammer to put in screws wrong. Which is cool, and good for learning why using a…
> Why use a database as a queue? Already have a central, configured and monitored server and need "just a small queue" for something. This is not per se a bad decision. For the same reason it doesn't have to be a bad idea to cache things in the main database, instead of using a dedicated cache like Redis.
It also makes it easier to delete or update the queue entry in an atomic transaction that spans other tables. If that has value for the specific use case.
Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
#6Why use a database as a queue? It is known this doesn't scale well nor work particularly well. If you need big scale, you can get something like RabbitMQ or Kafka. If you want to avoid server setup, use SQS or Cloud Pub/Sub. If you want it to be lighter weight, use Redis. This is kind of like an article talking about how most people use a hammer to put in screws wrong. Which is cool, and good for learning why using a…
Well, maybe because most application don't need/have the scale that require a messaging server?
A lot of projects i would say have a 80/20 read/write split, thus, there are not so many events and they don't need the complexity of rabbitmq when the db can handle a few events.
Having said all that, we did actually build a tool to connect together postgresql and rabbitmq :)
https://github.com/subzerocloud/pg-amqp-bridge
When you get to a big number of events or maybe there are lots of types and they all need to be routed in complex ways (real time updates) then indeed, rabbitmq is the right tool And this component enables you to execute NOTIFY in a trigger or stored procedure and have that message sent to rabbitmq.
Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
#7database rule #1, do not use your database as a work queue.
this article does a great job discussing why not, and finishes with a sane implementation that would work, but would fall over and shutter to a halt with a moderate amount of load. ( which is mentioned in the article too )
doing queue workloads in rdbms is a recipe for index contention, bugs, or both.
Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
#8Why use a database as a queue? It is known this doesn't scale well nor work particularly well. If you need big scale, you can get something like RabbitMQ or Kafka. If you want to avoid server setup, use SQS or Cloud Pub/Sub. If you want it to be lighter weight, use Redis. This is kind of like an article talking about how most people use a hammer to put in screws wrong. Which is cool, and good for learning why using a…
> Why use a database as a queue? Already have a central, configured and monitored server and need "just a small queue" for something. This is not per se a bad decision. For the same reason it doesn't have to be a bad idea to cache things in the main database, instead of using a dedicated cache like Redis.
Yep. Better asked as "Why use a conventional|relational database as a queue?"
Because all queues have to be databases i.e. have the same qualities / assurances as databases.
Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
#9database rule #1, do not use your database as a work queue. this article does a great job discussing why not, and finishes with a sane implementation that would work, but would fall over and shutter to a halt with a moderate amount of load. ( which is mentioned in the article too ) doing queue workloads in rdbms is a recipe for index contention, bugs, or both.
I've found that co-locating my queue with my application data enables a very powerful pattern around persisting actions and queuing up callbacks -- especially useful for smaller applications where scale isn't as much of a factor. It means I can persist both my data and my background jobs in a single transaction, and if something goes wrong during that transaction they both rollback (before the jobs are picked up by any workers). No need to coordinate two different systems (which can be full of pitfalls).
Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)
#10I've noticed long ago that RDBMS users don't actually care about consistency, so it's always kind of wrong at some level and when things break it's not that big of a deal for them. It's just annoying to see people claiming how transactions are easy, while they are only easy to do incorrectly.