---
title: "Predictive Maintenance Database Architecture"
description: "A predictive maintenance architecture needs high-frequency ingest, long retention, and real-time alerting in one schema. Here's how to build it."
section: "Postgres for IoT"
---
> **TimescaleDB is now Tiger Data.**
A predictive maintenance database is the storage and query layer beneath a predictive-maintenance system: it ingests continuous vibration, temperature, current, and pressure sensor data, retains enough history to train failure-prediction models, and serves real-time queries for alerting. Getting this predictive maintenance architecture right separates an alert that arrives in seconds from one that arrives after the failure. It is not predictive-maintenance *software*: CMMS and EAM platforms handle work-order management, and ML frameworks handle model training. This guide covers the data layer underneath both, not a replacement for either.
We should be upfront about where we're coming from: Tiger Data sells a database. If you're shopping for an out-of-the-box CMMS dashboard or a full ML platform, that’s not what this article covers. If you're the engineer who has to model, store, and query the sensor data those tools run on, keep reading.
The core idea worth carrying forward: the choice of storage layer determines whether an equipment-failure alert arrives in seconds or in hours, and whether a full year of failure history is affordable to keep at useful resolution.
## Why the traditional historian-and-batch-export path breaks down
A pattern shows up across many vendors' own engineering write-ups: sensor to historian to periodic batch export (CSV or Parquet) to feature store to ML pipeline. It's an industry-wide pattern, not a single vendor's mistake.
The problem is timing. Batch exports run on a schedule, hourly or nightly, so alerting lags the actual sensor reading by hours, sometimes days, precisely when early warning matters most for catching a failure before it happens.
Most predictive-maintenance content treats the storage layer as a solved, invisible commodity and spends its word count entirely on the ML model. That's the assumption this page pushes back on: the database is the reason alerts are fast or slow, and history is affordable or not.
## What predictive maintenance architecture actually asks of a database
Four requirements pull in different directions, and reconciling them, not picking a single silver-bullet setting, is the actual predictive maintenance architecture problem.
1. **High-frequency ingest.** Vibration sensors commonly sample at 1kHz to 10kHz or higher, alongside lower-frequency temperature, pressure, and current-draw readings. The database has to sustain continuous, high-cardinality writes across potentially thousands of sensors at once.
2. **Long retention for model training.** Failure-prediction models need months to years of historical data spanning multiple failure events, not a rolling 30-day window. A model that has only seen one failure event has not really learned failure patterns yet.
3. **Real-time query for alerting.** Threshold and trend detection need current-window queries answered in seconds, not on a batch-job cycle. A query that takes ten minutes to run defeats the purpose of an early-warning system.
4. **Feature extraction and downsampling.** Raw high-frequency readings are rarely queried directly. What matters is derived features, RMS, peak, crest factor, and moving averages, computed at write time or query time rather than recomputed from scratch on every request.
High write volume, long retention, and fast queries push against each other. A schema that handles one well while ignoring the others is a bottleneck waiting to surface, not an architecture.
## What belongs in Postgres, and what doesn't
This is a straight concession, not a hedge: structured, timestamped sensor readings and feature-extracted summaries, temperature, pressure, current draw, RMS, peak, crest-factor values, and downsampled vibration summaries are exactly Tiger Data's workload. Raw high-frequency vibration waveforms, thermal imagery, and audio recordings used for acoustic diagnosis are not a relational time-series database's job.
Independent validation of this split comes from an unlikely direction: a published comparison from ReductStore, a vendor focused on raw sensor-waveform storage, itself recommends a hybrid architecture where a relational time-series database like TimescaleDB stores structured readings such as temperature and pressure, while a purpose-built store handles infrared imagery, vibration waveforms, or audio.
The line to draw: a discrete numeric reading or computed feature belongs in the database this guide covers. A raw waveform, image, or audio blob captured for downstream signal processing belongs in object storage or a purpose-built store instead. This pre-empts the most likely objection, "but what about the raw vibration waveform," before anyone has to ask it.
## Architecture: schema, continuous aggregates, and compression
The schema and SQL below use TimescaleDB's time-series features - hypertables, continuous aggregates, and hypercore columnstore compression - on top of standard PostgreSQL, and run on Tiger Cloud or a self-hosted TimescaleDB instance. The plain CREATE TABLE statements are standard Postgres.
### Sensor data schema
Start with a metadata table for physical assets and a narrow-row hypertable for sensor readings:
`CREATE TABLE assets (
asset_id TEXT PRIMARY KEY,
asset_type TEXT NOT NULL,
site TEXT NOT NULL,
line TEXT,
install_date DATE
);
CREATE TABLE sensor_readings (
time TIMESTAMPTZ NOT NULL,
asset_id TEXT NOT NULL REFERENCES assets(asset_id),
sensor_type TEXT NOT NULL,
value DOUBLE PRECISION NOT NULL,
unit TEXT
);
SELECT create_hypertable('sensor_readings', by_range('time'));`
`create_hypertable()` is the first concrete step: it partitions `sensor_readings` by time so queries stay bounded as history accumulates, instead of scanning further back with every added day of data.
Computed feature values, RMS, peak, crest factor, can live two ways. The simplest is additional rows in the same table with a distinct `sensor_type` value (`'vibration_rms'` alongside `'vibration_raw'`), which keeps everything in one narrow-row shape. The alternative is a companion `features` table or a `JSONB` column on a separate row, which suits teams that want feature computation logically separated from raw ingest. Either works. Pick based on how your alerting queries are shaped: if alerting always reads features and rarely raw values, a separate table simplifies those queries.
### Continuous aggregates for rolling features
Without pre-computed rolling features, every alerting query re-scans raw high-frequency readings, and that gets slower as history accumulates. A continuous aggregate keeps those recent-window queries fast even as raw history grows, because the query reads pre-computed buckets instead of re-scanning raw rows.
`CREATE MATERIALIZED VIEW vibration_rms_1min
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 minute', time) AS bucket,
asset_id,
sqrt(avg(value * value)) AS rms_value
FROM sensor_readings
WHERE sensor_type = 'vibration_raw'
GROUP BY bucket, asset_id;
SELECT add_continuous_aggregate_policy('vibration_rms_1min',
start_offset => INTERVAL '1 hour',
end_offset => INTERVAL '1 minute',
schedule_interval => INTERVAL '1 minute');`
The same continuous aggregate that powers a real-time alert threshold can also be the input a training pipeline exports from. One computation, two consumers. The next section covers exactly how that works.
### Compression and retention economics
Raw high-frequency vibration waveforms and slower process variables compress very differently, and that gap is the actual economic argument for tiered retention. Industry reports suggest raw vibration waveforms tend to compress at roughly 2.5:1, while slower-changing variables like temperature or pressure tend to compress at roughly 28:1, since there's simply less second-to-second variation to encode. That's a directional, industry-wide figure, not a Tiger Data-specific benchmark, but it's the reason a flat retention policy gets expensive fast on vibration-heavy workloads.
Configure a columnstore policy so data older than a short rolling window - commonly seven days - converts to columnar storage automatically ([hypercore](https://www.tigerdata.com/docs/learn/columnar-storage/understand-hypercore)), while recent data stays in the rowstore for fast writes.
`ALTER TABLE sensor_readings SET (
timescaledb.enable_columnstore,
timescaledb.segmentby = 'asset_id, sensor_type',
timescaledb.orderby = 'time DESC'
);
CALL add_columnstore_policy('sensor_readings', after => INTERVAL '7 days');`
For the multi-year retention a training pipeline actually needs, pair compression with a tiered retention pattern: keep full resolution for a short recent window for immediate diagnosis, then downsample older data instead of storing it at full frequency indefinitely. See [Time-Series Downsampling: The Complete Guide](https://www.tigerdata.com/learn/time-series-downsampling) for the full methodology. A retention policy on the raw table handles the far end of that tier:
`SELECT add_retention_policy('sensor_readings', INTERVAL '2 years');`
Combined with downsampled continuous aggregates that persist longer than the raw retention window, this is what makes a year or more of failure history affordable to keep at useful resolution.
## Serving both sides: ML training and real-time alerting from one store
Two consumption patterns run against the same underlying schema: batch export of historical feature windows for model training, and continuous-aggregate-driven threshold or trend queries for real-time alerting.
One schema serving both beats separate systems for a concrete reason: no ETL step keeps a training data store in sync with an alerting store, and no risk exists of the two drifting out of consistency. A feature the alerting query reads at 2pm is the same row a training export reads that night.
A bulk export for training reads a rolling window of the continuous aggregate:
`SELECT bucket, asset_id, rms_value
FROM vibration_rms_1min
WHERE bucket > now() - INTERVAL '90 days'
ORDER BY asset_id, bucket;`
A threshold check for alerting reads the same aggregate, filtered differently:
`SELECT asset_id, bucket, rms_value
FROM vibration_rms_1min
WHERE rms_value > 4.5
AND bucket > now() - INTERVAL '10 minutes';`
Same table, same computation, two different questions.
## Illustrative example: vibration monitoring on a production line
Here is an illustrative scenario used to make the architecture concrete.
Picture a bottling-line motor with a vibration sensor sampling at several kHz, alongside a temperature sensor on the same asset. Raw vibration readings land in `sensor_readings` with `sensor_type = 'vibration_raw'`, and the continuous aggregate from the schema above rolls them into a 1-minute RMS value per asset. Temperature readings land in the same table under their own `sensor_type`, at a much lower sample rate, and never need their own separate schema.
Over several weeks, a slow upward trend in RMS on that specific motor, still well under any hard failure threshold, is exactly the pattern a maintenance engineer wants surfaced early: a query against `vibration_rms_1min` filtered to that `asset_id` shows the trend directly, without touching a single row of raw vibration data. That's the early-warning signal arriving weeks before a hard failure, not the lag a batch-export pipeline would introduce.
## Real-world predictive maintenance examples
[Takton](https://www.tigerdata.com/blog/how-startup-takton-shipped-just-two-months-tiger-data) runs its Sense machine-monitoring platform on Tiger Cloud, streaming power and vibration readings from shop-floor machines to surface failing equipment early, in one case flagging out-of-range vibration four days before a $50,000 CNC failure.
[Shoplogix](https://www.tigerdata.com/blog/implementation-of-change-data-capture-using-timescaledb-for-shoplogix-industrial-monitoring-services) runs batteryless wireless sensors that continuously monitor temperature, pressure, and vibration on critical manufacturing assets, feeding TimescaleDB for real-time visualization, anomaly detection, and predictive analytics.
[Soundsensing](https://www.tigerdata.com/case-studies/soundsensing) uses AI and sensors to provide predictive maintenance for HVAC units in commercial real estate, reducing downtime and energy costs by proactively identifying issues before they become critical.
Finally, [United Manufacturing](https://www.tigerdata.com/case-studies/united-manufacturing) and [Everactive](https://www.tigerdata.com/case-studies/everactive) are IIoT platforms in manufacturing settings where predictive maintenance is one capability.
## Predictive maintenance database vs. data historians and CMMS software
| **Predictive maintenance database (TimescaleDB)** | **Data historian (AVEVA PI System, dataPARC)** | **CMMS/EAM software** |
| --- | --- | --- |
| **What it is** | SQL-native storage and query layer for sensor data feeding alerting and model training | Purpose-built store for OT tag data with native protocol connectors | Application layer for work-order management and maintenance scheduling |
| **Primary buyer** | Data/platform engineering | Controls/automation engineering | Maintenance operations |
| **Query language** | SQL | Vendor-specific, some SQL bridges | Vendor-specific/proprietary |
| **Best-fit use case** | One schema serving real-time alerting and ML training exports | Brownfield sites with deep OT protocol investment | Scheduling and tracking maintenance work orders |
| **Strengths** | SQL joins between sensor data and asset metadata, no ETL between alerting and training | Native OPC/DNP3/Modbus connectors, decades of operational deployment in brownfield sites | Deep work-order and scheduling workflow logic |
| **Limitations** | Not a substitute for a historian's native protocol connectors in a brownfield site already standardized on one | Generally not built for SQL-native querying or feeding ML training pipelines directly | Not a data-infrastructure choice; doesn't touch the sensor storage layer at all |
For the fuller historian comparison, see [What Is a Data Historian?](https://www.tigerdata.com/learn/what-is-a-data-historian).
## Decision framework
**Choose a predictive maintenance database (TimescaleDB) if:**
- You need one schema to serve both real-time alerting and ML model-training exports, without keeping two systems in sync
- Your sensor data is structured or numeric (temperature, pressure, current draw, extracted vibration features) rather than primarily raw waveform or image capture
- You need SQL joins between sensor readings and asset or maintenance metadata without a separate ETL step
- You want continuous aggregates for rolling features instead of a scheduled batch job
**Choose a data historian if:**
- You're in a brownfield site with deep existing investment in OT protocol connectors and don't yet need SQL-native analytics or ML-pipeline access
**Choose CMMS/EAM software if:**
- Your primary need is work-order scheduling and maintenance-team workflow, not the underlying sensor data architecture
**Route to a purpose-built waveform/blob store if:**
- Your primary storage problem is raw high-frequency vibration waveforms, thermal imagery, or audio recordings used for signal-processing and diagnosis. That's not a workload a relational time-series database should try to own.
## Migration paths into this architecture
**From a legacy historian batch-export pipeline.** Teams outgrow the CSV-or-Parquet-on-a-schedule pattern once alert latency becomes the bottleneck. 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 historian-migration treatment.
**From a generic time-series database (e.g., InfluxDB).** Teams migrate when they need SQL joins between sensor readings and asset or maintenance metadata that a pure time-series database can't do natively. InfluxDB's version fragmentation (1.x, 2.x, 3.0, Cloud Serverless versus Cloud Dedicated) is worth factoring in directionally for teams standardizing long-term.
**From spreadsheet or manual condition-monitoring tracking.** Manual review of vibration and temperature logs stops scaling once a team is monitoring more than a handful of assets by hand.
## Related Tiger Data industrial and OT resources
While this page covers failure-prediction framing, the [Manufacturing Analytics Database](https://www.tigerdata.com/learn/manufacturing-analytics-database) page covers OEE and production-line framing. Both are built on the same sensor-data foundation.
Also informative for building industrial applications is [IIoT Database Requirements](https://www.tigerdata.com/learn/iiot-database-requirements). If you're working across industrial verticals beyond factory-floor sensors, see also [Robot Fleet Telemetry](https://www.tigerdata.com/learn/robot-fleet-telemetry) for a related schema pattern applied to mobile robots and AMRs.
A Digital Twin Architecture page sharing the same sensor-ingestion foundation is planned as a related future piece.