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

RudderStack case: Scaling PostgreSQL queues to 100k events per second

• Why RudderStack Used Postgres Over Apache Kafka for Streaming Engine
• Kafka Vs. PostgreSQL: How We Implemented Our Queueing System Using PostgreSQL
• Lessons from scaling PostgreSQL queues to 100k events per second