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

61–70 of 82 posts

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

#61

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.

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

#62

Post by asah »

mbell wrote: post #21
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…

ah! you're assuming the work is performed inside the same transaction as the dequeue operation, and locks held for the duration ?

If so...

While I suppose row level locking technically solves contention, it still feels like we're "asking for trouble" in holding databases locks while clients perform arbitrarily long work operations. There's also practical issues when the work itself is distributed and the original client can't itself keep state around, i.e. it has to end the low level transaction.

Hence my poor-man's question/proposal using worker IDs and timeouts...

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

#63

Post by mbell »

rpedela wrote: post #45
mbell wrote: post #26

Earlier quoted context omitted.

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.

I thought that was one of the primary use cases? Is that not true?

Kafka's primary use case is as an intermediary for a stream processing system. e.g. to ingest a firehouse of events, make them durable, then feed them to a number of different processors. More common use cases here: https://kafka.apache.org/uses. You _could_ use it's data model to implement a 'job queue', but it's not a great fit for a number of reasons.

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

#64

Post by mbell »

asah wrote: post #62
mbell wrote: post #21

Earlier quoted context omitted.

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

ah! you're assuming the work is performed inside the same transaction as the dequeue operation, and locks held for the duration ? If so... While I suppose row level locking technically solves contention, it still feels like we're "asking for trouble" in holding databases locks while clients perform arbitrarily long work operations. There's also practical issues when the work itself is distributed and the original cli…

> ah! you're assuming the work is performed inside the same transaction as the dequeue operation, and locks held for the duration ?

Yes that is the model the linked post is proposing, see the Example.

> While I suppose row level locking technically solves contention, it still feels like we're "asking for trouble" in holding databases locks while clients perform arbitrarily long work operations. There's also practical issues when the work itself is distributed and the original client can't itself keep state around, i.e. it has to end the low level transaction.

Not that I recommend using PG as a queue, but you have most/all those problems with any queuing backend. A problem you may have that is PG specific is that the # of open connections/transaction could become quite large with a lot of workers and PG doesn't play well with a lot of connections, it uses a process-per-connection model.

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

#65

Post by macdice »

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 calls it "read past" isolation.

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

#66

Post by brianwawok »

true_religion wrote: post #35
ruslan_talpa wrote: post #6

Earlier quoted context omitted.

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…

Agreed. To keep it simple day 1, on a single box put your entire stack.

1 Django web server

1 redis queue (can both caching and work queue and results queue)

1 Celery process for async tasks

1 PostgreSQL instance

(You can do the same with other stacks, I just know them less well)

Fairly standard stack. You can find preconfigured setups that can get this going in 10 minutes or less. And boom, you just made a stack that can scale from a 1 user POC to a 10 million user product.

This lasts until you get past 1 box stage.. and at that point you split it up by function. At that point you can go to 1 celery worker to 1000 celery workers by just pushing a button to make more boxes. Seems a pretty good setup.

Vs say, trying to skip the redis part (which took all of 4 minutes) and writing a bunch of database logic, and then you have to rewrite it all down the road.

I am all for start simple. But start simple with a path to the future. If your path to the future is "rip this all out and rewrite it", you should at least ponder what you are doing. Did I REALLY save that much time by using my database as a message queue??

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

#67

Post by mbell »

derefr wrote: post #58
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.

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.

That's a rather insulting description of Redis, care to elaborate?

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

#68

Post by mianos »

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.

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

#69

Post by [deleted] »

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

[deleted]

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

#70

Post by barrkel »

rpedela wrote: post #45
mbell wrote: post #26

Earlier quoted context omitted.

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.

I thought that was one of the primary use cases? Is that not true?

Think of Kafka as a replayable append-only log. It's tuned for ingestion of enormous quantities of data; it's best at buffering big data, or perhaps as an infinitely growing log of everything that happens in a system, than simply communication in a distributed system. It doesn't have back pressure and its namespacing is primitive (ie DIY with prefixes, and avoid Confluent libraries that assume they're the only client in the system).
Post reply on HN