13 ms·
Message DB: Event Store and Message Store for PostgreSQL
- kiwicopple 7y agoFor anyone just looking for the pub/sub functionality, I have been developing something that provides the functionality for PostgreSQL: https://github.com/supabase/realtime https://github.com/supabase/realtime It's an Elixir server (Phoenix) that allows you to listen to changes in your database via websockets. Basically the Phoenix server listens to PostgreSQL's replication functionality, converts the byte stream into JSON, and then broadcasts over websockets. The beauty of listening to the replication functionality is that you can make changes to your database from anywhere - your api, directly in the DB, via a console etc - and you will still receive the changes via websockets. I'd have to dig into their implementation to verify, but the main benefit over OP is scalability - i believe their implementation is tightly coupled to postgres, but with this you could scale the Elixir servers without any additional load on your DB It's still in very early stages, although I am using it in production at my company and will work on it full time starting Jan
- ralusek 7y agoIs this better than postgres's native NOTIFY?
- kiwicopple 7y agoI actually started with NOTIFY but the payload has a limit of 8000 bytes. Postgres throws an error for anything longer.
- manigandham 7y agoNOTIFY just sends a pub/sub message. You still need a trigger or something else to actually call it on updates, and it has payload size limits.
- hashhar 7y agoLook at embedded Debezium. https://debezium.io/docs/embedded/ https://debezium.io/docs/embedded/ Very similar to what you are doing but instead of Websockets you can decide where to send your data. The core-debezium sends it to Kafka.
- kiwicopple 7y agoI took a lot of inspiration from Debezium - at the time they required the wal2json plugin (not sure if that's still the case?). I didn't have an option to install the plugin and I mostly wanted a websocket-friendly implementation to replace Firebase. And if I'm 100% honest I just wanted to make something i thought was cool
- bobbydreamer 7y agoWhy replace Firebase ?
- kiwicopple 7y agoConsolidating our codebase - most of our infra was already built on top of Postgres and we were just using Firebase for realtime chat.
- gunnarmorling 7y agoBy now the Debezium Postgres connector also works with pgoutput (coming with Postgres by default as of PG 10 and later). Disclaimer: working on Debezium
- tyre 7y agoDo you know of anything like this for SQLite? I have a need for exactly this. Are there similar triggers?
- deleted 7y ago[deleted]
- kiwicopple 7y agoIt's a good question - I don't know the specifics of their implementations, but there are SQLite adapters for RxDB and PouchDB which both have a focus on realtime functionality. YMMV but I found them both pretty good for small implementations
- adamfeldman 7y agoReading the WAL is interesting. I've been other tools [1] accomplish similar ends using Postgres triggers [1]: https://github.com/hasura/graphql-engine/blob/master/event-triggers.md https://github.com/hasura/graphql-engine/blob/master/event-t...
- kiwicopple 7y agoHasura is an amazing tool. I tried with triggers + NOTIFY at the start, which was how Hasura does it (at least last I looked). I ran into the limitations of NOTIFY, which can only have a 8000 byte payload. The recommended resolution online was just to send a NOTIFY with a Primary key or identifier, then to query the database for the resource that changed. I didn't like this approach because it required more load on the database. One other advantage of using the WAL is that you don't have to set up then triggers everytime you set up a table. You just set and wal_level to "logical" on the database and then all your tables will be included in the stream replication
- frafra 7y agoSimilar, using triggers, Python, aiohttp + asyncpg: https://github.com/frafra/postgresql2websocket/ https://github.com/frafra/postgresql2websocket/
- fjp 7y agoAny reason for your choice of asyncpg vs aiopg? Curious as I have worked with both
- pythonaut_16 7y agoThis is pretty cool. Mnesia (one of the databases built into Erlang) has a similar ability to subscribe to its tables that I've used for realtime updates in some demo projects.
- meantheory 7y agothis looks very interesting
- techdragon 7y agoLooks interesting but needs a few more language libraries to demonstrate broader utility outside of just Ruby projects. Building on top of generic Postgres using a simple service is pretty neat but without the extra client libraries it’s hard to see a bright future. The npm module doesn’t look like it actually does anything besides unzip and run the dB setup code at the moment so it’s definitely early days for them. But with a few basic client libraries that can actually do the work of publishing and subscribing to messages, this will be a pretty neat tool. I’m going to have to watch how this develops.
- dmach 7y agoPublishing and subscribing are through SQL SELECT statements which can be called straight forward enough from most or all languages. Administering it does indeed require shell or ruby. Better still in my opinion if possible, implement your business logic in PL/pgSQL procs with these SELECTs within them and call them from whatever client/frontend you are using. I know it's not cool right now but there's big advantages and a disadvantage to putting business logic in the DB is said to be vendor lock but when it comes to Postgres, lock me up and throw away the key.
- adverbly 7y agoLooks cool. I can't find any performance or benchmarks though. Anyone care to guess/ballpark what kind of write speeds single nodes might be able to handle?
- GordonS 7y agoI got stuck looking for some perf figures too. I realise they are going to vary widely by hardware, batch size, message size etc, but it would be really useful to have a comparison against, for example, a single RabbitMQ instance.
- sbellware 7y agoA message store is really not a comparable pattern to a message queue. It's unlikely that the usages would be similar enough to engender a meaningful comparison. They're not as dissimilar as an apples-to-oranges comparison, but maybe an apples-to-applesauce comparison.
- sbellware 7y agoThere are too many "it depends" issues, especially the hardware, and not least the application architecture sitting on top of it. There's a script in the Message DB codebase that will do some fairly rudimentary read and write performance measures for your Postgres deployment: https://github.com/message-db/message-db/blob/master/database/benchmark.sh https://github.com/message-db/message-db/blob/master/databas... It's run right on the database, so no network I/O or client implementation in the loop - which is inevitably non-representative of "real world" scenarios.
- stevefan1999 7y agoIt looks very awesome! However, how does it compares with a tiered message queue like Redis as frontend and Postgres/MySQL as backend?
- PankajGhosh 7y agoOn behalf of a technology team, looking to incorporate a messaging platform to our landscape: I am curious to understand the motivation behind implementing a messaging system on top of a database technology. There are robust offerings both for small scale and large throughput systems. What are the benefits of this project over other implementations(RabbitMQ, Kafka, Celery, ActiveMQ, ZeroMQ, SNS/SQS)?
- paulryanrogers 7y agoBiggest wins I can imagine: Atomic changes (rollback can undo any unprocessed messages) Fewer moving parts, if you already need a DB Easily query state of queues and messages with famailar SQL
- ethangarofolo 7y agoThe technologies you listed are message brokers, while Message DB is a message store. The former transport messages, and the latter is a database specialized for storing message data and sourcing system state from those messages. Kafka can, for example, move a lot of events around, but it isn't suitable for event sourcing for 2 key reasons. The first is that one generally has a separate stream for each entity in an event-sourced system. Streams are sort of like topics in Kafka, but it would be quite challenging to, say, make a topic per user in Kafka. The second is Kafka's lack of optimistic concurrency support (see https://issues.apache.org/jira/browse/KAFKA-2260 https://issues.apache.org/jira/browse/KAFKA-2260). The decision to not support expected offsets makes perfect sense for what Kafka is, but it does make it unsuitable for event sourcing. Being built on top of Postgres, Message DB gives you access to event sourcing semantics using a familiar database technology. We use Message DB in our production systems, and I'd be happy to talk more about it if you have other questions. We've found it very reliable. As a disclaimer, I'm listed as a contributor at the Eventide Project, the project Message DB was extracted from, though I did not write any of the code behind Message DB.
- iamspoilt 7y agoI think the biggest difference is that it’s a Message Store as highlighted by the preceding comment. Some clarity by example: In financial systems, it’s extremely important to keep track of all the transactions between your microservices (if your architecture is based on that). You could potentially lose a message delivered to you via a broker if your service fails to write it to a persistent storage. If the producer of that message never stored that message or it was produced on wire and transmitted, there is no way to recover it anymore. A system designed around a Message Store can mitigate such problems. You can build that similar architecture with brokers as well but for each of your application, you will have to implement something analogous to a Message Store to handle idempotency and things like that.
- adamfeldman 7y agoVery interesting. This uses "Postgres server functions that can be used with any programming language or through the psql command line tool. Interaction with the underlying store through the Postgres server functions ensures correct writing and reading messages, streams, and categories." (https://github.com/message-db/message-db#api-overview https://github.com/message-db/message-db#api-overview) In Elixir-land, there is https://github.com/commanded/eventstore https://github.com/commanded/eventstore (which AFAICT is closely tied to the Commanded CQRS/RS framework https://github.com/commanded/commanded https://github.com/commanded/commanded)
- fiatjaf 7y ago"for"? I feel like this should be "in PostgreSQL". I thought this was something that was used internally by Postgres.
- SahAssar 7y ago"for" is pretty commonly used in this way. See for example https://koajs.com/ https://koajs.com/ and https://rocket.rs/ https://rocket.rs/ which both say "web framework for X"
- pavelevst 7y agoIf add retentions with automatic backup to bigquery or cloud storage we'll get infinity messaging with very reasonable resource usage
- dariubs 7y agoTimescaleDB is an alternative to this project
- SideburnsOfDoom 7y agoI have heard it said many times that it is a bad idea to implement message queues over a relational SQL database. e.g. "Databases suck for Messaging" https://www.rabbitmq.com/resources/RabbitMQ_Oxford_Geek_Night.pdf https://www.rabbitmq.com/resources/RabbitMQ_Oxford_Geek_Nigh... http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html http://mikehadlow.blogspot.com/2012/04/database-as-queue-ant... https://www.cloudamqp.com/blog/2015-11-23-why-is-a-database-not-the-right-tool-for-a-queue-based-system.html https://www.cloudamqp.com/blog/2015-11-23-why-is-a-database-... https://softwareengineering.stackexchange.com/questions/351449/message-queue-database-vs-dedicated-mq https://softwareengineering.stackexchange.com/questions/3514... I wonder if the designers of this system disagree, or if they did anything in particular to mitigate these issues?
- bradstewart 7y agoIt depends heavily on what exactly you need out of a message queue. E.g. if extremely (extremely) high throughput is required and you don't mind dropped or lost messages, SQL may be a bad choice. But I've had a lot of success using Postgres-backed messaging. Also your links are (mostly) from message queue companies who have a vested interest in your not using Postgres, or are from a time before LISTEN/NOTIFY and JSON(B) columns.
- mumblemumble 7y agoThere is definitely a performance ceiling if you use the RDMBS as your event store. I used to worry about that. Now that I've got a few years working at a very successful high frequency trading firm that did message queues in an RDBMS behind me, I don't worry about it so much anymore. They didn't use it for everything; there was also a blindingly efficient homegrown messaging system for the stuff that needed to be really fast. But that was for fairly well-defined use cases. (Latency and concurrency were the key limiting factors.) By default, everything went into the RDBMS-based system. It was inherently more robust, it replaced a whole quagmire of design questions you have to make when using other messaging systems with ACID guarantees, and it made it a lot quicker & easier to do post-mortem analysis of any surprising things that happened in production.
- ethangarofolo 7y ago
- kylecordes 7y agoIt is so interesting to see these problem solved again and again. Very important bit to understand, that many smart people (including perhaps some in this HN discussion) don't know yet: the semantics of an event store are meaningfully different from the semantics of a pub/sub system. If you pick up a pub/sub system and start trying to build an event store based system architecture with a set of CQRS-ES-style followers, you will find you are reinventing a bunch of things quite painfully. Here's what we did, a few years ago. In some ways it is similar to EventTide. https://blog.oasisdigital.com/category/cqrs/ https://blog.oasisdigital.com/category/cqrs/ https://github.com/OasisDigital/nges https://github.com/OasisDigital/nges We picked up JGroups, a truly spectacular piece of open source technology, and used it to automatically wake up event stream listeners in a distributed way. (At the time there was some limitation of the NOTIFY mechanism in PG that sent us this direction.). With very little code around automated wake-up, we achieved low latency (and therefore fast automated testing) of an arbitrary group of event stream followers.
- AtlasBarfed 7y agoJGroups seems superficially related to lots of the cassandra functions, especially gossip. Is it masterless, or does it handwave a lot of distribution problems like most java distributed projects by just using Zookeeper?
- anya_ 7y agoReach out to HYDRAHACKING01 @ HOTMAIL DOTCOM, for any type of hacking job to be done. Remove negative reports from your credit score, spy on your partner, deleted or encrypted messages and datas, to following up all social media activities anonymously, gaining full access to any mobile device, Hydra Hacking Inc are Prolific Professional Investigators, and they are one of the best groups (if not the best.) in the open market. i am a living witness