How to implement the Outbox pattern in Go and Postgres

Last year I was at the Container Days conference, where I attended a great talk from Nikolai Kuznetsov about the outbox pattern and resilient system design. It seemed like a very powerful solution to a common problem in a distributed system. And I wanted to dive deeper, and I want to summarize what I've learned and show to you guys in this video. In a modern event-driven architecture, services often communicate asynchronously using the message broker. A typical flow may look something like this. A service receives the request from the user, updates something in the internal database, for example creates a new record or updates one, and then sends the event to external systems to notify that some change has occurred. These two events can happen in parallel, one after another. Doesn't matter, the service could be a microservice, could be a monolith, but this is a very common use case. Here is the problem. What happens if the database commit succeeds, but the subsequent call to the message broker fails for some reason? I can mark it as error here. It could obviously happen for multiple reasons. For example, maybe the network error, the message broker is down, maybe our application, the service itself has crashed somewhere in the middle. There could be countless number of reasons here. As a result of that you end up in an inconsistent state where your local database has the record while the rest of the system doesn't get the notification. It's a serious problem because both committing to the database and sending the event don't succeed or fail as a single atomic unit or in the database wording they don't happen in a single transaction. Let me show you a simple Go program that creates the order in the database and sends the event somewhere. I have a docker-compose setup with Postgres as our database and Redis which we can use for its PubSub functionality. Then I also have the simple orders table. just simply id, product and the quantity. And we have a first version of our orders as service, essentially not as service, just a main program that creates the record and then sends or publishes an event in the Redis. I won't dive much into the code but let's just review the structure of the program. We have a type for our order that mimics the SQL schema and then we have some event type. In the beginning of our main function we create the connections so one to Postgres, using the nice pgx driver and then redis connection. Then we create a simple kind of fake order, insert these into the database and after that we publish the event. Now let's run this code and see what it produces. I'm sure that everything is already running so you can just go on main.go and as we would assume the order was created in the database and the event was published to the redis. It all worked perfectly. So why are we here then? But imagine something happens in between of those two. Maybe the Redis is unavailable. Maybe actually something happens during the JSON marshalling. Or to simulate something we could just maybe panic here and try to run it again. Also, as you can see, the order was inserted into the database, but the event was actually not sent. And now it would be very hard for us to replay this behavior because we are not so sure which events have been sent and which events haven't. This is where the outbox pattern comes to the rescue. The core idea is very simple but also powerful. Instead of directly publishing the message, we save it first in a special dedicated outbox table. Very crucial that it happens in the same transaction or in the same unit as your data changes. So in our example we would create an orders record as well as we would write to the outbox table. It leverages the atomicity of database transactions, meaning that they either fail together or succeed together as a single unit. Obviously our diagram will look a little bit different now. Instead of a single orders table now we have two tables in our database orders and outbox, but also simply storing the outbox or the message in the outbox table is not enough. We need to send it somehow. That's why we usually need some sort of a message relay or message dispatcher, a service or a process that periodically checks the records in the outbox table, sends them to a message broker and after receiving the receipt that the message is actually sent updates the status for example so this message is either deleted or maybe not processed the next time. This process is definitely more robust for distributed systems. It guarantees at least one delivery. Why do we say at least one? That's because in very rare cases the message may be sent to a message broker but it could fail to write the status back. So that's why your consumers have to be designed to be idempotent so they can process the duplicate messages. That's also very important in distributed systems. For example, by sending the unique ID of the message and there are other methods to achieve this as well. I also have another version of our program that does something similar. So let's review that. And so the first difference we see is the new outbox table in our schema. The schema itself may vary depending on your project and needs, but a typical one may look something like that. It has a topic where the message has to be sent. The message itself could be JSONB, could be something else. And status could be pending, sent, new, could be enum, doesn't matter, and some maybe helpful columns. Nothing changed here in terms of order, schema or struct and the event itself. We described our outbox message that's similar to our database schema and the main entry point is also similar. We have the database connection, we have the redis connection. The big difference is that somewhere here we start the relay process. As you can see I started in a go routine. This could be a completely separate microservice, could be a separate process. But in Go it's quite easy to schedule Go routines, so it could be just run in there. And then after that we create orders. As you can see, we don't send the messages. The message will be sent from the relay. So let's go here. And in our case, the relay service is that simple. It's simply a loop with one second interval that tries to call a relay function. Let's go into this function. It tries to get, so this query is important to understand, it tries to get a single message from our outbox table in the state pending. So we don't want to be sending messages that are already sent, for example. And this is a very important part of our query. So full update skip locked. That's an internal Postgres functionality to lock the rows. just in case we run maybe multiple relay services or we try to run this query at the same time just to make sure that the row is locked and only one consumer can read it. And similar to the code before we publish the message to the Redis and very important we update the state to processed in our case. And now as you can see something can happen in the middle maybe our relay is not working at all the message won't be marked as processed. and the next time we can replay it. And just to confirm that everything works, let's run this program from the v2 subfolder. So as you can see the orders were created first and then they've been published to the message broker from our relay goroutine. So what we did here was polling and while polling is effective and a simple solution it may introduce some latency resource intensive. Databases like Postgres contain a write-ahead log. or w-a-l, which is append-only log of all changes that happen to your database. And we can tap into this log and stream the changes to our service and then act accordingly. Instead of your relay constantly asking the database is there anything new, the database tells our relay that there are changes in the database in the tables that we want to monitor. And this can provide lower latencies, though it can be implementation-wise complex. With logical replication our diagram will look a little bit different, though not much. As you may already understand, we don't need the outbox table, just orders. It would be like a push from write-ahead log. We still need some sort of a receiver, you can still call it relay, so no changes here, and then message a broker, right? So not much changes, but instead of outbox table, it will be prescribed updates from the write-ahead log. In Go, you can use this great library for interacting with Postgres logical replication protocol. The library is called pglogreppl and is from the creator of pgx Postgres driver, Jack C. To summarize, the outbox pattern is really great for distancing items. It ensures that the message is sent at least once and it does so not by sending the message directly to the message broker but by storing it first in the box table. We also have seen that there are other approaches and alternatives like write-ahead log and logical replication, so you can try them out. And that's it for today.

LINKS:

Source Code: https://github.com/plutov/packagemain/tree/main/outbox
Newsletter: https://packagemain.tech