Postgres as a Message Queue? Hold My Coffee!

It's a quiet Saturday afternoon, and I'm cozied up on my couch with my laptop, a steaming cup of coffee within reach. The sun streams through the window, creating the perfect coding ambiance. I'm tinkering with Calmly, my Slack bot that's all about helping folks maintain their mental health. (Oh, the irony of a developer building a mental health app, right?)

As I sip my coffee, I ponder how I can get my Next.js frontend to politely chat with the Node.js backend. I'm trying to keep my Node.js backend as more of a worker service that isn't directly exposed to the internet - you know, to keep those cheeky internet sleuths at bay. At the same time, I'm aiming to keep my infrastructure setup simple and easy to manage. The less moving parts, the better! This poses a bit of a conundrum - I would really rather not resort to carrier pigeons or elaborate smoke signals for communication!

I scroll through countless Stack Overflow threads and GitHub discussions, each one a dead end. Just as I'm about to call it quits and head to the kitchen for a snack, something catches my eye. A random comment in an unrelated thread mentions using Postgres for message queuing.

"Wait a second," I mutter, sitting up straight. "Postgres? For message queuing?"

My curiosity and inner cynic are triggered simultaneously. Postgres is just for storing data, right? This will just be some horrible race condition riddled polling setup, surely? But as I dig deeper, a smile tugs at the corners of my mouth. This unassuming database might just be the ticket for pigeon avoidant communication! As I start coding, I can't help but chuckle at the thought of Postgres moonlighting as a message queue. Who knew it had such hidden talents?

My coffee grows cold beside me, forgotten in the quiet satisfaction of exploration. This could be a neat solution for Calmly - communication between my services without the need to wrangle a new system. And to think, it all started with a random comment on a lazy Saturday afternoon.

I crack my knuckles and dive in. Let's see if this database can pull double duty.

The Problem: Why Not Just Use HTTP?

You might be wondering, "Why not just use good ol' HTTP for this frontend-backend communication?" It's a fair question. In many cases, slapping an API endpoint on the Node.js backend and having the Next.js frontend make a few fetch calls would do the trick.

But here's the catch: I'm aiming to keep my Node.js backend isolated from direct internet access. This setup enhances security by reducing the attack surface exposed to potential threats. Exposing it directly to the web would go against this goal.

There's also the matter of asynchronous operations. In Calmly's case, the frontend often needs to initiate tasks that might take a while to complete. Waiting around for these tasks to finish isn't ideal for user experience. While HTTP can handle asynchronous operations, it's not always the most elegant solution for this kind of fire-and-forget scenario.

What I really need is a way for my frontend to send messages to the backend without direct communication, and for the backend to process these messages at its own pace. It's like passing notes in class, but without the risk of detention. (Okay, I couldn't resist one little metaphor!)

This setup gives me a bunch of flexibility in how I can choose to execute the various tasks and helps keep the backend away from the bad influence of the internet sleuths just a little bit longer. The question is, how can I achieve that without having to bring in additional infrastructure?

Enter Postgres: The Unexpected Hero

Now, I know what you're thinking: "Come on, Gareth, I know you can slap data in a table and hope for the best with race conditions, but this ain't a real messaging system". And you'd be right to be skeptical. But hold onto your hats, folks, because Postgres has a few tricks up its sleeve that might just surprise you.

You see, Postgres isn't just about storing data and making your DBA's life interesting. It actually has built-in functionality that essentially allows it to act as a pub/sub queue using LISTEN/NOTIFY commands. This isn't your grandma's "insert data and pray" setup – it's a legitimate, race-condition-avoiding messaging system baked right into the database.

But here's the rub: as much as I love a good SQL query, I wasn't exactly thrilled about writing a bunch of them to get this working. I wanted to stick to the cozy, type-safe land of TypeScript. That's where pg-boss enters the chat. It's like a lightweight superhero cape for Postgres, turning it into a full-fledged job queue without me having to write a single line of SQL. It handles all the nitty-gritty details like job persistence, retries, and scheduling. With pg-boss, Postgres isn't just storing data anymore; it's now the master of ceremonies for your application's tasks.

The best part? I already have Postgres set up for Calmly's data storage. So, I'm not adding any new infrastructure - just leveraging what I've already got in a clever new way. Talk about killing two birds with one stone (but, you know, in a nice, bird-friendly way).

Implementation: Easier Than Herding Cats

Now, I know what you're thinking: "Great, Gareth, you've sold me on this Postgres-as-a-queue thing, but how much of a headache is it to set up?" Well, buckle up, because it's about to get surprisingly smooth.

First off, pg-boss does a lot of the heavy lifting for us. It automatically configures its own schema inside the Postgres database, which means we don't have to fumble around with table creation or schema management. It's like having a tiny, efficient database admin living inside your app.

