IoT analytics platform: sensor data and machine logs in one SQL engine

An IoT analytics platform turns what machines and sensors report into answers: which machine is drifting, what it was doing when an alarm fired, how long the line stood still. SereneDB is its storage and query layer: sensor readings, the machine log and work orders sit in one Postgres-compatible engine, and SQL aggregates them over time, searches the log text and joins them.

Get Started Time functions docs →

curl https://install.serenedb.com | sh

readings · 4 machines · 11,520 rowsdriftquery

What is an IoT analytics platform

What does an IoT analytics platform do? Five questions about one shift on a machining line

An IoT analytics platform collects what machines and sensors report, from readings and alarms to state changes and operator notes, and answers questions about it: which machine is drifting, what it was doing when an alarm fired, how long it stood still. Below is one simulated shift on four CNC machines. Pick a question to run it; each is one SQL statement.

shift lab · line 2, four CNC machines

2026-11-05 · 06:00–14:0011,520 readings · 19 log entries · 3 work orders

Drift

sql

SELECT time_bucket(INTERVAL '1 hour', ts) AS hour,
       round(avg(vibration_mm_s) FILTER (WHERE machine = 'cnc-03'), 2) AS cnc_03,
       round(avg(vibration_mm_s) FILTER (WHERE machine <> 'cnc-03'), 2) AS other_three,
       round(max(spindle_temp_c) FILTER (WHERE machine = 'cnc-03'), 1) AS cnc_03_max_c
FROM readings
WHERE spindle_load > 0
GROUP BY hour ORDER BY hour;

result · 8 rows

hour	cnc_03	other_three	cnc_03_max_c
2026-11-05 06:00:00	1.3	1.2	42.8
2026-11-05 07:00:00	1.3	1.2	42.8
2026-11-05 08:00:00	1.51	1.2	45.7
2026-11-05 09:00:00	2.79	1.2	54.4
2026-11-05 10:00:00	5.36	1.2	68.9
2026-11-05 11:00:00	7.18	1.19	69.1
2026-11-05 12:00:00	1.1	1.2	39.7
2026-11-05 13:00:00	1.1	1.2	42.8

cnc-03's average vibration climbs from 1.3 mm/s at 06:00 to 5.36 mm/s in the 10:00 hour and 7.18 mm/s before it stops, while the other three machines stay at 1.2. Its spindle temperature peaks at 69.1 °C. spindle_load > 0 keeps idle minutes out of the averages.

Log search

sql

SELECT ts::TIME AS t, message
FROM machine_events_idx
WHERE machine = 'cnc-03' AND kind = 'note'
  AND message @@ ts_levenshtein('spindle', 1)
ORDER BY ts;

result · 4 rows

t	message
08:41:10	Faint whine from the spindle above 9000 rpm, parts still in tolerance
09:55:30	Spindel noise louder, vibraton trending up on the HMI
11:03:40	Grinding noise from the spindle nose, front bearing suspected. Machine stopped, maintenance called
12:31:15	Front spindle bearing pair replaced, runout 2 um, test cut in tolerance

The operator's notes on cnc-03 that mention the spindle, starting 92 minutes before the machine's first alarm. ts_levenshtein accepts one edit, so it also finds "Spindel noise louder", a typo an exact search for spindle misses.

At the alarm

sql

SELECT e.ts::TIME AS alarm_at, e.machine, e.code,
       r.ts::TIME AS reading_at, r.vibration_mm_s, r.spindle_temp_c
FROM machine_events e
ASOF JOIN readings r ON r.machine = e.machine AND e.ts >= r.ts
WHERE e.kind = 'alarm'
ORDER BY e.ts;

result · 5 rows

alarm_at	machine	code	reading_at	vibration_mm_s	spindle_temp_c
09:12:40	cnc-01	ALM-1150	09:12:40	1.22	41.1
10:13:02	cnc-03	ALM-2041	10:13:00	4.52	56.8
10:46:22	cnc-03	ALM-2042	10:46:20	6.33	65.1
11:30:00	cnc-02	ALM-3307	11:30:00	1.25	42.2
13:15:30	cnc-04	ALM-1150	13:15:30	1.09	40.5

ASOF JOIN pairs each alarm with the last reading of its machine at or before it, so the two clocks need not match. ALM-2041 fired at 10:13:02 on the 4.52 mm/s reading of 10:13:00, and ALM-2042 at 10:46:22 with the spindle at 65.1 °C.

Downtime

sql

SELECT machine, ts::TIME AS stopped_at, minutes, message AS reason
FROM (
    SELECT machine, ts, code, message,
           date_diff('second', ts, lead(ts, 1, TIMESTAMP '2026-11-05 14:00:00')
                     OVER (PARTITION BY machine ORDER BY ts)) / 60.0 AS minutes
    FROM machine_events
    WHERE kind = 'state'
)
WHERE code = 'STOPPED'
ORDER BY ts;

result · 2 rows

machine	stopped_at	minutes	reason
cnc-03	11:02:00	96	Feed hold after spindle alarms ALM-2041 and ALM-2042
cnc-02	11:30:20	11.5	Tool change T12

lead() gives each state change the time of the next one, so a STOPPED row lasts until the machine runs again: 96 minutes for cnc-03's bearing, 11.5 for cnc-02's tool change.

Repair

sql

SELECT w.wo_id, w.machine,
       round(avg(r.vibration_mm_s) FILTER (WHERE r.ts < w.opened_at), 2) AS mm_s_before,
       round(avg(r.vibration_mm_s) FILTER (WHERE r.ts > w.closed_at), 2) AS mm_s_after,
       round(max(r.spindle_temp_c) FILTER (WHERE r.ts < w.opened_at), 1) AS max_c_before,
       round(max(r.spindle_temp_c) FILTER (WHERE r.ts > w.closed_at), 1) AS max_c_after
FROM work_orders w
JOIN readings r ON r.machine = w.machine
 AND r.ts BETWEEN w.opened_at - INTERVAL '1 hour' AND w.closed_at + INTERVAL '1 hour'
 AND r.spindle_load > 0
WHERE w.kind = 'corrective' AND w.opened_at >= TIMESTAMP '2026-11-05 00:00:00'
GROUP BY ALL ORDER BY w.wo_id;

result · 3 rows

wo_id	machine	mm_s_before	mm_s_after	max_c_before	max_c_after
WO-4468	cnc-01	1.2	1.2	41.8	41.8
WO-4471	cnc-03	5.52	1.1	69.1	42.7
WO-4475	cnc-04	1	1	40.8	40.8

The day's corrective work orders joined to the readings an hour either side, while the spindle turned. The bearing replacement WO-4471 took cnc-03 from 5.52 to 1.1 mm/s and from 69.1 to 42.7 °C; the two coolant top-ups changed nothing.

Checklist

What an IoT analytics platform needs from its database, and how SereneDB does it in SQL

The database under an IoT analytics platform takes in readings as machines send them, aggregates them over time, lines up events that never share a timestamp, searches what machines and people write, joins maintenance records and keeps years of history. Lists of IoT platforms compare products; these are the jobs, each with the SereneDB feature that does it and its docs.

Need	How SereneDB does it	Looks like
Take in readings	Gateways write over the PostgreSQL protocol: INSERT for single readings, COPY for batches, from any PostgreSQL client library. Docs →	`COPY readings FROM STDIN`
Aggregate over time	time_bucket groups readings into minutes, hours or shifts, and FILTER compares machines in the same pass. Docs →	`time_bucket(INTERVAL '1 hour', ts)`
Line up events and readings	An alarm and a sensor reading never share a timestamp. ASOF JOIN pairs each event with the last reading of the same machine at or before it. Docs →	`ASOF JOIN readings r ON r.machine = e.machine AND e.ts >= r.ts`
Fill sensor dropouts	The fill window function interpolates missing values linearly, ordered by time. Docs →	`fill(spindle_temp_c) OVER (ORDER BY ts)`
Search machine logs	An inverted index over alarm texts and operator notes: exact alarm codes, BM25 ranking, phrases, and matches within an edit distance for typos. Docs →	`message @@ ts_levenshtein('spindle', 1)`
Turn states into downtime	Window functions give each state change the time of the next one, so stopped minutes and availability are a sum. Docs →	`lead(ts, 1, …) OVER (PARTITION BY machine ORDER BY ts)`
Join maintenance records	Work orders are a table in the same engine, or stay in the PostgreSQL database of the maintenance system, which ATTACH reads in place. Docs →	`JOIN readings r ON r.machine = w.machine`
Dashboards	Grafana's built-in PostgreSQL data source sends each panel's SQL to SereneDB. Docs →	`floor(extract(epoch from ts)/300)*300 AS "time"`
Keep the history as files	COPY writes old readings to Parquet files partitioned by day, and read_parquet queries them in place, on disk or in S3. Docs →	`(FORMAT parquet, PARTITION_BY (day), APPEND)`
Retention	A scheduled job inside the server archives and deletes the readings past their period. Docs →	`CREATE JOB readings_history EVERY 1 DAY OFFSET 2 HOURS AS $$ … $$`

Architecture

IoT analytics architecture: readings, machine logs and work orders in one engine

Gateways send readings over the PostgreSQL protocol, the machine log and work orders land in tables with inverted indexes over their text, codes and times, and every question is a SELECT: a time bucket, a search, an ASOF JOIN or a JOIN. Grafana reads the answers, and a job moves old readings to Parquet. Hover a stage, or a lane inside the index.

01 · ingest

COPY

gateway readings PostgreSQL :7890

01 · ingest

INSERT

alarms · states · notes work orders

02 · store

readings

one row per reading

02 · store

machine_events

the machine log

02 · store

work_orders

maintenance history

03 · inverted indexes

log and work orders

message, descriptiontext

machine, kind, codeexact

ts, opened_atrange

04 · aggregate

time_bucket

avg · max · FILTER

04 · search

BM25 · ts_levenshtein

04 · line up

ASOF JOIN

alarm → reading

04 · join

JOIN

work order ± 1 h

05 · dashboards

Grafana

PostgreSQL data source :7890

06 · history

CREATE JOB

Parquet · DELETE

> ingest · readings

Readings arrive over the PostgreSQL protocol

A gateway on the line batches readings and sends them with COPY … FROM STDIN, or with INSERT from any PostgreSQL client library. In COPY's default text format, each line is one reading with tab-separated values.

sql

COPY readings FROM STDIN;

cnc-01 2026-11-05 14:00:00 41.3 1.18 63

cnc-02 2026-11-05 14:00:00 43.4 1.43 66

cnc-03 2026-11-05 14:00:00 41.6 1.07 62

cnc-04 2026-11-05 14:00:00 40.2 1.02 64

SELECT count(*) FROM readings WHERE ts = TIMESTAMP '2026-11-05 14:00:00';

-- COPY 4, then 4

> ingest · log and work orders

Alarms, states, notes and work orders as rows

The machine log holds three kinds of entries: state changes, alarms with their numbers, and the notes operators type. Work orders come from the maintenance system, or stay in its PostgreSQL database and are read in place with ATTACH, as on the zero-ETL search page.

sql

SELECT kind, count(*) AS entries

FROM machine_events

GROUP BY kind ORDER BY kind;

-- alarm 5 · note 6 · state 8

> stage 02 · readings

A reading is a row

A plain table, one row per machine and timestamp, one column per measured value. When a sensor drops out, the fill window function interpolates the gap at query time.

sql

SELECT ts::TIME AS t, spindle_temp_c,

round(fill(spindle_temp_c) OVER (ORDER BY ts), 1) AS filled

FROM readings

WHERE machine = 'cnc-04'

AND ts BETWEEN TIMESTAMP '2026-11-05 07:29:40' AND TIMESTAMP '2026-11-05 07:32:10'

ORDER BY ts;

-- 07:29:50 39.2 → 39.2 · 07:30:00 NULL → 39.3 · 07:31:50 NULL → 39.8 · 07:32:00 39.9 → 39.9

> stage 02 · machine_events

The machine log, words and codes

The log's text goes through the plant_text dictionary: lowercased words, numbers such as 4.5 kept whole. Alarm numbers are indexed whole, so a count per code reads the term dictionary.

sql

SELECT ts_lexize('plant_text', 'Spindle vibration above 4.5 mm/s');

-- {spindle,vibration,above,4.5,mm,s}

SELECT unnest(ts_dict_agg(code)) AS code, unnest(ts_dict_count(code)) AS events

FROM machine_events_idx;

-- RUNNING 6 · ALM-1150 2 · STOPPED 2 · ALM-2041 1 · ALM-2042 1 · ALM-3307 1, in no fixed order

> stage 02 · work_orders

Maintenance history, searchable

Every work order with its machine, kind, times and description. The history goes back to 2025, so a symptom search finds the earlier repairs of the same part.

sql

SELECT wo_id, opened_at::DATE AS opened, kind, description

FROM work_orders

WHERE machine = 'cnc-03'

ORDER BY opened_at;

-- WO-3105 | 2025-12-02 | corrective | Spindle front bearing pair replaced after vi…

-- WO-3620 | 2026-04-21 | corrective | Front bearing pair of the spindle replaced, …

-- WO-4471 | 2026-11-05 | corrective | Spindle front bearing pair replaced (7014 an…

> stage 03 · inverted indexes

What is searched and filtered is indexed

One inverted index per searched table: the text through a dictionary, machines, kinds and codes as exact terms, times as ranges. New rows become searchable at the next refresh, every second by default, or at once after VACUUM (REFRESH_TABLE). Readings need no index.

sql

CREATE TEXT SEARCH DICTIONARY plant_text AS

split_text(case := 'lower')

WITH (frequency, position, norm);

CREATE INDEX machine_events_idx ON machine_events

USING inverted (id, ts, machine, kind, code, message plant_text);

CREATE INDEX work_orders_idx ON work_orders

USING inverted (wo_id, machine, kind, opened_at, description plant_text)

INCLUDE (closed_at);

> stage 04 · aggregate

Time windows over the readings

time_bucket groups readings into hours, and FILTER splits one machine from the rest in the same pass. This is the lab's first question.

sql

SELECT time_bucket(INTERVAL '1 hour', ts) AS hour,

round(avg(vibration_mm_s) FILTER (WHERE machine = 'cnc-03'), 2) AS cnc_03,

round(avg(vibration_mm_s) FILTER (WHERE machine <> 'cnc-03'), 2) AS other_three,

round(max(spindle_temp_c) FILTER (WHERE machine = 'cnc-03'), 1) AS cnc_03_max_c

FROM readings

WHERE spindle_load > 0

GROUP BY hour ORDER BY hour;

> stage 04 · search

Search the log, ranked

Words in a query match any entry that has them, and BM25 ranks the entries that fit best first. ts_levenshtein matches within an edit distance, for the typos in operator notes.

sql

SELECT id, machine, message, round(BM25(machine_events_idx.tableoid), 3) AS score

FROM machine_events_idx

WHERE message @@ 'bearing noise'

ORDER BY score DESC, id

LIMIT 3;

-- 12 · Grinding noise from the spindle nose, front bearing suspec… · 1.253

-- 8 · Spindel noise louder, vibraton trending up on the HMI · 0.828

-- 10 · Spindle bearing temperature above 65 C · 0.827

> stage 04 · line up

Each alarm, with the reading at that moment

ASOF JOIN pairs every alarm with the last reading of the same machine at or before it, so events and readings need no shared clock.

sql

SELECT e.ts::TIME AS alarm_at, e.machine, e.code,

r.ts::TIME AS reading_at, r.vibration_mm_s, r.spindle_temp_c

FROM machine_events e

ASOF JOIN readings r ON r.machine = e.machine AND e.ts >= r.ts

WHERE e.kind = 'alarm'

ORDER BY e.ts;

> stage 04 · join

Work orders next to the readings

A work order's times bound a range join on the readings: the hour before it opened and the hour after it closed, while the spindle turned.

sql

SELECT w.wo_id, w.machine,

round(avg(r.vibration_mm_s) FILTER (WHERE r.ts < w.opened_at), 2) AS mm_s_before,

round(avg(r.vibration_mm_s) FILTER (WHERE r.ts > w.closed_at), 2) AS mm_s_after,

round(max(r.spindle_temp_c) FILTER (WHERE r.ts < w.opened_at), 1) AS max_c_before,

round(max(r.spindle_temp_c) FILTER (WHERE r.ts > w.closed_at), 1) AS max_c_after

FROM work_orders w

JOIN readings r ON r.machine = w.machine

AND r.ts BETWEEN w.opened_at - INTERVAL '1 hour' AND w.closed_at + INTERVAL '1 hour'

AND r.spindle_load > 0

WHERE w.kind = 'corrective' AND w.opened_at >= TIMESTAMP '2026-11-05 00:00:00'

GROUP BY ALL ORDER BY w.wo_id;

> stage 05 · dashboards

Grafana panels in SQL

Add SereneDB as a PostgreSQL data source on port 7890 and write panels in raw SQL. Grafana replaces its time macros before sending; the statement below is what reached SereneDB.

sql

-- the panel, as written in Grafana:

-- SELECT $__timeGroupAlias(ts, '5m'), machine, round(avg(vibration_mm_s), 2) …

-- FROM readings WHERE $__timeFilter(ts) AND machine IN ('cnc-03', 'cnc-04') …

-- what SereneDB receives for the range 10:00–10:15:

SELECT floor(extract(epoch from ts)/300)*300 AS "time", machine,

round(avg(vibration_mm_s), 2) AS vibration_mm_s

FROM readings

WHERE ts BETWEEN '2026-11-05T10:00:00Z' AND '2026-11-05T10:15:00Z'

AND machine IN ('cnc-03', 'cnc-04')

GROUP BY 1, 2 ORDER BY 1, 2;

-- time is epoch seconds, 1793872800 = 10:00 UTC; one row per machine every 5 minutes

-- cnc-03 3.96 4.22 4.43 4.52 · cnc-04 1.02 1.01 0.98 1.15

> stage 06 · history

Old readings to Parquet, by a job

COPY … TO with PARTITION_BY writes one folder of Parquet files per day, and read_parquet queries them in place. With an S3 secret the same COPY writes to a bucket. A job does it daily and deletes what it moved.

sql

COPY (SELECT *, ts::DATE AS day FROM readings

WHERE ts < TIMESTAMP '2026-11-05 10:00:00')

TO '/tmp/readings_history' (FORMAT parquet, PARTITION_BY (day), APPEND);

SELECT machine, count(*) AS readings, max(vibration_mm_s) AS max_mm_s

FROM read_parquet('/tmp/readings_history/*/*.parquet', hive_partitioning = true)

WHERE day = DATE '2026-11-05'

GROUP BY machine ORDER BY machine;

-- COPY 5760, then 1440 readings per machine; cnc-03's highest before 10:00, 3.99 mm/s

CREATE JOB readings_history EVERY 1 DAY OFFSET 2 HOURS AS $$

COPY (SELECT *, ts::DATE AS day FROM readings

WHERE ts < current_date - INTERVAL '90 days')

TO '/tmp/readings_history' (FORMAT parquet, PARTITION_BY (day), APPEND);

DELETE FROM readings WHERE ts < current_date - INTERVAL '90 days';

$$;

EXECUTE JOB readings_history;

SELECT job_name, last_status FROM sdb_jobs();

-- readings_history | success

What machine data needs from one engine

readings · machine_events · work_orders

rows

Readings are plain rows

No special table type: a reading is a row, and an hour of a machine is an aggregate over its rows.

SELECT count(*) FROM readings

codes

Alarm codes are exact terms

ALM-2041 is indexed whole, so a count per alarm number is a dictionary read.

ts_dict_count(code)

typos

Typos still match

Operator notes match within an edit distance, and BM25 puts the closest notes first.

ts_levenshtein('spindle', 1)

searchbench

A billion log rows

SearchBench, 92 queries over 1B OpenTelemetry log rows: all 92 under the 60 s cap, median 31.0 ms.

otel_logs_1b · 26.09.1

SearchBench on otel_logs_1b →

Build it

Tutorial: analyze sensor data and machine logs in SQL, in five steps

From an empty server to the repair that fixed a machine: start SereneDB, create the tables, load one shift, find the drifting machine and search its log, then line up its alarms with the readings and check the repair.

step 01 / 05 · shell

docker run -d --name serenedb -p 7890:7890 \
  -e POSTGRES_PASSWORD=<password> serenedb/serenedb:26.11.2

psql -h localhost -p 7890 -U postgres

Any PostgreSQL client connects the same way, on port 7890.

step 02 / 05 · sql

CREATE TABLE readings (
    machine        VARCHAR,
    ts             TIMESTAMP,
    spindle_temp_c DOUBLE,
    vibration_mm_s DOUBLE,
    spindle_load   DOUBLE     -- percent of rated load
);

CREATE TABLE machine_events (
    id      INTEGER,
    ts      TIMESTAMP,
    machine VARCHAR,
    kind    VARCHAR,          -- state, alarm or note
    code    VARCHAR,          -- RUNNING or STOPPED, or the alarm number
    message VARCHAR
);

CREATE TABLE work_orders (
    wo_id       VARCHAR,
    machine     VARCHAR,
    kind        VARCHAR,      -- corrective or preventive
    opened_at   TIMESTAMP,
    closed_at   TIMESTAMP,
    description VARCHAR
);

CREATE TEXT SEARCH DICTIONARY plant_text AS
    split_text(case := 'lower')
    WITH (frequency, position, norm);

CREATE INDEX machine_events_idx ON machine_events
    USING inverted (id, ts, machine, kind, code, message plant_text);

CREATE INDEX work_orders_idx ON work_orders
    USING inverted (wo_id, machine, kind, opened_at, description plant_text)
    INCLUDE (closed_at);

CREATE TABLE three times, then the dictionary and the two indexes. plant_text lowercases and splits the text into words and keeps numbers such as 4.5 whole.

step 03 / 05 · sql

-- One simulated shift, 2026-11-05 06:00 to 14:00: four machines, a reading
-- every 10 seconds. cnc-03's spindle bearing wears from 08:00 until the
-- machine is stopped at 11:02; it runs again at 12:38 with a new bearing.
-- cnc-02 stops for a tool change from 11:30 to 11:41, and cnc-04's
-- temperature sensor drops out from 07:30 to 07:32.
INSERT INTO readings
SELECT machine, ts,
       round(CASE WHEN machine = 'cnc-04' AND h >= 1.5 AND h < 1.5333 THEN NULL
                  WHEN machine = 'cnc-03' AND stopped THEN 26 + 43 * exp(-4 * (h - 5.0333))
                  WHEN machine = 'cnc-03' AND h >= 6.6333 THEN temp - 14 * exp(-5 * (h - 6.6333)) + 0.8 * noise
                  WHEN stopped THEN temp - 3
                  ELSE temp + 27 * wear + 0.8 * noise END, 1),
       round(CASE WHEN stopped THEN 0.1
                  WHEN machine = 'cnc-03' AND h >= 6.6333 THEN 1.1 + 0.15 * noise
                  ELSE vib + 5.9 * wear + 0.15 * noise END, 2),
       CASE WHEN stopped THEN 0 ELSE round(64 + 6 * noise) END
FROM (
    SELECT *,
           sin(h * 97 + k) * cos(h * 41 + 2 * k) AS noise,
           CASE WHEN machine = 'cnc-03' AND h >= 2 AND h < 5.0333
                THEN power((h - 2) / 3.0333, 2) ELSE 0 END AS wear,
           (machine = 'cnc-03' AND h >= 5.0333 AND h < 6.6333)
             OR (machine = 'cnc-02' AND h >= 5.5056 AND h < 5.6972) AS stopped
    FROM (
        SELECT m.machine, m.k, m.temp, m.vib, g.ts,
               date_diff('second', TIMESTAMP '2026-11-05 06:00:00', g.ts) / 3600.0 AS h
        FROM (VALUES ('cnc-01', 1, 41.0, 1.2), ('cnc-02', 2, 43.0, 1.4),
                     ('cnc-03', 3, 42.0, 1.3), ('cnc-04', 4, 40.0, 1.0))
               AS m(machine, k, temp, vib),
             generate_series(TIMESTAMP '2026-11-05 06:00:00',
                             TIMESTAMP '2026-11-05 13:59:50',
                             INTERVAL '10 seconds') AS g(ts)
    )
);

SELECT count(*) FROM readings;

INSERT INTO machine_events VALUES
( 1, '2026-11-05 06:00:00', 'cnc-01', 'state', 'RUNNING',  'Cycle start, program O2201'),
( 2, '2026-11-05 06:00:00', 'cnc-02', 'state', 'RUNNING',  'Cycle start, program O3140'),
( 3, '2026-11-05 06:00:00', 'cnc-03', 'state', 'RUNNING',  'Cycle start, program O2201'),
( 4, '2026-11-05 06:00:00', 'cnc-04', 'state', 'RUNNING',  'Cycle start, program O1875'),
( 5, '2026-11-05 08:41:10', 'cnc-03', 'note',  NULL,       'Faint whine from the spindle above 9000 rpm, parts still in tolerance'),
( 6, '2026-11-05 09:12:40', 'cnc-01', 'alarm', 'ALM-1150', 'Coolant level low, tank B'),
( 7, '2026-11-05 09:20:05', 'cnc-01', 'note',  NULL,       'Coolant tank B topped up, alarm cleared'),
( 8, '2026-11-05 09:55:30', 'cnc-03', 'note',  NULL,       'Spindel noise louder, vibraton trending up on the HMI'),
( 9, '2026-11-05 10:13:02',           'cnc-03', 'alarm', 'ALM-2041', 'Spindle vibration above 4.5 mm/s'),
(10, '2026-11-05 10:46:22',           'cnc-03', 'alarm', 'ALM-2042', 'Spindle bearing temperature above 65 C'),
(11, '2026-11-05 11:02:00', 'cnc-03', 'state', 'STOPPED',  'Feed hold after spindle alarms ALM-2041 and ALM-2042'),
(12, '2026-11-05 11:03:40', 'cnc-03', 'note',  NULL,       'Grinding noise from the spindle nose, front bearing suspected. Machine stopped, maintenance called'),
(13, '2026-11-05 11:30:00', 'cnc-02', 'alarm', 'ALM-3307', 'Tool life expired: T12 end mill'),
(14, '2026-11-05 11:30:20', 'cnc-02', 'state', 'STOPPED',  'Tool change T12'),
(15, '2026-11-05 11:41:50', 'cnc-02', 'state', 'RUNNING',  'Cycle start after tool change'),
(16, '2026-11-05 12:31:15', 'cnc-03', 'note',  NULL,       'Front spindle bearing pair replaced, runout 2 um, test cut in tolerance'),
(17, '2026-11-05 12:38:00', 'cnc-03', 'state', 'RUNNING',  'Cycle start, program O2201'),
(18, '2026-11-05 13:15:30', 'cnc-04', 'alarm', 'ALM-1150', 'Coolant level low, tank D'),
(19, '2026-11-05 13:22:10', 'cnc-04', 'note',  NULL,       'Coolant tank D topped up, no leak found');

INSERT INTO work_orders VALUES
('WO-3105', 'cnc-03', 'corrective', '2025-12-02 07:40:00', '2025-12-02 10:05:00', 'Spindle front bearing pair replaced after vibration alarm ALM-2041'),
('WO-3388', 'cnc-01', 'preventive', '2026-01-15 06:10:00', '2026-01-15 08:00:00', 'Quarterly maintenance: way lube, coolant filter, spindle runout check'),
('WO-3620', 'cnc-03', 'corrective', '2026-04-21 13:20:00', '2026-04-21 16:05:00', 'Front bearing pair of the spindle replaced, temperature alarm ALM-2042 and grinding noise'),
('WO-3702', 'cnc-04', 'corrective', '2026-05-08 09:00:00', '2026-05-08 10:30:00', 'Coolant pump seal leaking, seal replaced'),
('WO-3951', 'cnc-02', 'corrective', '2026-07-30 11:15:00', '2026-07-30 12:00:00', 'Drawbar spring replaced, tool clamping force low'),
('WO-4120', 'cnc-01', 'corrective', '2026-09-03 08:30:00', '2026-09-03 12:45:00', 'X axis ballscrew support bearing replaced, backlash 12 um'),
('WO-4430', 'cnc-02', 'preventive', '2026-11-04 06:05:00', '2026-11-04 07:50:00', 'Quarterly maintenance: way lube, coolant filter, spindle runout check'),
('WO-4468', 'cnc-01', 'corrective', '2026-11-05 09:14:00', '2026-11-05 09:21:00', 'Coolant level low alarm ALM-1150, tank B topped up'),
('WO-4471', 'cnc-03', 'corrective', '2026-11-05 11:04:00', '2026-11-05 12:38:00', 'Spindle front bearing pair replaced (7014 angular contact), vibration alarm ALM-2041 and grinding noise'),
('WO-4475', 'cnc-04', 'corrective', '2026-11-05 13:16:00', '2026-11-05 13:23:00', 'Coolant level low alarm ALM-1150, tank D topped up, no leak found');

VACUUM (REFRESH_TABLE) machine_events;
VACUUM (REFRESH_TABLE) work_orders;
SELECT (SELECT count(*) FROM readings) AS readings,
       (SELECT count(*) FROM machine_events) AS events,
       (SELECT count(*) FROM work_orders) AS work_orders;

11,520 readings, 19 log entries and 10 work orders. VACUUM (REFRESH_TABLE) makes the new log entries and work orders searchable at once; the indexes otherwise refresh every second.

step 04 / 05 · sql

SELECT time_bucket(INTERVAL '1 hour', ts) AS hour,
       round(avg(vibration_mm_s) FILTER (WHERE machine = 'cnc-03'), 2) AS cnc_03,
       round(avg(vibration_mm_s) FILTER (WHERE machine <> 'cnc-03'), 2) AS other_three,
       round(max(spindle_temp_c) FILTER (WHERE machine = 'cnc-03'), 1) AS cnc_03_max_c
FROM readings
WHERE spindle_load > 0
GROUP BY hour ORDER BY hour;

SELECT ts::TIME AS t, message
FROM machine_events_idx
WHERE machine = 'cnc-03' AND kind = 'note'
  AND message @@ ts_levenshtein('spindle', 1)
ORDER BY ts;

cnc-03 climbs from 1.3 to 7.18 mm/s while the other three stay at 1.2. The search returns cnc-03's four notes on the spindle, the misspelled one included.

step 05 / 05 · sql

SELECT e.ts::TIME AS alarm_at, e.machine, e.code,
       r.ts::TIME AS reading_at, r.vibration_mm_s, r.spindle_temp_c
FROM machine_events e
ASOF JOIN readings r ON r.machine = e.machine AND e.ts >= r.ts
WHERE e.kind = 'alarm'
ORDER BY e.ts;

SELECT w.wo_id, w.machine,
       round(avg(r.vibration_mm_s) FILTER (WHERE r.ts < w.opened_at), 2) AS mm_s_before,
       round(avg(r.vibration_mm_s) FILTER (WHERE r.ts > w.closed_at), 2) AS mm_s_after,
       round(max(r.spindle_temp_c) FILTER (WHERE r.ts < w.opened_at), 1) AS max_c_before,
       round(max(r.spindle_temp_c) FILTER (WHERE r.ts > w.closed_at), 1) AS max_c_after
FROM work_orders w
JOIN readings r ON r.machine = w.machine
 AND r.ts BETWEEN w.opened_at - INTERVAL '1 hour' AND w.closed_at + INTERVAL '1 hour'
 AND r.spindle_load > 0
WHERE w.kind = 'corrective' AND w.opened_at >= TIMESTAMP '2026-11-05 00:00:00'
GROUP BY ALL ORDER BY w.wo_id;

CREATE JOB readings_history EVERY 1 DAY OFFSET 2 HOURS AS $$
    COPY (SELECT *, ts::DATE AS day FROM readings
          WHERE ts < current_date - INTERVAL '90 days')
    TO '/tmp/readings_history' (FORMAT parquet, PARTITION_BY (day), APPEND);
    DELETE FROM readings WHERE ts < current_date - INTERVAL '90 days';
$$;

ALM-2041 fired on a 4.52 mm/s reading and ALM-2042 at 65.1 °C. WO-4471 took cnc-03 from 5.52 to 1.1 mm/s. The job runs every day at 02:00 UTC and moves readings older than 90 days, so this shift stays in the table for now.

Use cases

What manufacturing teams build on SereneDB

Predictive maintenance: flag drift before the alarm

Each machine gets its own baseline, the average vibration of its first hour, and every 5-minute average after it is compared with it. cnc-03 passes 1.5 times its baseline at 09:00, 73 minutes before the controller raised ALM-2041. The rule is SQL, so the baseline window, the factor and the signal are yours to change.

built with

time_bucketWITH1.5 × baseline

Real-time analytics →

example · first 5 minutes over 1.5× baseline

WITH five AS (
    SELECT machine, time_bucket(INTERVAL '5 minutes', ts) AS t,
           avg(vibration_mm_s) AS mm_s
    FROM readings
    WHERE spindle_load > 0
    GROUP BY ALL
), baseline AS (
    SELECT machine, avg(mm_s) AS base
    FROM five
    WHERE t < TIMESTAMP '2026-11-05 07:00:00'
    GROUP BY machine
)
SELECT f.machine, round(b.base, 2) AS baseline_mm_s,
       min(f.t)::TIME AS first_over_1_5x
FROM five f JOIN baseline b USING (machine)
WHERE f.mm_s > 1.5 * b.base
GROUP BY ALL ORDER BY f.machine;

result

machine	baseline_mm_s	first_over_1_5x
cnc-03	1.3	09:00:00

Only cnc-03 crosses it. Its controller alarm, ALM-2041 at 4.5 mm/s, came at 10:13:02.

Availability, the first factor of OEE, from machine states

Availability is run time over planned production time. The machine log already holds every state change, and lead() turns them into stopped minutes per machine; the 480 minutes of the shift do the rest. OEE multiplies availability by performance and quality, which come from part counts and the ideal cycle time.

built with

lead()FILTER480-minute shift

OLAP database →

example · stopped minutes and availability

SELECT machine,
       round(coalesce(sum(minutes) FILTER (WHERE code = 'STOPPED'), 0), 1) AS stopped_min,
       round(100 * (480 - coalesce(sum(minutes) FILTER (WHERE code = 'STOPPED'), 0)) / 480, 1)
           AS availability_pct
FROM (
    SELECT machine, code,
           date_diff('second', ts, lead(ts, 1, TIMESTAMP '2026-11-05 14:00:00')
                     OVER (PARTITION BY machine ORDER BY ts)) / 60.0 AS minutes
    FROM machine_events
    WHERE kind = 'state'
)
GROUP BY machine ORDER BY machine;

result

machine	stopped_min	availability_pct
cnc-01	0	100
cnc-02	11.5	97.6
cnc-03	96	80
cnc-04	0	100

cnc-03 stood 96 minutes for its bearing and ran 80 % of the shift; cnc-02's tool change cost 11.5 minutes.

Has this failed before? Search work orders by symptom

Maintenance history is text: what failed, what was replaced, why. A phrase search over the work orders' descriptions finds every replacement of the same part, and the aggregate around it gives the interval between them, a figure to plan the next one by.

built with

ts_phrasedate_diffGROUP BY

Full-text search →

example · front bearing replacements

SELECT machine, count(*) AS replacements,
       min(opened_at)::DATE AS first, max(opened_at)::DATE AS last,
       round(date_diff('day', min(opened_at), max(opened_at)) / (count(*) - 1)) AS days_between
FROM work_orders_idx
WHERE description @@ ts_phrase('front bearing')
GROUP BY machine;

result

machine	replacements	first	last	days_between
cnc-03	3	2025-12-02	2026-11-05	169

Three front-bearing replacements on cnc-03 since December 2025, 169 days apart on average. The X axis's support bearing on cnc-01 is not a front bearing, so the phrase leaves it out.

FAQ

Frequently asked questions

Go to docs →

An IoT analytics platform collects the data connected devices and machines send, stores it and turns it into answers: dashboards, alerts, reports and predictions. Its parts are ingestion from the devices, storage and query, and the applications on top. SereneDB is the storage and query part, in SQL.

Machines and sensors report to a gateway, which batches the data and sends it on; a database stores the readings and events and answers queries; dashboards, alert rules and models read from it. With SereneDB the gateway writes over the PostgreSQL protocol, readings, the machine log and work orders are three tables, and the architecture above follows them from ingest to a Grafana panel.

Predictive maintenance from vibration and temperature trends, availability and OEE from machine states, alarm analysis that lines up events with readings, and maintenance planning from work-order history. The examples above are each one SQL query over the same shift.

Store time-stamped readings and aggregate them over time windows, and answer the questions the readings raise: what the machine log says, which work order touched the machine, what the sensors read when an alarm fired. SereneDB does all of it in one SQL engine; the checklist above maps each need to its feature and docs.

One row per reading: the machine or device, the timestamp and one column per measured value, as in step 2. Queries group the rows with time_bucket and filter by machine. Only the tables you search, such as the machine log, need an inverted index.

Index the log's text and codes with an inverted index, then search in SQL: exact alarm numbers, words ranked by BM25, phrases, and matches within an edit distance for typos. Each hit is a row, so it can be counted per machine, grouped by hour or joined to the readings.

A failing part shows in the data before it stops the machine: a worn bearing raises vibration and temperature for hours. Comparing each machine with its own baseline, as in the predictive maintenance example above, flagged cnc-03 73 minutes before its vibration alarm.

Availability is run time over planned production time. From a log of state changes, lead() gives each change the time of the next one, so stopped minutes are a sum: cnc-03's 96 stopped minutes over a 480-minute shift give 80 %. OEE multiplies availability by performance and quality.

With an ASOF JOIN from the alarms to the readings, on the machine and the time. Each alarm gets the last reading at or before it, even when the controller and the sensor gateway stamp different times; the lab's "At the alarm" question runs it.

As long as you choose: readings stay in their table until deleted. A scheduled job can copy the readings past a period to Parquet files partitioned by day and delete them, and read_parquet still queries those files, on disk or in S3.

Yes. Grafana's built-in PostgreSQL data source connects to SereneDB on port 7890, and each panel is a SQL query. Grafana expands macros such as $__timeFilter into plain SQL before sending it.

Yes, under the Apache-2.0 license, with its source on GitHub. Its Docker image runs the walkthrough above with one command.

> get started

Get started with SereneDB

Start SereneDB, load your readings and machine log, and query them in SQL from Grafana or any PostgreSQL client.

Get Started Time functions docs →

> Search

> Analytics & data

> AI & agents

> Security & operations

> Commerce & media

> Finance, health & industry

> Search engines

> Postgres & OLAP

> Vector

IoT analytics platform: sensor data and machine logs in one SQL engine

What does an IoT analytics platform do? Five questions about one shift on a machining line

What an IoT analytics platform needs from its database, and how SereneDB does it in SQL

IoT analytics architecture: readings, machine logs and work orders in one engine

Readings arrive over the PostgreSQL protocol

Alarms, states, notes and work orders as rows

A reading is a row

The machine log, words and codes

Maintenance history, searchable

What is searched and filtered is indexed

Time windows over the readings

Search the log, ranked

Each alarm, with the reading at that moment

Work orders next to the readings

Grafana panels in SQL

Old readings to Parquet, by a job

What machine data needs from one engine

Tutorial: analyze sensor data and machine logs in SQL, in five steps

What manufacturing teams build on SereneDB

Predictive maintenance: flag drift before the alarm

Availability, the first factor of OEE, from machine states

Has this failed before? Search work orders by symptom

Frequently asked questions

Get started with SereneDB

IoT analytics platform: sensor data and machine logs in one SQL engine

What does an IoT analytics platform do? Five questions about one shift on a machining line

What an IoT analytics platform needs from its database, and how SereneDB does it in SQL

IoT analytics architecture: readings, machine logs and work orders in one engine

Readings arrive over the PostgreSQL protocol

Alarms, states, notes and work orders as rows

A reading is a row

The machine log, words and codes

Maintenance history, searchable

What is searched and filtered is indexed

Time windows over the readings

Search the log, ranked

Each alarm, with the reading at that moment

Work orders next to the readings

Grafana panels in SQL

Old readings to Parquet, by a job

What machine data needs from one engine

Tutorial: analyze sensor data and machine logs in SQL, in five steps

What manufacturing teams build on SereneDB

Predictive maintenance: flag drift before the alarm

Availability, the first factor of OEE, from machine states

Has this failed before? Search work orders by symptom

Frequently asked questions

What is an IoT analytics platform?

What does an IoT analytics architecture look like?

What are examples of IoT analytics in manufacturing?

What should a database for IoT sensor data do?

How do I store sensor data in SQL?

How do I search machine logs and operator notes?

How does IoT data predict machine failures?

How do I calculate machine availability for OEE in SQL?

How do I find what a sensor read when an alarm fired?

How long can I keep sensor data in SereneDB?

Can Grafana show data from SereneDB?

Is SereneDB open source?

Get started with SereneDB