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). 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.

   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

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.

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

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.

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. 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).

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.

Reference

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.