From SCADA to Dashboard: Designing Telemetry Ingestion Without Drowning the Database | Blog

# From SCADA to Dashboard: Designing Telemetry Ingestion Without Drowning the Database

TL;DR: a well, an FPSO or a pumping station generate telemetry at a rate no relational ERP database was built to absorb directly; the right architecture separates capture (OPC UA, MQTT, Modbus), time-series storage with layered aggregation/retention, and only then exposes a processed summary to the ERP — never the raw reading, and never with the OT network reachable from the IT network.

The problem, in numbers

A single pressure sensor reporting at 1 Hz produces 86,400 readings a day. A platform with a few hundred SCADA tags (pressures, temperatures, flow rates, vibration, valve states) easily crosses tens of millions of readings a day from that unit alone. Multiply that across several wells, an FPSO and a pipeline network, and you're looking at a write volume no SensorReading table sitting inside the same Postgres database that holds invoices, proposals and leads is going to survive without degrading everything else.

Hence the rule this article argues for: industrial telemetry ingestion is a separate architectural problem, with its own storage engine, not "one more table" in the ERP. The most common mistake is exactly the opposite — wiring the PLC or SCADA gateway straight into the transactional database of the management application. It works in a demo with ten sensors. It does not survive real production.

Three real protocols — and what each one actually does

Before designing any pipeline, it's worth being precise about what each protocol is, because each solves a different problem and none fully substitutes for the others.

OPC UA (IEC 62541)

OPC UA (OPC Unified Architecture) is a platform-independent, service-oriented architecture specification that succeeds OPC Classic and is standardized as IEC 62541 by the OPC Foundation. It runs on Windows, Linux and embedded systems, defines a structured information model (not just loose values, but an address space with types, relationships and metadata), and bakes security — authentication, encryption, message signing — into the specification itself rather than bolting it on afterward. In industrial practice, it's the protocol used to expose, richly and with semantic description, what's happening inside a modern PLC, DCS or historian.

MQTT

MQTT is an extremely lightweight publish/subscribe messaging protocol standardized by OASIS (version 5.0 is current, with 3.1.1 still widely deployed). An MQTT broker acts as the intermediary: devices publish messages to a "topic," and whoever is subscribed to that topic receives them, without the publisher needing to know who's listening. It was designed for unreliable connections and low-capability devices — exactly the profile of a remote IoT sensor on an isolated well pad, or a gateway communicating over radio or satellite where fiber doesn't reach. MQTT doesn't itself define a rich data model the way OPC UA does; it defines transport and delivery semantics (QoS 0/1/2, retained messages, last will).

Modbus

Modbus is an older, open, royalty-free messaging protocol (Modicon, 1979), historically described as a master/slave architecture — terminology the Modbus Organization updated to client/server in specification version 1.1b3, while keeping the same data model and function codes. It exposes a simple address space of 16-bit registers and coils (bits), read and written by a client that polls each device individually. It's the protocol still running an enormous amount of older field instrumentation — many PLCs, meters and RTUs in older Angolan oil field installations speak only Modbus RTU (serial) or Modbus TCP.

It's also worth mentioning DNP3, which wasn't in this piece's original brief but shows up constantly alongside these three: it's the dominant SCADA protocol in electric, water and also oil & gas utilities for communication between a master station and RTUs/IEDs over long distances — maintained today by the DNP Users Group. If your field has pumping stations or associated electrical substations, you'll likely find it sitting next to Modbus.

Quick comparison

Protocol	Communication style	Strength	Where it typically shows up
OPC UA	Client/server + pub/sub, rich information model	Native security, structured semantics, cross-vendor interoperability	Modern PLCs and DCS, historians, MES
MQTT	Publish/subscribe via broker	Lightweight, tolerant of weak links, scales to thousands of devices	Remote IoT sensors, field gateways, satellite/radio
Modbus	Client/server, 16-bit registers	Simplicity, ubiquity in older equipment	Legacy PLCs, meters, older-generation RTUs
DNP3	Master/RTU, event-oriented	Reliability over long, unreliable links	Substations, pumping stations, electric utilities

