August 11, 2026 · Comparison

MQTT vs HTTP vs Direct Database Writes for IoT

By Dzulfikar Ats Tsauri, founder of Voltrus / JoyoDigitama. He builds the protocol tools covered in these posts. Updated October 28, 2026.

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. 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.

A device really has three ways to get data to a backend: write to the database directly, POST to an HTTP API, or publish to an MQTT broker. This post compares all three on connection scaling, schema coupling, offline buffering, security, battery, NAT and delivery guarantees. Direct writes and HTTP both have honest use cases, and I say where. For anything with a fleet, a field network and a need to talk back to the devices, the shape that holds up is an MQTT broker feeding a single ingest bridge, with HTTP kept for config, files and enrollment. 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 CONFLICT what 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 and the control system publishes a setpoint. A retained message means the latest setpoint is delivered the instant a reconnecting device subscribes, 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.

MQTT vs HTTP: The Middle Option

Many teams avoid direct SQL by having the device POST JSON to an HTTP API that writes to the database. That fixes the worst problems above: the API owns the schema and the database credential, so a compromised device cannot touch the database directly. What it does not fix is that HTTP was designed for clients asking servers for things, and a device pushing telemetry and receiving commands is the opposite shape.

Connection model

HTTP is request/response and client-initiated. Each request carries a method, path and headers, and the server cannot send anything unprompted. To learn that a command exists, the device must poll. MQTT opens one TCP connection to a broker (port 1883, or 8883 with TLS), sends CONNECT, and keeps the session open for hours or days. A 2-byte PINGREQ every 30 to 60 seconds keeps it alive, and the broker pushes messages the instant they are published. See MQTT keep alive for how that interval interacts with NAT timeouts.

Overhead per reading

A minimal HTTP/1.1 POST of a 13-byte JSON body is roughly 110 to 130 bytes of request headers before the body, plus a response with its own headers. An MQTT PUBLISH has a 2-byte fixed header, a 2-byte topic length, the topic and the payload: publishing 42 to plant/line3/cellA/temp is about 25 bytes. At 10,000 devices reporting once a minute, that is about 1.4 MB per minute of HTTP request headers against roughly 70 to 250 KB per minute of MQTT framing. HTTP/2 header compression and keep-alive narrow the gap, but most constrained devices and cellular modems still speak HTTP/1.1, and TLS session setup per connection is a cost you pay on every cold request.

Latency, power and NAT

• Latency: over MQTT, latency to the device is one network trip to the broker and one back out. Over HTTP polling it averages half the poll interval, so a 5 second poll means 2.5 seconds on average and 5 at worst. Polling at 50 ms to match MQTT would saturate the link and the server. Long polling and Server-Sent Events narrow the gap but are workarounds.
• Battery: the radio dominates power on a constrained device. MQTT holds one connection and lets the radio sleep between keepalives. A device polling HTTP every few seconds for commands never sleeps and spends most of its energy asking "anything new?". The exact saving depends on the radio (NB-IoT, LTE-M, Wi-Fi, LoRaWAN), but the direction is always the same.
• NAT: devices sit behind home routers, carrier-grade NAT and industrial cell modems that allow outbound and block inbound. An MQTT client makes one outbound connection and receives pushes over it, so no inbound port or public IP is needed. With HTTP the device must poll, or the server must reach it inbound through webhooks, which carrier-grade NAT rarely permits.
• Delivery guarantees: QoS 0, 1 and 2 give at-most-once, at-least-once and exactly-once per message, and the broker queues messages for offline clients with persistent sessions. A POST that times out may or may not have been delivered, so over HTTP you build idempotency keys, deduplication and retry with backoff yourself.

Where HTTP is the right tool

• Configuration and REST APIs: fetching a device's config, listing assets, one-shot commands that need an immediate return value.
• Files and bulk transfers: firmware images, log bundles, certificate requests. MQTT can carry them but is not the natural fit.
• Enrollment and auth: OAuth token exchange, EST or ACME certificate enrollment, webhooks from third parties.
• Browser-facing UI: browsers speak HTTP, so a dashboard backend that exposes REST to the browser and talks MQTT to the broker internally is the usual shape.

Most production systems use both: MQTT for telemetry, events and commands, HTTP for configuration, firmware download and enrollment. A device firmware stack can run both clients on the same network interface without conflict.

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:

1. 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.
2. 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.
3. The database (Postgres, MySQL, SQL Server, or a time-series store) sits behind the bridge and sees a handful of well-behaved clients.
4. 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.

Further Reading

• MQTT QoS Levels Explained: When to Use QoS 0, 1, or 2
• MQTT Topic Design: Best Practices for Naming and Wildcards
• MQTT Keep Alive Explained
• Industrial Protocol Comparison: Modbus, OPC UA, MQTT, S7