When an SQL Database Makes a Great Pub/Sub

188 点作者 m110超过 5 年前

15 条评论

inopinatus超过 5 年前

There’s a perspective that the transaction log of a typical RDBMS is the canonical form and the rows & tables merely the event-sourced projection. After all, if you replay the former, you should always get exactly the same in the latter.It’s curious that over those projections, we then build event stores for CQRS/ES systems with their own projections mediated by application code.Let’s also mention the journaled filesystem on which the database logs reside. And the log structure that your SSD is using internally to balance writes.It’s been a long time since we wrote an application event stream linearly straight to media, and although I appreciate the separate concerns that each of these layers addresses, I’d probably struggle to justify them from first principles to even a slightly more Socratic version of myself.

评论 #21844491 未加载

评论 #21843479 未加载

评论 #21845125 未加载

评论 #21844769 未加载

评论 #21845215 未加载

评论 #21843607 未加载

评论 #21843704 未加载

评论 #21843204 未加载

rmetzler超过 5 年前

Really, I wouldn’t teach junior developers that it’s ok to use a database table when a queue is needed. Sure, you can get away with this and there are cases when it’s all you need. But I’ve been one of those juniors who forgot to limit the query, who didn’t have enough indices, who tried to order all records by date and had full table scans everywhere, who implemented the worker with a cron job and didn’t synchronize this with a lock.It might work, but it’s not the general case and you might spend more time to debug your table then to write the code to use a real queue.And I’ve also seen people build their own queueing engine for a few hundred tasks per day. Why don’t they just choose one of the very good open source solutions?

评论 #21843978 未加载

评论 #21844273 未加载

评论 #21846376 未加载

评论 #21847329 未加载

评论 #21845603 未加载

zzzeek超过 5 年前

The "database as message queue" pattern is quite common and often considered to be an antipattern, which I tend to agree with but I don't have that strong of a position on it myself. I've certainly used this pattern for expediency, but that was before we had all the messaging solutions we do today. <a href="http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html" rel="nofollow">http://mikehadlow.blogspot.com/2012/04/database-as-queue-ant...</a> has some good points.

评论 #21844370 未加载

zinxq超过 5 年前

I've always considered message-queues as a close cousin (if not sibling) of databases. Arguably performing the same function with different foci. Pub/sub focusing on the "oplog". DBMS focusing on "state".(Blockchain another "oplog" that ends up caring a lot about state eventually).It's no wonder you can use them interchangeably in many common base cases.

rmrfchik超过 5 年前

Seems like they fall into the same pit as many does: using primary keys with autoincrement as offset. This leads to skipping messages because there is no guarantees that primary keys will be available in monotonic order. Because, you know, transactions.

评论 #21842918 未加载

评论 #21842928 未加载

评论 #21843299 未加载

linuxhansl超过 5 年前

Perhaps it's not so much about pub/sub, but about store-and-forward.When the "forward" part of "store-and-forward" is most important then Kafka is a fine solution.However, when the "store" part - for example you want to be able to stream historical data again, or interact with the data in different ways - is most important I have recommended HBase (+ Phoenix) as a better solution in the past.

kiwicopple超过 5 年前

For anyone just looking for ‘plug and play’ web socket pub/sub functionality, I have been developing something that provides the functionality for PostgreSQL: <a href="https://github.com/supabase/realtime" rel="nofollow">https://github.com/supabase/realtime</a>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.The article suggests Postgres’ native LISTEN/NOTIFY functionality. I tried that originally and found that NOTIFY payloads have a limit of 8000 bytes, as well a few other inconveniences.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.

评论 #21847602 未加载

marco_craveiro超过 5 年前

MessageDB was doing the rounds in reddit the other day [1]. Looks interesting for simple use cases...[1] <a href="https://www.reddit.com/r/PostgreSQL/comments/ebu6nh/message_db_event_store_and_message_store_for/" rel="nofollow">https://www.reddit.com/r/PostgreSQL/comments/ebu6nh/message_...</a>

gunnarmorling超过 5 年前

That's basically the same pattern as the "outbox pattern", e.g. listed in Chris Richardson's pattern of microservices patterns.An alternative implementation is provided by Debezium [1], a general solution for change data capture for MySQL, Postgres, MongoDB, SQL Server and others, based on top of Apache Kafka (but can also be used with Pulsar and others).There's support for outbox coming as part of Debezium out of the box [2].Disclaimer: I'm working on Debezium.[1] <a href="https://debezium.io/" rel="nofollow">https://debezium.io/</a> [2] <a href="https://debezium.io/documentation/reference/1.0/configuration/outbox-event-router.html" rel="nofollow">https://debezium.io/documentation/reference/1.0/configuratio...</a>

nickjj超过 5 年前

If anyone is using Elixir, Oban[0] is a job processor that uses PostgreSQL for its back-end and state management.It's incredibly well written and I am using it in a project.[0]: <a href="https://github.com/sorentwo/oban" rel="nofollow">https://github.com/sorentwo/oban</a>

TheCowboy超过 5 年前

One fun open source software I've played with, that I don't think many have heard of, is Deepstream.io. It attempts to be a batteries included real-time web server that works with websockets, and can function as pub/sub server and client. It has a connector for using PostgreSQL as the database. The frontend JavaScript library is really easy to get working.<a href="https://deepstream.io/tutorials/concepts/what-is-deepstream/" rel="nofollow">https://deepstream.io/tutorials/concepts/what-is-deepstream/</a><a href="https://github.com/deepstreamIO/deepstream.io" rel="nofollow">https://github.com/deepstreamIO/deepstream.io</a>(I'm not affiliated with the project.)

评论 #21847280 未加载

120bits超过 5 年前

This could slightly out of context.I'm working on a module that send notifications to a user when an alert is generated. I have PostGreSQL as the database and NodeJS is the handler and for connection pooling. Are there any good pub/sub tools that I can use. Thanks in advance.

评论 #21845510 未加载

vlasky超过 5 年前

Meteor provides pub/sub with a MySQL backend using the atmosphere package vlasky:mysql.It works by following the MySQL binary log and triggering a reactive query based on event conditions specified by the programmer, e.g. a change in a field.<a href="https://atmospherejs.com/vlasky/mysql" rel="nofollow">https://atmospherejs.com/vlasky/mysql</a>

slowhand09超过 5 年前

Oracle has a very advanced and flexible system for this. It is called Advanced Queueing.

Halluxfboy009超过 5 年前

Ever use google's firebase? While not SQL -- I've always felt `tis a nice solution to persistence+async...