Is it really so difficult for engineers to create a task to process a Kafka topic? It takes one day to write a program to consume from a topic of events and push to an API like Amplitude, and you have total flexibility in how you push to those integrations. Why would you use postgres for an event professing system? This seems like an inefficient architecture.
Great question. Complication arises because of failures. One or more destinations may be down for any length of time, individual payloads may be bad etc. To handle all these you need to retry with timeouts while not blocking other events to other destinations. Also, not all events may be going to all destinations. We build our own streaming abstraction on top of Postgres. Think layman's leveled compaction. We will wr…
I took a brief look at the code, and while the append-only dataset strategy is sound, it looks like your scenario only has a single reader and a single writer?
In my experience, it's not entirely trivial when you have:
1. Multiple readers who each needs to be able to follow the log from different positions in real time.
2. Multiple writers receiving events that need to be written to the end of the log.
3. Real-time requirements.
From what I can tell — I could be wrong here — your system doesn't need to poll the table constantly, because you also save the log to RAM, so whenever you receive an event, you can optimistically handle it in memory and merely issue status updates. If anything goes wrong, a reader can replay from the database.
But that doesn't work with multiple writer nodes where each node receives just a part of the whole stream. The only way for this to work would be dedicate a writer node to each stream so that it goes through the same RAM queue. So then you need a whole system that uses either Postgres or some consensus system like Etcd to route messages to a single writer, and you need to be able to recover when a writer has been unavailable.
Edit: I see you wrote that "we assign same user to same writer", so you're doing something like that.