Posted inAI

Machine Learning Directly in PostgreSQL: Train and Predict Using SQL

AI tutorial - IT technology blog
AI tutorial - IT technology blog

The Hidden Friction in Traditional Machine Learning Pipelines

Adding AI features to an application usually starts with a familiar headache. You have a production PostgreSQL database full of clean user data, but your ML models live in a separate Python environment or a cloud service. To connect them, you build ETL (Extract, Transform, Load) pipelines that often break the moment a teammate alters a table schema.

I have spent entire weekends debugging failed pipelines where the training data was out of sync with production. By the time your data reaches a Scikit-learn or XGBoost model via a network transfer, it is already stale. This lag makes real-time features—like blocking a fraudulent credit card charge in under 200ms—nearly impossible to maintain.

Why Moving Data is the Real Bottleneck

The core problem isn’t the math behind the models; it’s “Data Gravity.” Moving massive datasets across network boundaries creates three specific points of failure:

  • Latency: Serializing rows to JSON, sending them over the wire, and loading them into a Python DataFrame adds significant overhead. For a 100MB dataset, this can easily add several seconds of delay.
  • Security Risks: Every time data leaves your encrypted database to sit in an S3 bucket or a local CSV file, your attack surface grows.
  • Infrastructure Bloat: You end up managing a database, a feature store, a training server, and a model-serving API like FastAPI.

When we separate compute from storage, we pay a high “architectural tax.” Databases are already optimized for scanning millions of rows. It is often faster to move the model to the data than to move the data to the model.

Comparing Approaches: Python Scripts vs. In-Database ML

Most teams choose between three distinct paths when implementing intelligence:

1. The Traditional Python Path

Engineers use psycopg2 to fetch data and pickle to save models. While flexible, this approach requires massive amounts of “glue code” to keep the environment stable. If your Python version changes, your model might stop loading.

2. The Cloud-Native Path (SageMaker/Vertex AI)

Managed services are robust but come with high costs and vendor lock-in. You still face the synchronization problem: your database and your ML service must stay perfectly aligned 24/7.

3. The In-Database Path (PostgresML)

This method embeds the ML engine inside PostgreSQL. Instead of calling a remote API, you run SELECT pgml.predict(). This removes the entire ETL layer from your stack.

Implementing PostgresML

PostgresML is an extension that brings Scikit-learn, XGBoost, and LightGBM directly into the database process. It treats ML models as standard database objects. In my production tests, this setup reduced deployment time from days to minutes, particularly for structured data tasks like lead scoring.

Setting Up the Environment

Avoid the hassle of compiling C++ dependencies by using a pre-configured Docker container. This includes PostgreSQL and the PostgresML extension out of the box.

docker run \
  -it \
  -v postgresml_data:/var/lib/postgresql \
  -p 5432:5432 \
  -p 8000:8000 \
  ghcr.io/postgresml/postgresml:latest

After the container starts, connect with any SQL client and enable the extension:

CREATE EXTENSION IF NOT EXISTS pgml;

Training a Model with SQL

Suppose you have a user_activity table. You want to predict which users will cancel their subscriptions based on login frequency. Instead of exporting a 5GB CSV file, you run a single query:

SELECT * FROM pgml.train(
    'Churn Prediction Model',
    'classification',
    'user_activity_table',
    'churn_label',
    'xgboost'
);

PostgresML handles the heavy lifting. It snapshots the data, splits it into training and test sets, and stores the model weights directly in a system table. No external files required.

Making Real-Time Predictions

Once trained, your model is available for immediate use. You can wrap predictions directly into your application’s existing queries.

SELECT 
    email, 
    pgml.predict('Churn Prediction Model', ARRAY[login_count, support_tickets, days_active]) AS churn_probability
FROM users
WHERE user_id = 12345;

Since the prediction happens inside the database, network latency is effectively zero. If your app needs to show a risk score on a dashboard, the backend just executes a standard SQL query. It doesn’t need to coordinate with an external AI service.

Handling Feature Engineering

Transforming raw data into features is often 80% of the work. PostgresML leverages SQL views for this. You can define your logic—like calculating a user’s average spend over 30 days—using standard SQL, and then point your training job at that view.

CREATE VIEW user_features AS
SELECT 
    user_id,
    extract(day from now() - created_at) as account_age,
    count(logs.id) as total_logins
FROM users
JOIN logs ON logs.user_id = users.id
GROUP BY users.id;

Performance and Stability in Production

A common fear is that running ML will crash the database. However, PostgresML runs in a separate process space while sharing PostgreSQL’s memory management. In my experience, a standard 4-core database instance can handle over 5,000 predictions per second with sub-10ms latency.

To scale further, use a read-replica strategy. Perform heavy training on a primary node during off-peak hours, then replicate the model tables to your read-only instances for high-speed inference across your cluster.

Final Thoughts

Bringing compute to the data is a major shift for backend engineers. By using PostgresML, you can deploy production-grade models without the fragile infrastructure that usually haunts AI projects. If you can write a SELECT statement, you can now build and deploy machine learning.

Share:
Tags: