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

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

#41

Post by dom0 » Sat, Jul 01, 2017, 4:36 PM UTC

brianwawok wrote: post #29

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.

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

That's an entirely valid line of reasoning, but it only applies to a certain set of applications. Further, SQS or whatever have the same drawbacks as other external queues compared to an in-DB queue; see all the sibling comments in this big thread.

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

#42

Post by elygre » Sat, Jul 01, 2017, 4:51 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 it not work well? Oracle has built their OracleAQ [1] in the database, and it works quite nicely. It can be accessed using native APIs, or a JMS api if that is your thing.

Also, having the queue in the database gives you easy access to the queue for management of all sorts -- SQL gives you full access to the queue data.

[1]: http://www.oracle.com/technetwork/database/oracleadvancedque...

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

#43

Post by LoSboccacc » Sat, Jul 01, 2017, 4:53 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.

Depends on the scale mostly. People often forget how not every project is Google/Facebook/Twitter.

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

#44

Post by [deleted] » Sat, Jul 01, 2017, 4:55 PM UTC

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…

[deleted]

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

#45

Post by rpedela » Sat, Jul 01, 2017, 5:28 PM UTC

mbell wrote: post #26

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.

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

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

#46

Post by endymi0n » Sat, Jul 01, 2017, 5:29 PM UTC

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…

Also, while queuing systems are preferrable to a database for some of the properties mentioned by GP, they break down for some use cases. For example, for one operation, we needed a time based priority queue that deduplicates on some attributes and refreshes the timestamps. That's where Postgres really shines - and because it's not that many entries and throughput anyway, it was one system less to operate and works perfectly fine to this day.

Just keep in mind Postgres isn't made for high contention, deletion and mutation rates on really small tables (Uber "Schemaless" case)

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

#47

Post by brianwawok » Sat, Jul 01, 2017, 5:48 PM UTC

elygre wrote: post #42

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 it not work well? Oracle has built their OracleAQ [1] in the database, and it works quite nicely. It can be accessed using native APIs, or a JMS api if that is your thing. Also, having the queue in the database gives you easy access to the queue for management of all sorts -- SQL gives you full access to the queue data. [1]: http://www.oracle.com/technetwork/database/oracleadvancedque...

Oracle sells the license to use their products at 500k per server. of COURSE they are going to build products to use it. Doesn't matter if it's a subpar choice, they just made 500k per server.

I am not sure it makes sense to compare some guy throwing some messages in a queue or in postgreSQL to someone spending millions to get steak dinners from Oracle and use Oracle products.

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

#48

Post by brianwawok » Sat, Jul 01, 2017, 5:49 PM UTC

mbell wrote: post #26

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.

Touche.

I think you are right. Kafka can work as a queue like a RDBMS can work as a queue, but it's not a great idea.

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

#49

Post by [deleted] » Sat, Jul 01, 2017, 5:50 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…

[deleted]

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

#50

Post by zwischenzug » Sat, Jul 01, 2017, 5:51 PM UTC

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…

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.