Forget SQS, RabbitMQ and Kafka. Just use Postgres.

EnglishالعربيةDeutschEspañolFrançaisहिन्दीBahasa Indonesia日本語한국어PortuguêsРусский中文
BBetter Stack
Computing/SoftwareInternet Technology

Transcript

00:00:00SQS, Kafka and RabbitMQ can get right in the bin because you can now manage your queues directly
00:00:05inside Postgres with PGMQ. And I can already hear people saying, but it doesn't scale.
00:00:11Well, actually it does because Postgres can comfortably handle millions of rows and
00:00:15thousands of requests per second. So if you want to keep your infrastructure simple,
00:00:20today we'll explore building high volume distributed apps with PGMQ.
00:00:30Right, no messing around. We're going to jump straight into the demo. I've set up a project
00:00:34with Docker and installed PGMQ. You can use this with two flavors, either SQL only or one of the
00:00:41official client libraries like Rust or Python, plus a bunch of community libraries like Ruby or multiple
00:00:47flavors of TypeScript. Since we're never going to agree on language, I'll just run this demo with
00:00:52the SQL only option. Let's start with creating a new queue. We run select pgmq.create and then pass
00:00:58the queue name itself. Every queue here is its own table. If we look at the database directly,
00:01:04we can see the new table now exists. We can then send messages to our queue specifying the queue name,
00:01:09then the message as JSON. You could optionally include a delay. So the message is pushed to the
00:01:15queue, but can't be consumed for say five seconds. Okay, so now we've got the messages on the queue.
00:01:20Let's consume them. We can do this with the read command. VT is visibility timeout. And here it means
00:01:26the messages you read will be visible for 30 seconds. So no other process can pick them up.
00:01:31This is how PGMQ guarantees exactly once delivery. Quantity is then the amount of messages we wish to
00:01:38read. And if they're not deleted or archived within that window, they'll become visible again. And to do
00:01:44that, you can run either archive, which deletes from the queue and adds to the archive table, or by just
00:01:49running delete. For example, here, I delete the message with ID too. And just a side note, if you find this
00:01:54useful, then you do us a massive favor by subscribing to the channel. It helps us keep creating free content
00:01:59to help as many developers as possible. Okay, so let's stress test PGMQ. First, I'll add 100,000 rows
00:02:06into the queue. And this takes roughly 0.4 seconds. Now I'll launch 100 workers to each process a job in
00:02:13batches of 10 and see how long it takes to read all of them. Here, all the worker does is read the
00:02:18message, log what it read, and then delete it. Of course, you'd want to do something with that data.
00:02:23But I'm only concerned with testing the performance of PGMQ directly here. And that took nine seconds.
00:02:29Each worker processed on average 111 messages per second. So combined around 11,100 messages a second.
00:02:37I purposely limited the Docker container to two CPUs and a memory of two gigabytes, typical for a live
00:02:44service. Now, as I mentioned earlier, you can also use the client libraries to interact with PGMQ.
00:02:49Using TypeScript with Prisma, we can create a queue, send a message, read a batch of messages, and you can
00:02:55also do the same with Python as well. So if you don't want extra dependencies, you can genuinely run
00:03:01queues at scale inside Postgres with PGMQ. And we take this a lot further with replacing as much of
00:03:08a stack as possible with Postgres, which I cover in this video.

Key Takeaway

Postgres manages high-volume message queues natively through PGMQ, processing over 11,000 messages per second while eliminating external infrastructure dependencies like SQS or Kafka.

Highlights

  • Queues can be managed directly inside Postgres using PGMQ instead of using separate message brokers like SQS, Kafka, or RabbitMQ.

  • Postgres handles 100,000 rows added to a queue in roughly 0.4 seconds.

  • PGMQ with 100 workers processes a combined rate of 11,100 messages per second on a Docker container limited to two CPUs and two gigabytes of memory.

  • Visibility timeouts control message locking so that other processes cannot pick them up for a specified duration.

  • PGMQ supports SQL-only operations as well as official client libraries for Rust, Python, Ruby, and TypeScript.

Timeline

Postgres as a Message Queue

  • Message queues run directly inside Postgres using PGMQ.
  • Postgres handles millions of rows and thousands of requests per second.

Traditional message brokers like SQS, Kafka, and RabbitMQ can be replaced by Postgres to keep infrastructure simple. High-volume distributed applications operate efficiently using database-backed queues without scaling issues.

Queue Operations and Lifecycle

  • Each queue functions as its own table within the database.
  • Messages are sent as JSON with optional delays before consumption.
  • Visibility timeouts ensure exactly once delivery by locking read messages for a defined period.

Queues are initialized using SQL commands like pgmq.create. Messages pushed to the queue can include delivery delays. Reading messages applies a visibility timeout that prevents other processes from picking up the same data unless the window expires or the message is explicitly archived or deleted.

Performance and Scale Testing

  • Adding 100,000 rows into the queue takes approximately 0.4 seconds.
  • A test setup with 100 workers processes 11,100 messages per second under constrained hardware resources.
  • Client libraries for TypeScript and Python support PGMQ alongside SQL-only options.

Stress testing PGMQ inside a Docker container limited to two CPUs and two gigabytes of memory demonstrates high throughput. Workers process jobs in batches by reading and deleting messages. Client libraries extend integration capabilities across different programming languages.

Community Posts

No posts yet. Be the first to write about this video!

Write about this video