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

51–60 of 82 posts

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

#51

Post by znep »

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…

The biggest reason I've found for using a database as a queue is that you can actually run queries against it, both writes and reads. This is very useful when different entries in the queue can have some relationship that matters when processing.

Do you want to wait 30 minutes after an item is queued then process all queued items for a given entity (eg. user) at once? Do you want to support more complex retry logic, such as efficiently handling some subset of jobs failing due to external dependencies being unavailable while others can complete? Do you want to be able to track what jobs are in progress (doesn't really work with skip locked), and even have the ability to update their status while processing for finer grained tracking? Do you want to be able to effectively monitor the contents of the queue beyond just the size (and some queues make it quite inefficient just to get that)?

Some of these are possible with purpose made queueing systems but, in my experience, even if they are they can be quite inefficient, complicated, and are often poorly tested and supported areas of functionality. For example the last time I tried to use JMS selectors with ActiveMQ (selectors let you filter messages based on a subset of SQL) it was a nightmare both performance wise and functionality wise and I wished I just had a database that was actually built to support SQL.

Other points about the cost of introducing a new technology (especially if you need strong durability, availability, etc.) and being able to update your database in the same transaction are also valid, but can often be dealt with by some extra effort.

Don't get me wrong, if you just need a queuing system the first thing to consider is a system designed for that purpose, especially if you aren't very knowledgeable about databases and have basic requirements. It can be nuanced to correctly and efficiently use a general purpose database as queue. At the end of the day, however, keep in mind that under the hood a queueing system is just a database that exposes a limited API that makes it easier to get basic operations right and hopefully easier to scale (sadly I have seen all too many queuing systems that utterly fail at that part) at the expense of flexibility.

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

#52

Post by fencepost »

brianwawok wrote: post #31
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…

> 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 What? Most job queues are inherently to do something outside of the database. For example: I need to send some emails, or I need to resize some images. You cannot wrap sending an email in the same transaction as your queue wo…

There are plenty of situations where "good enough" is a better choice, and "not adding yet another tool set or system for our 2 person dev team" has a quality all its own.

There are absolutely situations where it must be both RIGHT and as fast as possible under all loads, and there are situations where it needs to be right and not do WRONG things under abnormal loads. For a startup worrying about handling thousands of transactions can wait until triple digit customers.

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

#53

Post by wbl »

IgorPartola wrote: post #12
dom0 wrote: post #4

Earlier quoted context omitted.

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

This. Also, things like RabbitMQ are complex and their durability properties can be different than those provided by your RDBMS. This can get problematic if you are mixing tasks in the queue that have different priorities. For example, emailing the invoice to a client should not fail silently and should happen at most once. Same with a notification from your doctor that you need to call to discuss your test results.…

Email is unreliable. You should number invoices, and tell recipients to ignore duplicates.

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

#54

Post by nraynaud »

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.

+ install, configure, deploy, troubleshoot those technologies.

I think there is a lot of value in using the wrong tool for the job until it becomes "an issue we need to fix".

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

#55

Post by jpalomaki »

BurningFrog wrote: post #13
dom0 wrote: post #4

Earlier quoted context omitted.

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

In my experience, the cost of adding and maintaining another storage subsystem to a project is often hugely underestimated. It's easy to see the benefits and ignore the costs. If I can solve a problem reasonably well by adding a table to my Postgres DB, that will always beat out adding the specialized ScrewdriverDB that does it perfectly.

I agree. If it sounds simple, then you are probably not thinking hard enough.

Think for example how to do backups. If you have a database and completely separate queue system your application state is distributed into two places. This means taking consistent backups is not straightforward. You can of course try to work around this on the application level (database as single source of truth, queues as "nice to have" -thing, but this makes things more complicated for the app).

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

#56

Post by codesnik »

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…

this article recently posted on HN is addressing this http://programmingisterrible.com/post/162346490883/how-do-yo...

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

#57

Post by dom0 »

jpalomaki wrote: post #55
BurningFrog wrote: post #13

Earlier quoted context omitted.

In my experience, the cost of adding and maintaining another storage subsystem to a project is often hugely underestimated. It's easy to see the benefits and ignore the costs. If I can solve a problem reasonably well by adding a table to my Postgres DB, that will always beat out adding the specialized ScrewdriverDB that does it perfectly.

I agree. If it sounds simple, then you are probably not thinking hard enough. Think for example how to do backups. If you have a database and completely separate queue system your application state is distributed into two places. This means taking consistent backups is not straightforward. You can of course try to work around this on the application level (database as single source of truth, queues as "nice to have"…

> This means taking consistent backups is not straightforward.

And they're not even straightforward to begin with...

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

#58

Post by derefr »

mbell wrote: post #23
BurningFrog wrote: post #13

Earlier quoted context omitted.

In my experience, the cost of adding and maintaining another storage subsystem to a project is often hugely underestimated. It's easy to see the benefits and ignore the costs. If I can solve a problem reasonably well by adding a table to my Postgres DB, that will always beat out adding the specialized ScrewdriverDB that does it perfectly.

Depends on what you're adding. Running Redis is dead simple and easy to have visibility into. RabbitMQ / Kafka are much larger undertakings.

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 have a full-time ops staff, throwing another marginal piece of infrastructure on the pile isn't much of an issue. If you're your own "ops staff of one", each moving part of your infrastructure—that you need to ensure works, to guarantee you won't lose client data—is another thing slowing down your iteration speed.

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

#59

Post by asah »

dom0 wrote: post #27
asah wrote: post #19

(possibly dumb q, pls be gentle) Hmmm... What happens if the application crashes immediately after removing the work item but before it can do anything else? Doesn't this break exactly-once semantics... ? i.e. wouldn't a complete implementation include a second table with "who's working on what" and a transaction that moves the records from one table to the either... and come to think of it, why not store both tables…

The row lock is held by the RDBMS itself on behalf of the client. When the client goes away, times out (configuration) etc. -- the transaction aborts -- then the RDBMS releases it. You don't get exactly once here, because between performing some action on the work item and marking the item as finished and committing you can still crash. This is an at least once solution in the general case.

sorry!! I'm not sure I understand your response? when you say 'You don't get exactly once here' what is does 'here' refer to?

Upon reflection, true exactly-once semantics requires multi-phase commit, transitively through the system - this sort of tight coupling is tricky in practice.

Hence my question/proposal, which is practical/poor-man's solution using timeouts.

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

#60

Post by Thaxll »

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…

Pretty much like Rust users, they advertise Postgres for everything, even usage pattern that don't make sense.
Post reply on HN