pql

927 downloads this week 35 stars Extension descriptor

Predictive queries as SQL. TRAIN a model on the tables you already have, PREDICT with it, BACKTEST it, and OPTIMIZE a plan from its predictions.

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL pql FROM community;
LOAD pql;

Example

-- 1,200 customers, their orders, and a question about the future.
CREATE TABLE customers AS
  SELECT i AS id, 1 + i % 4 AS tier,
         DATE '2025-01-01' + INTERVAL (i % 300) DAY AS last_seen
  FROM range(1200) t(i);
CREATE TABLE orders AS
  SELECT c.id AS customer_id,
         c.last_seen - INTERVAL (7 * k + (c.id * 7 + k * 13) % 5) DAY AS ts,
         10 + (c.id * 31 + k * 17) % 90 AS amount
  FROM customers c, range(1, 9) t(k)
  WHERE k <= c.tier + c.id % 3;
INSERT INTO orders
  SELECT id, last_seen + INTERVAL (1 + id * 13 % 28) DAY, 10 + id * 3 % 90
  FROM customers WHERE tier * 20 + id * 37 % 60 > 70;

-- Will each customer order again within 30 days of when we last saw them?
TRAIN MODEL reorder PREDICT EXISTS(orders) FOR customers AT last_seen HORIZON 30 DAYS;

-- One row per customer, with a probability.
PREDICT EXISTS(orders) FOR customers WHERE tier >= 3 USING MODEL reorder;

About pql

PQL adds predictive statements to SQL. A model is trained directly on the tables in the catalog: PQL follows the foreign keys, builds features from the related tables (counts, averages, recency, spacing), holds out a slice by time, and reports how good the model is before you use it. No feature engineering, no export, no separate service.

The statements

TRAIN [OR REPLACE] MODEL <name>
  PREDICT <target>                        -- a column, or COUNT / EXISTS / SUM / AVG / MIN / MAX over a related table
  FOR <table> [AS <alias>]
  [ WHERE <filter> ]                      -- which entities take part
  [ AT <column> | EVERY <n> <unit> ]      -- the anchor time of each example
  [ HORIZON <n> <unit> ]                  -- how far ahead the target is counted
  [ USING GRAPH (<table>, ...) ]          -- restrict the tables used
  [ EXCLUDE (<column>, ...) ]             -- columns the model must not see
  [ SPLIT TEMPORAL VALIDATE FROM <ts> TEST FROM <ts> ]
  [ OPTIONS (<key> = <value>, ...) ];

PREDICT <target> FOR <table> [ WHERE <filter> ] USING MODEL <name>;
BACKTEST MODEL <name> [ FROM <ts> ] [ TO <ts> ];   -- replay it row by row against what happened
EXPLAIN MODEL <name>;                                -- which features it relies on
DROP MODEL [IF EXISTS] <name>;

[EXPLAIN] OPTIMIZE <table> [ WHERE <filter> ]       -- a plan from the predictions (three forms, below)
  [ CHOOSE <column> IN (<value>, ...) | ASSIGN <column> OVER <table>.<key> [ ... ] ]
  MAXIMIZE | MINIMIZE <total> [ SUBJECT TO <limit> [ AND ... ] ]
  USING MODEL <name>;

Every statement is also a table function, pql_exec('<statement>'), so a prediction joins to anything. pql_models() lists the models in the session.

What TRAIN returns

The hello-world above prints one row like this:

model    target          train_rows val_rows test_rows metric  val    test   pr_auc  baseline features
reorder  EXISTS(orders)  710        237      237       auroc   0.871  0.815  0.891   0.608    26

• test is the number to trust: scored once, on a later slice the model never saw during selection.
• baseline is what you get with no model at all: the positive rate for a yes/no target, "the same as last window" for a quantity. If test does not beat it, PQL has just told you so.
• pr_auc is average precision, the honest score when positives are rare.
• val picked the epoch and is optimistic by construction. Do not quote it.

Two kinds of question

A column that already exists is imputation and needs no dates:

TRAIN MODEL vip PREDICT customers.is_vip FOR customers;

Something in the future is a forecast. AT says where "now" is for each row, HORIZON how far to look, and the target is any aggregate over a related table, optionally narrowed:

TRAIN MODEL spend   PREDICT SUM(orders.amount) FOR customers AT last_seen HORIZON 60 DAYS;
TRAIN MODEL refunds PREDICT EXISTS(orders WHERE orders.status = 'refunded')
  FOR customers AT last_seen HORIZON 60 DAYS;

