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

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

#21

Post by mbell » Sat, Jul 01, 2017, 3:34 PM UTC

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…

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

If the connection is broken the transaction would be aborted and the lock released. If the worker hit an infinite loop or something like that you'd use something like `idle_in_transaction_session_timeout` to set a transaction timeout and/or have a worker monitoring system in place to kill long running jobs.

The important thing to note is that a single DB instance and a client still comprises a distributed system. You still have almost all the same problems with 'exactly-once' semantics you would with a distributed queue. You should make all processing jobs idempotent and support retry regardless of the tech backing the queue if you want a system that provides effectively exactly-once semantics.

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

#22

Post by ruslan_talpa » Sat, Jul 01, 2017, 3:40 PM UTC

Smudge wrote: post #18

ruslan_talpa wrote: post #16

Earlier quoted context omitted.

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…

Short answer no, it doesn't (at least now, release 1 week ago :) ), but dismissing it only for that would be a superficial look. Most cases don't need those guarantees, i.e. you can tolerate a few lost messages.

For example, you are implementing real time updates in your app using this. What's the probability of a network glitch happening at the same time as two users being logged in at the same time in one system where an event produced by one needs to be synced to the other, even more, say he lost that event, is it that critical considering he will soon move to another screen and reload the data entirely?

From rabbitmq point of view, db&bridge are producers. You are really asking here, does the "producer" guarantee delivery? To do that, it means the producer needs to become himself a "queue" system in case he fails to communicate with rabbit.

Considering we are talking web here, the producers are usually scripts invoked by a http call so there is no such guarantee in any system (when communication to rabbitmq fails).

However i think network (in the datacenter) is quite reliable so there is no point in overengineering for that case.

If the system can tolerate a few seconds of downtime, it's easy enough to implement a heartbeat system which would restart this tool in case it's needed, also, you can run 2-3 of them to make it redundant then use corelationId to dedup the messages.

A more robust tool would be https://github.com/confluentinc/bottledwater-pg but it's for kafka and the major downside for me is that it cant be used with RDS since it's installed as a plugin to postgresql

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

#23

Post by mbell » Sat, Jul 01, 2017, 3:41 PM UTC

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.

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

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

#24

Post by ruslan_talpa » Sat, Jul 01, 2017, 3:41 PM UTC

balfirevic wrote: post #17

ruslan_talpa wrote: post #16

Earlier quoted context omitted.

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?

see my comment above

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

#25

Post by ruslan_talpa » Sat, Jul 01, 2017, 3:44 PM UTC

Smudge wrote: post #18

ruslan_talpa wrote: post #16

Earlier quoted context omitted.

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…

Notify is very lightweight in postgres, it takes up very little cpu if that's your worry. anyway, this is not meant for log storing/streaming (same as postgresql). My usecase for it is "real time updates" (reasonable amount of events, that need to be routed in complex way to multiple consumers)

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

#26

Post by mbell » Sat, Jul 01, 2017, 3:49 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…

Why would you use Kafka as a queue? It's not a great fit, I mean, you could make it work, but it's a similar square peg in round hole design choice as using a relational DB as a queue.

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

#27

Post by dom0 » Sat, Jul 01, 2017, 3:52 PM UTC

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.

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

#28

Post by jlg23 » Sat, Jul 01, 2017, 3:57 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…

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

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

#29

Post by brianwawok » Sat, Jul 01, 2017, 3:58 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.

> Already have a central, configured and monitored server and need "just a small queue" for something.

Fine. So use SQS or cloud pub sub. Both take 0 "server configuration work", and you aren't adding load to likely the single most expensive part of your infrastructure (RDBMS).

(The exception to where a RDBMS is not the most expensive part of your infastructure is where you have very large data with either nosql something, or a machine learning GPU array.. but not sure that is super relevant here)

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

#30

Post by brianwawok » Sat, Jul 01, 2017, 3:59 PM UTC

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.

RabbitMQ is on docker, its more or less the same work to launch as Redis these days.

(It can take a bit more tuning so I think it is unfair to say it is the SAME work, but it is seriously not a huge deal to run RabbitMQ in the post docker world)