Let's start with the Next.js side of things. Here's a snippet that handles enqueueing messages:

import PgBoss from "pg-boss";

export async function enqueueMessage(
  queue: string,
  payload: object,
): Promise<void> {
  const boss = new PgBoss(CalmlyConfig.PG_BOSS_CONNECTION_STRING);

  try {
    await boss.start();
    await boss.send(queue, payload);

  } catch (error) {
    console.error("Error enqueuing message", { error });
    throw new Error("Unable to queue message");

  } finally {
    await boss.stop();
  }
}

        

This function is pretty straightforward. It creates a new pg-boss instance, starts it up, sends a message to the specified queue, and then shuts down. It's like a digital carrier pigeon that delivers its message and then immediately retires.

On the Node.js service side, we set up a subscriber to process these messages:

async function subscribe<TPayload>(
  boss: PgBoss,
  queue: string,
  handler: (payload: TPayload) => Promise<void>
) {
  await boss.work<TPayload>(queue, async ([job]) => {
    await handler(job.data);
    await boss.deleteJob(queue, job.id);
  });
}

        

This function sets up a worker that listens to a specific queue. When a message arrives, it processes the job using the provided handler and then deletes the job. It's like having a dedicated assistant who reads your post-it notes, does the task, and then eats the post-it. Efficient and tidy!

In Calmly, I use this setup for things like sending welcome messages to new users. The frontend drops a "new user" message into the queue, and the backend picks it up and sends out a warm, fuzzy welcome email, all without direct communication between the two.

The beauty of this setup is its simplicity and flexibility. Whether I'm sending one message or one million (okay, I'm not actually sure on how big this setup will let me go, but at one message it works great!), the code remains the same. And thanks to pg-boss, I don't have to worry about the underlying database operations. It's all handled automagically.

The Payoff: Why This is Actually Pretty Cool

So, I've gone through all this trouble to turn my trusty Postgres database into a part-time message queue. Was it worth it? Spoiler alert: Oh yeah, it was.

Let's talk simplicity. By using Postgres as my message queue, I've kept my infrastructure lean. There's no need to set up and maintain a separate message queue system. One less moving part means fewer potential points of failure and less cognitive overhead when I'm working on Calmly.

Security-wise, I'm in a good place. My Node.js backend can now stay tucked away in its little network bunker, away from direct internet access. This setup allows me to maintain communication between components while keeping the backend isolated, which is exactly what I was aiming for.

Flexibility is another big win here. If I need to add a new type of notification or change how I process messages, it's straightforward. Pg-boss provides a lot of options out of the box, so I can adapt the system as Calmly's needs evolve without major overhauls.

For Calmly specifically, this setup has been really useful. I can now handle asynchronous tasks like sending welcome emails, scheduling check-ins, or processing user feedback more efficiently. The frontend remains responsive, and the backend processes these tasks at its own pace.

In short, I've got a setup that's simple, secure, and flexible, all while using a database I was already familiar with. It's not revolutionary, but it's a solid solution that fits my needs perfectly.

Gotchas and Considerations

Now, I'd be remiss if I didn't mention a few potential gotchas. But don't worry, I'm not about to rain on our Postgres parade just yet.

First up, performance at scale. While my little setup is humming along nicely, I've heard whispers that Postgres might start to sweat a bit under really heavy loads. But hey, that's a problem for future Gareth, right?

Then there's the matter of complex features. If you're looking for fancy routing or need to perform quantum calculations on your messages mid-flight, you might find yourself longing for a more specialised solution. But for my straightforward needs? Postgres is nailing it.

Lastly, there's the familiarity factor. If you're used to working with dedicated pub/sub queues, Postgres might feel a bit like trying to spread butter with a spoon - it works, but it's not quite what you're used to.

Are there probably more considerations? Sure. But for now, I'm choosing to bask in the glory of ignorance and enjoy my simple little messaging queue setup. Sometimes, not knowing every potential pitfall is bliss, especially when everything's working smoothly.

Wrapping Up: Queue the Applause

So there you have it, folks - the tale of how Postgres became the unexpected hero in Calmly's messaging saga. Who knew a database could moonlight as a pretty decent message queue?

This little adventure has taught me that sometimes, the best solution is hiding in plain sight. It's easy to get caught up in the latest and greatest tech, but often, we can do amazing things with the tools we already have.

Is Postgres-as-a-queue the right solution for everyone? Probably not. But for Calmly, it's hit that sweet spot of simplicity, functionality, and "hey, it actually works!"

So, next time you're facing a technical challenge, take a look around. Your Swiss Army knife solution might be closer than you think.

Got thoughts? Questions? War stories about your own unconventional tech solutions? Drop them in the comments. I'm always up for a good tech tale!