Database application

[Sy]

I have a requirement for a large number of clients to be able to post interest in data stored and updated in a database, at the moment clients post individual requests to the server for data in various tables.  The server then performs the queries and sends back the results to the client.

This isn't very effecient and isn't very scalable.  The current scenario puts a lot of strain on the server and also on the network.  What I would like is an MQTT style solution where clients can subscribe to tables for updates and then receive notifications when new updates are written into the database published by the server in exactly the same way MQTT functions.

The current system uses node.js on the server.  I've done a quick search on google for MQTT and database and I'm not sure if what exits will allow me to go as far as I would like to, I'm not looking for an MQTT solution that allows clients to subscribe to messages in a database, what I want is a system that allows clients to issue a request using a query style syntax for data in any format of table.

Does anything like this exist?

[Sy]

I would like to see triggers that would enable MQTT style logic to be applied when a record is updated or inserted into a table.

[Laing, Michael]

Hi Sy,

We are experimenting around this using MQTT, AMQP, and a variety of databases.

The short answer is 'no' AFAIK...

The longer answers I can provide depend upon whether the queries you wish to process can be expressed using MQTT 'wildcard' syntax or not.

ml

--
To learn more about MQTT please visit http://mqtt.org
---
You received this message because you are subscribed to the Google Groups "MQTT" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mqtt+uns...@googlegroups.com.
To post to this group, send email to mq...@googlegroups.com.
Visit this group at http://groups.google.com/group/mqtt.
For more options, visit https://groups.google.com/d/optout.

[Sy]

Hi Michael,

What I'm looking for doesn't really fall into the existing way that MQTT works, in that subscribers susbscribe to a topic, where as I am looking for something closer to a query and when the conditions of that query are met, and continue to be met, results are published to subscribers.

[Laing, Michael]

Yes... hmm.

We do that with custom code now - and I am experimenting with ways to replace our custom code with more standard approaches, while also scaling from our current 1M clients to 20M.

The most promising strategy thus far has been to start with an open source MQTT broker and "decompose" it into a microservice implementation. Essentially this creates "hooks" and "injection points" in the broker for CONNECT, PUBLISH, SUBSCRIBE, etc. Importantly, a microservice can subscribe a client to a topic without the client requesting it.

A mechanism for accomplishing "special" client requests, without breaking the protocol, is to use the "$" hierarchy (other than $SYS) - brokers are supposed to "dampen" the propagation of such messages, but we can grab them via a hook.

We have clients send specials as (sort of) restful URLs, in your case a client might subscribe using a topic like:

'$QUERY/my-app/my-db?query=select * from foo where bar = baz'

This would be intercepted in the broker and forwarded to the QUERY microservice which would: 1) set up a trigger to publish new results if this query does not yet have such a trigger; 3) subscribe the user to the unique id for this query (together with other users if any); 3) execute the query and return current results to the user if the RETAIN flag was set.

There are lots of other little details involved of course, but this is the sort of thing we are experimenting with. 

Currently we are using mosquitto for MQTT. We have 0MQ/RabbitMQ forming the microservice and enterprise bus. We use Postgres, Neo4j, and/or Cassandra for data stores. We will be adding golang and nsq to the experimental mix over the next few months. Postgres triggers might be useful in your case.

It's quite a bit of work, but all the pieces are reasonably small once it is factored. Certainly not a ready made solution for your requirement, and, as I said, experimental for us at this point.

ml

[Simon Platten]

Thank you, I will give this some thought, its a big and common problem that must be an issue for lots of large data providers with many clients.

		Simon Platten about.me/SPlatten