9 ms·

When an SQL Database Makes a Great Pub/Sub

188 points by m110 7y ago

snazzycalynx 7y ago
firestore is really awesome TBH, queries are fast, composite indexes are very usefull, free tier is very generous, the documentation is very comprehensive and the platform is very appealing in general... the only problem i see is vendor locking. in terms of costs, i think even tho its not the cheapest out there it is still very competitive. i think if ones use case requires very large documents (close to the 1mb hard cap) one could make the case that it is cheaper than any other solution, but im too dumb to do the math.
Halluxfboy009 7y ago
Ever use google's firebase? While not SQL -- I've always felt `tis a nice solution to persistence+async...
rmrfchik 7y ago
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.
- Fire-Dragon-DoL 7y ago
  Can you expand a bit on this? From my understanding, autoincrement keys ca mn have gaps, but are always increasing. Sometimes a message might arrive "late", so you get a 3,then a 2. This problem cannot really be solved without giant locks that are not ideal. As far as I'm aware, all messaging systems are subject to this problem. Messages will never arrive, arrive out of order and I don't remember the third one right now (messages will arrive late?)
  - rmrfchik 7y ago
    Yes, you described the problem exactly as it is. The problem is not in arrive order to subscriber, the problem is "selecting next messages with offset > last_offset". And in this case you simply miss late messages.
    - Fire-Dragon-DoL 7y ago
      Oh ok. Well, I don't believe is permanently solvable, but there can be mitigation techniques where instead the software reads messages way back every now and then, to recover some messages. Some messages might still be too late and get missed, but most of them should get through, which is what every messaging service is currently doing
      - rmrfchik 7y ago
        It is solvable, but not with SQL. I mean, one have to have side logic to keep all transactions, messages and such and use as simple storage (i.e. do not use database transaction/locking mechanisms for main business logic).
        Fire-Dragon-DoL 7y ago
        Yeah my point is, it's not solvable by the transport mechanism. The application logic can indeed solve it, "eventually consistent systems" are a thing. My main goal was figuring out if this was impossible in SQL for some reason, but my understanding is just that the implementations are usually weak and do the "read-back" they need to, to recover late messages.
  - nicois 7y ago
    Databases such as postgresql will effectively issue a buffer of keys to each connection, meaning in some circumstances the sequences will not be monotonic with respect to time. Also that usually long running transactions will use the timestamp the transaction was opened, regardless of how many seconds have passed between then and when the statement is executed.
    - Fire-Dragon-DoL 7y ago
      Very interesting details, thanks. So the alternative is have inconsistency, or "giant locks". One is not performant, the other is inconsistent. Tough choice, interesting nevertheless
- abhishekjha 7y ago
  Off topic but does this not effect the Pagination functionality of databases as well? Using primary keys to skip first N pages and then limit the count of results seems to be the suggested way for getting items for the Nth page. If primary key is not monotonic then this is going to give jumbled results thus messing up results in the Nth page. EDIT: More context for the above process[1] [1]https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/ https://www.eversql.com/faster-pagination-in-mysql-why-order...
  - siscia 7y ago
    Hummm, not sure I follow but most likely no. What parent mean is that there may be holes in the sequence of primary keys. What you do with pagination is that you first sort the sequence, then thrown away the first N results, and finally select only the next M results. It will work just fine.
    - abhishekjha 7y ago
      I have linked the article for the above process.
    - felixyz 7y ago
      That is one way, not necessarily the most efficient. And having gaps in the id sequence can complicate pagination. Recommended reading: https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/ https://www.citusdata.com/blog/2016/03/30/five-ways-to-pagin...
    - quietbritishjim 7y ago
      > What parent mean is that there may be holes in the sequence of primary keys. Are you sure that's what they mean? It's not what they said. "Monotonic" means "strictly increasing" (or decreasing) e.g. 1, 2, 5, 7 is monotonic even though it has gaps. "Contiguous" means "without gaps".
- deleted 7y ago
  [deleted]
inopinatus 7y ago
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.
- notretarded 7y ago
  Okay...
- etaioinshrdlu 7y ago
  Maybe all this results in a really durable and foolproof system. I don't see how this is a bad thing. It looks like defense in depth against errors and corruption. Also, to my knowledge, the logs in a DB are not kept forever. Instead they are trimmed as soon as reasonable. It starts to smell a little bit like a https://en.wikipedia.org/wiki/Log-structured_merge-tree https://en.wikipedia.org/wiki/Log-structured_merge-tree
  - notduncansmith 7y ago
    It also smells like an example of the https://en.wikipedia.org/wiki/Inner-platform_effect https://en.wikipedia.org/wiki/Inner-platform_effect
- dtech 7y ago
  > It’s curious that over those projections, we then build event stores for CQRS/ES systems with their own projections mediated by application code. That's really logical. From the view of the application there is no transaction log, only a table. It's an implementation detail of the database. The application wants similar guarantees a log can provide, so they build their own.
rmetzler 7y ago
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?
- blowski 7y ago
  Like many design patterns when implemented badly, queuing will usually result in a lot of problems. But if your team is competent, you’re using an off-the-shelf library, and you don’t have crazy demands, then re-using infrastructure can be a good idea as it’s fewer things to manage.
  - tonetheman 7y ago
    Fewer things to manage is the key!
- rumanator 7y ago
  What's the rationale to teach databases as message queues when it requires special querying and updates when there are so many message queues services already available, easy to use, and standard compliant?
  - to11mtm 7y ago
    > What's the rationale to teach databases as message queues when it requires special querying and updates when there are so many message queues services already available, easy to use, and standard compliant? If you already need a database for something else, using the DB as a Queue means you don't need to list {mqFlavorOfChoice} as a requirement for new hires. You also don't have to manage that extra infrastructure. Of course, you are putting additional load on the DB. Mind you, I'm speaking of a pub-sub type queue and not a FIFO here. You can do FIFO queues in DB as well of course, it's just not as compelling of a story nowadays. Also way easier to look at and 'poke' a Database queue if you need to. The queries are also not really difficult to write for a general purpose use case.
zzzeek 7y ago
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. http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html http://mikehadlow.blogspot.com/2012/04/database-as-queue-ant... has some good points.
- bradstewart 7y ago
  A lot has changed in the 7 years since that was written. Polling isn't a huge issue to begin with, and is mitigated with LISTEN/NOTIFY (on certain DBs). Inserts with indexes are not a performance problem at the scale of most applications. A separate messaging service won't prevent you from building a "hugely coupled monster". Personally, I almost always start with the database as a queue. The operational overhead of running, updating, and monitoring another entire service is non trivial. If the messaging rate exceeds the database's capabilities in the future, I'll migrate then.
  - tartoran 7y ago
    If you need just one queue yes. If you have lots of queues it’s worth investing in a queue service of some sort and there are many of them out there which is a good thing but could turn into a bad thing quickly. In the past I worked at a place that had 3 different queueing services implemented by different developers and it became a pain to manage them or to even know what was on the queues.
zinxq 7y ago
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.
linuxhansl 7y ago
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.
marco_craveiro 7y ago
MessageDB was doing the rounds in reddit the other day [1]. Looks interesting for simple use cases... [1] https://www.reddit.com/r/PostgreSQL/comments/ebu6nh/message_db_event_store_and_message_store_for/ https://www.reddit.com/r/PostgreSQL/comments/ebu6nh/message_...
120bits 7y ago
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.
- porsager 7y ago
  How about simply having an after insert trigger on an alerts table that calls notify, and then you listen for that in node? It's a simple setup with less moving parts and could probably get you a long way...
  - 120bits 7y ago
    Thanks, this will probably work for me. However, if the inserts are higher, I don't want to get notified that frequently. How would I add a periodic alerts to this? Thank you!
    - porsager 7y ago
      You could use a column/table to track notifications. Eg. have an alert_sent_at column on the alerts table. Then when the node service receives the notify on an insert you can defer based on any logic you need, and send notifications when needed by a query that fetches alerts with alert_sent_at = null.
      - 120bits 7y ago
        Thank you! Thank you so much :)
        porsager 7y ago
        You're welcome. To ensure you're not sending alerts twice either select and update in a transaction or use a CTE like: with alert as ( select alert_id from alerts where alert_sent_at is null ) update alerts set alert_sent_at = now() from alert returning *
nickjj 7y ago
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]: https://github.com/sorentwo/oban https://github.com/sorentwo/oban
slowhand09 7y ago
Oracle has a very advanced and flexible system for this. It is called Advanced Queueing.
kiwicopple 7y ago
For anyone just looking for ‘plug and play’ web socket 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. 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.
- starik36 7y ago
  One way to get around the 8k NOTIFY limit is to only use the capability to notify only. It would them be incumbent on the client to go fetch the data from a table somewhere. I ran into a similar limitation with SQL Server 2005 years ago and used this approach with great success.
TheCowboy 7y ago
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. https://deepstream.io/tutorials/concepts/what-is-deepstream/ https://deepstream.io/tutorials/concepts/what-is-deepstream/ https://github.com/deepstreamIO/deepstream.io https://github.com/deepstreamIO/deepstream.io (I'm not affiliated with the project.)
- _frkl 7y ago
  Thanks, this does really look interesting. I'd like to find something generic, lightweight to replace Kafka or Pulsar. Not sure this could be it, but it looks like it'd be worth having a look at...
gunnarmorling 7y ago
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] https://debezium.io/ https://debezium.io/ [2] https://debezium.io/documentation/reference/1.0/configuration/outbox-event-router.html https://debezium.io/documentation/reference/1.0/configuratio...
vlasky 7y ago
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. https://atmospherejs.com/vlasky/mysql https://atmospherejs.com/vlasky/mysql

188 points by m110 7y ago

firestore is really awesome TBH, queries are fast, composite indexes are very usefull, free tier is very generous, the documentation is very comprehensive and the platform is very appealing in general... the only problem i see is vendor locking. in terms of costs, i think even tho its not the cheapest out there it is still very competitive. i think if ones use case requires very large documents (close to the 1mb hard cap) one could make the case that it is cheaper than any other solution, but im too dumb to do the math.

Halluxfboy009 7y ago

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

rmrfchik 7y ago

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.

Fire-Dragon-DoL 7y ago
Can you expand a bit on this? From my understanding, autoincrement keys ca mn have gaps, but are always increasing. Sometimes a message might arrive "late", so you get a 3,then a 2. This problem cannot really be solved without giant locks that are not ideal. As far as I'm aware, all messaging systems are subject to this problem. Messages will never arrive, arrive out of order and I don't remember the third one right now (messages will arrive late?)
- rmrfchik 7y ago
  Yes, you described the problem exactly as it is. The problem is not in arrive order to subscriber, the problem is "selecting next messages with offset > last_offset". And in this case you simply miss late messages.
  - Fire-Dragon-DoL 7y ago
    Oh ok. Well, I don't believe is permanently solvable, but there can be mitigation techniques where instead the software reads messages way back every now and then, to recover some messages. Some messages might still be too late and get missed, but most of them should get through, which is what every messaging service is currently doing
    - rmrfchik 7y ago
      It is solvable, but not with SQL. I mean, one have to have side logic to keep all transactions, messages and such and use as simple storage (i.e. do not use database transaction/locking mechanisms for main business logic).
      - Fire-Dragon-DoL 7y ago
        Yeah my point is, it's not solvable by the transport mechanism. The application logic can indeed solve it, "eventually consistent systems" are a thing. My main goal was figuring out if this was impossible in SQL for some reason, but my understanding is just that the implementations are usually weak and do the "read-back" they need to, to recover late messages.
- nicois 7y ago
  Databases such as postgresql will effectively issue a buffer of keys to each connection, meaning in some circumstances the sequences will not be monotonic with respect to time. Also that usually long running transactions will use the timestamp the transaction was opened, regardless of how many seconds have passed between then and when the statement is executed.
  - Fire-Dragon-DoL 7y ago
    Very interesting details, thanks. So the alternative is have inconsistency, or "giant locks". One is not performant, the other is inconsistent. Tough choice, interesting nevertheless
abhishekjha 7y ago
Off topic but does this not effect the Pagination functionality of databases as well? Using primary keys to skip first N pages and then limit the count of results seems to be the suggested way for getting items for the Nth page. If primary key is not monotonic then this is going to give jumbled results thus messing up results in the Nth page. EDIT: More context for the above process[1] [1]https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/ https://www.eversql.com/faster-pagination-in-mysql-why-order...
- siscia 7y ago
  Hummm, not sure I follow but most likely no. What parent mean is that there may be holes in the sequence of primary keys. What you do with pagination is that you first sort the sequence, then thrown away the first N results, and finally select only the next M results. It will work just fine.
  - abhishekjha 7y ago
    I have linked the article for the above process.
  - felixyz 7y ago
    That is one way, not necessarily the most efficient. And having gaps in the id sequence can complicate pagination. Recommended reading: https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/ https://www.citusdata.com/blog/2016/03/30/five-ways-to-pagin...
  - quietbritishjim 7y ago
    > What parent mean is that there may be holes in the sequence of primary keys. Are you sure that's what they mean? It's not what they said. "Monotonic" means "strictly increasing" (or decreasing) e.g. 1, 2, 5, 7 is monotonic even though it has gaps. "Contiguous" means "without gaps".
deleted 7y ago
[deleted]

inopinatus 7y ago

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.

notretarded 7y ago
Okay...
etaioinshrdlu 7y ago
Maybe all this results in a really durable and foolproof system. I don't see how this is a bad thing. It looks like defense in depth against errors and corruption. Also, to my knowledge, the logs in a DB are not kept forever. Instead they are trimmed as soon as reasonable. It starts to smell a little bit like a https://en.wikipedia.org/wiki/Log-structured_merge-tree https://en.wikipedia.org/wiki/Log-structured_merge-tree
- notduncansmith 7y ago
  It also smells like an example of the https://en.wikipedia.org/wiki/Inner-platform_effect https://en.wikipedia.org/wiki/Inner-platform_effect
dtech 7y ago
> It’s curious that over those projections, we then build event stores for CQRS/ES systems with their own projections mediated by application code. That's really logical. From the view of the application there is no transaction log, only a table. It's an implementation detail of the database. The application wants similar guarantees a log can provide, so they build their own.

rmetzler 7y ago

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?

blowski 7y ago
Like many design patterns when implemented badly, queuing will usually result in a lot of problems. But if your team is competent, you’re using an off-the-shelf library, and you don’t have crazy demands, then re-using infrastructure can be a good idea as it’s fewer things to manage.
- tonetheman 7y ago
  Fewer things to manage is the key!
rumanator 7y ago
What's the rationale to teach databases as message queues when it requires special querying and updates when there are so many message queues services already available, easy to use, and standard compliant?
- to11mtm 7y ago
  > What's the rationale to teach databases as message queues when it requires special querying and updates when there are so many message queues services already available, easy to use, and standard compliant? If you already need a database for something else, using the DB as a Queue means you don't need to list {mqFlavorOfChoice} as a requirement for new hires. You also don't have to manage that extra infrastructure. Of course, you are putting additional load on the DB. Mind you, I'm speaking of a pub-sub type queue and not a FIFO here. You can do FIFO queues in DB as well of course, it's just not as compelling of a story nowadays. Also way easier to look at and 'poke' a Database queue if you need to. The queries are also not really difficult to write for a general purpose use case.

zzzeek 7y ago

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. http://mikehadlow.blogspot.com/2012/04/database-as-queue-anti-pattern.html http://mikehadlow.blogspot.com/2012/04/database-as-queue-ant... has some good points.

bradstewart 7y ago
A lot has changed in the 7 years since that was written. Polling isn't a huge issue to begin with, and is mitigated with LISTEN/NOTIFY (on certain DBs). Inserts with indexes are not a performance problem at the scale of most applications. A separate messaging service won't prevent you from building a "hugely coupled monster". Personally, I almost always start with the database as a queue. The operational overhead of running, updating, and monitoring another entire service is non trivial. If the messaging rate exceeds the database's capabilities in the future, I'll migrate then.
- tartoran 7y ago
  If you need just one queue yes. If you have lots of queues it’s worth investing in a queue service of some sort and there are many of them out there which is a good thing but could turn into a bad thing quickly. In the past I worked at a place that had 3 different queueing services implemented by different developers and it became a pain to manage them or to even know what was on the queues.

zinxq 7y ago

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.

linuxhansl 7y ago

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.

marco_craveiro 7y ago

MessageDB was doing the rounds in reddit the other day [1]. Looks interesting for simple use cases... [1] https://www.reddit.com/r/PostgreSQL/comments/ebu6nh/message_db_event_store_and_message_store_for/ https://www.reddit.com/r/PostgreSQL/comments/ebu6nh/message_...

120bits 7y ago

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.

porsager 7y ago
How about simply having an after insert trigger on an alerts table that calls notify, and then you listen for that in node? It's a simple setup with less moving parts and could probably get you a long way...
- 120bits 7y ago
  Thanks, this will probably work for me. However, if the inserts are higher, I don't want to get notified that frequently. How would I add a periodic alerts to this? Thank you!
  - porsager 7y ago
    You could use a column/table to track notifications. Eg. have an alert_sent_at column on the alerts table. Then when the node service receives the notify on an insert you can defer based on any logic you need, and send notifications when needed by a query that fetches alerts with alert_sent_at = null.
    - 120bits 7y ago
      Thank you! Thank you so much :)
      - porsager 7y ago
        You're welcome. To ensure you're not sending alerts twice either select and update in a transaction or use a CTE like: with alert as ( select alert_id from alerts where alert_sent_at is null ) update alerts set alert_sent_at = now() from alert returning *

nickjj 7y ago

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]: https://github.com/sorentwo/oban https://github.com/sorentwo/oban

slowhand09 7y ago

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

kiwicopple 7y ago

For anyone just looking for ‘plug and play’ web socket 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. 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.

starik36 7y ago
One way to get around the 8k NOTIFY limit is to only use the capability to notify only. It would them be incumbent on the client to go fetch the data from a table somewhere. I ran into a similar limitation with SQL Server 2005 years ago and used this approach with great success.

TheCowboy 7y ago

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. https://deepstream.io/tutorials/concepts/what-is-deepstream/ https://deepstream.io/tutorials/concepts/what-is-deepstream/ https://github.com/deepstreamIO/deepstream.io https://github.com/deepstreamIO/deepstream.io (I'm not affiliated with the project.)

_frkl 7y ago
Thanks, this does really look interesting. I'd like to find something generic, lightweight to replace Kafka or Pulsar. Not sure this could be it, but it looks like it'd be worth having a look at...

gunnarmorling 7y ago

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] https://debezium.io/ https://debezium.io/ [2] https://debezium.io/documentation/reference/1.0/configuration/outbox-event-router.html https://debezium.io/documentation/reference/1.0/configuratio...

vlasky 7y ago

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. https://atmospherejs.com/vlasky/mysql https://atmospherejs.com/vlasky/mysql