PGML: the PostgresML interface of GizDB

GizDB gives the SQL interface of PostgresML 2.10 (pgml-extension). The contract is the external API and its results, not the internal structure. GizDB does not load the pgrx extension and does not copy PostgreSQL internals. The internals follow the Gizlang rules: one owner for each decision, one copy of each fact, remove before you add.

The reference is postgresml/postgresml 2.10.0: sql/schema.sql, src/api.rs, src/orm/*, src/bindings/*, src/vectors.rs, tests/test.sql and examples/*.sql. The acceptance test is tests/test.sql: the 11 examples it runs (clustering, decomposition, binary, multi and image classification, joint regression, regression, vectors, chunking, preprocessing, embedding) run with no error. Deterministic results equal those of PostgresML. Stochastic training gives the same output shape and the same quality range (§7).

1. Owners

SQL and KV that do not use pgml pay no cost. The binder separates a pgml.* name with one comparison.

2. The pgml namespace

GizDB keeps one namespace for one database. Parser.tableName keeps only information_schema, pg_catalog and sys, and drops the other schema names. The schema pgml is a real namespace too:

1. A call in schema pgml has the name pgml.NAME. A call in another schema loses its schema, as before.
2. A relation in schema pgml has the stored name pgml.NAME (pgml.projects, pgml."jdxcosta/diamonds"). tableName and the FROM item give this name to every statement. Without an alias, the alias of pgml.models is models, so models.id and pgml.models.id name its column.
3. DROP SCHEMA pgml CASCADE and DROP EXTENSION pgml drop every object whose name starts with pgml..
4. A relation name as a string ('pgml.diabetes', 'diabetes_view') is the stored name. A name without a dot is a name of the one namespace.

Thus a user table projects and pgml.projects do not collide.

3. Arrays and the f32 buffer

real[] and pgvector's vector(n) are different SQL types. The declared type (declared, pgDeclared) holds the identity:

• real[] prints as {1,2} and takes subscripts, array_length and unnest.
• vector(n) prints as [1,2] and takes the distance operators.
• Only an explicit cast (::vector, ::real[]) changes one to the other.

Storage does not change. An array is its literal text. vector(n) is a column of contiguous f32 values. Only the text holds every array: more than one dimension, NULL elements, a lower bound other than 1. Two forms of one array type would give every array operator two paths.

The ML boundary is one contiguous f32 buffer (db/sql/types/reals.giz floats):

• A Value.vector (a vector(n) column, ::vector) is used as it is, with no copy.
• A text array (real[], float8[], int2/4/8[], bool[]) goes through one fast parser into f32, as PostgresML converts it.
• ARRAY[a, b, ...] as a pgml.* argument binds to \u0001f32_array, which fills the f32 buffer and writes no text.
• A training matrix fills a row-major f32 buffer from the column batches, with no Value for each cell.

A pgml function that returns real[] (embed, predict_proba, normalize_*) gives an array value in the float4 output format of PostgreSQL.

A stored embedding in a real[] column is parsed when a function reads it. When a measured scan shows that the parse is the main cost, a new storage format version keeps a one-dimensional real[] with no NULLs as variable-length f32 values, and the reader keeps reading the past versions.

4. Installation and functions

CREATE EXTENSION [IF NOT EXISTS] pgml runs the SQL of db/ml/schema.giz in the session: the tables, columns, defaults, enum types and views of PostgresML schema.sql. It adds the routines below with language native, with the parameter names, defaults and result columns of api.rs. Conn.invokeBody sends a native routine to db/ml/pgml.giz pgmlCall.

The vector functions are pure functions of func.giz: add, subtract, multiply, divide, norm_l0, norm_l1, norm_l2, norm_max, normalize_l1, normalize_l2, normalize_max, distance_l1, distance_l2, dot_product and cosine_similarity. The aggregates pgml.sum, min, max, min_abs and max_abs are pure too. A float4[] input computes in f32 and a float8[] input in f64, and the result has the type of the input, as the PostgresML overloads do.

5. Training

The semantics are those of PostgresML.

1. Project: when it does not exist, the task is necessary. When it exists, the task must be the same. Without a relation, training uses the last snapshot of the project.
2. Defaults: algorithm linear is kmeans for clustering and pca for decomposition. The default runtime is that of api.rs. GizDB records the runtime but has one engine.
3. Snapshot:
   • The columns are in the order of the relation. The first row gives the size of an array column.
   • Sampling is random, last or stratified. test_size above 1 is a count of rows.
   • Statistics (f32): min, max, max_abs, mean, median, mode, variance, std_dev, missing, distinct, a histogram of 20 bins, 19 ventiles and categories.
   • Categories start with __NULL__ as 0, then come in the order they first appear.
   • materialize_snapshot makes pgml.snapshot_{id}.
   • The rows come from the executor as column batches (Conn.spiBatches, the snapshot of the statement): int and float columns are read in place, and no rows of SQL values are made. On persisted roots the batches stream, each held on its delivery's lease until the next one. The features are then one column a feature element, analyzed and preprocessed from there into the row-major matrix that the fit reads.
4. Preprocessing: encode native, target, one_hot or ordinal. Impute error, mean, median, mode, min, max or zero. Scale preserve, standard, min_max, max_abs or robust. The model keeps the statistics of training, and prediction applies the same transform.
5. Search: grid or random (n_iter 10 by default). cv is 5 with a search and 1 without one. Folds use only the training rows. metrics.search_results has the keys of scikit-learn cv_results_.
6. Metrics:
   • Regression: r2, mean_absolute_error, mean_squared_error.
   • Classification: f1 (macro; the positive class when binary), precision, recall, accuracy, mcc; for binary also roc_auc and log_loss.
   • Clustering: silhouette. Decomposition: cumulative_explained_variance.
   • All tasks: fit_time and score_time.
7. Storage: a pgml.models row (in_progress, then successful or failed), and the model bytes in pgml.files(model_id, 'estimator.rmp', 0, data). The bytes start with a format version. Each later version reads the earlier ones.
8. Seed and lineage (GizDB, not PostgresML): the seed is random_state of the hyperparameters, else 0 (PostgresML takes the clock), also for a random search, so the same rows and settings give the same model bytes. Each model has a pgml.lineage row: the MVCC snapshot its rows were read at, its source (the relation, or the materialized snapshot), the seed, the snapshot's columns with their preprocessors and statistics, the versions (pgml, the model format, runtime, algorithm) and the SHA-256 of its bytes. A model whose bytes do not match is not loaded. Training, its files, its lineage and its deployment are one statement: one transaction.
9. Automatic deploy: the new model deploys with strategy new_score unless the deployed model is better on the key metric of the task. The key metric is higher-is-better, except perplexity for text_generation and text2text.
10. Deploy strategies:

• best_score: the metric as text in descending order, NULLS LAST, as PostgresML sorts it.
• most_recent, rollback, specific.

11. Privileges and limits (GizDB): the pgml functions run as the calling role, as PostgreSQL runs SECURITY INVOKER functions. Training needs SELECT on the relation and the privileges on the pgml.* tables that it writes (GRANT ALL ON ALL TABLES IN SCHEMA pgml TO role). Two settings limit one pgml.train:
    • pgml.max_training_bytes (B, kB, MB, GB, TB): the bytes of the rows read for training. Past it the error is "pgml.train: the training data needs more than pgml.max_training_bytes (N bytes)".
    • pgml.max_training_seconds (s, min, h): checked at each batch and before and after each fit of the search. One fit runs to its end. ALTER ROLE r SET pgml.max_training_bytes = '1GB' gives the role its limit at login. A SET in the session can lower a limit but not raise it past the role's. statement_timeout applies as in PostgreSQL. Past pgml.max_training_bytes, an algorithm that learns in batches streams (Ml.streams, Ml.Partial): stochastic_gradient_descent and passive_aggressive (regression and classification), perceptron and mini_batch_kmeans. The statistics of preprocessing and the category codes come from the first rows that fit half the limit. Then each batch of training rows is preprocessed and learned, and the test rows, which come last, are predicted by the final model. One batch and the test labels and predictions are in memory. Other algorithms, a search and a joint model fail past the limit, and the error names the algorithms that stream.
12. Warm start (GizDB; scikit-learn warm_start): with "warm_start": true in the hyperparameters, the new model goes on from the project's deployed model with the new rows only, in one streamed pass (Ml.resume). The algorithm must be the deployed model's and stream, with no search. The relation must have the deployed snapshot's columns: the encodings, imputation, scaling and statistics are the deployed snapshot's, so old and new models read a row the same way. The automatic deploy compares the two models on the same test rows (the new rows): the warm model is deployed only when it beats the deployed one there. With automatic_deploy => false the model waits for a deploy (most_recent), which swaps it in for the next statement. So a model learns each day's rows without a refit of the past, a feature store or a stream bus. bench/db/ml/realtime.giz (50,000 rows a day, laptop): full fit of two days 265 ms, warm fit of the new day 257 ms, r2 on the new day 0.948 (yesterday's model) and 0.9998 (warm).

6. Prediction

• A statement reads each model once: Conn.rowCalls loads the model of each distinct project name or model id of the query, and the function slot predicts from each row. No SQL runs for each row.
• A project name reads its latest deployment; an integer is a model id.
• The rows of one call predict in one pass (Model.predictRows, probaRows), with no slice a row.
• pgml.predict(name, ARRAY[columns]) gives each row's f32 buffer to the model. Its length must be a multiple of the model's width.
• predict(name, ROW(...)) encodes the record: the i-th field goes to the i-th feature column, with the category codes, imputation and scaling of the snapshot.
• predict_proba is for classification, predict_joint for regression, with the errors of PostgresML otherwise.
• A statement sees one model for each key for all its rows. A new deployment is visible from the next statement.
• The server keeps up to 64 loaded models for all its sessions, keyed by model id and the SHA-256 of their bytes. A statement reads the key's deployment and lineage in one query and decodes the model only when that key is new. After a swap, the server decodes the new model once.
• The answer of that query is kept for each user, role and key with the last commit that wrote pgml.deployments, pgml.lineage and pgml.projects (Engine.writeStamp). While no commit after it writes them and the snapshot sees it, a statement runs no query: one SELECT pgml.predict(...) takes 125 us, against 55 us for SELECT 1 (laptop, bench/db/ml/realtime.giz). A transaction at REPEATABLE READ keeps the model of its snapshot.
• A plan that calls a pgml function stays in the plan cache: the function reads its model at each execution.
• The conditions of a WHERE that call no routine run first: a prediction sees only the rows they keep, as PostgreSQL orders conditions by cost.

6.1 Exchange: ONNX and MLflow (GizDB)

gizml:onnx owns the ONNX format; the pgml functions only read and record models.

• pgml.export_onnx(model_id) gives the model as an ONNX file (IR 8, opset 17, ai.onnx.ml 3): LinearRegressor, LinearClassifier (with an L1 Normalizer for the logistic links), TreeEnsembleRegressor and TreeEnsembleClassifier (all the tree families whose trees are summed or averaged), PCA (Sub, MatMul) and k-means (the nearest center). The input X is f32 [N, features]: the features as pgml.predict takes an array, after preprocessing. svm, AdaBoost and the other clustering algorithms are an error ("… has no ONNX export").
• pgml.import_onnx(project, model, task, automatic_deploy) reads an ONNX file with one LinearRegressor, LinearClassifier, TreeEnsembleRegressor or TreeEnsembleClassifier, as skl2onnx and onnxmltools write them for scikit-learn, XGBoost and LightGBM, with Cast, Identity, ZipMap, Softmax and Normalizer (L1) around it, and a Scaler before a linear operator (folded into its coefficients). Any other operator is an error that names it. The model has the algorithm onnx and no snapshot, so it predicts from arrays. Class labels must be integers or numbers as text. pgml.lineage has source onnx and the SHA-256 of the file in versions.onnx_sha256.
• ONNX Runtime and scikit-learn round an input to f32 before a tree compares it with its f32 threshold. Import turns each f32 test (BRANCH_LEQ, LT, GT, GTE) into the f64 test that gives the same branch, so an imported model predicts as ONNX Runtime. Export writes the nearest f32 of each threshold: an input within f32 rounding of a threshold can take the other branch in ONNX Runtime. Linear models run in f32 there, so their outputs agree to f32 precision. Missing-value tracks (nodes_missing_value_tracks_true) are not read: preprocessing fills NULLs.
• pgml.mlflow_model(model_id) gives the MLmodel file of MLflow for the export saved as model.onnx: the onnx and python_function flavors, the signature (the tensor specs of the graph's inputs and outputs), model_uuid (the first 32 hex digits of the file's SHA-256) and the model's pgml.lineage as metadata, in the MLmodel format that mlflow.onnx writes. The tests check the file's text; they do not run MLflow.
• No ONNX Runtime is linked: GizDB runs the imported operators with its own trees and linear models. Arrow C Data, ADBC and DLPack are not supported.

tests/ml/onnx checks round trips of each model kind and BRANCH_LT; tests/db/pgml.test.giz checks export, import, the same predictions on all rows and the signature.

7. Algorithms

All algorithms are native Gizlang in the SDK, with the default hyperparameters of scikit-learn, xgboost and lightgbm. Each family module imports only gizlang:linalg and gizlang:stats; gizml:ml imports the families.

GizDB does not link xgboost or lightgbm. Tools are Gizlang only, the default binary has no added dependency, and one GBDT owns the four families.

The one histogram GBDT (gizml:trees boost) follows each library in the rules below. It is not the library: the same data and settings give a model of the same kind and quality, not the same trees or the same numbers. tests/ml/boost.test.giz checks the split rules.

NULLs and text columns are prepared by preprocess (§5) before the GBDT sees them, for all four families.

Result compatibility:

tests/db/pgml.test.giz runs the statements of tests/test.sql and the examples in a GizDB session and checks these criteria.

8. Transformers

pgml.embed gives the embeddings of a sentence-transformers model, as SentenceTransformer.encode of sentence-transformers 3 gives them. pgml.rank gives the scores of a cross-encoder, as CrossEncoder.rank gives them. pgml.transform and pgml.transform_stream run the text-generation and conversational tasks by a decoder in the server (Llama, Mistral, Qwen2) when the models folder holds the model, else through a chat model behind an OpenAI-compatible API. Each part is in the SDK, so a program that does not use the database uses the same code:

• A model is its repository files in the models folder: REPO/config.json, tokenizer.json, model.safetensors and, for a sentence-transformers model, modules.json, sentence_bert_config.json and the pooling config.json. The folder is $GIZDB_MODELS, else $GIZLANG_CACHE/huggingface, else ~/.cache/gizlang/huggingface. GIZDB_MODELS=none turns models off.
• A missing file is downloaded from https://huggingface.co/REPO/resolve/REVISION/FILE (with HF_TOKEN when it is set) and written to its place at once. A repository that the hub does not have is PostgresML's OSError.
• Pins: .pins/REPO.pin in the folder keeps the commit that the first download of the repository resolved (revision COMMIT) and the SHA-256 of each file (sha256 HEX FILE). Later downloads use that commit, and each read checks the file: a file that does not match is an error. A large file must also match the SHA-256 that the hub states (X-Linked-Etag). To pin a model before its first download, write its pin by hand.
• Downloads: only a superuser or a member of role pgml_download downloads; for other roles a missing model is "permission denied to download model REPO". GIZDB_MODELS_ALLOW (a comma list of owner/name and owner/) limits downloads to those repositories, and GIZDB_MODELS_LIMIT limits each file (bytes; 4 GiB by default, 0 for no limit). The size is checked from the headers and again while the file streams; a larger file leaves nothing.
• Model files are data (JSON, safetensors, tokenizer files). No model runs code of its own, and no file is a pickle.
• The server keeps the models it loaded for all its sessions. A statement that embeds a column loads the model once and encodes the rows in batches of texts with the same token count (Encoder.embedBatch), so no token is padding.
• embed kwargs: prompt goes before each text, normalize_embeddings normalizes, batch_size (32 by default, at least 1) is the most texts in one forward pass. device is cpu (the default) or cuda, cuda:N, gpu or hip: the server's GPU (gizhw:gpu, chosen by GIZLANG_DEVICE). With no GPU the call is an error; it does not fall back to the CPU. trust_remote_code, show_progress_bar, convert_to_numpy, convert_to_tensor and precision change nothing. Another key is the TypeError of encode.
• A generated column embeds each row when it is written: embedding real[] GENERATED ALWAYS AS (pgml.embed('intfloat/multilingual-e5-small', 'passage: ' || body)) STORED.
• sentence-transformers/all-MiniLM-L6-v2 gives the cosine similarities of the sentence-transformers quickstart to four digits (0.6660, 0.1046, 0.1411).
• rank(transformer, query, documents text[], kwargs) gives TABLE(corpus_id, score, text): corpus_id is the index of the document from 0, the rows are by score, the highest first, and equal scores keep the documents' order. kwargs: top_k (all by default), return_documents (false: text is NULL), batch_size and device as for embed. The model must have one label.
• transform(task, args, inputs): task is 'text-generation' or 'conversational', or JSON {"task": ..., "model": ...}. The model is the task's model, else $AI_MODEL. text-generation sends each input as a user message and gives a JSON array of the generated text of each input (the text that follows the prompt). conversational reads the inputs as JSON messages ({"role": ..., "content": ...}) of one conversation and gives an array of one reply. args: max_new_tokens (or max_tokens, max_length), temperature, top_p, seed, stop; do_sample changes nothing; another key is a TypeError.
• transform_stream(task, args, input) gives a row ["PIECE"] for each piece of text that the model sent, in order. Its input is one text (or a conversation's array of messages).
• transform picks its model this way. A model that the models folder holds (its config.json is there; no download) runs in the server on gizml:llm: the first use converts config.json, tokenizer.json and model.safetensors (or the shards of model.safetensors.index.json) to 8-bit codes in the folder's .gizlang-llm/REPO, with the chat template of tokenizer_config.json (ChatML, with the default system message of the template, or Llama 3; none: the messages' contents, one a line) and the eos_token_id of generation_config.json. The session keeps the model's engine, on the server's GPU when it has one. Any other model goes to the model API.
• The model API is $AI_URL (an OpenAI-compatible base URL: OpenAI, vLLM, Ollama, llama.cpp) with $AI_API_KEY when it needs one, else OpenRouter with $AI_API_KEY. A local model uses the server's compute, and an API call sends its inputs to that server and may cost money, so only a superuser or a member of role pgml_llm calls transform; for other roles it is "permission denied". A model that the folder does not hold, with no model API, is an error. cache changes nothing.
• Local sampling: temperature 0 or less (the default) takes the most likely token each step; above 0 it draws from the softmax over the temperature, cut to top_p, from seed (the same seed gives the same text). max_new_tokens is 256 by default. The text stops at a stop token or before the first stop string; a stream sends it as one piece.
• Other model types (gte's NewModel, T5 for instructor, other decoders) are errors that name the type. Other transform tasks are errors that name the task.
• vectorize(relation, text_column, vector_column, transformer, kwargs, limit) (GizDB) keeps a vector column up to date with a text column. The first call records the pair in pgml.vectorizers and adds a BEFORE UPDATE OF trigger that sets the vector to NULL when the text changes. Each call embeds the rows whose vector is NULL and whose text is not (at most limit), by embed of each row, and records the commit it read through (lsn). A call with another transformer or other kwargs sets every vector to NULL first. It gives the count of rows it embedded.
• gizdb vectorize --url URL [--wait MS] [--batch N] [--once yes] (db/ml/worker.giz) calls vectorize for each row of pgml.vectorizers in batches until no row waits, then waits on changes(relation, lsn, wait) for a commit and calls it again.
• tune_search(search, judgments, kwargs) (GizDB) measures a search. search is a query of $1, the query text, whose first column is the ranked ids; judgments is a query of rows (query, relevant ids[, gains]). For each value of rrf_k (default [10, 30, 60, 100]), text_weight (default [0.25, 0.5, 1, 2, 4]) and ef_search (default: the session's value) it sets search.rrf_k, search.text_weight and hnsw.ef_search, runs the search for each judgment and gives the mean ndcg_at_k and recall_at_k at k (default 10), the best nDCG first. The session's settings come back after the call. (docs/internals/database.md has the hybrid search that these settings fuse.)

generate and tune fail with "pgml.NAME is not available in GizDB yet".

9. Review blockers

1. SQL, a model load or a text round trip for each row.
2. pgml metadata outside ordinary tables.
3. A second function dispatcher or vector engine.
4. A default that differs from PostgresML.
5. A new array storage format that cannot read the past format.