What is ‘skip locked’ for in PostgreSQL 9.5? (2016) • Hacker News Board

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

#11

Post by koolba » Sat, Jul 01, 2017, 2:47 PM UTC

ngriffith wrote: post #9

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.

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

Yes for infrequent yet high value tasks where consistency is paramount, DB queues are king. The alternatives using an externalized queue is either non-transactional without failure handling (yet everybody pretends it is), transactional with 2PC where recovery was never thought through (or tested), or devolves to having the equivalent of a DB queue anyway to track work with re-insertion of work into the primary queue.

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

#12

Post by IgorPartola » Sat, Jul 01, 2017, 2:47 PM UTC

dom0 wrote: post #4

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? 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. Tossing that into an ephemeral queue is probably not the best solution.

Having said that, RabbitMQ does have a ton of settings where you can turn durability up/down as much as you want.

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

#13

Post by BurningFrog » Sat, Jul 01, 2017, 2:48 PM UTC

dom0 wrote: post #4

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

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

#14

Post by Smudge » Sat, Jul 01, 2017, 2:49 PM UTC

tyingq wrote: post #5

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.

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.

Very common in the web context -- you perform some form of relational persistence while also inserting a job to schedule background work (like sending an email). Having those both in the same transaction gets rid of a lot of tricky failure cases.

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

#15

Post by based2 » Sat, Jul 01, 2017, 2:51 PM UTC

Why not using scheduled timestamped checkpoint synchronization, and then you can get Loose coupling.

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

#16

Post by ruslan_talpa » Sat, Jul 01, 2017, 2:54 PM UTC

Smudge wrote: post #14

tyingq wrote: post #5

Earlier quoted context omitted.

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.

Very common in the web context -- you perform some form of relational persistence while also inserting a job to schedule background work (like sending an email). Having those both in the same transaction gets rid of a lot of tricky failure cases.

you can still have the notion of a transaction while integrating rabbitmq like this

you do your mutations in a transaction and also in that transaction you execute a NOTIFY command. If transaction is successful, the notify will go through at the end of the transaction. the notify events and be "bridged" to a messaging server like rabbitmq (see my other comment)

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

#17

Post by balfirevic » Sat, Jul 01, 2017, 3:16 PM UTC

ruslan_talpa wrote: post #16

Smudge wrote: post #14

Earlier quoted context omitted.

Very common in the web context -- you perform some form of relational persistence while also inserting a job to schedule background work (like sending an email). Having those both in the same transaction gets rid of a lot of tricky failure cases.

you can still have the notion of a transaction while integrating rabbitmq like this you do your mutations in a transaction and also in that transaction you execute a NOTIFY command. If transaction is successful, the notify will go through at the end of the transaction. the notify events and be "bridged" to a messaging server like rabbitmq (see my other comment)

What if your network has a glitch when you try to write to RabbitMQ after you receive NOTIFY? You will lose that message, correct?

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

#18

Post by Smudge » Sat, Jul 01, 2017, 3:17 PM UTC

ruslan_talpa wrote: post #16

Smudge wrote: post #14

Earlier quoted context omitted.

Very common in the web context -- you perform some form of relational persistence while also inserting a job to schedule background work (like sending an email). Having those both in the same transaction gets rid of a lot of tricky failure cases.

you can still have the notion of a transaction while integrating rabbitmq like this you do your mutations in a transaction and also in that transaction you execute a NOTIFY command. If transaction is successful, the notify will go through at the end of the transaction. the notify events and be "bridged" to a messaging server like rabbitmq (see my other comment)

Nice -- pg-amqp-bridge seems like a clever solution for coordinating the two. It still puts some amount of the queuing load on the DB, but I'd be comfortable with that trade-off if I really wanted to use rabbitmq as the primary queue.

Question though -- Does it guarantee at-least-once or at-most-once on the NOTIFY? (Like, if there is a network blip, will it retry the NOTIFY?) And if it is at-least-once, I assume that consumer apps will have to handle deduplication/idempotency.

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

#19

Post by asah » Sat, Jul 01, 2017, 3:21 PM UTC

(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 in the same place? I.e. don't delete records, just mark them as taken and include a timestamp so we can timeout and give the work to another worker?

UPDATE queue_table SET taken_by = :myId, time_taken = now() WHERE id = ( SELECT id FROM queue_table WHERE taken_by IS NULL LIMIT 1 FOR UPDATE)

Note: completion can be signalled by setting taken_by to NULL, or but adding another column e.g. completion time, which then enables computing stats on completion times.

For high volume systems, we eventually want to garbage collect but that's easy since we have timestamps, i.e. put a partial index on timestamp (WHERE time_taken IS NOT NULL) and scan the oldest ones...

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

#20

Post by BenoitEssiambre » Sat, Jul 01, 2017, 3:23 PM UTC

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…

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 over multiple network hops having uncertain timing, congestion and reliability.

The performance and scalability of monolithic dbs is often better than people expect because the data stays closer to CPUs and doesn't move across multiple nodes on a relatively speaking, slow and unreliable network. Trying to add transactional safety after the fact on top of a clustered db/queue is a huge headache and you will never get it as good as transactions that happen inside a single server.

Of course there are concerns about scalability with a monolith. Manually sharding monoliths is a bit of work but it forces you to do your homework and find the natural and optimal sharding points. In a lot of cases, there are natural separation lines such as between groups of customers that you can use to shard and scale things up. Blindly distributing all data across a bunch of nodes has huge performance and reliability implications. A cpu's connection to it's ram and disks is orders of magnitude more reliable and fast than connections over the network. Unless you are building something like a social network where everything is connected to everything, you don't need things to default to run over clusters and over the network.