pgml

Overview

PIGSTY 3rd Party Extension: pgml : PostgresML: Run AL/ML workloads with SQL interface

Information

• Extension ID: 1890
• Extension Name: pgml
• Package Name: pgml
• Category: RAG
• License: MIT
• Website: https://github.com/postgresml/postgresml
• Language: Rust
• Extra Tags: pgrx
• Comment: N/A

Metadata

• Latest Version: 2.10.0
• Postgres Support: 17,16,15,14
• Need Load: Explicit Loading Required
• Need DDL: Need CREATE EXTENSION DDL
• Relocatable: Can be installed into other schemas
• Trusted: Untrusted, Require Superuser to Create
• Schemas: pgml
• Requires: N/A

RPM / DEB

• RPM Repo: None
• RPM Name: N/A
• RPM Ver : N/A
• RPM Deps: N/A
• DEB Repo: PIGSTY
• DEB Name: postgresql-$v-pgml
• DEB Ver : 2.10.0
• DEB Deps: N/A

Packages

Installation

Install pgml via the pig CLI tool:

pig ext install pgml

Install pgml via Pigsty playbook:

./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pgml"]}' # -l <cls>

Install pgml DEB from APT repo directly:

apt install postgresql-17-pgml;
apt install postgresql-16-pgml;
apt install postgresql-15-pgml;
apt install postgresql-14-pgml;

Extension pgml has to be added to shared_preload_libraries

shared_preload_libraries = 'pgml'; # add to pg cluster config

Enable pgml extension on PostgreSQL cluster：

CREATE EXTENSION pgml;

Usage

After installing the pgml extension and python dependencies on all cluster nodes, you can enable pgml on the PostgreSQL cluster.

Configure cluster with patronictl command and add pgml to shared_preload_libraries, and specify your venv dir in pgml.venv:

shared_preload_libraries: pgml, timescaledb, pg_stat_statements, auto_explain
pgml.venv: '/data/pgml'

After that, restart database cluster, and create extension with SQL command:

CREATE EXTENSION vector;        -- nice to have pgvector installed too!
CREATE EXTENSION pgml;          -- create PostgresML in current database
SELECT pgml.version();          -- print PostgresML version string

If it works, you should see something like:

# create extension pgml;
INFO:  Python version: 3.11.2 (main, Oct  5 2023, 16:06:03) [GCC 8.5.0 20210514 (Red Hat 8.5.0-18)]
INFO:  Scikit-learn 1.3.0, XGBoost 2.0.0, LightGBM 4.1.0, NumPy 1.26.1
CREATE EXTENSION

# SELECT pgml.version(); -- print PostgresML version string
 version
---------
 2.7.8

You are all set! Check PostgresML for more details: https://postgresml.org/docs/guides/use-cases/