Machine learning in Gizlang

Machine learning has one algorithm implementation and two optional entry paths:

gizai:ml/* owns the estimators, training algorithms, model bytes, prediction and metrics. db/ml/* is a SQL adapter over the same implementation, not another ML engine. An app can train outside a database and store its model wherever it chooses. A SQL user gets project metadata, snapshots, preprocessing, deployment and SQL functions in addition. For neural networks, tensors, autograd and accelerators, see Compute.

Supported ML algorithms: exact runtime catalog

The source of truth is gizai:ml/catalog; Catalog.list(task) exposes every supported task/algorithm combination as structured Gizlang data. The native Train.fit(task, name, ...) validates against that same catalog. As of this version, 38 distinct algorithm names cover 57 task/algorithm combinations:

import * as Catalog from "gizai:ml/catalog";

const all = Catalog.algorithms();
const classifiers = Catalog.list("classification");
const canStream = Catalog.find("regression", "stochastic_gradient_descent")!.incremental;

Run the full executable inventory from your checkout:

giz examples/ml/catalog.giz

Algorithm includes task, name, family, and incremental. Only the advertised task/name combinations are executable. Some legacy pgml.algorithm SQL enum labels (such as dbscan, optics, and spectral) exist for PostgresML schema compatibility but have no native training implementation; they are deliberately absent from the executable catalog. Conversely, decision_tree is available in the native code API but is not a member of the existing PostgresML 2.10 SQL enum. An SQL enum value or documentation reference is not proof that training succeeds.

Compatibility and performance: xgboost, lightgbm, catboost, and related names share a native Gizlang histogram boosting implementation with model-specific parameter defaults. They do not load the vendor implementations, and their predictions, optimization or GPU capabilities must not be assumed identical. Similarly, the current classical estimators train on CPU numeric vectors; GPU/NPU deep-learning and tensor models are a separate Compute API. See the implementation differences.

Run a model without a database

Install the Gizlang toolchain, then create this program. It needs no connection string, database schema or file on disk.

// Classical machine learning without a DB, SQL connection or external ML runtime.
// Run: giz examples/ml/standalone.giz
import {Json} from "gizlang:json";
import * as Ml from "gizai:ml/data";
import * as Train from "gizai:ml/classify";
import * as Codec from "gizai:ml/bytes";

function main(): void {
    // y = 1 + 2*x; each row has the same number of feature and target values.
    const data = Ml.tabular([[0.0], [1.0], [2.0], [3.0]],
        [[1.0], [3.0], [5.0], [7.0]]);
    const model = Train.fit("regression", "linear", Json.object([], []), data, 7u);
    print(`prediction=${model.predictChecked([4.0])[0]}`);
    const saved = Codec.encode(model);
    const loaded = Codec.decode(saved);
    print(`reloaded=${loaded.predictRowsChecked([4.0, 5.0], 2).length} rows`);
}

giz examples/ml/standalone.giz

prediction=9
reloaded=2 rows

The result is an in-memory Ml.Model. Its encoded bytes may be stored in a file, object store or database by the caller. Codec.decode restores the model without retraining or querying a catalog. Model bytes have their own format version (ML_FORMAT). The decoder rejects truncated or trailing bytes, overlarge length fields, incorrect weight or node-array dimensions, and invalid tree node references. Still authenticate and size-limit uploaded model files: format validation does not establish the model's origin, intent or quality.

Define the training data once

Ml.tabular(featureRows, targetRows) converts regular 2D numeric rows into a row-major Ml.Data contract (rows, features, flattened x, targets, flattened y). Omit targetRows for clustering or PCA:

import * as Ml from "gizai:ml/data";

const unlabeled = Ml.tabular([[1.0, 2.0], [3.0, 4.0]]);
// unlabeled.targets == 0; unlabeled.y is empty.

Ml.Data.validate(task) refuses malformed flat buffers, zero-width inputs and incompatible target counts before algorithms index them. Train.fit validates the contract too. Classification uses one target value per row; regression uses one or more. Every row must have the same feature width and target width. For streamed jobs, pass the same row-major contract into Partial.feed (it checks each batch).

Use the same contract for arrays created from CSV, JSON, an HTTP response, tensors converted to host numeric rows, a GizDB query or any other provider. None of those sources are coupled into the algorithm module. Data ingestion and model computation are separate decisions.

Reproducible train/validation split

Before fitting a model, split the dataset to avoid evaluating it on the same rows used for training. Data.split(validationRows, seed) uses a seeded Fisher-Yates shuffle, keeps the feature and target rows paired, and returns independent train and validation datasets. Each must contain at least one row.

import {Json} from "gizlang:json";
import * as Ml from "gizai:ml/data";
import * as Train from "gizai:ml/classify";

const records = Ml.tabular([[0.0], [1.0], [2.0], [3.0]],
    [[1.0], [3.0], [5.0], [7.0]]);
const holdout = records.split(1, 42u);
const model = Train.fit("regression", "linear", Json.object([], []), holdout.train, 42u);
const predictions = model.predictRowsChecked(holdout.validation.x, holdout.validation.rows);

Data.folds(k, seed) yields disjoint, reproducible K-fold partitions. For classification, Data.stratifiedFolds(k, seed) preserves class proportions, requires at least k examples per class, and is used automatically by Study.search. A temporal forecast still requires chronological splits; none of these random splitters understands event time or group correlation. Fit learned normalization or feature imputation only on training rows; apply the same fitted transformation to validation and inference rows.

Standardized feature pipelines and portable artifacts

gizai:ml/preprocess trains one fitted, reusable standardization transform (mean and population standard deviation per input column) and carries it alongside the model. Training-only statistics are essential: do not fit a scaler separately on evaluation or prediction data. Constant columns get unit scale; NaN/infinite features and incompatible dimensions are rejected.

import {Json} from "gizlang:json";
import * as Ml from "gizai:ml/data";
import * as Prep from "gizai:ml/preprocess";

const train = Ml.tabular([[100.0], [200.0], [300.0]],
    [[1.0], [2.0], [3.0]])!;
const pipeline = Prep.fit("regression", "linear", Json.object([], []), train, 7u)!;
const prediction = pipeline.predict([400.0])!;
const artifact = pipeline.encode()!;
const restored = Prep.decode(artifact)!;
const same = restored.predict([400.0])!;

The portable pipeline artifact carries the scaler and native model together; Prep.decode validates its version, field dimensions, positive scales, duplicate JSON keys, and model integrity. The bundle is a bounded JSON manifest with base64-encoded native model bytes, not an MLflow model registry or an ONNX bundle. Verify authenticity, authorization and retention separately. pipeline.transform.meanShiftZ(rawFeatureRows, count) provides a bounded, per-feature numeric mean-shift signal in training-standard-deviation units. It is a diagnostic for application-defined monitoring, not a full drift detector, performance monitor, alerting system or automated retraining job. The in-memory Study.search API currently evaluates raw Ml.Data; for models that require feature scaling, prepare each training fold separately or use your own fold-aware pipeline evaluation. A future unified cross-validation pipeline must not fit transforms on all rows before splitting.

Train, predict and evaluate

Select the algorithm by its name ("linear", "xgboost", "kmeans", "pca", etc.) and pass a Json object of hyperparameters. The native algorithms implement their own rules: names shared with scikit-learn, XGBoost or LightGBM do not imply byte-for-byte numerical parity with those external libraries. Read the algorithm and precision notes for those differences.

Ml.Model.predict(row) and predictRows(x, rows) are the lower-level inference methods. At public API or file boundaries prefer the checked methods:

The checked methods return errors for wrong dimensions instead of allowing an out-of-bounds read. Probability inference rejects unsupported tasks/algorithms. gizai:ml/metrics supplies regression and classification metrics and ranking utilities live under gizai:ml/rank.

Train incrementally without holding all rows

Some algorithms support bounded batches:

import {Json} from "gizlang:json";
import * as Train from "gizai:ml/classify";

function main(): void {
    let learner = Train.partial("regression", "stochastic_gradient_descent",
        Json.object([], []), 1, 1, [], 42u);
    learner.feed([0.0, 1.0], [1.0, 3.0], 2);
    learner.feed([2.0, 3.0], [5.0, 7.0], 2);
    const model = learner.result();
}

Train.streams(task, algorithm) tells whether an algorithm can learn incrementally. Train.resume(model, params, seed) supports the documented SGD warm-start path; it is not a general checkpoint of every optimizer. Models other than supported streaming algorithms require all training data for fit.

A streaming provider must preserve row order and keep its batches dimensionally consistent; Partial.feed rejects mismatched buffers or unsupported classification labels before training. For a production trainer, choose bounded batches, monitor errors and persist model bytes explicitly.

Experiments, cross-validation and model selection

gizai:ml/experiment is a database-independent supervised model-selection API. Provide explicit model candidates (an algorithm and its hyperparameters), a seeded disjoint K-fold split, a metric and a maximum fit budget. Every candidate uses the same folds (stratified by class for classification). Higher scores win; ties retain the original candidate order. The champion model is refitted on the full dataset after selection. This is a reproducible bounded search, not an automatic feature-engineering or nested cross-validation system. A held-out final test set should be kept separate from the training/search data for unbiased reporting.

// Run: giz examples/ml/experiment.giz
// Compare ML algorithms with the same seeded out-of-fold validation rows.
import {Json} from "gizlang:json";
import * as Data from "gizai:ml/data";
import * as Catalog from "gizai:ml/catalog";
import * as Study from "gizai:ml/experiment";

function main(): void {
    const data = Data.tabular(
        [[0.0], [1.0], [2.0], [3.0], [4.0], [5.0]],
        [[1.0], [3.0], [5.0], [7.0], [9.0], [11.0]]
    );
    const none = Json.object([], []);
    const candidates = [Study.Candidate("linear", none), Study.Candidate("ridge", none)];
    const study = Study.search("regression", data, candidates, 3, 42u, "r2", 6);
    print(`registered regression algorithms: ${Catalog.list("regression").length}`);
    print(`leaderboard: ${study.trials.length} candidates, ${study.folds} folds`);
    print(`winner: ${study.trials[0].algorithm}`);
    print(`prediction: ${study.champion.predictChecked([6.0])[0]}`);
}

Study.search supports regression (r2, neg_mse, neg_mae) and classification (accuracy). It caps searches at 32 candidates, 20 folds and a configurable total fit budget. It returns a Study with a sorted trials leaderboard, every fold score, seed, metric, and trained champion. The search evaluates user-specified candidates: it does not invent a hyperparameter grid, choose expensive algorithms without consent, or persist any secret dataset.

Study.importance(task, metric, model, validationData, seed) estimates feature importance by permuting one column at a time of independent validation rows and measuring the performance loss. It is model agnostic and CPU based, but a single rotation is only a rough estimate, not SHAP, causal attribution, or statistically stable multi-repeat permutation importance.

Incremental learners are exposed separately as Train.partial / Partial.feed / Train.resume, using only the supported streaming algorithms in the catalog. Do not assume every model is an online learning algorithm.

GizDB SQL workflows

This section extends the standalone runtime with the PostgresML-compatible pgml SQL namespace, projects, stored snapshots, deployment history and text-model tasks. It requires a running GizDB database and a PostgreSQL-compatible client such as psql. It does not require a standalone ML application or a Python runtime. The sample outputs below come from the repository's GizDB SQL tests. The pgml SQL namespace is distinct from the gizai:ml/* library API.

Train a first model

1. Turn on the interface. pgml.version() names the PostgresML version that GizDB follows.

   sqlCopy

   CREATE EXTENSION IF NOT EXISTS pgml;
   SELECT pgml.version();

   textCopy

   2.10.0 (gizdb)

2. Load a sample dataset. pgml.load_dataset makes the table pgml.iris with 150 rows.

   sqlCopy

   SELECT * FROM pgml.load_dataset('iris');

   textCopy

   pgml.iris|150

3. Train a classifier. The arguments are the project name, the task, the relation and the label column. The last column of the result tells you that the new model is deployed.

   sqlCopy

   SELECT * FROM pgml.train('Iris', 'classification', 'pgml.iris', 'target');

   textCopy

   Iris|classification|linear|t

4. Predict. Give the features in the order of the table's columns, as an array or as a row.

   sqlCopy

   SELECT pgml.predict('Iris', ARRAY[5.1, 3.5, 1.4, 0.2]);
   SELECT pgml.predict('Iris', ROW(5.1, 3.5, 1.4, 0.2));

   textCopy

   0
   0

5. Predict for each row of a table in one statement. The statement loads the model once and predicts all the rows in one pass.

   sqlCopy

   SELECT count(*) > 120 FROM pgml.iris
   WHERE pgml.predict('Iris', ARRAY[sepal_length, sepal_width, petal_length, petal_width]) = target;

   textCopy

   t

SQL workflows

Choose an algorithm and search its settings

The fifth argument is the algorithm and the sixth its hyperparameters, with the names of scikit-learn, XGBoost and LightGBM. A grid or random search tries each setting with 5-fold cross-validation and keeps the best.

SELECT project, task, algorithm FROM pgml.train('Iris', NULL, NULL, NULL, 'xgboost', '{}',
  'grid', '{"n_estimators": [2, 4]}');

Iris|classification|xgboost

SQL ML implementation §7 lists all the algorithms and how close each one is to its reference library.

xgboost, lightgbm, hist_gradient_boosting and catboost share one GBDT. Each has the defaults and the split rules of its library, but GizDB does not link the libraries: the trees and the numbers are not the same as the library's. The table in SQL ML implementation §7 names each difference.

Prepare the columns

preprocess encodes text columns, fills NULLs and scales numbers. The model keeps the statistics of training and applies the same steps when it predicts.

SELECT project, algorithm FROM pgml.train('Gems', 'regression', 'pgml.gems', 'price', 'linear',
  preprocess => '{"carat": {"impute": "mean", "scale": "standard"},
                  "cut": {"encode": {"ordinal": ["Fair", "Good", "Ideal"]}},
                  "color": {"encode": "one_hot"}, "grade": {"encode": "target"}}');
SELECT round(pgml.predict('Gems', ROW(1.0, 'Good', 'E', 'b'))::numeric, 1);

108.0

Deploy, roll back and pin a model

Training deploys a new model when it is better than the deployed one. You can also choose:

SELECT * FROM pgml.deploy('Test project', 'most_recent');
SELECT * FROM pgml.deploy(1);                          -- a model id
SELECT * FROM pgml.deploy('Test project', 'rollback'); -- the model deployed before

A statement reads one model for each project from its start to its end. A deployment that commits while a query runs is visible from the next statement.

Get the same model again

The seed of training is random_state of the hyperparameters, else 0. The same rows and the same settings give the same model bytes. Each model has a row in pgml.lineage: the snapshot of the data it read, its source, its seed, its columns and preprocessing, the versions, and the SHA-256 of its bytes.

SELECT count(*), count(DISTINCT sha256), min(seed) FROM pgml.lineage WHERE seed = 7;

2|1|7

Two models trained with random_state 7 have one SHA-256. A model whose bytes do not match the SHA-256 in its lineage does not load: the prediction fails with "pgml.files of model N does not match the SHA-256 in pgml.lineage".

Exchange models with ONNX and MLflow

pgml.export_onnx gives a model as an ONNX file. ONNX Runtime and other ONNX tools can run it. The input is the feature array that pgml.predict takes.

SELECT pgml.export_onnx(1);  -- bytea

pgml.import_onnx adds a model from an ONNX file to a project and deploys it. It reads the linear and tree models that skl2onnx and onnxmltools write for scikit-learn, XGBoost and LightGBM.

SELECT * FROM pgml.import_onnx('Points ONNX', pgml.export_onnx(1));
SELECT pgml.predict('Points ONNX', ARRAY[0.5, 1.0]);

Points ONNX|classification|onnx|t

An imported tree model takes the same branches as in ONNX Runtime, which rounds each input to f32 first. pgml.mlflow_model gives the MLmodel file of MLflow for the export, in the format that mlflow.onnx writes. Save it beside the export as model.onnx:

SELECT pgml.mlflow_model(1);

The file has the signature of the model and its pgml.lineage row as metadata. SQL ML implementation §6.1 lists the operators that each direction reads and writes.

Embed text

pgml.embed gives the embedding of a text from a sentence-transformers model, as an array of real. Cast it to vector to store and search it (refer to Search text and vectors).

SELECT pgml.embed('sentence-transformers/all-MiniLM-L6-v2', 'hello world')::vector;
SELECT count(*) FROM pgml.embed('sentence-transformers/all-MiniLM-L6-v2', ARRAY['hi', 'mom']);

BERT models and SentencePiece models (XLM-RoBERTa, such as intfloat/multilingual-e5-small and BAAI/bge-m3) are read. To embed each row when it is written, use a generated column:

CREATE TABLE notes (id int PRIMARY KEY, body text,
  embedding real[] GENERATED ALWAYS AS (pgml.embed('intfloat/multilingual-e5-small', 'passage: ' || body)) STORED);

To embed on the server's GPU, give '{"device": "cuda"}' as the kwargs. Without a GPU the call fails.

A generated column embeds in the transaction that writes the row. To embed after the commit, in batches, use pgml.vectorize. It keeps a vector column up to date with a text column:

CREATE TABLE notes (id int PRIMARY KEY, body text, embedding real[]);
SELECT pgml.vectorize('notes', 'body', 'embedding', 'intfloat/multilingual-e5-small');

• The first call records the column in pgml.vectorizers and adds a trigger. An UPDATE that changes body sets embedding to NULL.
• Each call embeds the rows whose vector is NULL and gives their count. A last argument limits the count of rows of one call.
• A call with another model or other kwargs embeds every row again.

gizdb vectorize --url postgres://... runs these calls in the background. It embeds the rows that wait, then waits on the changes of the tables (changes(table, lsn, wait)) and embeds again after each commit. --once yes embeds once and stops.

The models folder is $GIZDB_MODELS, else ~/.cache/gizlang/huggingface. A model that the folder does not have is downloaded from Hugging Face once. The console's Data area lists the models of the folder.

Rank documents with a cross-encoder

A cross-encoder reads the query and a document together, so it orders search results better than a distance between embeddings. Give it the query and the documents of a first search:

SELECT corpus_id, score, text
FROM pgml.rank('cross-encoder/ms-marco-MiniLM-L-6-v2', 'how many people live in Berlin?',
  ARRAY['Berlin has 3.5 million people.', 'Berlin is known for its museums.'],
  '{"return_documents": true, "top_k": 1}');

corpus_id is the index of the document in the array, from 0.

To order the rows of a first search again, give their texts as an array. pgml.rank can be in a CTE or a subquery:

WITH hits AS (
  SELECT id, body FROM docs WHERE match(body, 'berlin population') ORDER BY score() DESC LIMIT 50)
SELECT r.score, r.text
FROM pgml.rank('cross-encoder/ms-marco-MiniLM-L-6-v2', 'how many people live in Berlin?',
  ARRAY(SELECT body FROM hits), '{"return_documents": true, "top_k": 5}') AS r;

Generate text

pgml.transform gives the text that a chat model generates for each input, as a jsonb array:

SELECT pgml.transform(task => '{"task": "text-generation", "model": "Qwen/Qwen2.5-0.5B-Instruct"}',
  inputs => ARRAY['Write a title for: a database that runs models'], args => '{"max_new_tokens": 20}');

• A model that the models folder holds runs in the server, on its GPU when it has one. Llama, Mistral and Qwen2 models (model.safetensors in F32, F16 or BF16) are read, with their chat templates (ChatML and Llama 3). The server does not download a model for pgml.transform: put it in the folder first.
• Any other model goes to a chat model behind an OpenAI-compatible API. The server reads the API from AI_URL (for example http://127.0.0.1:11434/v1 for Ollama) and AI_API_KEY, and the default model from AI_MODEL.

The args are max_new_tokens, temperature (0 or less: the most likely token each step), top_p, seed and stop. pgml.transform_stream gives a row for each piece of text as the model writes it. The conversational task reads the inputs as the messages of one conversation.

Control who trains and downloads

The pgml functions run with the privileges of the role that calls them.

• To train, a role needs SELECT on the data and the privileges on the pgml tables:

  sqlCopy

  GRANT ALL ON ALL TABLES IN SCHEMA pgml TO analyst;
  GRANT SELECT ON sales TO analyst;

  Without them, training fails with "permission denied for table NAME".

• To download a model, a role must be a superuser or a member of pgml_download:

  sqlCopy

  CREATE ROLE pgml_download;
  GRANT pgml_download TO analyst;

  Other roles can use only the models that the folder has. For them, a missing model fails with "permission denied to download model REPO".

• To generate text (pgml.transform), a role must be a superuser or a member of pgml_llm. A local model uses the server's compute. A model API gets the inputs and may cost money.

• The server limits what is downloaded. GIZDB_MODELS_ALLOW=sentence-transformers/,BAAI/bge-small-en permits only those repositories (owner/ permits all of one owner). GIZDB_MODELS_LIMIT is the most bytes of one file (4 GiB by default).

• The first download of a repository records its commit and the SHA-256 of each file in .pins/REPO.pin in the models folder. Later downloads use the same commit, and each read checks the file. To pin a model to a known commit before its first download, write the pin yourself, with the 40-letter commit id from the history of the repository:

  textCopy

  revision COMMIT

Model files are data (JSON, safetensors and tokenizer files). A model cannot run code of its own.

Limit the resources of training

Two settings limit one pgml.train call:

Give a role its limits with ALTER ROLE. They apply when the role logs in. A session can lower its limit with SET, but it cannot raise it above the role's limit.

ALTER ROLE analyst SET pgml.max_training_bytes = '1GB';
ALTER ROLE analyst SET pgml.max_training_seconds = '10min';
ALTER ROLE analyst RESET pgml.max_training_seconds;

Past the byte limit, an algorithm that needs all its rows fails with "pgml.train: the training data needs more than pgml.max_training_bytes (N bytes)". statement_timeout also applies, as in PostgreSQL.

Past the byte limit, an algorithm that learns in batches does not fail: it reads the rows in batches, and only one batch is in memory. These algorithms stream: stochastic_gradient_descent, passive_aggressive, perceptron and mini_batch_kmeans.

SET pgml.max_training_bytes = '8kB';
SELECT project, algorithm FROM pgml.train('Line', 'regression', 'line', 'y',
  'stochastic_gradient_descent', '{"max_iter": 20}');

Line|stochastic_gradient_descent

Other algorithms, a search and a joint model need all the rows in memory. Past the limit they fail, and the error names the algorithms that stream.

SQL reference

The tables are pgml.projects, pgml.snapshots, pgml.models, pgml.files, pgml.deployments and pgml.lineage. The full contract, with differences from PostgresML, is in the SQL ML implementation.

ML platform capabilities and remaining integrations

A commercial ML platform such as MLflow covers experiment tracking, registered versions, deployment and production monitoring. Gizlang's current APIs implement specific parts of that lifecycle, not a drop-in full substitute. Keep model algorithms in gizai:ml, SQL deployment and persistence in db/ml, and application routes, authentication and background jobs in the existing GizWeb/GizDB owners. Do not create a second model registry or another learner implementation.

Architecture, interoperability and limits

Standalone Gizlang app / worker ────> gizai:ml/* <──── GizDB SQL adapter (db/ml/)
                                         |
                                    Model / bytes
                                         |
                                caller-owned persistence

The gizai:ml/* modules do not import gizdb:*. A native model can be encoded and decoded outside GizDB; SQL-specific snapshots, training projects and deployments are additional services, not preconditions for the algorithm. Source data may come from in-memory vectors, CSV/JSON, HTTP or Data Access, but conversion to typed Ml.Data remains the caller's responsibility.

The classical estimators use host Vec<f64> and Gizlang's numeric routines. Sharing the algorithm implementation does not imply that every estimator runs on GPU/NPU; Compute is the tensor and accelerator path. The XGBoost, LightGBM and CatBoost names map to a common Gizlang GBDT implementation with documented semantic and numerical differences, not the vendors' native libraries. See ML implementation notes.

Modules and tests

Run giz test tests/ai/ml/model.test.giz. It checks serialization, independent training and inference, holdout reproducibility, corrupt model bytes, invalid matrix shapes and incremental batch validation. examples/ml/standalone.giz is the complete runnable example above.