Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

blog.2ndquadrant.com

Post reply on HN
Print view View original

71–80 of 82 posts

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#71

Post by strictfp »

macdice wrote: post #65
brianwawok wrote: post #2

Why 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…

Author of PostgreSQL SKIP LOCKED feature here. I gave a talk about queues in PostgreSQL covering the question "why do this in a database", for what it's worth: https://www.pgcon.org/2016/schedule/track/Applications/929.e... Mostly the same arguments made by commentators here. SKIP LOCKED exists in several major RDMSs. We borrowed Oracle's syntax and MySQL has just done the same. The Gray transaction processing book c…

"...you like PostgreSQL enough to attend a conference". Future classic right there. (Not being sarcastic, I loved that joke)

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#72

Post by cwyers »

mbell wrote: post #67
derefr wrote: post #58

Earlier quoted context omitted.

If you already had a need for a durable database, and so you properly implemented Postgres streaming-archiving + disaster recovery, at much personal effort for your tiny startup... and you now need a durable queue as well... then "just installing Redis" won't get you there. You'll need to that whole ops setup all over again for Redis. Whereas, if your queue is in Postgres, the ops problem is already solved. If you ha…

You're making a lot of assumptions that aren't terribly valid for most use cases. Not to say that strict consistency requirements don't exist, they certainly do, but they are the exception rather than the norm. A one person startup doesn't usually care too much about losing a bit of client data. > If you have a full-time ops staff, throwing another marginal piece of infrastructure on the pile isn't much of an issue.…

It's nothing like insulting. Think of it like absolute and marginal costs. You have absolute infrastructure -- the sum total of all your infrastructure -- and you have marginal infrastructure -- bits and pieces that can be added and removed. If you have no Redis and then you have Redis, NewAbsoluteInfrastructure = OldAbsoluteInfrastructure + Redis, and Redis is your marginal infrastructure.

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#73

Post by SideburnsOfDoom »

zwischenzug wrote: post #50
BenoitEssiambre wrote: post #20

Earlier quoted context omitted.

There are many benefits to the transactional integrity you get when using direct connections to a relational database like PostgreSQL. Putting a queue outside of your db breaks ACID compliance and may result in silent conflicts or corruption when different services read and update the same data. DB transactions eliminate a bunch of the weird and dangerous corner cases you will get with data flows that are performed o…

In addition to all these other benefits, don't forget the impedance match you get in support. If you have a monolithic DB, the interface to all application information is the same, rather than having to learn multiple technologies.

That's nothing compared to the impedance mismatch from using a SQL database for everything, regardless of if it's the right tool.

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#74

Post by atombender »

brianwawok wrote: post #2

Why 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…

In addition to the many good replies:

One thing I like to point out whenever this comes up is that you should aim for a hybrid solution. Don't use a queue to store state, use it to coordinate. Databases are great at dealing with transactional state, queues are good at sequentially handing out data in order.

For example: Say we're generating reports. First you create a table "report_tasks", each row representing a report to be generated. Let each task have a status field and a description of what's to be done or whatever. You can use foreign keys here. Then create the rows, and for each row, publish a queue message with the ID of your row. The queue consumer then needs to read the town by its ID, do the necessary processing and update the status. You can of course also do things like avoid duplicate processing by looking at the status field.

What this solves is introspection/queriability. Queues are for the most part opaque (although Kafka at least keeps the queue around even after processing). This allows you to know exactly what work is pending and what has been completed, and do things like periodically retry the tasks or even preemptively cancel them. With RabbitMQ alone this is much more difficult, since you can't look into the queue without republishing the contents. The best you can do is to funnel ACKed messages into another queue, but you can't see what's pending.

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#75

Post by int_19h »

brianwawok wrote: post #33
jlg23 wrote: post #28

Earlier quoted context omitted.

> Why use a database as a queue? Quote from the article: "You don’t need to import a large and complex 3rd party app or library to implement a queue, and you don’t need to deal with the key mapping and namespace issues with advisory locking."

That is a strawman though. "Why would you need to buy an expensive screwdriver? Your hammer will do a just dandy job of putting in screws"

Well, if the hammer does actually do a good enough job of putting in screws for your use case, what's the problem?

And let's be honest: for most people, it's good enough, scalability-wise. "You are not Google" and all that.

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#76

Post by int_19h »

spotman wrote: post #7

database 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.

Define "moderate amount of load".

More importantly, how many existing Postgres users would fall in that bucket?

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#77

Post by gkop »

SideburnsOfDoom wrote: post #73
zwischenzug wrote: post #50

Earlier quoted context omitted.

In addition to all these other benefits, don't forget the impedance match you get in support. If you have a monolithic DB, the interface to all application information is the same, rather than having to learn multiple technologies.

That's nothing compared to the impedance mismatch from using a SQL database for everything, regardless of if it's the right tool.

Regarding queues, Delayed Job [0] is a popular queue abstraction over a SQL database that suggests the impedance mismatch in this case may be successfully mitigated.

[0] https://github.com/collectiveidea/delayed_job

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#78

Post by bsder »

brianwawok wrote: post #2

Why 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? It is known this doesn't scale well nor work particularly well.

PostgreSQL scales to 100K writes per second on my laptop.

I can probably get another x10 from a big cloud instance.

You can probably run 99+% of real applications in that range.

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#79

Post by mianos »

mianos wrote: post #68
brianwawok wrote: post #2

Why 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…

Also Skype created pgpool to use Postgresql as a queue at massive scale. As much it is often called an 'anti-pattern', it is durable and can massively scale way beyond the likely size of most companies, unless they need more scale than Skype.

oops, misstype, although pgpool also exists, the queing framework is pgqueue.

Re: What is ‘skip locked’ for in PostgreSQL 9.5? (2016)

#80

Post by kalleboo »

int_19h wrote: post #76
spotman wrote: post #7

database 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.

Define "moderate amount of load". More importantly, how many existing Postgres users would fall in that bucket?

Right. I use a MySQL-based queue for a moderate amount of load, but that's because it's relatively few tasks that do relatively much work per task. But if your idea of moderate load is hundreds of tasks per second that individually do very little work each, then you may be right.
Post reply on HN