- 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)
- PGCon - 2016
-
- https://www.pgcon.org/2016/schedule/track/Applications/929.en.html (palestra sobre Filas no Postgres - by Thomas Munro)
-
- https://www.pgcon.org/2016/schedule/attachments/414_queues-pgcon-2016.pdf (PDF da palestra sobre Filas no Postgres - by Thomas Munro)
-
- https://www.youtube.com/watch?v=WIRy1Ws47ic&ab_channel=MicrosoftDeveloper (Queues in PostgreSQL | Citus Con: An Event for Postgres 2022 - by Thomas Munro)
-
- 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
Last active
August 30, 2025 21:31
-
-
Save rponte/a69b9cf237a81857bfceff6a5b18c09b to your computer and use it in GitHub Desktop.
Content about implementing a database-based queue with SQL, SKIP LOCKED and LISTEN/NOTIFY
Excelente artigo no blog da Sequin sobre como desenhar sua fila de mensagens no RDBMS inspirada no design de brokers populares:
- SQS (Standard e FIFO);
- Kafka;
- 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:
- Try to have index-only scans and very low BUFFERS numbers in the execution plans (not degrading over time;
- 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;
- 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
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.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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:
E também com exemplo funcional em Elixir + testes de integração 🤤
⭐️ Request-reply in Postgres