# 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 | Subject | Owner | Not made | | --- | --- | --- | | pgml metadata: projects, snapshots, models, deployments, files | Ordinary GizDB tables and views that `CREATE EXTENSION pgml` makes. Catalog, Store and MVCC as for any table. | A model registry or an AI catalog | | Signatures, named arguments, defaults, set-returning calls, calls for each row | Routine objects (`db/sql/catalog/routine.giz`) with language `native`: `positional`, `Conn.routineFrom`, `Conn.rowCalls` | A second function dispatcher | | Pure vector functions and aggregates | `db/sql/types/reals.giz`, in the scalar and aggregate registry of `db/sql/functions/func.giz` | A separate vector engine | | Algorithms, statistics and linear algebra | The SDK, for every program: `gizlang:linalg`, `gizlang:stats`, `gizml:trees`, `gizml:svm`, `gizml:cluster`, and `gizml:ml` (one interface over them: fit, predict, metrics, preprocessing, search, model bytes) | Python, scikit-learn, or a linked xgboost or lightgbm C library | | Text models: tokenizers, text splitters, the BERT-family encoders, pooling, cross-encoders, the Hub folder, the model API client | `gizml:tokenizer`, `gizml:nn` on `gizml:tensor`, and `gizlang:hub` (§8) | Python, transformers, or a linked ONNX or libtorch runtime | | The SQL surface: schema, routines, snapshots, deployments | `db/ml/` (`pgml.giz`, `schema.giz`, `datasets.giz`, `embed.giz`, `transform.giz`) over `gizml:ml` and `gizml:nn` | A second copy of an algorithm in `db/` | | Reads of user data | SQL that the session runs (`Conn.innerQuery`): snapshots, permissions and row security apply | A path that reads tables around the executor | 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`. | Function | Result | Runs | | --- | --- | --- | | `train`, `train_joint` | `TABLE(project, task, algorithm, deployed)` | Session: snapshot, preprocess, search, fit, metrics, save, automatic deploy | | `deploy(model_id)`, `deploy(project, strategy, algorithm)` | `TABLE(project, strategy, algorithm)` | Session | | `predict`, `predict_proba`, `predict_joint`, `predict_batch`, `decompose` | `real`, `real[]`, `SETOF real` | The statement reads the deployed model once and puts a closure in the function slot for each row (`Conn.rowCalls`) | | `snapshot` | `TABLE(relation, y_column_name)` | Session | | `load_dataset` | `TABLE(table_name, rows)` | Session. The six scikit-learn datasets are in the source (`db/ml/datasets.giz`), gzip-compressed. Another source (HuggingFace) is an error. | | `chunk` | `TABLE(chunk_index, chunk)` | Pure (`gizml:tokenizer` splitters, kwargs in `db/ml/chunk.giz`): character, recursive_character, python, markdown and latex of langchain-text-splitters 0.3.5. nltk, spacy and `is_separator_regex` are errors. | | `embed(transformer, text, kwargs)`, `embed(transformer, inputs text[], kwargs)` | `real[]`, `SETOF real[]` | Session: §8 | | `rank(transformer, query, documents text[], kwargs)` | `TABLE(corpus_id, score, text)` | Session: §8 | | `transform(task, args, inputs)`, `transform_stream(task, args, input)` | `jsonb`, `SETOF jsonb` | Session: a decoder in the server, else the model API: §8 | | `vectorize(relation, text_column, vector_column, transformer, kwargs, limit)` (GizDB) | `bigint` | Session: §8 | | `tune_search(search, judgments, kwargs)` (GizDB) | `TABLE(rrf_k, text_weight, ef_search, ndcg, recall)` | Session: §8 | | `generate`, `tune` | | Not supported: an error | | `version`, `python_version`, `validate_*`, `activate_venv`, `clear_gpu_cache`, `python_package_version`, `python_pip_freeze` | Text or boolean | Constant answers; `version()` is `2.10.0 (gizdb)` | | `sklearn_*_score`, `sklearn_*_metrics` | `real`, `jsonb` | Pure: `gizml:ml` | | `dump_all`, `load_all` | `void` | Session, through COPY | | `export_onnx(model_id)`, `import_onnx(project, model, task, automatic_deploy)`, `mlflow_model(model_id)` (GizDB) | `bytea`, `TABLE(project, task, algorithm, deployed)`, `text` | Session: §6.1 | 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. | Module | Algorithms | | --- | --- | | `gizlang:linalg` | Matrix product, Cholesky, Householder QR, symmetric eigendecomposition (Jacobi), SVD | | `gizml:ml` (linear) | linear (OLS; logistic regression with lbfgs for classification), ridge, lasso, elastic_net (coordinate descent), least_angle, lasso_least_angle, orthogonal_matching_pursuit, bayesian_ridge, automatic_relevance_determination, stochastic_gradient_descent, perceptron, passive_aggressive, ransac, theil_sen, huber, quantile, kernel_ridge, gaussian_process | | `gizml:svm` | svm (SVC and SVR with rbf, SMO), nu_svm, linear_svm (dual coordinate descent of liblinear) | | `gizml:trees` | CART (gini, entropy, squared error), random_forest, extra_trees, bagging, ada_boost (SAMME), gradient_boosting_trees, and one histogram GBDT that xgboost, xgboost_random_forest, lightgbm, hist_gradient_boosting and catboost share, each with its own defaults | | `gizml:cluster` | kmeans (k-means++), mini_batch_kmeans, dbscan, birch, affinity_propagation, mean_shift, optics, spectral, spectral_bi, spectral_co, feature_agglomeration | | `gizlang:linalg` (decomposition) | pca | | `gizml:ml` (metrics) | The metrics of §5, silhouette | | `gizml:ml` (model bytes) | The model byte format and the prediction closures | | `gizlang:stats` | Moments, median, mode, quantiles, histograms: the snapshot statistics | 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. | Rule | xgboost (`hist`) | LightGBM | scikit-learn HistGradientBoosting | CatBoost | | --- | --- | --- | --- | --- | | Tree growth | Depth-wise | Leaf-wise, `num_leaves` | Leaf-wise, `max_leaf_nodes` | Oblivious (one split for each level) | | Leaf value | -G / (H + λ) × rate | Same | Same | Same, but CatBoost scales λ and uses gradient steps for RMSE: differs | | Split gain | `gamma` on ½ × gain | `min_split_gain` on the full gain | No threshold | No threshold; CatBoost's Cosine score and `random_strength` are not done: differs | | Child limits | Hessian sum ≥ `min_child_weight` | `min_child_samples` and `min_child_weight` | `min_samples_leaf` | None | | Start score | Mean or log-odds of the mean; zero for multiclass. xgboost estimates its intercept by its own method: the first trees can differ | `boost_from_average`: mean or log-odds; zero for multiclass | Mean, log-odds or log of the class priors | Mean for regression; zero for classification | | Softmax hessian | 2p(1 - p) | k / (k - 1) × p(1 - p) | p(1 - p) | p(1 - p) | | Row and column samples | `subsample`, `colsample_bytree` for each tree | `subsample` only with `subsample_freq` > 0, for each tree (LightGBM draws again each `subsample_freq` rounds: differs) | None | None (CatBoost's Bayesian bootstrap is not done: differs) | | Bins | Quantile cuts, at most `max_bin`; xgboost uses a weighted quantile sketch: differs | At most `max_bin`; LightGBM's bin rules (`min_data_in_bin`) differ | At most `max_bins` | `border_count` | | Not done | `alpha`, `max_delta_step`, `colsample_bylevel`, missing-value directions, `xgboost_random_forest` samples columns for each tree, not each node | `lambda_l1`, `extra_trees`, categorical splits, missing-value directions | Early stopping, which scikit-learn turns on past 10000 rows | Ordered boosting, categorical target statistics, the automatic learning rate (GizDB uses 0.03 × √(1000 / iterations)) | NULLs and text columns are prepared by `preprocess` (§5) before the GBDT sees them, for all four families. Result compatibility: | Kind | Criterion | | --- | --- | | Vector functions, aggregates, chunk, load_dataset, snapshot (`last`), deploy strategies, metric formulas | The values of PostgresML, f32 rounding included | | Deterministic training (linear, ridge, lasso, elastic_net, lars, omp, pca) | Coefficients and metrics of scikit-learn within 1e-4 relative | | Stochastic training (forests, boosting, sgd, svm approximations) | The same output shape and metric keys; metrics within 0.05 of scikit-learn on the same dataset | | Errors | An error in the same condition, with the message of PostgresML | `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: | Part | Owner | | --- | --- | | Token ids from `tokenizer.json` (tokenizers 0.19): the WordPiece model (BERT), the Unigram model (SentencePiece: XLM-RoBERTa, multilingual E5, BGE-M3) and the BPE model (merges in both forms, `ignore_merges`, byte fallback: GPT-2, Llama 3, Qwen2); the normalizers BertNormalizer, Precompiled (the SentencePiece character map), NFC, NFD, NFKC, NFKD, Lowercase, Strip, StripAccents, Replace, Prepend and Sequence; the pre-tokenizers BertPreTokenizer, Metaspace, WhitespaceSplit, ByteLevel, Split and Sequence; the post-processors TemplateProcessing, BertProcessing and RobertaProcessing; added tokens; truncation of one text and of a pair (`longest_first`); the decoders ByteLevel, Replace, Strip, Metaspace, WordPiece, ByteFallback, Fuse and Sequence | `gizml:tokenizer` (`parse`, `Tokenizer.encode`, `Tokenizer.encodePair`, `Tokenizer.tokens`, `Tokenizer.decode`) | | `model.safetensors` (F32, F16, BF16, F64 and integer tensors) and the encoder (`model_type` bert, roberta and xlm-roberta: absolute positions from 0, or from `pad_token_id + 1` for RoBERTa; erf or tanh GELU or ReLU; post-LayerNorm) | `gizml:nn` (`load`, `bert`) on `gizml:tensor` | | `modules.json`, the pooling config (mean, cls, max, mean_sqrt_len, last token), Normalize, `sentence_bert_config.json` `max_seq_length` | `gizml:nn` (`encoderOf`, `Encoder.embedBatch`) | | The sequence classification head (BERT: the pooler and `classifier`; RoBERTa and XLM-RoBERTa: `classifier.dense` and `classifier.out_proj`) and its activation (sigmoid for one label, else identity; `sentence_transformers.activation_fn` in `config.json` names another) | `gizml:nn` (`crossEncoderOf`, `CrossEncoder.scores`, `CrossEncoder.rank`) | | The language model (`model_type` llama, mistral, qwen2, qwen3 and Qwen3-VL's text model: converted once to 8-bit codes, then a paged key and value cache), the chat templates ChatML (with the template's default system message) and Llama 3, and sampling (greedy, temperature, top_p, seed, stop tokens; stop strings cut in pgml) | `gizml:llm` (`convert`, `load`, `Model.chatPrompt`, `Engine.complete`) | | The weights on a GPU | `gizml:nn` (`Encoder.on`, `CrossEncoder.on`) and `gizml:llm` (`load`) on `gizhw:gpu` | | The models folder and the download from huggingface.co | `gizlang:hub` (`Hub.file`, `Hub.repositories`) | | The model API client: requests, server-sent events, sampling options | `gizlang:hub` (`modelApi`, `stream`, `pieces`, `complete`), also used by the GizStack builder | | The server's models, the GPU and the kwargs | `db/ml/embed.giz`; the transform tasks and their args: `db/ml/transform.giz`; vectorize and its worker: `db/ml/vectorize.giz`, `db/ml/worker.giz`; tune_search: `db/ml/tune.giz` | - 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.