---
title: "Manufacturing Analytics Database"
description: "Learn how to model OEE, downtime, and quality data as a manufacturing analytics database on Postgres, with schema patterns and SQL examples. "
section: "Postgres for IoT"
---
> **TimescaleDB is now Tiger Data.**
A manufacturing analytics database ingests high-frequency machine, line, and process data (PLC/SCADA tags, MES events, quality and production counts) and makes it queryable for real-time dashboards and historical trend analysis. It's the data-infrastructure layer underneath OEE dashboards, downtime tracking, and quality reporting, distinct from an MES (which manages work orders and shop-floor execution) and from generic BI tools (which query aggregated data, not raw sensor-frequency streams).
This guide covers the data-infrastructure layer that OEE dashboards, downtime tools, and quality systems sit on top of, not application-layer MES or CMMS software. If you're shopping for an out-of-the-box OEE dashboard or a work-order system, look at Siemens Opcenter, SAP, or a dedicated CMMS instead. If you're the engineer who has to model, store, and query the data those tools (or your own dashboards) run on, keep reading.
## Why this is a database problem, not just a software problem
Factory floors generate continuous, high-cardinality sensor streams: temperature readings, vibration signatures, cycle counts, state changes, all arriving multiple times a second across dozens or hundreds of tags per line. Relational OLTP databases tuned for order-management workloads, and spreadsheet-based tracking, weren't built to sustain that volume and velocity. Query a raw sensor table with a million rows per day per machine, and a dashboard that should load in milliseconds starts taking seconds. Try to compute OEE by re-scanning a year of press-cycle events on every page load, and it gets worse from there.
This is where Tiger Data (creator of [TimescaleDB](https://www.tigerdata.com/timescaledb)) fits. [Hypertables](https://www.tigerdata.com/docs/learn/hypertables/understand-hypertables) automatically partition data by time, so queries stay bounded as data grows instead of scanning further back with every added day of production. [Hypercore](https://www.tigerdata.com/docs/learn/columnar-storage/understand-hypercore) compression moves older chunks into columnar storage for long-term retention at a fraction of the size. [Continuous aggregates](https://www.tigerdata.com/docs/learn/continuous-aggregates) pre-compute rollups (OEE by shift, throughput by line) in the background, so a dashboard reads a small materialized result instead of re-scanning raw sensor data every time someone opens it.
This isn't a hypothetical concern at scale. [Flogistix](https://www.tigerdata.com/blog/how-flogistix-by-flowco-reduced-infrastructure-management-costs-by-66-with-tiger-data), which runs high-frequency telemetry across remote field compression units, and [Mechademy](https://www.tigerdata.com/blog/how-mechademy-cut-hybrid-digital-twin-infrastructure-costs), which runs high-frequency turbomachinery diagnostics, both moved to hypertables and continuous aggregates specifically to keep infrastructure costs and query latency under control as their sensor volume grew. Neither is a discrete-manufacturing factory floor, but the underlying pressure, high-cardinality industrial telemetry outgrowing a general-purpose database, is the same one a stamping line or assembly cell runs into.
## The core data types a manufacturing analytics database must model
A manufacturing analytics database earns its keep by modeling four data types well. Tiger Data's [six requirements for an IIoT database](https://www.tigerdata.com/learn/iiot-database-requirements) (high-throughput writes, time-based partitioning, compression, SQL joins, continuous aggregates, and OPC UA/MQTT ingestion) are the underlying capability set that makes all four possible. This section focuses on the schema patterns themselves.
### Real-time production and OEE data
Overall equipment effectiveness (availability × performance × quality) is a computed metric, not a stored field. Store the raw press-cycle and production-count events, then compute OEE with a continuous aggregate:
`-- Raw press-cycle / production-count events
CREATE TABLE machine_events (
time TIMESTAMPTZ NOT NULL,
machine_id TEXT NOT NULL,
line_id TEXT NOT NULL,
cycle_count INT NOT NULL,
good_count INT NOT NULL
);
SELECT create_hypertable('machine_events', by_range('time'));
-- Continuous aggregate: OEE inputs rolled up per 8-hour shift and line
CREATE MATERIALIZED VIEW oee_by_shift_line
WITH (timescaledb.continuous) AS
SELECT
time_bucket('8 hours', time) AS shift,
line_id,
sum(cycle_count) AS total_cycles,
sum(good_count) AS good_cycles
FROM machine_events
GROUP BY shift, line_id;
-- Refresh the rollup on a schedule so it stays current
SELECT add_continuous_aggregate_policy('oee_by_shift_line',
start_offset => INTERVAL '1 day',
end_offset => INTERVAL '8 hours',
schedule_interval => INTERVAL '1 hour');`
A database vendor (InfluxDB) already publishes content on computing OEE from sensor data, but that content stops at the sensor table: it doesn't show the rollup joined against quality codes or ERP data. InfluxDB 3's SQL layer added JOIN support, but quality codes and ERP records are relational business data, not time-series measurements, so they still need a home outside a time-series-first store. The continuous aggregate above runs on an ordinary Postgres table, so oee_by_shift_line joins directly against quality_events or an ERP extract in the same database, without standing up a second system to hold that relational data.
### Machine and line telemetry
PLC and SCADA tag data (temperature, pressure, vibration, current draw) arrives at native reporting frequency, often per-second or faster, across every tag on a line. Tiger Data's existing plant-floor content ([Ask Your Factory Floor Anything](https://www.tigerdata.com/blog/ask-factory-floor-anything-structuring-industrial-data-ai-agents) and [Unify Your Plant-Floor Data with Claude Code and TimescaleDB](https://www.tigerdata.com/blog/unify-plant-floor-data-claude-code-timescaledb)) uses a Unified Namespace pattern, following the ISA-95 hierarchy, as the schema backbone:
`CREATE TABLE uns_namespace (
namespace_id TEXT PRIMARY KEY,
enterprise TEXT,
site TEXT,
area TEXT,
line TEXT,
cell TEXT
);
CREATE TABLE tag_history (
time TIMESTAMPTZ NOT NULL,
namespace_id TEXT NOT NULL REFERENCES uns_namespace(namespace_id),
tag_name TEXT NOT NULL,
value DOUBLE PRECISION
);
SELECT create_hypertable('tag_history', by_range('time'));
SELECT th.time, th.tag_name, th.value
FROM tag_history th
JOIN uns_namespace ns ON ns.namespace_id = th.namespace_id
WHERE ns.line = 'stamping-3' AND ns.cell = 'press-2'
AND th.time > now() - INTERVAL '1 hour';`
### Downtime and machine-state data
Downtime is a state-change event, not a raw uptime counter. Model transitions (running → idle → down → running) and use window functions to compute duration per incident:
`CREATE TABLE machine_state_events (
time TIMESTAMPTZ NOT NULL,
machine_id TEXT NOT NULL,
state TEXT NOT NULL
);
SELECT
machine_id,
state,
time AS state_start,
LEAD(time) OVER (PARTITION BY machine_id ORDER BY time) AS state_end,
LEAD(time) OVER (PARTITION BY machine_id ORDER BY time) - time AS duration
FROM machine_state_events
WHERE state = 'down'
ORDER BY machine_id, time;`
"Machine downtime tracking" and "downtime tracking software" are CMMS-vendor search terms (Guidewheel, Tractian, MachineMetrics). This section models the underlying data; it isn't trying to compete for that application-layer search term.
### Throughput and quality analytics
Batch and quality traceability data (lot numbers, quality codes, pass/fail counts) lives alongside sensor time-series for audit and genealogy queries:
`-- Quality / traceability data lives alongside the production time-series
CREATE TABLE quality_events (
time TIMESTAMPTZ NOT NULL,
line_id TEXT NOT NULL,
lot_no TEXT,
pass_count INT,
fail_count INT
);
SELECT create_hypertable('quality_events', by_range('time'));
-- Yield per shift/line, joined to quality data.
-- Aggregate each side to one row per (shift, line_id) BEFORE joining,
-- so the join is 1:1 and the production sums aren't fanned out.
WITH prod AS (
SELECT time_bucket('8 hours', time) AS shift,
line_id,
sum(good_count)::float / NULLIF(sum(cycle_count), 0) AS yield_pct
FROM machine_events
GROUP BY shift, line_id
),
qual AS (
SELECT time_bucket('8 hours', time) AS shift,
line_id,
sum(fail_count) AS fails
FROM quality_events
GROUP BY shift, line_id
)
SELECT p.shift,
p.line_id,
p.yield_pct,
q.fails
FROM prod p
LEFT JOIN qual q USING (shift, line_id)
ORDER BY p.shift, p.line_id;`
"Manufacturing traceability" and "batch traceability" sit in QMS/MES-vendor territory (Tulip, SAP, Codra). The same principle applies: model the data, don't chase the application-layer keyword.
## Reference architecture: from PLC/SCADA to query-ready analytics
The full pipeline runs from edge and PLC devices, through OPC UA, MQTT, or Modbus, into an ingestion layer, then into Tiger Cloud where hypertables and continuous aggregates live, and finally out to a dashboard or BI tool.
In practice, the bottleneck shows up first at the OPC UA-to-MQTT translation step, not at the database. An OPC UA server polling hundreds of PLC tags per second can saturate a broker's connection pool or a gateway's CPU well before Tiger Cloud becomes the constraint, which is why sizing the ingestion layer, not the database, is usually the first capacity conversation on a new line.
For protocol-level detail on getting data from MQTT brokers into Postgres, see [connecting MQTT to PostgreSQL](https://www.tigerdata.com/learn/mqtt-to-postgresql) rather than re-deriving Telegraf, HiveMQ, or custom-Python ingestion paths here. For handling connectivity gaps at the edge, store-and-forward buffering, and on-device caching, see [edge database patterns for factory devices](https://www.tigerdata.com/learn/edge-database). This page stays at the "what data, how modeled, how queried" altitude.
## Illustrative example: modeling ACME Manufacturing's production data
Let’s consider ACME Manufacturing, an illustrative example used here simply to show how the schema patterns above compose into one working system.
ACME runs a stamping line producing sheet-metal brackets. Every press cycle emits a row to `machine_events`. The line's PLC also streams vibration and temperature readings into `tag_history` under the namespace `acme.plant1.stamping.press2`, using the same Unified Namespace pattern from the telemetry section above. When the press stops for a tool change, a row lands in `machine_state_events` with `state = 'down'`.
Three queries now answer three different questions from one schema: the continuous aggregate on `machine_events` gives OEE by shift, the `LEAD()` window function on `machine_state_events` gives downtime duration per incident, and a join between `tag_history` and `uns_namespace` gives a maintenance engineer a live vibration trend for press 2 specifically, without touching the other two tables.
As `tag_history` grows, apply columnstore compression after a rolling window, commonly seven days, so the most recent data stays in row format for fast writes and older data converts to columnar storage. High-cardinality sensor data like this commonly compresses 90%+ with Hypercore, matching the figure cited on the [IIoT database requirements](https://www.tigerdata.com/learn/iiot-database-requirements) page.
What this looks like in production: [United Manufacturing Hub](https://www.tigerdata.com/case-studies/united-manufacturing) and [Everactive](https://www.tigerdata.com/case-studies/everactive) both run this general pattern, high-frequency sensor ingestion feeding real-time and historical queries, at production scale for IIoT and predictive-maintenance workloads.
## Manufacturing analytics database vs. MES, data historian, and generic time-series databases
| **Manufacturing analytics database (Tiger Data)** | **MES (SAP, Siemens Opcenter)** | **Data historian (dataPARC, AVEVA/OSIsoft PI)** | **Generic time-series database (InfluxDB)** |
| --- | --- | --- | --- |
| **What it is** | SQL-native store for production, downtime, and quality time-series | Work-order execution and shop-floor scheduling system | Purpose-built store for OT tag data with native protocol connectors | Purpose-built store for single-purpose metrics |
| **Primary buyer** | Data/platform engineering | Operations/manufacturing IT | Controls/automation engineering | Platform engineering (monitoring-first) |
| **Query language** | SQL | Vendor-specific/proprietary | Vendor-specific, some SQL bridges | SQL/InfluxQL/Flux (version-dependent) |
| **Best-fit use case** | Custom dashboards, AI-agent access, joining production/quality/ERP data | Routing, work orders, execution tracking | Brownfield sites standardized on OT protocol connectors | Single-purpose metrics/monitoring |
| **Strengths** | Relational joins, SQL familiarity, one system for time-series and relational data | Deep shop-floor workflow logic | Native OPC/DNP3/Modbus connectors, decades of OT integration | Fast time-series writes for pure metrics workloads |
| **Limitations** | Not a substitute for work-order management | Not built for historical sensor analytics at scale | Limited SQL-native querying and joins against relational data | JOIN support is new to InfluxDB 3's SQL layer and still time-series-first, with no relational home for business data like quality codes or ERP dimensions; version fragmentation is a consideration for teams standardizing long-term |
None of these four replace each other outright. A manufacturing analytics database isn't a substitute for MES work-order management, and it isn't a substitute for a historian's native OT protocol connectors in a brownfield environment already standardized on one. For a deeper look at historian replacement and coexistence specifically, see [what is a data historian](https://www.tigerdata.com/learn/what-is-a-data-historian).
## Decision framework
**Choose a manufacturing analytics database (Tiger Data) if:** you need SQL-native querying across production, downtime, and quality data in one place, you're building custom dashboards or feeding AI-agent workflows off factory data, and you want continuous aggregates instead of re-scanning raw sensor data for every report.
**Choose an MES if:** your primary need is work-order execution, routing, and shop-floor scheduling, not historical analytics.
**Choose a dedicated data historian if:** you're in a brownfield environment with deep existing investment in OT protocol connectors (Ignition, PI) and don't yet need SQL-native analytics or AI-agent access.
**Choose a generic time-series database (e.g., InfluxDB) if:** your workload is single-purpose metrics or monitoring with no need for relational joins against production, quality, or ERP data.
## Migration paths into a manufacturing analytics database
**From a legacy historian.** Teams with deep OT protocol investment tend to keep the historian for ingestion and add SQL-native analytics alongside it rather than ripping it out. See [SCADA data management at scale](https://www.tigerdata.com/learn/scada-data-management-at-scale-architecture-historians-and-the-modern-database) for the fuller migration patterns; dashboard query latency is usually the first thing that breaks as sensor cardinality grows against a historian alone.
**From InfluxDB or a generic time-series database.** This path tends to start when a team needs production data to live alongside quality or ERP tables in one queryable system, and finds that a time-series-first database doesn't give relational business data a natural home, even where SQL joins are supported.
**From spreadsheet or CMMS-export tracking.** Manual OEE calculation stops scaling once a plant adds lines or shifts faster than someone can update a spreadsheet by hand; the first thing to break is usually the person doing the calculation, not the software. Concretely, that's often a shared workbook with multiple shift leads editing the same tabs: version conflicts and end-of-shift copy-paste errors start silently corrupting the OEE numbers well before anyone notices the process itself has outgrown a spreadsheet.
**From MongoDB or another NoSQL store.** This path shows up when schema flexibility mattered early on but query performance and SQL joins started mattering more as the system matured. The typical breaking point: a dashboard query that used to return in milliseconds now needs a multi-stage `$lookup` aggregation pipeline once a team joins sensor documents against a separate quality or maintenance collection, and that pipeline gets slower, not faster, as both collections grow. [Mechademy's migration from MongoDB to Tiger Data](https://www.tigerdata.com/blog/how-mechademy-cut-hybrid-digital-twin-infrastructure-costs) for hybrid digital-twin infrastructure is a useful pattern-level reference here.
## Additional Tiger Data resources on industrial data management
[A Beginner's Guide to IIoT and Industry 4.0](https://www.tigerdata.com/learn/a-beginners-guide-to-iiot-and-industry-4-0)
[Data Historian vs. Time-Series Database](https://www.tigerdata.com/learn/moving-past-legacy-systems-data-historian-vs-time-series-database)
[SCADA Data Management at Scale](https://www.tigerdata.com/learn/scada-data-management-at-scale-architecture-historians-and-the-modern-database)
[IIoT Database Requirements](https://www.tigerdata.com/learn/iiot-database-requirements)
[TimescaleDB for Manufacturing IoT: Building a Data Pipeline](https://www.tigerdata.com/blog/timescaledb-manufacturing-iot-building-data-pipeline)
[TimescaleDB for Manufacturing IoT: Optimizing for High-Volume Data](https://www.tigerdata.com/blog/timescaledb-for-manufacturing-iot-optimizing-for-high-volume-production-data)
[Top 5 IoT Manufacturing Industry Trends](https://www.tigerdata.com/blog/top-5-iot-manufacturing-industry-trends-and-their-data-challenges)