Anomaly detection inside the database

Analytical databases already hold the rows used to detect outliers. In many systems, however, the detection itself happens somewhere else.

A common workflow queries a warehouse, moves the result into a DataFrame, fits a model in Python and writes the scores back. That is often the right arrangement for exploratory model development. It is less attractive when a stable model must score the same database repeatedly: the application then owns another runtime, another copy of the data and another boundary to operate.

Stratum therefore includes a native, SIMD-accelerated isolation forest in its query engine. A SQL client can train a model and use it in later queries without calling a Python worker.

SELECT * FROM transactions
WHERE ANOMALY_SCORE('fraud_model') > 0.7;

For an in-process query, no intermediate DataFrame is serialized. The query planner can also apply ordinary filters and chunk pruning before scoring. In the benchmark configuration described below, one row took 6 microseconds and a batch of 1,000 rows took 1.6 milliseconds. Those measurements make synchronous scoring plausible, but production latency still includes ingestion, query planning and the surrounding application.

Why isolation forests

Many SQL examples use a z-score: (value - AVG(value)) / STDDEV(value) > 3. That can be useful for a roughly Gaussian single variable, but it does not describe interactions between several features.

Many anomalies are multivariate. A transaction amount or frequency may each look ordinary in isolation while their combination is unusual for one account. Detecting that case requires a model that considers several features together.

Isolation forests (Liu, Ting & Zhou, 2008) take a different approach. Instead of modeling what “normal” looks like (a density estimate, a distribution fit, a cluster boundary), they directly measure how easy it is to isolate a point from everything else. Build a tree of random splits across random features. Anomalous points, being few and different, get isolated in fewer splits. Normal points, packed into dense regions, require many splits to separate.

Several properties make the algorithm a useful fit for a columnar engine.

Isolation forests are non-parametric and do not require a Gaussian distribution. They are not assumption-free: feature selection, scaling, sample size, tree count and the contamination threshold still affect the result.

Each tree is trained on a random subsample. With the configuration measured here, 100 trees use 256 rows each, so the tree-building phase examines 25,600 sampled rows. Sampling those rows and preparing input still depends on the surrounding query.

Scoring is linear in the number of rows and trees. Stratum packs each tree node into a long, traverses it without pointer chasing and divides rows into morsels for parallel execution. The implementation details are covered below.

Each split selects a feature, so the ensemble can capture cross-feature interactions without a hand-written rule for every combination.

Training does not require anomaly labels. That is useful when labelled examples are scarce, but it also means a high score identifies statistical isolation rather than proving fraud, failure or bad data.

What other analytical databases offer

As of April 2026, the nearby database options covered different parts of this problem:

DuckDB itself did not provide a persistent isolation-forest model. The third-party anofox-tabular extension offered a broader family of algorithms, including Extended Isolation Forest and SCiForest. Its execution and model lifecycle differed from Stratum’s packed, persistent model. We did not run a head-to-head benchmark, so this article makes no performance claim against it.

ClickHouse provided seriesOutliersDetectTukey, a univariate IQR method for time-series data. Cloudflare used ClickHouse in its anomaly-detection platform while running its HBOS detection logic in separate services.

TimescaleDB has an open issue proposing ARIMA and DBSCAN anomaly detection. It remains unimplemented.

PostgreSQL users could also install MADlib for a much broader in-database machine-learning surface.

These are not interchangeable products. Stratum’s narrower choice is to keep one persistent isolation-forest implementation close to its columnar data and SQL planner.

The cost of exporting

Moving data to a separate model runtime has costs beyond the extra application code.

Serialization, transfer and write-back add latency. The amount depends on the database driver, row shape and deployment, so it should be measured rather than assumed.

An external worker may also hold a second materialized representation of the selected rows. Large inputs then require more memory or explicit batching.

The model service, numerical libraries and database client become another deployment to version, observe and coordinate. That cost may be justified when the service hosts several models or needs Python’s ecosystem.

Data copied into another process also enters that process’s access-control, encryption and audit boundary.

Keeping scoring in the query engine lets ordinary planning happen first. Filters and zone maps can reduce the rows presented to the model, and the scores can feed later SQL operators without a write-back step.

How it works in Stratum

SQL interface

Stratum speaks the PostgreSQL wire protocol. Connect with psql, DBeaver, JDBC, or any PostgreSQL client, then train and query models entirely from SQL:

-- Train a model directly from SQL
CREATE MODEL fraud_model
  TYPE ISOLATION_FOREST
  OPTIONS (n_trees = 200, sample_size = 256, contamination = 0.05)
  AS SELECT amount, freq FROM transactions;

The AS SELECT query defines the training data, and any valid SELECT works, including WHERE filters and JOINs. Column names become the model’s feature names. Once created, the model remembers its features, so you don’t need to repeat them:

-- Short form: model knows its features from training
SELECT *, ANOMALY_SCORE('fraud_model') AS score
FROM transactions;

-- All four functions support both forms
SELECT *, ANOMALY_PREDICT('fraud_model') AS is_anomaly FROM transactions;
SELECT *, ANOMALY_PROBA('fraud_model') AS prob FROM transactions;
SELECT *, ANOMALY_CONFIDENCE('fraud_model') AS conf FROM transactions;

