Content about implementing a database-based queue with SQL and SKIP LOCKED

links-skiplocked.md

• Distributed Polling with SKIP LOCKED
  1. https://www.evernote.com/client/web#?an=true&fs=true&n=93f6cdf2-633d-4051-8b99-7e2d05498ead&s=s173&
  2. https://gist.github.com/rponte/0c5b0e3c1b84c0c2e49c863215c2c0f4
  3. https://gist.github.com/rponte/5e8d41fd3b2ced22206dce788208c30b (Implementing, rewriting, and rebuilding a task queue - by @tef_ebooks)
  4. https://todd-hubers.medium.com/you-really-can-replace-kafka-with-a-database-9e82a7c248a6 (Replacing Kafka with a database?)
  5. https://www.cloudamqp.com/blog/2015-11-23-why-is-a-database-not-the-right-tool-for-a-queue-based-system.html
  6. https://viralpatel.net/blogs/oracle-skip-locked/
  7. https://vladmihalcea.com/database-job-queue-skip-locked/
  8. https://github.com/vladmihalcea/high-performance-java-persistence/blob/master/core/src/test/java/com/vladmihalcea/book/hpjp/hibernate/concurrency/SkipLockJobQueueTest.java#L186
  9. https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#locking-follow-on
  10. https://hibernate.atlassian.net/browse/HHH-12848
  11. https://markjbobak.wordpress.com/2010/04/06/unintended-consequences/
  12. https://jonathanlewis.wordpress.com/2010/05/31/skip-locked/
  13. https://medium.com/@FranckPachot/oracle-hibernate-de-queuing-7454432a7738?sk=b50b8829c359fe7239f0356337afecee
  14. https://stackoverflow.com/questions/297280/the-best-way-to-use-a-db-table-as-a-job-queue-a-k-a-batch-queue-or-message-queu/55702399?stw=2#55702399
  15. http://www.sqlines.com/oracle-to-mysql/skip_locked
  16. https://stackoverflow.com/questions/27968877/how-to-avoid-two-different-threads-read-the-same-rows-from-db-hibernate-and-ora
  17. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9538644700346644113 (FOR UPDATE SKIP LOCKED with ROWNUM)
  18. https://asktom.oracle.com/pls/apex/asktom.search?tag=select-for-update-skip-locked (SELECT FOR UPDATE SKIP LOCKED)
  19. http://rwijk.blogspot.com/2009/02/for-update-skip-locked.html
  20. https://db-blog.web.cern.ch/blog/franck-pachot/2018-09-oracle-write-consistency-bug-and-multi-thread-de-queuing
  21. https://stackoverflow.com/questions/33816750/oracle-table-acting-like-a-queue
  22. https://www.codeproject.com/Tips/778259/Using-an-Oracle-Database-Table-as-a-Multithreaded
  23. https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:2060739900346201280 (favoring Oracle AQ over SKIP LOCKED)
  24. https://stackoverflow.com/questions/6117254/force-oracle-to-return-top-n-rows-with-skip-locked (lukas seder - top N rows with skip locked on Oracle)
  25. https://stackoverflow.com/questions/42899520/does-oracle-skip-locked-prevent-non-repeatable-reads
      
      (how skip locked works behind the scenes)
  26. http://stevenfeuersteinonplsql.blogspot.com/2016/05/types-of-cursors-available-in-plsql.html
  27. https://stackoverflow.com/questions/13265659/when-should-i-commit-when-using-for-update-in-a-procedure
  28. https://www.cs.umb.edu/~eoneil/introPLSQL/06_ora.htm (fetching across commits)
  29. http://www.oracledba.co.uk/tips/fetch_across_commits.htm
  30. http://www.java2s.com/Tutorial/Oracle/0500__Cursor/FetchingAcrossCommitsExample2.htm
  31. https://stackoverflow.com/questions/21637470/bulk-collect-using-for-update/21637619#21637619
  32. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7661190956484
  33. https://stackoverflow.com/questions/5757268/how-to-fetch-delete-commit-from-cursor
  34. https://orastory.wordpress.com/category/fetch-across-commit/
  35. http://askmaclean.com/wp-content/uploads/2009/09/Secrets%20of%20the%20Oracle%20Database.pdf (book Secrets of the Oracle Database)
  36. https://yaocm.wordpress.com/2019/04/13/technical-note-on-skip-locked/ (exemplos praticos para Oracle 12c)
  37. https://yaocm.wordpress.com/2019/04/13/technical-note-on-skip-locked/#comment-1493
  38. https://github.com/dfhawthorne/demos/tree/master/select_with_update
  39. https://news.ycombinator.com/item?id=14676859 (SKIP LOCKED no MySQL e Postgres)
  40. https://news.ycombinator.com/item?id=14730685 (SKIP LOCKED Postgres)
  41. https://www.pgcon.org/2016/schedule/track/Applications/929.en.html (palestra sobre Filas no Postgres)
  42. https://www.pgcon.org/2016/schedule/attachments/414_queues-pgcon-2016.pdf (PDF da palestra sobre Filas no Postgres)
  43. http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html (problemas de filas no SQL Server)
  44. https://it.toolbox.com/blogs/georgealexander/the-database-as-queue-anti-pattern-or-is-it-043012 (contra-argumento do artigo acima)
  45. https://softwareengineering.stackexchange.com/questions/231410/why-database-as-queue-so-bad
  46. https://softwareengineering.stackexchange.com/questions/351449/message-queue-database-vs-dedicated-mq
  47. https://stackoverflow.com/questions/2177880/using-a-database-table-as-a-queue
  48. https://vladmihalcea.com/sql-order-by-random/
      1. shuffling rows - with Oracle it's better to use the SAMPLE(N) function;
      2. interessante para diminuir a contigência quando usando FOR UPDATE;
      3. outra forma de diminuir a contigência é usando batches (lotes);
      4. emulando shuffling com PostgreSQL e spatial columns
      5. usando modulo/remainder - function MOD():
         1. https://www.w3schools.com/sql/func_mysql_mod.asp
         2. https://twitter.com/rponte/status/1590751858213416962?s=20&t=NsJ_AWtqtBV2h5p4mDm6-A
         3. https://stackoverflow.com/questions/3756928/selecting-rows-where-remainder-modulo-is-1-after-division-by-2
         4. https://stackoverflow.com/questions/49291926/how-to-partition-a-sql-server-table-with-a-modulus-function
         5. http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=AEAC4DE8-2337-4A3D-9AAA-1A3C221226A6&tkw=uses-of-the-modulo-(%)-operator
         6. https://www.yugabyte.com/blog/distributed-databases-hotspots-range-based-indexes/ (tem exemplo legal no uso de modulo com random());
  49. https://medium.com/@gajus/lessons-learned-scaling-postgresql-database-to-1-2bn-records-month-edc5449b3067
      1. desafios no Postgresql ao usar SKIP LOCKED com muitos workers e grande volume de dados;
  50. https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/
      1. tem uma boa explanação sobre maneiras errada de implementar job queues com PostgreSQL;
  51. https://engineering.contaazul.com/mitigando-problema-de-concorr%C3%AAncia-com-postgresql-e-skip-locked-7662581d166c
  52. https://lobste.rs/s/lfuy71/postgresql_listen_notify#c_a3rywz
  53. https://twitter.com/jfischoff/status/1219640306066313216?s=20
  54. https://github.com/jfischoff/postgresql-queue#readme
  55. https://medium.com/hackernoon/testing-postgresql-for-fun-af891047e5fc
  56. https://habr.com/ru/post/481556/ (russo - implementações para fila prioritária, retries etc)
  57. https://habr.com/ru/post/481556/#comment_21062504 (comentários com boas discusões; esse em particular: limitações num broker)
  58. https://github.com/yandex-money-tech/db-queue (implementação java de fila de exemplo - muito boa)
  59. http://www.celeryproject.org/ (Python Scheduler)
  60. https://cadenceworkflow.io/ (Uber Scheduler)
  61. https://wiki.postgresql.org/wiki/PGQ_Tutorial (PGQ)
      1. https://github.com/pgq
      2. https://www.pgcon.org/2009/schedule/attachments/91_pgq.pdf
      3. https://www.reddit.com/r/PostgreSQL/comments/8gr893/experience_with_pgq/ (muito bom, relato de centenas de milhões de eventos por dia)
  62. https://habr.com/ru/company/oleg-bunin/blog/455248/ (Erros comuns no Postgres e uso de PGQ)
  63. https://gist.github.com/rponte/066de2cb02815e6ec100b4daa4bd4db6 (simples passo a passo, e uso de savepoints)
  64. https://twitter.com/cowtowncoder/status/847186661528276994 (thread antiga onde pergunto sobre uso de SKIP LOCKED)
  65. https://twitter.com/rponte/status/1148594095159480322 (thread minha com discussão bacana sobre uso de fila no banco)
  66. https://stackoverflow.com/questions/56908888/wait-until-row-becomes-available-with-skip-locked (sobre usar NOTIFY/LISTEN)
  67. https://paquier.xyz/postgresql-2/postgres-12-vacuum-skip-locked/ (melhorias no Postgres 12: VACUUM + SKIP LOCKED)
  68. https://tnishimura.github.io/articles/queues-in-postgresql/ (on a completely unoptimized installation of PostgreSQL 9.5 running on an AWS c4.2xlarge (8 cores, 16 gb ram, $0.40/hour) yields a not-too-shabby 7200 retrievals per second.)
  69. https://news.ycombinator.com/item?id=14676859 (discussão bacana sobre filas no banco com PostgreSQL)
  70. https://github.com/subzerocloud/pg-amqp-bridge (uso de LISTEN/NOTIFY integrado ao RabbitMQ)
  71. https://www.youtube.com/watch?v=B81nQLg4RuU (palestra daquela apresentação sobre SKIP LOCKED)
  72. https://news.ycombinator.com/item?id=21536698 (thread discutindo sobre escalar fila no Postgres para 10k jobs/s)
  73. https://brandur.org/postgres-queues (Tradeoffs, problemática e soluções de ter fila no Postgres - EXCELENTE!)
      1. https://twitter.com/brandur/status/601044562669379584?s=20 (thread do @brandur sobre o post dele)
      2. https://github.com/que-rb/que/compare/master...brandur:transaction-tolerant (patch que o @brandur fez pra lib Que)
      3. https://brandur.org/job-drain (solução para Sidekiq usando outbox-pattern basicamente)
      4. http://rhaas.blogspot.com/2018/01/do-or-undo-there-is-no-vacuum.html?m=1 (discute problematica do Postgres com relação a long-running transactions e VACUUM)
      5. https://brandur.org/postgres-atomicity (entendendo como MVCC do Postgres fuciona)
      6. https://news.ycombinator.com/item?id=9576864 (post do @brandur no Hacker News e discussão)
  74. https://github.com/mperham/sidekiq/wiki/FAQ#what-kind-of-performance-can-i-expect-to-see-with-sidekiq (throughput do Sidekiq)
  75. https://blog.cocalc.com/2017/02/09/rethinkdb-vs-postgres.html (uso de NOTIFY/LISTEN)
      1. https://news.ycombinator.com/item?id=12649712
  76. PostgreSQL at 10TB and Beyond: Solving Volume, Velocity, and Variety [Video]
      1. https://www.postgresql.org/about/event/2093/ (descrição)
      2. https://dzone.com/articles/postgresql-at-10tb-and-beyond-solving-volume-velocity-and-variety (video)
      3. https://www.reddit.com/r/Database/comments/5tt23z/postgresql_at_10tb_and_beyond_recorded_talk/ (discussão no Reddit)
      4. https://news.ycombinator.com/item?id=13484910 (discussão no HackerNews)
      5. https://medium.com/@bdbuschg/postgresql-at-10tb-and-beyond-bab937f2393d (outro link pro video)
      6. (job queue table com 1bi de linhas)
  77. Que benchmark - 10k job/sec usando PostreSQL's Advisory Locks
      1. https://gist.github.com/chanks/7585810
  78. Job Queue com Rails e PostgreSQL com suporte a multi-tenant + Sidekiq
      1. https://www.holistics.io/blog/how-we-built-a-multi-tenant-job-queue-system-with-postgresql-ruby/
  79. https://www.percona.com/blog/2020/08/03/using-skip-lock-for-queue-processing-in-mysql/ (MySQL - exemplo não tão legal)
  80. https://www.postgresql.org/message-id/16676-fd62c3c835880da6%40postgresql.org (BUG quando usando WITH TIES)
  81. https://blog.crunchydata.com/blog/message-queuing-using-native-postgresql (DELETE + SKIP LOCKED)
  82. https://webapp.io/blog/postgres-is-the-answer/ (SKIP LOCKED + PUB/SUB with PostgreSQL)
  83. https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/ (SKIP LOCKED + NOWAIT com MySQL 8 )
  84. https://www.digitalocean.com/blog/from-15-000-database-connections-to-under-100-digitaloceans-tale-of-tech-debt (DigitalOcean usou fila em MySQL por quase 5 anos antes de migrar para RabbitMQ, após crescer 10k%)
  85. Facebook usando fila em banco (priority queue FOQS)
      1. https://engineering.fb.com/2022/01/18/production-engineering/foqs-disaster-ready/
      2. https://engineering.fb.com/2020/08/17/production-engineering/async/
  86. https://planetscale.com/blog/how-we-made-planetscale-background-jobs-self-healing-with-sidekiq (fila simples no banco para permitir replay em caso de crash)
  87. SKIP LOCKED with Python
      1. https://twitter.com/isotopp/status/1415035560423575552?s=11
      2. https://blog.koehntopp.info/2021/07/13/mysql-a-job-queue-in-python.html
  88. https://vincent.composieux.fr/article/distribute-the-rows-of-a-sql-table-between-several%20multi-instantiated-applications
  89. Citus Con 2022
      1. Queues in PostgreSQL: https://www.youtube.com/watch?v=WIRy1Ws47ic&ab_channel=MicrosoftDeveloper
  90. Very good article with benchmark using PgBench
      1. https://chbussler.medium.com/implementing-queues-in-postgresql-3f6e9ab724fa
  91. Apresenta pontos positivos e desafios na adoção de fila com Postgres para ingestão de eventos de logs
      1. https://dagster.io/blog/skip-kafka-use-postgres-message-queue
  92. LISTEN/NOTIFY e SKIP LOCKED para rede social
      1. https://www.evernote.com/client/web#?n=84d2720c-9ad4-4381-9402-3f9403c2a10b&
  93. YugabyteDB and SKIP LOCKED + partitioning via range-sharded tables
      1. "With 500 threads processing each job in 1 second"
      2. Testes de performance com Pgbench.
      3. Faz fanout (partitioning) através de uma View para distribuir de forma aleatoria + range-sharded tables para distriuir linhas entre os nodes/tablets
      4. https://dev.to/yugabyte/scalable-job-queue-in-sql-yugabytedb-4ma5
      5. https://twitter.com/rponte/status/1600190673566646273?s=20&t=NeV6IW9w64Cbz5cEJe0dTw
      6. Scalable Sequences with Postgres: https://dev.to/aws-heroes/scalable-sequence-for-postgresql-34o7
      7. https://en.wikipedia.org/wiki/Partition_(database)
      8. https://learn.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning
      9. Xxx
  94. Postgres, SKIP LOCKED and consumers in Go
      1. https://ente.io/blog/tech/postgres-queue/
      2. boa discussão sobre FOR UPDATE + ORDER BY Random()
      3. boa disucssao sobre uso de indexes;
  95. SQL Server já possui serviço de fila usando tabelas
      1. https://docs.particular.net/transports/sql/
  96. @rafaelcodes
      1. https://www.javaadvent.com/2022/12/using-postgres-as-a-message-queue.html
      2. https://twitter.com/rafaelcodes/status/1606572522061049856?s=43&t=wRAVlfxecOiSTzw9BkwlSQ
  97. Podcast sobre Queues in Postgres
      1. https://postgres.fm/episodes/queues-in-postgres