None of these is "best" in the abstract — the choice depends on what's already installed in the field. A well-designed edge gateway typically speaks Modbus or OPC UA inward (to the equipment) and publishes MQTT outward (to the ingestion pipeline), doing that protocol translation — which is, at its core, the same problem as any integration between heterogeneous systems we solve on any API project, except here one side speaks an industrial protocol instead of REST or GraphQL.

Why you don't write every raw reading into the ERP database

There are three concrete technical reasons, not just a "best practice" hand-wave:

1. Incompatible write pattern. A relational database optimized for transactions (leads, proposals, invoices — the pattern of a Postgres instance backing an ERP) is designed for relatively few writes per second with many indexes and strong referential integrity. Telemetry is the opposite: thousands of writes per second, almost no need for traditional indexing, almost never updated once written (append-only). Forcing both patterns into the same table saturates the WAL, bloats indexes, and slows down queries that have nothing to do with sensors — invoices start taking longer to load because a vibration sensor is writing at 10 Hz.

2. Unbounded growth. Without a time-series-specific retention strategy, the table grows indefinitely. An ERP doesn't have, by definition, native mechanisms for automatic age-based expiration and aggregation — that's a feature of time-series databases (TimescaleDB, InfluxDB and similar), not of a generic relational schema.

3. Operational coupling. If sensor history lives in the same database as the ERP, maintenance, a load spike, or a telemetry ingestion failure can drag down billing and CRM with it — two domains that shouldn't share operational risk.

The correct separation is: a dedicated time-series engine holds the raw (or near-raw) reading for a short window and the aggregates for much longer; the ERP only receives, via API, the already-processed indicators that matter to the business — daily production per well, equipment downtime hours, alerts for deviation from operating limits.

The Angolan context: where bandwidth decides the architecture

In Angola the distance between sensor and database is geographic, not just logical. An offshore operation on Block 17 or 32 connects by satellite; a support base at Soyo or in Cabinda connects by fibre or radio; and the data centre may sit in Luanda or outside the country. That changes the design, not merely the cost.

The infrastructure has improved materially — Angola Cables runs the AngoNAP facility in Luanda and the Angonix exchange point, the country has thousands of kilometres of national fibre and connects to international submarine cables — but none of that solves the problem at the origin: an FPSO still depends on a link with limited latency and bandwidth, and telemetry has to cross that link.

The practical consequence is simple, and it drives everything else: aggregate at source. Shipping 1 Hz of every tag to shore and deciding afterwards what matters wastes the scarcest resource the operation has. What goes up should already be reduced, prioritised, and — when the link drops — queued locally for resend.

Aggregation and downsampling: what stays, what gets summarized, what expires

"Downsampling" is the process of reducing the time resolution of historical data, replacing many readings with a statistical summary (average, min, max, last value) per interval. It's the standard technique in time-series databases such as TimescaleDB (via continuous aggregates, which can be combined with a retention policy to drop raw data while keeping the aggregates) or InfluxDB (via retention policies paired with downsampling tasks, keeping high-resolution data for a short time and summarized data for much longer).

A reasonable policy for oil & gas field telemetry usually has three to four layers:

Raw (seconds to minutes): kept for days to a few weeks, for immediate diagnostics and investigation of recent incidents.
1-minute or 5-minute aggregate: kept for several months, enough for short/medium-term operational analysis.
Hourly aggregate: kept for one to several years, for trends and maintenance planning.
Daily aggregate: kept indefinitely (or for periods set by regulatory/audit requirements), for production, ESG and year-over-year reporting.

This isn't an aesthetic choice — it's what keeps the database sustainable long-term without losing the ability to answer "what happened to well 12's pressure at 3am on Tuesday," which still needs fine-grained data, just only for a recent window.

Retention: decide before you accumulate