For forecasts the engine computes the label itself from the window after the anchor and builds features only from before it, so the answer cannot leak into the inputs. Rows without a natural timestamp can use EVERY 1 MONTH to generate a grid of anchors from the data's own span.

See why, and check it row by row

EXPLAIN MODEL reorder;

feature              slots importance
tier                 1     0.381
orders: count 365d   1     0.018
orders: count 30d    1     0.010

Each feature group is shuffled in turn and importance is what the model loses. If something you did not expect sits at the top, that is usually a leak; EXCLUDE (that_column) removes it before anything is fitted.

SELECT count(*) AS n,
       round(avg(abs(error)), 2)             AS model_mae,
       round(avg(abs(baseline - actual)), 2) AS persistence_mae
FROM pql_exec('BACKTEST MODEL spend FROM ''2025-09-15''');

From a prediction to a plan: OPTIMIZE

OPTIMIZE is one statement. It turns a model's predictions into a plan that maximises or minimises a total under limits, in one of three forms:

-- Which rows: fifty calls, to the customers worth most who would not reorder on their own.
OPTIMIZE customers
  MAXIMIZE SUM((1 - prediction) * tier * 100)
  SUBJECT TO COUNT(*) <= 50
  USING MODEL reorder;

-- A value per row: the offer for each customer, under a budget.
OPTIMIZE customers
  CHOOSE offer IN ('none', '10%', '20%')
  MAXIMIZE SUM(prediction - CASE offer WHEN '10%' THEN 10 WHEN '20%' THEN 20 ELSE 0 END)
  SUBJECT TO SUM(CASE offer WHEN '10%' THEN 10 WHEN '20%' THEN 20 ELSE 0 END) <= 5000
  USING MODEL spend;

-- A resource per row, in sequence: jobs on crews, with travel, time windows and each crew's hours.
OPTIMIZE jobs WHERE open = 1 AT received_at
  ASSIGN crew OVER crews.crew ELSE 'none'
    ONLY IF resource.kind = kind
    SEQUENCE FROM resource.free_from
      START    DISTANCE(resource.lat, resource.lon, from_lat, from_lon) / 40
      SETUP    DISTANCE(prev.to_lat, prev.to_lon, from_lat, from_lon) / 40
      DURATION miles / 40 + 1
      WINDOW BETWEEN window_from AND window_to
  MAXIMIZE SUM(prediction - plan.setup * 60)
  SUBJECT TO SUM(plan.setup + plan.duration) PER crew <= resource.hours
  USING MODEL job_margin;

With CHOOSE, one row's choice never changes what another row is worth. With ASSIGN it does: a job uses its crew's hours, and its travel depends on the job before it. So ASSIGN has its own solver and plan columns (sequence, begin, setup, duration). In every form, EXPLAIN OPTIMIZE reports the plan's total beside a bound no plan can beat; when the two are equal, the plan is proven best.

It is still SQL

SELECT c.tier, round(avg(p.prediction), 3) AS chance_to_reorder
FROM pql_exec('PREDICT EXISTS(orders) FOR customers USING MODEL reorder') p
JOIN customers c ON c.id = p.id
GROUP BY c.tier ORDER BY 1;

Good to know

• Needs DuckDB v1.5.5 or later; built against the current release and tested against the next one on every change.
• Models live for the session and are not saved in the database file. Retraining is seconds, so TRAIN in the same job you PREDICT in.
• Predicting a column that already exists has no leakage guarantee: every other column on the row is a feature, including ones that are consequences of the target. A near-perfect score there usually means a tautology. Forecast targets are the path with the structural guarantee.
• Filters compare a column to a literal (=, <, IN, IS NULL, AND, OR). Tables are named without a schema. Text targets are refused rather than trained as multi-class.
• SHOW MODELS is not available on its own (DuckDB parses it first); use pql_models() or pql_exec('SHOW MODELS').
• On RelBench, the standard benchmark for prediction over relational databases, PQL scores in the same class as the published graph neural network and as gradient boosting over hand-built features, from a single statement on a laptop CPU.

Learn more

• Tutorial: a 15-minute walk from first model to backtest, with real output.
• Reference: full syntax, options, result columns, and the current limitations.
• How it works: features, leakage, the two model architectures, and the OPTIMIZE solvers.
• Issues and questions: https://github.com/Guepard-Corp/duckdb-pql/issues

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings