A device can reconnect successfully while its last reading disappears between the MQTT broker and the database. That is why I would not make a central streaming platform the default first step in IoT integration: it adds another place to lose, duplicate, or misroute events before most teams have defined who owns recovery. For production support, a small durable ingestion boundary is usually the better starting point.
Putting Kafka first moves the failure boundary out of sight
The popular diagram sends device traffic through an MQTT broker, an adapter, Kafka, and several consumers. It looks clean because each component has a distinct job. It is harder to operate because a device reconnect, broker restart, adapter retry, and consumer replay can all produce the same visible symptom: an apparently late or duplicated reading. If nobody has specified where an event becomes the system’s responsibility, the on-call engineer has to reconstruct that answer during an incident.
Seamless IoT Integration for Modern Software Systems is a useful compatibility goal, but a connection that appears seamless says little about recovery because transport success and application durability occur at different points. MQTT 5.0 QoS 1 means the protocol can deliver a publication at least once; it does not mean a downstream PostgreSQL transaction committed exactly once. A client may retry after an uncertain acknowledgement, while an adapter may retry after a database timeout whose transaction actually succeeded. Both paths require duplicate handling.
For a team with one operational consumer, I would start with Eclipse Mosquitto 2.x or another MQTT broker at the device boundary and write validated events into a PostgreSQL 16 inbox table before downstream processing. That gives operators a durable place to ask, “Did we receive this device sequence number?” It does not eliminate broker-to-writer failure: an MQTT acknowledgement can precede the database commit unless the writer and its delivery path are designed around that gap. The point is to make the gap explicit and testable, rather than hiding it behind more services.
Consider a planning workload of 12,000 devices publishing once a minute. That is an average of 200 events per second, calculated from the assumed device count rather than measured traffic. Peaks, reconnect storms, and payload size still need a load test. At that assumed rate, Kafka may be justified by several independent consumers or long replay requirements; it is not justified merely because the word “IoT” appears on the architecture diagram. Running brokers, partitions, adapters, access controls, and retention policies creates additional failure modes that somebody must diagnose at 03:00.
A durable inbox beats an elegant pipeline when recovery is unclear
The decision is not “modern streaming” versus “old database.” It is MQTT to PostgreSQL inbox versus MQTT to Kafka to consumers, with an explicit recovery contract for either option. The PostgreSQL path wins when there are few consumers, modest traffic, and a need to inspect or correct individual events with familiar SQL tools. Its costs are database write capacity, table maintenance, and a deliberate plan for processing and pruning rows. The Kafka path wins when multiple consumers need independent offsets, substantial replay, or sustained throughput that has outgrown the inbox design. Its costs are another operational cluster or managed-service bill, connector maintenance, partition planning, and a second set of retention and authorization decisions.
Neither option repairs an unstable device identity. Give each event a stable device ID and a sequence number that survives reconnects, or use another idempotency key whose uniqueness rules are documented. PostgreSQL’s ON CONFLICT behavior can then reject a repeated key without treating every retry as a fresh reading. If a device resets its sequence number after a firmware update, include a boot or session identifier in the key; otherwise valid new events can collide with old ones. Treat device timestamps as data, not ordering authority, because device clocks can drift or be reset.
The following Python 3 example is a runnable local illustration of a persistent inbox. Save it as inbox.py and pipe in newline-delimited JSON containing device_id and seq. Its SQLite WAL file makes the uniqueness rule easy to test; it is not a claim that SQLite alone is the right shared production store.
import json, sqlite3, sys
db = sqlite3.connect("inbox.db")
db.execute("PRAGMA journal_mode=WAL")
db.execute("CREATE TABLE IF NOT EXISTS inbox "
"(device TEXT, seq INTEGER, payload TEXT, "
"PRIMARY KEY(device, seq))")
for line in sys.stdin:
event = json.loads(line)
with db:
db.execute("INSERT OR IGNORE INTO inbox VALUES (?, ?, ?)",
(event["device_id"], event["seq"], line.strip()))
print("stored or already present", event["device_id"],
event["seq"], flush=True)
That last line reports a database outcome, not an MQTT acknowledgement. In production, persist the payload and its provenance, then make the worker’s completion state separate from receipt; otherwise a process can commit an inbox row, crash before doing the work, and leave no safe way to resume. Use a PostgreSQL transaction to claim work and record its result, and make the downstream action idempotent where possible. “Exactly once” is an unsafe runbook instruction when the external action and inbox update cannot share one transaction.
Broker settings cannot substitute for a reconnect test
Mosquitto’s persistence setting and autosave_interval are worth reviewing, but enabling persistence does not turn every MQTT message into a permanent event log. A retained message represents the latest retained value for a topic, not a history of readings. A persistent session can help with queued messages for a disconnected client, but its behavior depends on session expiry, subscriptions, queue limits, and how the publisher and subscriber reconnect. Write those choices down alongside the firmware’s retry behavior; otherwise the broker configuration and device behavior can make contradictory promises.
I would not set MQTT QoS 2 everywhere to “solve duplicates,” because its additional protocol exchange does not make a database write and an external side effect atomic. QoS 1 plus a stable event key is often easier to recover, provided the application accepts and handles redelivery. That is a position to verify with a kill test, not a universal performance claim. Disconnect the writer after it receives a publication, restart the broker, and compare device sequence numbers with committed inbox rows. Then repeat while the database is unavailable and while many devices reconnect together.
Set a provisional 30-second reconnect backoff ceiling in a test configuration, then tune it against device behavior and broker load; synchronized retries can create a larger incident than the original outage. Reserve an initial 2 GiB of gateway-side buffer space only as a capacity budget to revise after measuring actual payloads and outage duration. Those values are operational choices, not vendor guarantees. An edge buffer also needs a defined overflow policy: dropping the oldest telemetry, rejecting new writes, and blocking a producer have different consequences. The on-call engineer needs to know which one the implementation will choose before the disk fills.
Prometheus counters can show accepted publications, inbox inserts, duplicates, rejects, and worker failures, while a Grafana panel can put those rates beside broker connections and PostgreSQL commit latency. OpenTelemetry traces can connect an adapter attempt to a database write, but sampled traces cannot prove that no event was lost. Keep a queryable count of missing sequence numbers per device or session; it gives an incident responder a more direct question to investigate than an aggregate “healthy” dashboard. Protect that view from unbounded cardinality by storing per-device detail in the inbox or an investigation query rather than making every device ID a Prometheus label.
Release ownership matters more than another integration layer
Scope IoT Integration Around Metrics That Prove It Works can help decide whether a pilot succeeds, but production support also needs a named owner for each transition because an alert cannot repair an ambiguous handoff. Put the device team, broker operator, ingestion service owner, and database owner on one failure-path review. For each boundary, record who retries, what key makes the retry safe, how long data remains recoverable, and which log or query establishes what happened. If two teams both believe the other side owns a lost event, the design is not ready to deploy.
Firmware releases deserve the same review as backend releases because a change in topic naming, sequence reset, or payload encoding can invalidate a perfectly healthy ingestion worker. Use a schema identifier in the payload or topic contract and reject unknown versions into an inspectable quarantine rather than silently coercing them. JSON Schema can enforce an envelope at ingestion; if binary payload size warrants Protocol Buffers, its field-number compatibility rules must be part of the release checklist. Neither format removes the need to test old and new firmware publishing at the same time.
Start with a 14-day inbox retention policy as a value to tune against storage cost and the longest realistic investigation window. Deleting rows sooner can erase the evidence needed to reconcile a delayed device; retaining them indefinitely can make maintenance and privacy obligations harder. Document what happens to unfinished work before pruning. For Kafka deployments, make the same decision for topic retention and consumer lag: a consumer that falls behind the retention window cannot recover by simply restarting.
Make one deployment gate a recovery exercise. Publish known sequence numbers, interrupt the adapter between receipt and storage, interrupt a worker after it performs its downstream action, and roll back a schema change while older devices continue sending. Record the missing and duplicate keys, not merely whether services returned to “up.” A team that can explain each result has earned the option to add Kafka later. A team that cannot explain them will only make the incident longer by adding it now.
The first production task is to draw the acknowledgement line
Before selecting another service, take one real device event and mark every acknowledgement from publication through the final write. At each mark, ask what survives a process crash and how a retry is identified. Then run one crash test at the least certain boundary. If the team cannot tell whether that event was committed, fix that boundary before expanding the pipeline.
