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

31–40 of 82 posts

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

#31

Post by brianwawok »

BenoitEssiambre wrote: post #20
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 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 work, nor can you wrap resizing some images in the same transaction. The ENTIRE reason you are using a message queue is to go to some external work. So this literally makes no sense.

> and may result in silent conflicts or corruption when different services read and update the same data.

You need to program this in one way or another no matter what. If you programmed your code to do "silent conflicts or corruption" then I guess you are going to be in trouble. So don't do that.

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

Again, you are missing the point. MOST job queue work is stuff outside of the database anyway. You still have stuff outside the database.

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

Not that relevant. If you are doing 10,000 messages a second (or more!) to your job queue, and are looking to hold open a bunch of transactions, you are going to be in for some pain.

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

And trying to use PostgreSQL as a job queue is going to give you 1% or .1% of using RabbitMQ or Kafka or SQS or Cloud Pub Sub as a job queue. You are trying too hard to use a database for the wrong thing.

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

#32

Post by brianwawok »

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.

A transaction in a database does not help you here.

Let's say these are your steps:

1) Open a transaction

2) Claim an email to send

3) Send the email

4) Make email as sent

5) Close transaction

Say your web client crashes between 3 and 4? The email is not going to get marked as sent, and the transaction will rollback. You have no choice but to resend the email.

You could have done this same exact thing with RabbitMQ and an external worker (Celery etc. etc.). You chose to either ack just BEFORE you start the work (between 2 and 3). You will never double send, but risk dropping, or you choose to ack just AFTER you start the work (between 3 and 4), and guarantee to always do the work, but at the risk of a double send.

If your task is idempotent this is super easy, just ack after the work is complete and you will be good. If your task is not idempotent (like sending an email), this takes a bit more work... but I think you have that same exact work in the database transaction example (see above)

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

#33

Post by brianwawok »

jlg23 wrote: post #28
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."

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"

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

#34

Post by true_religion »

brianwawok wrote: post #30
mbell wrote: post #23

Earlier quoted context omitted.

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)

Is Docker really necessary for this? I've never had any trouble just installing RabbitMQ from the package manager, and running it with only a tiny bit of initial configuration.

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

#35

Post by true_religion »

ruslan_talpa wrote: post #6
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…

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 have a messaging server.... you can start using it to help parallelize all kinds of tasks. Things you wouldn't previously think about because they'd take days or even weeks to complete, even in parallel, can all run on your infrastructure now. Spinning up 20 or even a 100 new workers is easy, and follows the same principle as just spinning up 1 or 2.

That's been my experience. Before we did everything in the DB, and implicitly ignored the mere possibility of doing some tasks because we knew it'd be too hard with the DB as the event store.

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

#36

Post by allover »

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…

> The ENTIRE reason you are using a message queue is to go to some external work.

There are other reasons to use queues besides that, e.g. async communication between two services. This is an example where you could feasibly use a database-backed queue instead. Not saying it's a good or bad idea, depends on the circumstances ofc.

And GPP was only answering a hypothetical and you came out swinging:

> The ENTIRE reason [...] > So this literally makes no sense. > So don't do that.

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

#37

Post by BenoitEssiambre »

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…

Oh sure if you want to use an outside queue for something simple, one directional and unacknowledged like sending emails that is fine.

People these days want to use queues to send all kinds of event messages multi directionally between systems. They break their ACID and they corrupt their data. At some scales you don't have a choice, but if you can keep all your lower bandwidth stuff happening through a direct connection to postgres you get a more reliable system and it's worth putting some efforts to achieve that. And "lower bandwidth" here is not that low. Postgres scales better than most people think if you put a bit of efforts to optimize.

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

#38

Post by brianwawok »

true_religion wrote: post #34
brianwawok wrote: post #30

Earlier quoted context omitted.

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)

Is Docker really necessary for this? I've never had any trouble just installing RabbitMQ from the package manager, and running it with only a tiny bit of initial configuration.

Well, if for example your entire infrastructure is in K8s, then yes you kind of need docker.

Redis vs RabbitMQ stateful-set.yaml would be me or less the same, and something like 20 lines of yaml.

I was never a big fan of docker for running some thing on a server, but once you start to go down the K8s path, it finally makes sense.

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

#40

Post by Smudge »

brianwawok wrote: post #32
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.

A transaction in a database does not help you here. Let's say these are your steps: 1) Open a transaction 2) Claim an email to send 3) Send the email 4) Make email as sent 5) Close transaction Say your web client crashes between 3 and 4? The email is not going to get marked as sent, and the transaction will rollback. You have no choice but to resend the email. You could have done this same exact thing with RabbitMQ a…

Email is just one example, but maybe a better example is talking to an API that supports some form of idempotency. You don't want to talk to the API during a transaction that involves other related data persistence, but you can transactionally store the intent to talk to that API (i.e. you insert a job into a queue, and that job will eventually do the API interaction). But even in the email case, you can benefit from a transaction:

1) Open a transaction

2) Persist some business/app data

3) Persist a job into your queue that will send an email relating to #2

4) Close transaction

So you've at least transactionally stored the data changes and the intent to send an email. When actually sending the email you probably want something that guarantees at-most-once delivery with some form of cleanup in the failure states (it's a bit more work, as you said).

Post reply on HN