# Machine learning in Gizlang Machine learning has one algorithm implementation and two optional entry paths: | You want to | Start here | Required runtime | | --- | --- | --- | | Train or infer inside an app, service, script or worker | [Standalone ML](#run-a-model-without-a-database) | Gizlang with `gizai:ml/*`; no database | | Train over SQL tables and deploy through the model catalog | [GizDB SQL ML](#gizdb-sql-workflows) | GizDB with `pgml` enabled | `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](compute.md). ## 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**: | Task | Family | Supported algorithm names | | --- | --- | --- | | Regression (31) | Linear/robust/Bayesian | `linear`, `ridge`, `lasso`, `elastic_net`, `least_angle`, `lasso_least_angle`, `orthogonal_matching_pursuit`, `bayesian_ridge`, `automatic_relevance_determination`, `stochastic_gradient_descent`, `passive_aggressive`, `ransac`, `theil_sen`, `huber`, `quantile` | | Regression | Kernel | `svm`, `nu_svm`, `linear_svm`, `kernel_ridge`, `gaussian_process` | | Regression | Trees and ensembles | `decision_tree`, `random_forest`, `extra_trees`, `bagging`, `ada_boost`, `gradient_boosting_trees` | | Regression | Histogram boosting | `xgboost`, `xgboost_random_forest`, `lightgbm`, `hist_gradient_boosting`, `catboost` | | Classification (20) | Linear | `linear` (logistic / softmax), `ridge`, `stochastic_gradient_descent`, `perceptron`, `passive_aggressive` | | Classification | Kernel | `svm`, `nu_svm`, `linear_svm`, `gaussian_process` | | Classification | Trees and ensembles | `decision_tree`, `random_forest`, `extra_trees`, `bagging`, `ada_boost`, `gradient_boosting_trees` | | Classification | Histogram boosting | `xgboost`, `xgboost_random_forest`, `lightgbm`, `hist_gradient_boosting`, `catboost` | | Clustering (5) | Unsupervised | `kmeans`, `mini_batch_kmeans`, `birch`, `affinity_propagation`, `mean_shift` | | Decomposition (1) | Dimensionality reduction | `pca` | ```typescript gizlang 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: ```sh 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](compute.md) API. See the [implementation differences](../internals/sql-ml.md#7-algorithms). ## Run a model without a database Install the [Gizlang toolchain](../quickstart.md), then create this program. It needs no connection string, database schema or file on disk. ```typescript gizlang file="examples/ml/standalone.giz" // 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`); } ``` ```sh giz examples/ml/standalone.giz ``` ```text output 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: ```typescript gizlang 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. ```typescript gizlang 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. ```typescript gizlang 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 | Task | `Train.fit` task | Result | | --- | --- | --- | | Regression | `"regression"` | One value per target, per row | | Classification | `"classification"` | Predicted class label, optional class probabilities | | Clustering | `"clustering"` | Cluster assignment | | Dimensionality reduction | `"decomposition"` with `"pca"` | Principal components | 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](../internals/sql-ml.md) 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: | Method | Contract | | --- | --- | | `model.predictChecked(row)` | Exactly `model.features` numeric values | | `model.predictRowsChecked(flatRows, count)` | Exactly `count * model.features` values | | `model.probaChecked(row)` | Checked classification probability request | | `model.probaRows(flatRows, count)` | Checked batch probabilities for supported classifiers | 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: ```typescript gizlang 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. ```typescript gizlang file="examples/ml/experiment.giz" // 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](data.md) 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. ```sql CREATE EXTENSION IF NOT EXISTS pgml; SELECT pgml.version(); ``` ```text 2.10.0 (gizdb) ``` 2. Load a sample dataset. `pgml.load_dataset` makes the table `pgml.iris` with 150 rows. ```sql SELECT * FROM pgml.load_dataset('iris'); ``` ```text 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. ```sql SELECT * FROM pgml.train('Iris', 'classification', 'pgml.iris', 'target'); ``` ```text Iris|classification|linear|t ``` 4. Predict. Give the features in the order of the table's columns, as an array or as a row. ```sql 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)); ``` ```text 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. ```sql SELECT count(*) > 120 FROM pgml.iris WHERE pgml.predict('Iris', ARRAY[sepal_length, sepal_width, petal_length, petal_width]) = target; ``` ```text 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. ```sql SELECT project, task, algorithm FROM pgml.train('Iris', NULL, NULL, NULL, 'xgboost', '{}', 'grid', '{"n_estimators": [2, 4]}'); ``` ```text Iris|classification|xgboost ``` [SQL ML implementation](../internals/sql-ml.md) §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](../internals/sql-ml.md) §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. ```sql 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); ``` ```text 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: ```sql 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. ```sql SELECT count(*), count(DISTINCT sha256), min(seed) FROM pgml.lineage WHERE seed = 7; ``` ```text 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. ```sql 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. ```sql SELECT * FROM pgml.import_onnx('Points ONNX', pgml.export_onnx(1)); SELECT pgml.predict('Points ONNX', ARRAY[0.5, 1.0]); ``` ```text 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`: ```sql SELECT pgml.mlflow_model(1); ``` The file has the signature of the model and its `pgml.lineage` row as metadata. [SQL ML implementation](../internals/sql-ml.md) §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](data.md#search-text-and-vectors)). ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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`: ```sql 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: ```text 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: | Setting | Unit | Limit | | --- | --- | --- | | `pgml.max_training_bytes` | B, kB, MB, GB, TB | The bytes of the rows that training reads | | `pgml.max_training_seconds` | s, min, h | The time of training, checked between batches and between fits | 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. ```sql 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`. ```sql SET pgml.max_training_bytes = '8kB'; SELECT project, algorithm FROM pgml.train('Line', 'regression', 'line', 'y', 'stochastic_gradient_descent', '{"max_iter": 20}'); ``` ```text 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 | Function | What it does | | --- | --- | | `pgml.train(project, task, relation, y_column, algorithm, hyperparams, search, search_params, search_args, test_size, test_sampling, runtime, automatic_deploy, materialize_snapshot, preprocess)` | Trains, records and maybe deploys a model | | `pgml.predict(project_or_id, features)` | The prediction of one row | | `pgml.predict_proba`, `pgml.predict_joint`, `pgml.predict_batch`, `pgml.decompose` | Probabilities, joint regression, a batch, PCA components | | `pgml.deploy(project, strategy, algorithm)`, `pgml.deploy(model_id)` | Deploys a model: `best_score`, `most_recent`, `rollback` | | `pgml.snapshot(relation, y_column)` | A materialized snapshot of the rows | | `pgml.load_dataset(name)` | One of the scikit-learn datasets as a table | | `pgml.embed(model, text, kwargs)` | The embedding of a text, or of each text of an array | | `pgml.rank(model, query, documents, kwargs)` | The documents by the score of a cross-encoder | | `pgml.vectorize(relation, text_column, vector_column, model, kwargs, limit)` | Embeds the rows whose vector is NULL; keeps the column up to date | | `pgml.tune_search(search, judgments, kwargs)` | The nDCG and recall of a search under each fusion setting | | `pgml.transform(task, args, inputs)`, `pgml.transform_stream(task, args, input)` | Text from a chat model in the models folder, else behind an OpenAI-compatible API | | `pgml.chunk(splitter, text, kwargs)` | The text in chunks, with the langchain splitters | | `pgml.export_onnx(model_id)`, `pgml.import_onnx(project, model, task, automatic_deploy)` | A model as an ONNX file, and a model from one | | `pgml.mlflow_model(model_id)` | The MLflow `MLmodel` file of the ONNX export | 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](../internals/sql-ml.md). ## ML platform capabilities and remaining integrations | Platform requirement | Current implementation | Important boundary | | --- | --- | --- | | Model discovery | `Catalog.algorithms()` / `list(task)` | Only executable algorithm names are listed | | Native training and batch inference | `Train.fit`, `Ml.Model` methods | Classical CPU models, numeric features | | Incremental fitting | `Train.partial` / `feed` / `resume` | SGD / perceptron / passive-aggressive / mini-batch K-means only | | Validation & experiment comparison | Seeded holdout, disjoint K-fold, stratified classification K-fold, `Study.search` leaderboard | Standalone in memory, caller-supplied candidates | | Evaluation / basic explainability | Regression & classification metrics; permutation importance | Not full SHAP, drift monitoring or causal explanations | | Preprocessing | `Prep.fit` standard-scaling pipelines with paired portable artifact; SQL `pgml.train(preprocess => ...)` | Categorical/feature-store/SQL preprocessing integration not unified; experiment search does not automatically scale folds | | HPO / AutoML | SQL `pgml.train` grid/random search; standalone bounded candidate comparison | No distributed tuning scheduler or fully automatic feature search | | Model artifacts / interchange | Native model bytes, scaler+model pipeline bundle, SQL ONNX export/import, MLflow `MLmodel` metadata | No complete MLflow tracking-server compatibility | | Registry / deployment / rollback | GizDB SQL `pgml.projects`, `pgml.models`, `pgml.deploy`, `pgml.lineage` | Database-side service, not a standalone central registry | | Permissions / auditing | GizDB SQL and GizWeb policy boundaries | Caller must enforce authorization for standalone inference | | Real-time inference | Application can call in-process model prediction | No dedicated autoscaling classical ML inference gateway | | GPU/NPU execution | `gizai:tensor`, tile kernels, device-specific inference | Classical ML `gizai:ml` is not automatically hardware-accelerated | | Production monitoring / drift / retraining jobs | Standardizer mean-shift diagnostic plus application-defined logging/scheduling | Automatic calibrated drift monitors, alerts and orchestration are not implemented | | Multi-DB data | Typed DAL fan-out and Relay CDC ingest | No global distributed ACID or built-in all-provider CDC consumer | 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 ```text 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](data-access.md), but conversion to typed `Ml.Data` remains the caller's responsibility. The classical estimators use host `Vec` and Gizlang's numeric routines. Sharing the algorithm implementation does not imply that every estimator runs on GPU/NPU; [Compute](compute.md) 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](../internals/sql-ml.md). ## Modules and tests | Module | Responsibility | | --- | --- | | `gizai:ml/catalog` | Task-specific executable model discovery and incremental capability | | `gizai:ml/experiment` | K-fold/stratified model comparison, leaderboards, evaluation and importance | | `gizai:ml/preprocess` | Training-only standard scaler, transform-safe prediction and portable pipeline artifact | | `gizai:ml/data` | Datasets, shape checks, model representation and checked inference | | `gizai:ml/classify` | Fitting, streaming learners, warm starts | | `gizai:ml/bytes` | Encode/decode native model bytes | | `gizai:ml/metrics` | Evaluation metrics | | `gizai:ml/linear`, `trees`, `svm`, `cluster` | Algorithm implementations | | `gizai:ml/rank` | Retrieval and ranking metrics | 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.