# Machine learning in GizDB GizDB trains and runs models inside the database, with the SQL functions of PostgresML 2.10 (`pgml.train`, `pgml.predict`, `pgml.deploy`, `pgml.embed`). The models are native Gizlang. There is no Python, no extension to install and no second server. A model, its files and its history are rows in ordinary tables, so they commit, replicate and back up with your data. This guide is for developers who know SQL. You need a GizDB server and `psql` (refer to [Use GizDB](data.md)). Each output on this page is the output of its example in the GizDB test suite. ## 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 ``` ## How-to guides ### 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 ``` `docs/internals/sql-ml.md` §7 in the source 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 `docs/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. `docs/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. ## 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 each difference from PostgresML, is `docs/internals/sql-ml.md` in the source.