• Introducing PGMQ: Simple Message Queues built on Postgres
  • https://github.com/tembo-io/pgmq/blob/main/core/src/query.rs#L202-L223
• Devious SQL: Message Queuing Using Native PostgreSQL

• Choose Postgres queue technology
• HackerNews: Post about the artigle "Choose Postgres queue technology (adriano.fyi)"
• System design hack: Postgres is a great pub/sub & job server
• Neoq library: Neoq is a queue-agnostic background job library for Go, with a pleasant API and powerful features. Queue-agnostic means that whether you're using an in-memory queue for developing and testing, or Postgres or Redis queue in production.

PGMQ: Lightweight Message Queue on Postgres with No Background Worker

Use FOR UPDATE so that you ensure messages are only read by one consumer at time. SKIP LOCKED so that you can have multiple workers processing messages concurrently. Finally, implement a visibility timeout so that you no longer need to rely on an external process to handle messages that have failed to process. [...]

River: a Fast, Robust Job Queue for Go + Postgres

• River: a Fast, Robust Job Queue for Go + Postgres
• River website
• River documentation
• River github repository
• HackerNews: River launching
• Twitter: @rponte's thread about River
• River UI - Dashboard

• Introducing Solid Queue
• Mission Control — Jobs

• YugabyteDB avoids bloat with the outbox pattern
• MyRocks, InnoDB and Postgres as a queue

