MQTT vs Direct Database Writes from IoT Devices: Why the Broker Wins
There is an integration pattern that will not die. A PLC or IoT device is given a database connection string, and every measurement cycle it runs INSERT INTO readings (device, ts, value) VALUES (...) straight into Postgres, MySQL, or SQL Server. No broker. No middleware. The device writes directly to the database. It works on the bench, it ships to one site, and a surprising number of engineers still reach for it because it feels like the simplest possible thing. It is also one of the most expensive anti-patterns in industrial IoT once you leave the bench.
This is not a knee-jerk "use a broker because brokers are modern" argument. Direct database writes have a real appeal, and for a narrow set of cases they are the right call. The goal here is to be precise about where the pattern breaks — connection scaling, schema coupling, offline buffering, security blast radius — and why an MQTT broker feeding a single ingest bridge is the architecture that survives contact with a real field fleet. If you are prototyping on a local broker on your Mac or wiring up a plant, the trade-off is the same.
The Appeal of the Direct Write
Start with why people do it, because the motivation is not irrational:
- One fewer component. No broker to install, operate, secure, or back up. The database already exists. The shortest path from device to stored data is a straight line.
- SQL is powerful. A direct connection gives the device transactions, JOINs, upserts, triggers, and stored procedures. You can do in one
INSERT ... ON CONFLICTwhat would take a subscriber plus custom logic over MQTT. - Existing skills. Every team knows SQL. Relational databases are operationally mature — backups, replication, monitoring are all solved problems. MQTT is newer and feels like extra surface area to learn.
- No new protocol on the device. Many PLCs and embedded platforms ship an ODBC client or a libpq binding. MQTT would mean adding a client library and a new network path.
None of that is wrong. The problem is that every one of those advantages is a local, short-term advantage that becomes a liability the moment the deployment grows past a single cabinet on a single LAN.
Where It Breaks
1. Schema Coupling
The moment the device runs INSERT INTO readings (device, ts, temp, hum), your firmware now embeds the database schema. Rename temp to temperature and every device in the field stops writing until it is reflashed. Add a pressure sensor and you coordinate a column migration with a firmware rollout across hundreds of devices, some of which are on cellular links and only check in once a day. The schema and the firmware are now the same artefact, and they have to evolve in lockstep.
The broker model inverts this. The device publishes an opaque payload to a topic like plant/line3/cellA/env. It does not know a database exists. A single ingest bridge subscribes, parses the payload, and maps it to whatever the schema is this week. Change the schema in one place, redeploy one bridge, leave every device alone.
2. Connection Scaling
This is the one that bites first. Relational databases are tuned for query throughput over a modest number of connections, not for holding thousands of idle client sockets.
- PostgreSQL defaults to
max_connections = 100. - MySQL defaults to
max_connections = 151. - SQL Server is dynamic but still bounded by memory and the licensing model.
If every device holds a persistent connection, a few hundred devices exhaust the pool and the database spends its time on connection management instead of querying. If every device reconnects per insert, you pay a TLS handshake and an auth round-trip on every single reading. A purpose-built MQTT broker, by contrast, routinely holds 10,000 to 100,000 concurrent connections, then fans those down to a small pool of database connections held by the ingest bridge. The database sees a handful of well-behaved clients, not a mob.
3. No Offline Buffering
Field networks are unreliable. Cellular drops, satellite has latency and gaps, sites lose power. When the link is down, a direct INSERT fails. What happens to that reading?
Without extra work, it is lost. To avoid loss, each device now has to ship its own retry queue, local persistent buffer, deduplication, and exponential backoff — which is to say, every device reimplements a little message broker. You have not avoided middleware; you have distributed it across the fleet, in firmware, where it is hardest to fix.
MQTT builds this in. With QoS 1 and a persistent session, the broker (or a local edge bridge) queues messages while the device or the link is down and delivers them when the connection returns. The device stays dumb. The buffering lives in one place you can actually operate.
4. Security Blast Radius
In the direct-write pattern, every device carries database credentials — a username and password, or a key. Now think about what happens when one device is compromised. The attacker has a working credential for your readings table, and if that role was over-granted (as field roles tend to be, because the device "needed" to write to three tables), they have far more. Rotating the credential means reflashing the fleet.
In the broker model the device holds only broker credentials. The single database credential lives in one place — the ingest bridge — where it is centrally rotated, narrowly scoped, and never distributed to a device sitting in a wet cabinet. A compromised device can publish junk to its own topic; it cannot DROP TABLE.
5. Write Amplification and Burst Handling
A device that inserts one row per reading at high frequency produces write amplification: every insert is its own transaction, its own WAL record, its own index update. On Postgres that means more WAL, more checkpoint pressure, more vacuuming. The database becomes a bottleneck for the one thing it should be good at.
A broker-plus-bridge smooths this. The bridge subscribes to the firehose, accumulates rows, and writes them in batches — a single multi-row INSERT of a thousand rows, or a COPY stream — turning thousands of tiny transactions into one big one. The database sees a manageable, burst-absorbed write pattern.
6. One-Way Only
A database write is telemetry, one direction. The moment you need to push a new setpoint, a config change, or a firmware-trigger command back to the device, the direct-write pattern has nothing to offer. You either have the device poll the database for its own configuration (burning connections and cycles), open a second inbound channel (which NAT will not allow), or bolt on a separate command mechanism. Now you have two integration paths to maintain.
MQTT is bidirectional on the same connection. The device subscribes to a command topic; the control system publishes a setpoint; a retained message means the latest setpoint is delivered the instant a reconnecting device comes back, with no extra logic.
7. Fan-Out and Observability
A single reading often needs to reach several consumers: the historian, the alarm engine, the live dashboard, the analytics pipeline. With direct writes, every consumer polls the database. With a broker, one publish fans out to N subscribers, each consuming the same stream independently. And when a direct INSERT fails silently, you discover the gap days later as a hole in the table. The broker logs every publish — topic, QoS, payload size — and with stored sessions you can replay the stream into a rebuilt database.
Quick Comparison
When Direct DB Write Is Actually Fine
- A single device on a LAN. One energy meter on the local network logging a daily summary into an internal table. No fleet, no scale problem.
- Low-frequency, batched data. A device that writes one row an hour, or once a day. Connection churn and write amplification are irrelevant at that rate.
- Internal tooling and prototypes. The "device" is actually a server-side script, or a bench rig for a proof of concept. Optimise for speed of iteration, not for operations.
- Already-transactional work. Where the device genuinely needs ACID semantics with other writes in the same transaction and a broker would only add indirection.
Notice the common thread: small scale, reliable network, no command path back to the device. The pattern is tolerable exactly where its failure modes never trigger.
The Right Architecture
The shape that survives looks like this:
- Devices publish to a broker over MQTT (QoS 1, persistent session). The payload can be JSON, protobuf, or Sparkplug B — see the Sparkplug B guide for a ready-made industrial payload and topic namespace.
- A single ingest bridge subscribes to the relevant topics, maps payloads to rows, deduplicates, and writes to the database in batches (multi-row insert or COPY). This is the only component that knows the schema and holds DB credentials.
- The database — Postgres, MySQL, SQL Server, or a time-series store — sits behind the bridge and sees a handful of well-behaved clients.
- For brownfield PLCs that cannot speak MQTT, a protocol gateway translates Modbus, S7, OPC UA, or EtherNet/IP into MQTT upstream, so even legacy controllers join the same pipeline.
The database is still there. What disappears is the per-device database connection, the embedded schema, the credential sprawl, and the silent data loss. The complexity moves from N devices into one bridge you can actually monitor, test, and redeploy.
To debug the pipeline before it ever touches the database, watch the broker directly. A tool like MacTools MQTT Explorer lets you connect to the broker, browse the topic tree, inspect real payloads, and confirm each device is publishing what the bridge will eventually consume — so you catch a misbehaving device at the broker, not as a gap in the table three days later.
Frequently Asked Questions
Is it ever OK for an IoT device to write directly to a database?
For a single device on a LAN writing low-frequency data (a daily summary, a batch record) to an internal database, direct writes are fine. The pattern breaks once you scale past a handful of devices, cross an unreliable network, or need to send commands back to the device. For anything resembling a field fleet, a broker plus a single ingest bridge is the safer architecture.
Why do direct database writes from IoT devices break at scale?
Each device holds a database connection, and databases cap concurrent connections low (Postgres defaults to 100, MySQL to 151), so a few hundred devices exhaust the pool. The device firmware also embeds the table schema, so any column rename or addition forces a coordinated firmware reflash across every device. There is also no offline buffering: a failed INSERT on a flaky cellular link is silently lost unless each device ships its own bespoke retry and dedup logic.
Does MQTT replace the database?
No. MQTT replaces the direct device-to-database connection. Devices publish to a broker, and a single ingest bridge subscribes, maps the payloads to rows, batches them, and writes to the database once, centrally. The database is still there. What disappears is the per-device database connection, the embedded schema, and the credential sprawl.
How many MQTT connections can a broker handle versus a database?
A purpose-built MQTT broker routinely handles 10,000 to 100,000 or more concurrent client connections, then fans those down to a small pool of database connections held by the ingest bridge. A relational database defaults to roughly 100 to 151 connections and is optimized for query throughput, not for holding tens of thousands of idle client sockets. Putting the broker in front is what lets the database do its actual job.
MQTT Explorer for macOS
MacTools MQTT Explorer — built-in broker, publish/subscribe, topic tree browser, message history. Inspect every payload at the broker before it reaches the database. $14.99 one-time.
Get MacTools MQTT ExplorerRelated: Full SCADA System
Need the historian, dashboards, and alarms on the far side of the bridge? Voltrus SCADA ingests MQTT alongside Modbus, OPC-UA, Siemens S7, BACnet, and DNP3 — single binary, lifetime license from $249.