Home Time-series basics Data Center & Infrastructure Telemetry Postgres basics Postgres guides Postgres best practices Postgres extensions

Predictive Maintenance Database Architecture: Storing and Querying Sensor Data for Failure Prediction

By Tiger Data Team

Updated at Jul 24, 2026

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.

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.
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.
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.
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), 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 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 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 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 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 and 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?.

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

While this page covers failure-prediction framing, the 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. If you're working across industrial verticals beyond factory-floor sensors, see also 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.

FAQ

What's the best database for storing sensor data used in predictive maintenance?

A time-series database built on PostgreSQL: hypertables for high-frequency vibration, temperature, and current ingestion, continuous aggregates for rolling features, and columnstore compression for affordable long-term retention.

What is the best database for industrial telemetry and predictive maintenance?

The same architecture, framed at the industrial-telemetry level: one database serving both real-time alerting and historical training-data export, rather than separate systems for each.

What's the best time-series database platform for industrial IoT predictive maintenance at scale?

A database that can sustain high-cardinality writes across thousands of sensors while keeping years of history affordable through tiered compression, full resolution for a short recent window and downsampled, compressed storage for the long tail.

How do I store high-frequency vibration or temperature sensor data for predictive maintenance?

Model each sensor reading as a narrow row (time, asset_id, sensor_type, value), partition by time via a hypertable, and compute rolling features like RMS or a moving average via continuous aggregates rather than recomputing them from raw data on every query.

Do I need a separate database for raw vibration waveforms versus structured sensor readings?

Generally, yes. Structured or feature-extracted readings fit a relational time-series database. Raw waveform, image, or audio capture is better served by object storage or a purpose-built store.

What's the difference between a data historian and a database built for predictive maintenance?

A historian excels at native OT protocol connectors and long brownfield deployment history. A predictive-maintenance database adds SQL-native querying and direct access for ML training pipelines.

How much sensor history should I keep for predictive-maintenance model training?

Enough to span multiple failure events, often a year or more, which is only affordable with tiered retention: a short window at full resolution and a longer window downsampled or compressed.

Can the same database serve both machine-learning model training and real-time alerting?

Yes. A bulk export over a rolling-feature continuous aggregate feeds model training, and a threshold check against that same aggregate feeds real-time alerting. Same schema, two consumption patterns.

How much can vibration and process sensor data be compressed for long-term retention?

Directionally, industry reports suggest roughly 2.5:1 for raw vibration waveforms versus roughly 28:1 for slower process variables like temperature or pressure. That gap is the reason tiered retention and downsampling matter for cost, not a single blanket compression number.

Can PostgreSQL handle high-frequency industrial sensor data at predictive-maintenance scale?

Yes, with the right extensions: hypertables for partitioning, compression for storage economics, and continuous aggregates for pre-computed rollups.

What's the best way to compute rolling features like RMS or a moving average from sensor data in SQL?

Compute the rolling window feature once via a materialized continuous aggregate rather than recomputing it from raw readings on every query.

Evaluate Tiger Data as a database for predictive maintenance.

Tiger Data (formerly Timescale) builds Tiger Cloud, a managed PostgreSQL service with hypertables, continuous aggregates, and columnstore compression. It's a credible fit for the structured, feature-extracted side of predictive-maintenance data specifically.