Example of how partitioning a counter can improve throughput without giving up locks:

PostgreSQL Lock Contention For Frequently Updated Rows

• Trying out Solid Queue and Mission Control with PostgreSQL

Excelente artigo demonstrando como criar uma solução Request-Reply usando o Pub/Sub (LISTEN/NOTIFY) do Postgres! Além de tirar proveito do banco com o uso de:

• SKIP LOCKED
• Advisory Locks
• Unlogged tables
• CTEs e returning clause
• Stored procedures

E também com exemplo funcional em Elixir + testes de integração 🤤

⭐️ Request-reply in Postgres

Excelente artigo no blog da Sequin sobre como desenhar sua fila de mensagens no RDBMS inspirada no design de brokers populares:

1. SQS (Standard e FIFO);
2. Kafka;
3. Combinação de Kafka+SQS;

⭐️ Sequin: Build your own SQS or Kafka with Postgres

Pontos interessantes sobre a implementação SQS-like:

• The staple feature of SQS (and most queues) is exactly once delivery within a visibility timeout.

• Postgres is a great fit for taking on SQS workloads.

  If a non-FIFO SQS queue is sufficient, the biggest advantage SQS has is that it has unlimited throughput (according to AWS). So if your throughput is very high and ordering doesn't matter, SQS might be hard to beat with Postgres.

  But if you need FIFO or your throughput is in the ~thousands per second, Postgres is a great option. FIFO SQS can support up to 3,000 messages per second. A basic machine running Postgres can achieve that easily as well.

Pontos interessantes sobre a implementação Kafka-like:

• So, it's a matter of your requirements. If you need massive scale and throughput (> 100k messages/sec), Kafka will be hard to beat. But if you're not operating at that scale, Postgres is hard to beat. You can use one system in your stack (Postgres) instead of two (Postgres and Kafka), and Postgres is far more observable than Kafka (everything is just a table).

Thread no Twitter do criador da Crunchy Data, Craig Kerstiens, com dicas sobre como desenhar uma boa solução de fila de mensagens no Postgres.

De acordo com o Craig, não é algo muito dificil, basta seguir estas 3 regras:

1. Try to have index-only scans and very low BUFFERS numbers in the execution plans (not degrading over time;
2. Most important (derived from "1", actually): take into consideration the Postgres MVCC model. Don't use DELETE for cleanup. Remember that any UPDATE is physically DELETE+INSERT. Design for low bloat. Most likely, decisions will involve partitioning and/or TRUNCATE;
3. Consider using SKIP LOCKED to have multiple consumers. In most cases, this is overrated, though, being a premature optimization for throughput – if you have low latency (SQL is written well), a single consumer might be enough;

Ele também cita mais algumas dicas após eu questioná-lo sobre práticas de design para evitar BLOAT no Postgres.