Need to score on different columns, computed expressions, or join results? Use the long form with explicit arguments (mapped positionally to the model’s features):

-- Explicit columns
SELECT *, ANOMALY_SCORE('fraud_model', amount, freq) AS score
FROM transactions;

-- Score on expressions
SELECT *, ANOMALY_SCORE('fraud_model', amount * 100, LOG(freq)) AS score
FROM transactions;

-- Score across JOINs
SELECT t.*, ANOMALY_SCORE('fraud_model', t.amount, r.rate) AS score
FROM transactions t JOIN rates r ON t.currency = r.code;

Model management is also SQL-native:

SHOW MODELS;                    -- list all registered models
DESCRIBE MODEL fraud_model;     -- features, hyperparameters, threshold
DROP MODEL fraud_model;         -- remove a model
DROP MODEL IF EXISTS old_model; -- remove only if it exists

The anomaly functions look and compose like any other SQL expression: filter on them, aggregate them, join them.

Clojure API

Stratum has a direct Clojure API for programmatic workflows (custom training pipelines, model rotation, or embedding Stratum as a library):

require('[stratum.api :as st])

;; Your data: plain Java arrays
def amounts: double-array([10 15 12 11 14 200 13 11 300 12])
def freqs: double-array([5 6 4 5 7 1 5 4 1 6])

;; Train: 100 trees, 256 samples each, expect ~5% anomalies
def model: st/train-iforest(
             {:from {:amount amounts, :freq freqs}, :contamination 0.05})

;; Score: double[] in [0, 1], with higher values more anomalous
st/iforest-score(model, {:amount amounts, :freq freqs})

;; Binary prediction: long[] with 1 = anomaly, 0 = normal
st/iforest-predict(model, {:amount amounts, :freq freqs})

;; Confidence: how much do the trees agree? [0, 1]
st/iforest-predict-confidence(model, {:amount amounts, :freq freqs})

(require '[stratum.api :as st])

;; Your data: plain Java arrays
(def amounts (double-array [10 15 12 11 14 200 13 11 300 12]))
(def freqs   (double-array [ 5  6  4  5  7   1  5  4   1  6]))

;; Train: 100 trees, 256 samples each, expect ~5% anomalies
(def model (st/train-iforest {:from {:amount amounts :freq freqs}
                              :contamination 0.05}))

;; Score: double[] in [0, 1], with higher values more anomalous
(st/iforest-score model {:amount amounts :freq freqs})

;; Binary prediction: long[] with 1 = anomaly, 0 = normal
(st/iforest-predict model {:amount amounts :freq freqs})

;; Confidence: how much do the trees agree? [0, 1]
(st/iforest-predict-confidence model {:amount amounts :freq freqs})

Scores integrate directly with the query engine. They’re another column:

def scores: st/iforest-score(model, data)
st/q(
  {:from assoc(data, :score, scores),
   :where [[:> :score 0.7]],
   :group [:region],
   :agg [[:avg :score] [:count]],
   :having [[:> :avg 0.5]],
   :order [[:avg :desc]]})

(def scores (st/iforest-score model data))
(st/q {:from   (assoc data :score scores)
       :where  [[:> :score 0.7]]
       :group  [:region]
       :agg    [[:avg :score] [:count]]
       :having [[:> :avg 0.5]]
       :order  [[:avg :desc]]})

Online adaptation

Data distributions shift. Fraud patterns evolve. A model trained last month may not catch today’s anomalies. Retraining from scratch is wasteful when only the recent distribution has changed.

iforest-rotate replaces the oldest k trees with new ones trained on fresh data. The original model is unchanged. Copy-on-write semantics mean you can keep the old model for comparison:

;; Replace 10% of trees with new ones trained on this week's data
def updated-model: st/iforest-rotate(model, this-week-data)

;; Score with recency bias: newer trees weighted higher
st/iforest-score-weighted(updated-model, data, 0.98)

;; Replace 10% of trees with new ones trained on this week's data
(def updated-model (st/iforest-rotate model this-week-data))

;; Score with recency bias: newer trees weighted higher
(st/iforest-score-weighted updated-model data 0.98)

Rotating 10 trees trains on 2,560 sampled rows. In Stratum’s synthetic concept-drift evaluation, where the outlier region shifts at the midpoint, the rotating model kept AUC above 0.95 across the measured segments while the static model fell to 0.75. Synthetic drift is useful for regression testing, but it does not predict performance on a production distribution.

Performance

Measured on an Intel Core Ultra 7 258V (8 cores, Lunar Lake), JDK 25, 100 trees with sample size 256:

Batch scoring (online processing)

In this benchmark, a 1,000-row batch stayed under 2 milliseconds. Whether that is small enough for an application depends on its full request path and latency budget.

Full-table scoring (analytics)

Tree construction uses 25,600 sampled rows in this configuration. Scoring scales linearly with the number of input rows and is divided across cores with morsel-driven execution.

The project benchmark suite reports AUC-ROC on the Shuttle, Http, ForestCover, Mammography and CreditCard datasets at equivalent hyperparameters. It also includes a comparison with PyOD that can be run with clj -M:iforest pyod.

Under the hood

The tree structure is packed for cache efficiency. Each node is a single long:

• Internal nodes: split feature index (upper 32 bits) + split value as float (lower 32 bits).
• Leaf nodes: path length adjustment stored as Double.doubleToRawLongBits(c(leafSize)).
• Trees are contiguous in memory: forest[tree × maxNodes + nodeIdx].

Scoring traverses each tree with node = 2*node + 1 + (val >= splitVal ? 1 : 0), avoiding pointer chasing through separately allocated nodes. The anomaly score is 2^(-E(h(x)) / c(ψ)), where E(h(x)) is the mean path length across all trees and c(ψ) is the expected path length of an unsuccessful BST search. This normalizes scores to [0, 1].

Parallel scoring uses the query engine’s morsel-driven architecture. The ForkJoinPool processes rows in 64K-row morsels, and each thread writes to its own score region.

The confidence metric (predict-confidence) uses the coefficient of variation of per-tree path lengths. Agreement between trees produces a higher value; disagreement produces a lower one. It is a diagnostic for model agreement, not a calibrated probability that a row is anomalous.

What this enables

One possible use is low-latency fraud screening. A registered model can score a transaction or settlement batch in the same process as the SQL query. The score should remain one signal in a reviewed decision system; an unsupervised outlier score is not a fraud verdict.

For data-quality monitoring, ANOMALY_SCORE can rank staging rows that differ from a historical baseline before promotion.

For sensor monitoring, a model can compare combinations of vibration, temperature and power consumption that single-column thresholds do not express.

Because Stratum datasets are immutable values with copy-on-write branching, the same model can also be evaluated against a historical snapshot.

Try it yourself

Start the demo server, which loads 100K taxi ride rows and a pre-trained anomaly model:

java --add-modules jdk.incubator.vector \
     --enable-native-access=ALL-UNNAMED \
     -jar stratum-standalone.jar --demo

Connect with a PostgreSQL client and query the demo model:

psql -h localhost -p 5432 -U stratum

-- Find the most anomalous taxi rides
SELECT fare_amount, tip_amount, pickup_hour,
       ANOMALY_SCORE('taxi_anomaly', fare_amount, tip_amount,
                     total_amount, passenger_count, pickup_hour) AS score
FROM taxi
WHERE ANOMALY_SCORE('taxi_anomaly', fare_amount, tip_amount,
                    total_amount, passenger_count, pickup_hour) > 0.7
ORDER BY score DESC
LIMIT 20;

-- Binary prediction: which rides are anomalous?
SELECT fare_amount, tip_amount,
       ANOMALY_PREDICT('taxi_anomaly', fare_amount, tip_amount,
                       total_amount, passenger_count, pickup_hour) AS is_anomaly
FROM taxi
WHERE ANOMALY_PREDICT('taxi_anomaly', fare_amount, tip_amount,
                      total_amount, passenger_count, pickup_hour) = 1;

-- How confident is the model about each prediction?
SELECT fare_amount,
       ANOMALY_SCORE('taxi_anomaly', fare_amount, tip_amount,
                     total_amount, passenger_count, pickup_hour) AS score,
       ANOMALY_CONFIDENCE('taxi_anomaly', fare_amount, tip_amount,
                          total_amount, passenger_count, pickup_hour) AS confidence
FROM taxi
ORDER BY score DESC
LIMIT 10;

The demo includes synthetic high-fare, zero-tip rides late at night so the expected outliers are easy to inspect. It also ranks other unusual combinations of fare, tip, passenger count and hour from the sample data.

Getting started with your own data

Start the server (requires JDK 22+):

java --add-modules jdk.incubator.vector \
     --enable-native-access=ALL-UNNAMED \
     -jar stratum-standalone.jar

Then connect with any PostgreSQL client and do everything from SQL:

-- Load your data
CREATE TABLE transactions (amount DOUBLE PRECISION, freq BIGINT, hour BIGINT);
INSERT INTO transactions VALUES (10.0, 5, 14), (15.0, 6, 9), ...;

-- Or query directly from files
SELECT * FROM read_csv('/path/to/transactions.csv');

-- Train a model
CREATE MODEL fraud_model
  TYPE ISOLATION_FOREST
  OPTIONS (n_trees = 200, contamination = 0.05)
  AS SELECT amount, freq, hour FROM transactions;

-- Score your data
SELECT *, ANOMALY_SCORE('fraud_model', amount, freq, hour) AS score
FROM transactions
ORDER BY score DESC;

For programmatic workflows, Stratum also has a Clojure API for model training, online rotation, and integration with the query engine. Add to deps.edn:

{:deps {org.replikativ/stratum {:mvn/version "RELEASE"}}}

{:deps {org.replikativ/stratum {:mvn/version "RELEASE"}}}

Source and full documentation: github.com/replikativ/stratum. The anomaly detection guide has the complete API reference.

Feedback welcome on Clojurians #datahike or email.