Retention shouldn't be decided after the fact, once the disk is full — it should be part of the initial design, tag by tag, because not every sensor has the same historical value. A vibration sensor on a critical pump may justify longer raw retention for predictive failure analysis; an ambient temperature sensor on a container probably doesn't. Deciding this early avoids two traps: paying for expensive storage for data with no analytical value, or discovering too late that exactly the period the engineering team needed to investigate a shutdown has already been deleted.

The security boundary: the OT network is not exposed through the IT layer

This is where ingestion architecture directly intersects with industrial security, and where we see the most projects fail from rushing. The reference model used across the industry — the Purdue model, now incorporated into the zones-and-conduits structure of IEC 62443 — explicitly separates control levels (PLCs, RTUs, SCADA/DCS systems) from corporate IT levels, with security zones defined by risk assessment and controlled "conduits" between them, instead of a flat network.

In practice, that translates into concrete rules for any ingestion pipeline:

Data flow direction must be inside-out. The edge gateway on the OT network publishes data to a broker or service in the DMZ/IT network; the IT network should not be able to initiate direct connections into the OT network.
No IT system — including the ERP itself — should have direct network access to a PLC, RTU or control-side historian. If the management dashboard needs a value, it fetches it from the time-series layer or an intermediary API — never directly from the field equipment.
Protocol translation happens at the boundary, not past it. It's the gateway/edge device, physically and logically inside the OT zone or a dedicated DMZ, that speaks Modbus/OPC UA inward and MQTT/HTTPS outward — and that's where authentication, encryption and filtering controls belong, not left to "the corporate network has a firewall."
OT network credentials and certificates should not be shared with IT systems, even administrative ones — a compromise in the ERP or CRM shouldn't, by design, hand anyone a path back into a well's or a process unit's control system.

This isn't regulatory paranoia — it's why security incidents in industrial environments tend to start exactly where the OT/IT boundary was treated as a network detail instead of an architectural requirement from the first design.

A reference architecture, in text

1. Field: PLCs, RTUs and sensors speak Modbus, OPC UA or DNP3 depending on what's installed.

2. Edge (inside the OT zone): a gateway aggregates and translates protocol, applies basic filtering (e.g., dropping readings outside physical range before forwarding), and publishes via MQTT (or OPC UA over a secured proxy) out of the OT zone, through a controlled conduit.

3. Ingestion (DMZ or data zone): an MQTT broker or ingestion service receives the messages and writes to the time-series database.

4. Time-series storage: short-term raw data plus layered aggregates, with retention defined per tag.

5. Integration layer: an API (authenticated, held to the same rigor as any system integration exposed to a management system) exposes only the already-processed indicators to the ERP — production, availability, alerts — never the raw real-time reading.

6. ERP/dashboard: consumes the API, shows trends, generates business alerts, feeds production and maintenance reports.

FAQ

Does MQTT replace OPC UA?

No. They solve different problems. MQTT is a lightweight publish/subscribe message transport with no rich information model of its own; OPC UA defines both the transport and a structured data model with native security. It's common for a gateway to speak OPC UA to the PLC and publish over MQTT to the ingestion pipeline — they aren't mutually exclusive.

Is it still worth investing in Modbus support if it's a protocol from the 1970s?

Yes, when the installed equipment only speaks Modbus — and in many older installations across Angola, that's still the reality for most field instrumentation. The goal isn't to replace Modbus on the equipment; it's to make sure the edge gateway translates it securely into a protocol better suited to ingestion at scale.

Is a time-series database always necessary, even for small volumes?

If the real volume is low (a few dozen sensors, a handful of readings per minute), a dedicated, well-indexed table in a relational database separate from the ERP's can hold for a while. The tipping point arrives faster than expected as soon as you add wells, high-frequency vibration equipment, or multiple sites — and by then, migrating already-accumulated data is far more expensive than designing with the right architecture from the start.

Who should keep the raw history: the industrial historian or the management application's ingestion layer?

The industrial historian (where one exists) is normally the operational source of truth for engineering and maintenance, with its own retention and tooling. The ingestion layer feeding the ERP doesn't need to duplicate that function — it needs to capture, aggregate and expose what matters for business decisions, without building a second, informal historian inside a database that wasn't designed for it.