- Distributed Polling with SKIP LOCKED
- https://www.evernote.com/client/web#?an=true&fs=true&n=93f6cdf2-633d-4051-8b99-7e2d05498ead&s=s173&
- https://gist.github.com/rponte/0c5b0e3c1b84c0c2e49c863215c2c0f4
- https://gist.github.com/rponte/5e8d41fd3b2ced22206dce788208c30b (Implementing, rewriting, and rebuilding a task queue - by @tef_ebooks)
- https://todd-hubers.medium.com/you-really-can-replace-kafka-with-a-database-9e82a7c248a6 (Replacing Kafka with a database?)
- https://www.cloudamqp.com/blog/2015-11-23-why-is-a-database-not-the-right-tool-for-a-queue-based-system.html
- https://viralpatel.net/blogs/oracle-skip-locked/
- https://vladmihalcea.com/database-job-queue-skip-locked/
- https://github.com/vladmihalcea/high-performance-java-persistence/blob/master/core/src/test/java/com/vladmihalcea/book/hpjp/hibernate/concurrency/SkipLockJobQueueTest.java#L186
- https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#locking-follow-on
- https://hibernate.atlassian.net/browse/HHH-12848
- https://markjbobak.wordpress.com/2010/04/06/unintended-consequences/
- https://jonathanlewis.wordpress.com/2010/05/31/skip-locked/
- https://medium.com/@FranckPachot/oracle-hibernate-de-queuing-7454432a7738?sk=b50b8829c359fe7239f0356337afecee
- 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
- http://www.sqlines.com/oracle-to-mysql/skip_locked
- https://stackoverflow.com/questions/27968877/how-to-avoid-two-different-threads-read-the-same-rows-from-db-hibernate-and-ora
- https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9538644700346644113 (FOR UPDATE SKIP LOCKED with ROWNUM)
- https://asktom.oracle.com/pls/apex/asktom.search?tag=select-for-update-skip-locked (SELECT FOR UPDATE SKIP LOCKED)
- http://rwijk.blogspot.com/2009/02/for-update-skip-locked.html
- https://db-blog.web.cern.ch/blog/franck-pachot/2018-09-oracle-write-consistency-bug-and-multi-thread-de-queuing
- https://stackoverflow.com/questions/33816750/oracle-table-acting-like-a-queue
- https://www.codeproject.com/Tips/778259/Using-an-Oracle-Database-Table-as-a-Multithreaded
- https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:2060739900346201280 (favoring Oracle AQ over SKIP LOCKED)
- 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)
- https://stackoverflow.com/questions/42899520/does-oracle-skip-locked-prevent-non-repeatable-reads
(how skip locked works behind the scenes) - http://stevenfeuersteinonplsql.blogspot.com/2016/05/types-of-cursors-available-in-plsql.html
- https://stackoverflow.com/questions/13265659/when-should-i-commit-when-using-for-update-in-a-procedure
- https://www.cs.umb.edu/~eoneil/introPLSQL/06_ora.htm (fetching across commits)
- http://www.oracledba.co.uk/tips/fetch_across_commits.htm
- http://www.java2s.com/Tutorial/Oracle/0500__Cursor/FetchingAcrossCommitsExample2.htm
- https://stackoverflow.com/questions/21637470/bulk-collect-using-for-update/21637619#21637619
- https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7661190956484
- https://stackoverflow.com/questions/5757268/how-to-fetch-delete-commit-from-cursor
- https://orastory.wordpress.com/category/fetch-across-commit/
- http://askmaclean.com/wp-content/uploads/2009/09/Secrets%20of%20the%20Oracle%20Database.pdf (book Secrets of the Oracle Database)
- https://yaocm.wordpress.com/2019/04/13/technical-note-on-skip-locked/ (exemplos praticos para Oracle 12c)
- https://yaocm.wordpress.com/2019/04/13/technical-note-on-skip-locked/#comment-1493
- https://github.com/dfhawthorne/demos/tree/master/select_with_update
- https://news.ycombinator.com/item?id=14676859 (SKIP LOCKED no MySQL e Postgres)
- https://news.ycombinator.com/item?id=14730685 (SKIP LOCKED Postgres)
- https://www.pgcon.org/2016/schedule/track/Applications/929.en.html (palestra sobre Filas no Postgres)
- https://www.pgcon.org/2016/schedule/attachments/414_queues-pgcon-2016.pdf (PDF da palestra sobre Filas no Postgres)
- http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html (problemas de filas no SQL Server)
- https://it.toolbox.com/blogs/georgealexander/the-database-as-queue-anti-pattern-or-is-it-043012 (contra-argumento do artigo acima)
- https://softwareengineering.stackexchange.com/questions/231410/why-database-as-queue-so-bad
- https://softwareengineering.stackexchange.com/questions/351449/message-queue-database-vs-dedicated-mq
- https://stackoverflow.com/questions/2177880/using-a-database-table-as-a-queue
- https://vladmihalcea.com/sql-order-by-random/
- shuffling rows - with Oracle it's better to use the SAMPLE(N) function;
- interessante para diminuir a contigência quando usando FOR UPDATE;
- outra forma de diminuir a contigência é usando batches (lotes);
- emulando shuffling com PostgreSQL e spatial columns
- usando modulo/remainder - function MOD():
- https://www.w3schools.com/sql/func_mysql_mod.asp
- https://twitter.com/rponte/status/1590751858213416962?s=20&t=NsJ_AWtqtBV2h5p4mDm6-A
- https://stackoverflow.com/questions/3756928/selecting-rows-where-remainder-modulo-is-1-after-division-by-2
- https://stackoverflow.com/questions/49291926/how-to-partition-a-sql-server-table-with-a-modulus-function
- http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=AEAC4DE8-2337-4A3D-9AAA-1A3C221226A6&tkw=uses-of-the-modulo-(%)-operator
- https://www.yugabyte.com/blog/distributed-databases-hotspots-range-based-indexes/ (tem exemplo legal no uso de modulo com random());
- https://medium.com/@gajus/lessons-learned-scaling-postgresql-database-to-1-2bn-records-month-edc5449b3067
- desafios no Postgresql ao usar SKIP LOCKED com muitos workers e grande volume de dados;
- https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/
- tem uma boa explanação sobre maneiras errada de implementar job queues com PostgreSQL;
- https://engineering.contaazul.com/mitigando-problema-de-concorr%C3%AAncia-com-postgresql-e-skip-locked-7662581d166c
- https://lobste.rs/s/lfuy71/postgresql_listen_notify#c_a3rywz
- https://twitter.com/jfischoff/status/1219640306066313216?s=20
- https://github.com/jfischoff/postgresql-queue#readme
- https://medium.com/hackernoon/testing-postgresql-for-fun-af891047e5fc
- https://habr.com/ru/post/481556/ (russo - implementações para fila prioritária, retries etc)
- https://habr.com/ru/post/481556/#comment_21062504 (comentários com boas discusões; esse em particular: limitações num broker)
- https://github.com/yandex-money-tech/db-queue (implementação java de fila de exemplo - muito boa)
- http://www.celeryproject.org/ (Python Scheduler)
- https://cadenceworkflow.io/ (Uber Scheduler)
- https://wiki.postgresql.org/wiki/PGQ_Tutorial (PGQ)
- https://github.com/pgq
- https://www.pgcon.org/2009/schedule/attachments/91_pgq.pdf
- https://www.reddit.com/r/PostgreSQL/comments/8gr893/experience_with_pgq/ (muito bom, relato de centenas de milhões de eventos por dia)
- https://habr.com/ru/company/oleg-bunin/blog/455248/ (Erros comuns no Postgres e uso de PGQ)
- https://gist.github.com/rponte/066de2cb02815e6ec100b4daa4bd4db6 (simples passo a passo, e uso de savepoints)
- https://twitter.com/cowtowncoder/status/847186661528276994 (thread antiga onde pergunto sobre uso de SKIP LOCKED)
- https://twitter.com/rponte/status/1148594095159480322 (thread minha com discussão bacana sobre uso de fila no banco)
- https://stackoverflow.com/questions/56908888/wait-until-row-becomes-available-with-skip-locked (sobre usar NOTIFY/LISTEN)
- https://paquier.xyz/postgresql-2/postgres-12-vacuum-skip-locked/ (melhorias no Postgres 12: VACUUM + SKIP LOCKED)
- 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.)
- https://news.ycombinator.com/item?id=14676859 (discussão bacana sobre filas no banco com PostgreSQL)
- https://github.com/subzerocloud/pg-amqp-bridge (uso de LISTEN/NOTIFY integrado ao RabbitMQ)
- https://www.youtube.com/watch?v=B81nQLg4RuU (palestra daquela apresentação sobre SKIP LOCKED)
- https://news.ycombinator.com/item?id=21536698 (thread discutindo sobre escalar fila no Postgres para 10k jobs/s)
- https://brandur.org/postgres-queues (Tradeoffs, problemática e soluções de ter fila no Postgres - EXCELENTE!)
- https://twitter.com/brandur/status/601044562669379584?s=20 (thread do @brandur sobre o post dele)
- https://github.com/que-rb/que/compare/master...brandur:transaction-tolerant (patch que o @brandur fez pra lib Que)
- https://brandur.org/job-drain (solução para Sidekiq usando outbox-pattern basicamente)
- 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)
- https://brandur.org/postgres-atomicity (entendendo como MVCC do Postgres fuciona)
- https://news.ycombinator.com/item?id=9576864 (post do @brandur no Hacker News e discussão)
- https://github.com/mperham/sidekiq/wiki/FAQ#what-kind-of-performance-can-i-expect-to-see-with-sidekiq (throughput do Sidekiq)
- https://blog.cocalc.com/2017/02/09/rethinkdb-vs-postgres.html (uso de NOTIFY/LISTEN)
- PostgreSQL at 10TB and Beyond: Solving Volume, Velocity, and Variety [Video]
- https://www.postgresql.org/about/event/2093/ (descrição)
- https://dzone.com/articles/postgresql-at-10tb-and-beyond-solving-volume-velocity-and-variety (video)
- https://www.reddit.com/r/Database/comments/5tt23z/postgresql_at_10tb_and_beyond_recorded_talk/ (discussão no Reddit)
- https://news.ycombinator.com/item?id=13484910 (discussão no HackerNews)
- https://medium.com/@bdbuschg/postgresql-at-10tb-and-beyond-bab937f2393d (outro link pro video)
- (job queue table com 1bi de linhas)
- Que benchmark - 10k job/sec usando PostreSQL's Advisory Locks
- Job Queue com Rails e PostgreSQL com suporte a multi-tenant + Sidekiq
- https://www.percona.com/blog/2020/08/03/using-skip-lock-for-queue-processing-in-mysql/ (MySQL - exemplo não tão legal)
- https://www.postgresql.org/message-id/16676-fd62c3c835880da6%40postgresql.org (BUG quando usando WITH TIES)
- https://blog.crunchydata.com/blog/message-queuing-using-native-postgresql (DELETE + SKIP LOCKED)
- https://webapp.io/blog/postgres-is-the-answer/ (SKIP LOCKED + PUB/SUB with PostgreSQL)
- 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 )
- 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%)
- Facebook usando fila em banco (priority queue FOQS)
- 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)
- SKIP LOCKED with Python
- https://vincent.composieux.fr/article/distribute-the-rows-of-a-sql-table-between-several%20multi-instantiated-applications
- Citus Con 2022
- Queues in PostgreSQL: https://www.youtube.com/watch?v=WIRy1Ws47ic&ab_channel=MicrosoftDeveloper
- Very good article with benchmark using PgBench
- Apresenta pontos positivos e desafios na adoção de fila com Postgres para ingestão de eventos de logs
- LISTEN/NOTIFY e SKIP LOCKED para rede social
- YugabyteDB and SKIP LOCKED + partitioning via range-sharded tables
- "With 500 threads processing each job in 1 second"
- Testes de performance com Pgbench.
- Faz fanout (partitioning) através de uma View para distribuir de forma aleatoria + range-sharded tables para distriuir linhas entre os nodes/tablets
- https://dev.to/yugabyte/scalable-job-queue-in-sql-yugabytedb-4ma5
- https://twitter.com/rponte/status/1600190673566646273?s=20&t=NeV6IW9w64Cbz5cEJe0dTw
- Scalable Sequences with Postgres: https://dev.to/aws-heroes/scalable-sequence-for-postgresql-34o7
- https://en.wikipedia.org/wiki/Partition_(database)
- https://learn.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning
- Xxx
- Postgres, SKIP LOCKED and consumers in Go
- https://ente.io/blog/tech/postgres-queue/
- boa discussão sobre FOR UPDATE + ORDER BY Random()
- boa disucssao sobre uso de indexes;
- SQL Server já possui serviço de fila usando tabelas
- @rafaelcodes
- Podcast sobre Queues in Postgres
Created
April 28, 2023 14:01
-
-
Save rponte/a69b9cf237a81857bfceff6a5b18c09b to your computer and use it in GitHub Desktop.
Content about implementing a database-based queue with SQL and SKIP LOCKED
Author
rponte
commented
Aug 9, 2023
•
- 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.
River: a Fast, Robust Job Queue for Go + Postgres
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment