PostgreSQL Anomaly Detection: Predicting Machine Degradation with SQL

Move from reactive to proactive maintenance. Use PostgreSQL time-series analysis, moving averages and statistical anomaly detection to detect abnormal machine behavior early, before failures occur.

From Root Cause Analysis to Early Detection

In the previous article, we investigated Line L02 and identified M005 as the strongest root-cause candidate.

We found three important signals:

• M005 had the highest reject rate among the L02 machines.
• It accumulated the highest absolute downtime.
• Five SPINDLE-VIB incidents accounted for 372 minutes of downtime before a corrective spindle-bearing replacement.

The database even records the corrective intervention on April 28, 2026: spindle bearing replacement after repeated vibration alarms.

But this creates a more interesting industrial question:

Could we have detected that M005 was deteriorating before the failure became obvious?

This is the transition from descriptive analytics to anomaly detection.

---

1. The Goal: Detect Degradation Before Failure

A traditional maintenance workflow is reactive:

Failure → downtime → diagnosis → repair.

A data-driven workflow tries to move the intervention earlier:

Normal behavior → deviation → anomaly → investigation → maintenance.

Our dataset is particularly useful for this exercise because it contains a coherent industrial story: the data includes production, downtime, maintenance, quality and energy measurements, and the product documentation explicitly describes progressive deterioration on a Line 2 machine before maintenance intervention.

We will therefore use M005 as our case study and investigate its behavior month by month.

---

2. Establishing the Normal Baseline

Before detecting an anomaly, we need to define what normal looks like.

For this educational example, we will use January and February as the initial baseline period.

We will monitor four indicators:

• average cycle time;
• reject rate;
• downtime;
• average power.

The database contains the required production events, downtime events and energy measurements linked to machines.

SELECT
    DATE_TRUNC('month', event_timestamp)::date AS month,
    SUM(quantity_produced) AS produced_units,
    SUM(quantity_rejected) AS rejected_units,
    ROUND(AVG(cycle_time_sec), 2) AS avg_cycle_time_sec,
    ROUND(
        100.0 * SUM(quantity_rejected)
        / NULLIF(SUM(quantity_produced), 0),
        2
    ) AS reject_rate_pct
FROM smart_factory.production_events
WHERE machine_id = (
    SELECT machine_id
    FROM smart_factory.machines
    WHERE machine_code = 'M005'
)
GROUP BY DATE_TRUNC('month', event_timestamp)
ORDER BY month;

Real result: M005 production evolution

Month	Produced	Rejected	Reject Rate	Avg Cycle
Jan	9,770	223	2.28%	76.84 s
Feb	9,623	230	2.39%	66.86 s
Mar	9,721	457	4.70%	82.83 s
Apr	10,567	737	6.97%	85.85 s
May	9,490	429	4.52%	76.83 s
Jun	11,791	279	2.37%	78.16 s

There is already a very strong signal.

The reject rate rises from roughly 2.3% in January-February to 4.70% in March and peaks at 6.97% in April.

Average cycle time follows the same general deterioration, reaching 85.85 seconds in April.

---

3. A Simple Moving Average

Raw industrial measurements are noisy.

A single production event can be unusually fast or slow. A moving average helps us distinguish short-term noise from a persistent change in behavior.

PostgreSQL window functions are ideal for this.

WITH daily_metrics AS (
    SELECT
        DATE_TRUNC('day', event_timestamp)::date AS day,
        AVG(cycle_time_sec) AS avg_cycle_time
    FROM smart_factory.production_events
    WHERE machine_id = (
        SELECT machine_id
        FROM smart_factory.machines
        WHERE machine_code = 'M005'
    )
    GROUP BY DATE_TRUNC('day', event_timestamp)
)

SELECT
    day,
    ROUND(avg_cycle_time, 2) AS avg_cycle_time,
    ROUND(
        AVG(avg_cycle_time) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7d
FROM daily_metrics
ORDER BY day;

The important concept is:

The moving average gives us a dynamic reference for recent machine behavior.

If the daily cycle time rises while the moving average also rises for several consecutive days, we have stronger evidence of a persistent change.

---

4. The First Warning Sign: Cycle Time

Let's compare the first two months against the following months.

Period	Avg Cycle	Change vs Jan-Feb Baseline
Jan-Feb baseline	71.85 s	—
Mar	82.83 s	+15.28%
Apr	85.85 s	+19.48%
May	76.83 s	+6.93%
Jun	78.16 s	+8.78%

March is already interesting.

Before the major April deterioration, the average cycle time had already moved approximately 15% above the initial baseline.

That is exactly the kind of signal an anomaly-detection system should surface.

---

5. Reject Rate: A Second Independent Signal

Now let's test whether quality tells the same story.

The January-February baseline reject rate is approximately 2.34%.

Month	Reject Rate	Change vs Baseline	Interpretation
Jan	2.28%	-2.3%	Normal
Feb	2.39%	+2.3%	Normal
Mar	4.70%	+101.2%	Strong anomaly
Apr	6.97%	+198.5%	Severe anomaly
May	4.52%	+93.5%	Still abnormal
Jun	2.37%	+1.3%	Near baseline

This is much stronger than the cycle-time signal alone.

Two independent indicators deteriorate at approximately the same point in time.

---

6. Downtime Confirms the Pattern

Now we add availability.

SELECT
    DATE_TRUNC('month', start_time)::date AS month,
    COUNT(*) AS downtime_events,
    ROUND(
        SUM(
            EXTRACT(EPOCH FROM (end_time - start_time))
        ) / 60.0,
        2
    ) AS downtime_minutes
FROM smart_factory.downtime_events
WHERE machine_id = (
    SELECT machine_id
    FROM smart_factory.machines
    WHERE machine_code = 'M005'
)
GROUP BY DATE_TRUNC('month', start_time)
ORDER BY month;

Month	Downtime Events	Downtime Minutes
Jan	2	49
Feb	0	0
Mar	3	173
Apr	3	237
May	1	38
Jun	1	14

January and February establish a relatively quiet baseline.

Then downtime jumps to 173 minutes in March and 237 minutes in April.

Again, the signal appears before the April 28 corrective intervention.

---

7. Energy Provides a Fourth Signal

Machine degradation does not necessarily appear only in production quality or downtime.

Energy behavior can also change.

Month	Energy	Avg Power	Power vs Baseline
Jan	7,577.738 kWh	28.31 kW	+0.6%
Feb	6,969.295 kWh	27.95 kW	-0.6%
Mar	9,165.211 kWh	34.47 kW	+22.5%
Apr	8,712.558 kWh	33.68 kW	+19.7%
May	8,507.579 kWh	31.41 kW	+11.7%
Jun	7,538.018 kWh	28.78 kW	+2.3%

Average power rises from a January-February baseline of approximately 28.13 kW to 34.47 kW in March.

We now have four independent signals:

cycle time ↑ + reject rate ↑ + downtime ↑ + power ↑

That is much more convincing than one abnormal measurement.

---

8. Statistical Anomaly Detection with Z-Scores

We can formalize the idea with a Z-score.

The basic formula is:

Z = (value - mean) / standard_deviation

A common educational rule is:

• |Z| < 2 → usually within expected variation;
• |Z| ≥ 2 → investigate;
• |Z| ≥ 3 → strong anomaly signal.

These thresholds are not universal industrial standards. They are a practical starting point for this tutorial.

Let's calculate a baseline from January-February and compare later months.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', event_timestamp)::date AS month,
        AVG(cycle_time_sec) AS avg_cycle_time
    FROM smart_factory.production_events
    WHERE machine_id = (
        SELECT machine_id
        FROM smart_factory.machines
        WHERE machine_code = 'M005'
    )
    GROUP BY DATE_TRUNC('month', event_timestamp)
),

baseline AS (
    SELECT
        AVG(avg_cycle_time) AS mean_cycle_time,
        STDDEV_SAMP(avg_cycle_time) AS std_cycle_time
    FROM monthly
    WHERE month < DATE '2026-03-01'
)

SELECT
    m.month,
    ROUND(m.avg_cycle_time, 2) AS avg_cycle_time,
    ROUND(b.mean_cycle_time, 2) AS baseline_mean,
    ROUND(b.std_cycle_time, 2) AS baseline_std,
    ROUND(
        (m.avg_cycle_time - b.mean_cycle_time)
        / NULLIF(b.std_cycle_time, 0),
        2
    ) AS z_score
FROM monthly m
CROSS JOIN baseline b
ORDER BY m.month;

Real result: cycle-time anomaly score

Month	Avg Cycle	Z-Score	Signal
Jan	76.84 s	+0.71	Normal
Feb	66.86 s	-0.71	Normal
Mar	82.83 s	+1.56	Warning
Apr	85.85 s	+1.98	Borderline anomaly
May	76.83 s	+0.71	Normalizing
Jun	78.16 s	+0.89	Normalizing

The cycle-time Z-score alone does not cross the strict +2 threshold.

And this is an important lesson.

One statistical indicator can miss a real industrial problem.

The anomaly becomes much clearer when several indicators move together.

---

9. Multi-Signal Detection Is Stronger

We can therefore create a simple diagnostic score based on independent signals.

Signal	March	April	Interpretation
Reject rate	4.70%	6.97%	Strong degradation
Cycle time	82.83 s	85.85 s	Slower process
Downtime	173 min	237 min	Availability deterioration
Average power	34.47 kW	33.68 kW	Energy behavior changed

This is the key insight:

March is not just a month with a bad KPI. It is the beginning of a multi-dimensional change in machine behavior.

---

10. The Maintenance Intervention Gives Us a Natural Experiment

On April 28, 2026, M005 received corrective maintenance for repeated vibration alarms and a spindle bearing replacement.

The maintenance record is explicit in the database.

After the intervention, the data shows a strong normalization:

