Skip to content

Instantly share code, notes, and snippets.

@rponte

rponte/links-skiplocked.md

Last active August 30, 2025 21:31
Show Gist options
  • Save rponte/a69b9cf237a81857bfceff6a5b18c09b to your computer and use it in GitHub Desktop.
Save rponte/a69b9cf237a81857bfceff6a5b18c09b to your computer and use it in GitHub Desktop.
Download ZIP
Content about implementing a database-based queue with SQL, SKIP LOCKED and LISTEN/NOTIFY
@rponte
Copy link
Author

rponte commented Nov 6, 2024 •
edited
Loading

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).

@rponte
Copy link
Author

rponte commented Nov 6, 2024

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.

@rponte
Copy link
Author

rponte commented Nov 21, 2024

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

@rponte
Copy link
Author

rponte commented Nov 22, 2024

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

@rponte
Copy link
Author

rponte commented Nov 22, 2024 •
edited
Loading

Mastering SKIP LOCKED in MySQL

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.

@rponte
Copy link
Author

rponte commented Mar 3, 2025

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment