Content about implementing a database-based queue with SQL, SKIP LOCKED and LISTEN/NOTIFY

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. PGCon - 2016
      • 42. https://www.pgcon.org/2016/schedule/track/Applications/929.en.html (palestra sobre Filas no Postgres - by Thomas Munro)
      • 43. https://www.pgcon.org/2016/schedule/attachments/414_queues-pgcon-2016.pdf (PDF da palestra sobre Filas no Postgres - by Thomas Munro)
      • 44. https://www.youtube.com/watch?v=WIRy1Ws47ic&ab_channel=MicrosoftDeveloper (Queues in PostgreSQL | Citus Con: An Event for Postgres 2022 - by Thomas Munro)
  42. http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html (problemas de filas no SQL Server)
  43. https://it.toolbox.com/blogs/georgealexander/the-database-as-queue-anti-pattern-or-is-it-043012 (contra-argumento do artigo acima)
  44. https://softwareengineering.stackexchange.com/questions/231410/why-database-as-queue-so-bad
  45. https://softwareengineering.stackexchange.com/questions/351449/message-queue-database-vs-dedicated-mq
  46. https://stackoverflow.com/questions/2177880/using-a-database-table-as-a-queue
  47. 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());
  48. 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;
  49. 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;
  50. https://engineering.contaazul.com/mitigando-problema-de-concorr%C3%AAncia-com-postgresql-e-skip-locked-7662581d166c
  51. https://lobste.rs/s/lfuy71/postgresql_listen_notify#c_a3rywz
  52. https://twitter.com/jfischoff/status/1219640306066313216?s=20
  53. https://github.com/jfischoff/postgresql-queue#readme
  54. https://medium.com/hackernoon/testing-postgresql-for-fun-af891047e5fc
  55. https://habr.com/ru/post/481556/ (russo - implementações para fila prioritária, retries etc)
  56. https://habr.com/ru/post/481556/#comment_21062504 (comentários com boas discusões; esse em particular: limitações num broker)
  57. https://github.com/yandex-money-tech/db-queue (implementação java de fila de exemplo - muito boa)
  58. http://www.celeryproject.org/ (Python Scheduler)
  59. https://cadenceworkflow.io/ (Uber Scheduler)
  60. 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)
  61. https://habr.com/ru/company/oleg-bunin/blog/455248/ (Erros comuns no Postgres e uso de PGQ)
  62. https://gist.github.com/rponte/066de2cb02815e6ec100b4daa4bd4db6 (simples passo a passo, e uso de savepoints)
  63. https://twitter.com/cowtowncoder/status/847186661528276994 (thread antiga onde pergunto sobre uso de SKIP LOCKED)
  64. https://twitter.com/rponte/status/1148594095159480322 (thread minha com discussão bacana sobre uso de fila no banco)
  65. https://stackoverflow.com/questions/56908888/wait-until-row-becomes-available-with-skip-locked (sobre usar NOTIFY/LISTEN)
  66. https://paquier.xyz/postgresql-2/postgres-12-vacuum-skip-locked/ (melhorias no Postgres 12: VACUUM + SKIP LOCKED)
  67. 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.)
  68. https://news.ycombinator.com/item?id=14676859 (discussão bacana sobre filas no banco com PostgreSQL)
  69. https://github.com/subzerocloud/pg-amqp-bridge (uso de LISTEN/NOTIFY integrado ao RabbitMQ)
  70. https://www.youtube.com/watch?v=B81nQLg4RuU (palestra daquela apresentação sobre SKIP LOCKED)
  71. https://news.ycombinator.com/item?id=21536698 (thread discutindo sobre escalar fila no Postgres para 10k jobs/s)
  72. 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)
  73. https://github.com/mperham/sidekiq/wiki/FAQ#what-kind-of-performance-can-i-expect-to-see-with-sidekiq (throughput do Sidekiq)
  74. https://blog.cocalc.com/2017/02/09/rethinkdb-vs-postgres.html (uso de NOTIFY/LISTEN)
      1. https://news.ycombinator.com/item?id=12649712
  75. 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)
  76. Que benchmark - 10k job/sec usando PostreSQL's Advisory Locks
      1. https://gist.github.com/chanks/7585810
  77. 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/
  78. https://www.percona.com/blog/2020/08/03/using-skip-lock-for-queue-processing-in-mysql/ (MySQL - exemplo não tão legal)
  79. https://www.postgresql.org/message-id/16676-fd62c3c835880da6%40postgresql.org (BUG quando usando WITH TIES)
  80. https://blog.crunchydata.com/blog/message-queuing-using-native-postgresql (DELETE + SKIP LOCKED)
  81. https://webapp.io/blog/postgres-is-the-answer/ (SKIP LOCKED + PUB/SUB with PostgreSQL)
  82. 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 )
  83. 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%)
  84. 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/
  85. 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)
  86. 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
  87. https://vincent.composieux.fr/article/distribute-the-rows-of-a-sql-table-between-several%20multi-instantiated-applications
  88. Citus Con 2022
      1. Queues in PostgreSQL: https://www.youtube.com/watch?v=WIRy1Ws47ic&ab_channel=MicrosoftDeveloper
  89. Very good article with benchmark using PgBench
      1. https://chbussler.medium.com/implementing-queues-in-postgresql-3f6e9ab724fa
  90. 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
  91. LISTEN/NOTIFY e SKIP LOCKED para rede social
      1. https://www.evernote.com/client/web#?n=84d2720c-9ad4-4381-9402-3f9403c2a10b&
  92. 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
  93. 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;
  94. SQL Server já possui serviço de fila usando tabelas
      1. https://docs.particular.net/transports/sql/
  95. @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
  96. Podcast sobre Queues in Postgres
      1. https://postgres.fm/episodes/queues-in-postgres

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.

This very good article explains how to build a sophisticated and highly-scalable platform (social network) using Postgres LISTEN/NOTIFY and SKIP LOCKED:
Listening to Postgres: How LISTEN and NOTIFY Syntax Promote High Availability at the Application Layer

Hacker News: I use Postgres SKIP LOCKED as a queue. (2019)

Mastering SKIP LOCKED in MySQL

• MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows
• PlanetScale blog: B-trees and database indexes
• A Comprehensive (and Animated) Guide to InnoDB Locking

Over the past few months, I've been deeply involved in designing a system that pushes MySQL's SKIP LOCKED feature to its limits. This journey revealed several nuances and optimization opportunities that can significantly impact performance. In this post, we'll explore these insights, demonstrating how to reduce lock overhead, optimize schema design, and fine-tune transaction isolation levels to achieve remarkable performance gains.

Solid Queue internals, externals and all the things in between - by Rosa Gutiérrez