Metric	April	May	June
Reject rate	6.97%	4.52%	2.37%
Avg cycle	85.85 s	76.83 s	78.16 s
Downtime	237 min	38 min	14 min
Avg power	33.68 kW	31.41 kW	28.78 kW

We should not claim that SQL has mathematically proven causality.

However, the temporal relationship is compelling:

degradation → repeated vibration failures → corrective maintenance → multi-KPI normalization.

---

11. Turning the Detection Logic into SQL

We can now build a reusable anomaly-detection pattern.

WITH monthly_metrics AS (

    SELECT
        DATE_TRUNC('month', event_timestamp)::date AS month,

        AVG(cycle_time_sec) AS avg_cycle_time,

        SUM(quantity_rejected)::numeric
        / NULLIF(SUM(quantity_produced), 0)
        * 100 AS reject_rate_pct

    FROM smart_factory.production_events

    WHERE machine_id = (
        SELECT machine_id
        FROM smart_factory.machines
        WHERE machine_code = 'M005'
    )

    GROUP BY DATE_TRUNC('month', event_timestamp)
),

baseline AS (

    SELECT
        AVG(avg_cycle_time) AS cycle_mean,
        STDDEV_SAMP(avg_cycle_time) AS cycle_std,

        AVG(reject_rate_pct) AS reject_mean,
        STDDEV_SAMP(reject_rate_pct) AS reject_std

    FROM monthly_metrics

    WHERE month < DATE '2026-03-01'
)

SELECT
    m.month,

    ROUND(m.avg_cycle_time, 2)
        AS avg_cycle_time,

    ROUND(
        (m.avg_cycle_time - b.cycle_mean)
        / NULLIF(b.cycle_std, 0),
        2
    ) AS cycle_z_score,

    ROUND(m.reject_rate_pct, 2)
        AS reject_rate_pct,

    ROUND(
        (m.reject_rate_pct - b.reject_mean)
        / NULLIF(b.reject_std, 0),
        2
    ) AS reject_z_score

FROM monthly_metrics m
CROSS JOIN baseline b

ORDER BY m.month;

This pattern is reusable.

You can replace M005 with another machine, change the baseline period, or calculate the metrics weekly instead of monthly.

---

12. What We Actually Learned

At the beginning of the series, we were asking:

Which machine is causing the problem?

Now we can ask a much more advanced question:

When did the machine start behaving differently from its historical baseline?

For M005, the answer is visible in several dimensions:

• cycle time rises substantially in March;
• reject rate more than doubles in March and peaks in April;
• downtime jumps from a quiet baseline to 173 minutes in March and 237 minutes in April;
• average power increases by more than 20% in March;
• repeated spindle-vibration failures appear during the degradation period;
• the machine receives corrective spindle-bearing maintenance on April 28;
• the major KPIs move back toward normal levels afterwards.

That is much closer to the reasoning used in real industrial analytics.

---

13. SQL Is Not Predictive Maintenance — Yet

There is an important distinction.

What we have built is anomaly detection.

We are detecting unusual behavior using historical data.

Predictive maintenance goes one step further:

Can we estimate the probability that a failure will occur within a future time window?

That requires additional techniques such as:

• feature engineering;
• rolling statistics;
• lag variables;
• failure labels;
• classification models;
• survival analysis;
• machine-learning models.

But PostgreSQL can already perform a surprising amount of the preparation.

---

14. The Industrial Analytics Pattern

We have now built a complete progression:

Raw events → Time series → Baseline → Moving average → Anomaly score → Multiple signals → Maintenance decision

This is the point where SQL starts becoming an industrial analytics tool rather than simply a database language.

---

15. The Investigation Continues

We know how to detect abnormal machine behavior.

But there is still a gap.

An anomaly tells us:

"Something unusual is happening."

It does not automatically tell us:

"Which exact condition is responsible?"

For that, we need to combine more complex filtering logic with subqueries and existence tests.

In the next article, we will investigate subqueries, EXISTS and advanced filtering patterns to answer questions such as:

• Which machines have experienced repeated failures?
• Which production orders were affected by downtime?
• Which machines had quality problems after abnormal events?
• Which machines have maintenance history but no recent preventive intervention?

Next: PostgreSQL Subqueries & EXISTS — Finding Hidden Relationships in Industrial Data.

---

Ready to work with the complete Smart Factory dataset?

The Smart Factory Manufacturing Dataset v0.1 contains the complete synthetic manufacturing environment used throughout this SQL series.

Included:

• PostgreSQL 17 database dump
• 1,143 production orders
• 17,167 production events
• 82 downtime events
• 8 maintenance events
• 1,143 quality inspections
• 2,172 energy measurements
• 3 production lines and 12 machines
• Data Dictionary and database schema
• Starter SQL queries
• Industrial investigation scenario

Smart Factory Manufacturing Dataset v0.1 — $9

→ Get the complete dataset on Gumroad

---

Practice this investigation yourself. The complete Smart Factory Manufacturing Dataset contains the PostgreSQL database, CSV reference files, schema documentation, data dictionary and industrial investigation scenario.