Integrate HiveMQ with Tiger Cloud

HiveMQ is an enterprise MQTT broker used to move data between IoT devices, applications, and backend systems in real time.

This page shows you how to connect HiveMQ to your Tiger Cloud service using the HiveMQ Enterprise Extension for PostgreSQL to stream MQTT messages into TimescaleDB.

Prerequisites for this tutorial

To follow the procedure on this page, you'll need:

• A target Tiger Cloud service (this procedure also works for self-hosted TimescaleDB)
• A HiveMQ Enterprise broker with file system access on the host
• The HiveMQ Enterprise Extension for PostgreSQL downloaded

Create a target table in Tiger Console

1. Connect to your service

   Use the Tiger Console, psql, or any other SQL editor to connect to your Tiger Cloud service.

2. Create a hypertable for MQTT messages

   Create a hypertable that matches the columns you want HiveMQ to populate from each MQTT message. Partition on ts so the table scales as message volume grows:

   CREATE TABLE mqtt_messages (
      ts          TIMESTAMPTZ NOT NULL,
      topic       TEXT        NOT NULL,
      client_id   TEXT,
      qos         SMALLINT,
      payload     JSONB
   ) WITH (
      tsdb.hypertable = true,
      tsdb.partition_column = 'ts',
      tsdb.chunk_interval = '1 day',
      tsdb.columnstore = true,
      tsdb.segmentby = 'topic',
      tsdb.orderby = 'ts DESC'
   );

   The columns on this table must match the fields you map in the HiveMQ extension routes in the next section. Use JSONB for payload if your devices publish JSON, or BYTEA for raw binary payloads.

   Aim for 1–20 million rows per chunk. That’s roughly 1 day for 10,000 messages/min, or 1 hour for 300,000 messages/min. Adjust tsdb.chunk_interval to match your broker’s throughput.

   Setting tsdb.columnstore = true automatically adds a columnstore policy that compresses chunks once they reach tsdb.chunk_interval (1 day above). To use a different interval, remove the auto-created policy and call add_columnstore_policy with your preferred after value, for example INTERVAL '7 days'.

Install and configure the HiveMQ Enterprise Extension for PostgreSQL

1. Install the extension

   Unpack the extension into the extensions directory of your HiveMQ installation:

   unzip hivemq-postgresql-extension-<version>.zip -d <HIVEMQ_HOME>/extensions/

   The extension folder is disabled by default. Remove the DISABLED marker file once you have finished configuring it in the next steps:

   rm <HIVEMQ_HOME>/extensions/hivemq-postgresql-extension/DISABLED

2. Configure the database connection

   Edit <HIVEMQ_HOME>/extensions/hivemq-postgresql-extension/conf/config.xml and add a PostgreSQL connection that points to your Tiger Cloud service. Use the username, password, host, and port from your service connection details:

   <hivemq-postgresql-extension>
       <postgresqls>
           <postgresql>
               <id>tiger-cloud</id>
               <host>YOUR_SERVICE_HOST.tsdb.cloud.timescale.com</host>
               <port>5432</port>
               <database>tsdb</database>
               <username>tsdbadmin</username>
               <password>YOUR_PASSWORD</password>
               <tls>
                   <enabled>true</enabled>
               </tls>
           </postgresql>
       </postgresqls>
   </hivemq-postgresql-extension>

   Tiger Cloud requires TLS. Make sure the <tls><enabled>true</enabled></tls> block is present, otherwise the extension will not be able to connect.

3. Define a route from MQTT topics to your table

   Still in config.xml, add an MQTT-to-PostgreSQL route that maps incoming messages to the columns on mqtt_messages:

   <mqtt-to-postgresql-routes>
       <mqtt-to-postgresql-route>
           <id>mqtt-to-tiger-cloud</id>
           <postgresql-id>tiger-cloud</postgresql-id>
           <mqtt-topic-filters>
               <mqtt-topic-filter>#</mqtt-topic-filter>
           </mqtt-topic-filters>
           <processor>
               <insert-statement>
                   <table>mqtt_messages</table>
                   <columns>
                       <column>
                           <name>ts</name>
                           <value>${mqtt-message.timestamp}</value>
                       </column>
                       <column>
                           <name>topic</name>
                           <value>${mqtt-message.topic}</value>
                       </column>
                       <column>
                           <name>client_id</name>
                           <value>${mqtt-message.client-id}</value>
                       </column>
                       <column>
                           <name>qos</name>
                           <value>${mqtt-message.qos}</value>
                       </column>
                       <column>
                           <name>payload</name>
                           <value>${mqtt-message.payload-utf8}</value>
                       </column>
                   </columns>
               </insert-statement>
           </processor>
       </mqtt-to-postgresql-route>
   </mqtt-to-postgresql-routes>

   Narrow the <mqtt-topic-filter> to the topics you want to persist. Use # only if you want to capture every message on the broker. See the HiveMQ extension reference for the full list of available placeholders.

4. Start HiveMQ and verify the extension is loaded

   Start (or restart) HiveMQ and check the broker log for a line confirming the extension is started and the route is active:

   tail -f <HIVEMQ_HOME>/log/hivemq.log

   Publish a test message to a topic that matches your filter, then query the table to confirm rows are arriving:

   SELECT * FROM mqtt_messages ORDER BY ts DESC LIMIT 10;

With messages flowing into mqtt_messages, add a retention policy to cap how long historical data is kept. The step below can be performed in the Tiger Console or any other SQL editor.

Optimize TimescaleDB for HiveMQ workloads

1. Optional: add a retention policy

   SELECT add_retention_policy('mqtt_messages', INTERVAL '1 year');

You have successfully integrated HiveMQ with Tiger Cloud.