PostgresML aims to be the easiest way to gain value from machine learning. Anyone with a basic understanding of SQL should be able to build and deploy models to production, while receiving the benefits of a high performance machine learning platform. PostgresML leverages state of the art algorithms with built in best practices, without having to setup additional infrastructure or learn additional programming languages.


PostgresML is a Proof of Concept to create the simplest end-to-end machine learning system. We're building on the shoulders of giants, namely Postgres which is arguably the most robust storage and compute engine that exists, and we're coupling that with Python machine learning libraries (and their c implementations) to prototype different machine learning workflows.

Common architectures driven by standard organizational hierarchies make it hard to employ machine learning successfully, i.e[Conway's Law](https://en.wikipedia.org/wiki/Conway%27s_law). A single model at a unicorn scale startup may require work from Data Scientists, Data Engineers, Machine Learning Engineers, Infrastructure Engineers, Reliability Engineers, Front & Backend Product Engineers, multiple Engineering Managers, a Product Manager and finally, the Business Partner(s) this "solution" is supposed to eventually address. It can take multiple quarters of effort to shepherd a first effort. The typical level of complexity adds risk, makes maintenance a hot potato and iteration politically difficult. Worse, burnout and morale damage to expensive headcount have left teams and leadership warry of implementing ML solutions throughout the industry, even though FAANGs have proven the immense value when successful.

Our goal is that anyone with a basic understanding of SQL should be able to build and deploy machine learning models to production, while receiving the benefits of a high performance machine learning platform. Ultimately, PostgresML aims to be the easiest, safest and fastest way to gain value from machine learning.

*How far can this scale?*

Petabyte sized Postgres deployements are[documented](https://www.computerworld.com/article/2535825/size-matters--yahoo-claims-2-petabyte-database-is-world-s-biggest--busiest.html) in production since at least 2008, and[recent patches](https://www.2ndquadrant.com/en/blog/postgresql-maximum-table-size/) have enabled working beyond exabyte up to the yotabyte scale. Machine learning models can be horizontally scaled using industry proven Postgres replication techniques.

*How reliable can this be?*

Postgres is widely considered mission critical, and some of the most[reliable](https://www.postgresql.org/docs/current/wal-reliability.html) technology in any modern stack. PostgresML allows an infrastructure organization to leverage pre-existing best practices to deploy machine learning into production with less risk and effort than other systems. For example, model backup and recovery happens automatically alongside normal data backup procedures.

*How good are the models?*

Model quality is often a tradeoff between compute resources and incremental quality improvements. Sometimes a few thousands training examples and an off the shelf algorithm can deliver significant business value after a few seconds of training a model. PostgresML allows stakeholders to choose several different algorithms to get the most bang for the buck, or invest in more computationally intensive techniques as necessary. In addition, PostgresML automatically applies best practices for data cleaning like imputing missing values by default and normalizing data to prevent common problems in production.

PostgresML doesn't help with reformulating a business problem into a machine learning problem. Like most things in life, the ultimate in quality will be a concerted effort of experts working over time. PostgresML is intended to establish successful patterns for those experts to collaborate around while leveraging the expertise of open source and research communities.

*Is PostgresML fast?*

Colocating the compute with the data inside the database removes one of the most common latency bottlenecks in the ML stack, which is the (de)serialization of data between stores and services across the wire. Modern versions of Postgres also support automatic query parrellization across multiple workers to further minimize latency in large batch workloads. Finally, PostgresML will utilize GPU compute if both the algorithm and hardware support it, although it is currently rare in practice for production databases to have GPUs. We're working on[benchmarks](sql/benchmarks.sql).

- Train models directly in Postgres with data from a table or view

- Make predictions in Postgres using SELECT statements

- Manage new versions and algorithms over time as your solution evolves

- Model management dashboard

- Data explorer

- Scheduled training

- More algorithms and libraries including custom algorithm support

The two most important functions the framework provides are:

1.`pgml.train(project_name TEXT, objective TEXT, relation_name TEXT, y_column_name TEXT)`,

1.`pgml.train(project_name TEXT, objective TEXT, relation_name TEXT, y_column_name TEXT, algorithm TEXT)`,

2.`pgml.predict(project_name TEXT, VARIADIC features DOUBLE PRECISION[])`.

The first function trains a model, given a human-friendly project name, a`regression` or`classification` objective, a table or view name which contains the training and testing datasets,

and the name of the`y` column containing the target values. The second function predicts novel datapoints, given the project name for an exiting model trained with`pgml.train`,

and a list of features used to train that model.

The first function trains a model, given a human-friendly project name, a`regression` or`classification` objective, a table or view name which contains the training and testing datasets, and the name of the`y` column containing the target values. The second function predicts novel datapoints, given the project name for an exiting model trained with`pgml.train`, and a list of features used to train that model.

We'll be using the[Red Wine Quality](https://www.kaggle.com/datasets/uciml/red-wine-quality-cortez-et-al-2009) dataset from Kaggle for this example. You can find it in the`data` folder in this repository.

You can import it into PostgresML running in Docker with this:

You can also browse complete[code examples in the repository](examples/).

We'll be using the[Red Wine Quality](https://www.kaggle.com/datasets/uciml/red-wine-quality-cortez-et-al-2009) dataset from Kaggle for this example. You can find it in the`data` folder in this repository. You can import it into PostgresML running in Docker with this:

$ psql -f data/winequality-red.sql -p 5433 -U root -h 127.0.0.1

(1 row)

Checkout the[documentation](https://TODO) to view the full capabilities, including:

-[Creating Training Sets](https://TODO)

-[Classification](https://TODO)

-[Regression](https://TODO)

-[Supported Algorithms](https://TODO)

-[Scikit Learn](https://TODO)

-[XGBoost](https://TODO)

-[Tensorflow](https://TODO)

-[PyTorch](https://TODO)

- Model management dashboard

- Data explorer

- More algorithms and libraries incluiding custom algorithm support

*How well does this scale?*

Petabyte sized Postgres deployements are[documented](https://www.computerworld.com/article/2535825/size-matters--yahoo-claims-2-petabyte-database-is-world-s-biggest--busiest.html) in production since at least 2008, and[recent patches](https://www.2ndquadrant.com/en/blog/postgresql-maximum-table-size/) have enabled working beyond exabyte up to the yotabyte scale. Machine learning models can be horizontally scaled using well tested Postgres replication techniques on top of a mature storage and compute platform.

*How reliable is this system?*

Postgres is widely considered mission critical, and some of the most[reliable](https://www.postgresql.org/docs/current/wal-reliability.html) technology in any modern stack. PostgresML allows an infrastructure organization to leverage pre-existing best practices to deploy machine learning into production with less risk and effort than other systems. For example, model backup and recovery happens automatically alongside normal data backup procedures.

*How good are the models?*

Model quality is often a tradeoff between compute resources and incremental quality improvements. PostgresML allows stakeholders to choose algorithms from several libraries that will provide the most bang for the buck. In addition, PostgresML automatically applies best practices for data cleaning like imputing missing values by default and normalizing data to prevent common problems in production. After quickly enabling 0 to 1 value creation, PostgresML enables further expert iteration with custom data preperation and algorithm implementations. Like most things in life, the ultimate in quality will be a concerted effort of experts working over time, but that shouldn't get in the way of a quick start.

*Is PostgresML fast?*

Colocating the compute with the data inside the database removes one of the most common latency bottlenecks in the ML stack, which is the (de)serialization of data between stores and services across the wire. Modern versions of Postgres also support automatic query parrellization across multiple workers to further minimize latency in large batch workloads. Finally, PostgresML will utilize GPU compute if both the algorithm and hardware support it, although it is currently rare in practice for production databases to have GPUs. Checkout our[benchmarks](https://todo).

Follow the installation instructions to create a local working Postgres environment, then install your PgML package from the git repository:

Run thetest:

Run thetests from the root of the repo:

Make sure to run it exactly like this, from the root directory of the repo.