You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
ONNX Runtime integrated with PostgreSQL. Perform ML inference with data in your database.
PostgreSQL creates a process every new client connects. If every process ran ONNX inference, the system and GPU would
run out of memory. pg_onnx runs onnxruntime-server as a background
worker and creates and recycles one onnx runtime session per ONNX file.
graph LR
subgraph P[PostgreSQL]
direction LR
PM((postmaster))
PS1[postgres]
PS2[postgres]
PS3[postgres]
TABLE[(onnx model data)]
subgraph ONNX[pg_onnx Background Worker]
direction TB
OS[onnxruntime-server]
ONNX1([onnxruntime\nsession:\nmodel1])
ONNX2([onnxruntime\nsession:\nmodel2])
OS -. " create/execute session " .-o ONNX1 & ONNX2
end
PM -. " fork " .-o PS1 & PS2 & PS3
PM -. " dynamic background worker " .-o ONNX
PS3 <-- " import model " --> TABLE
PS1 & PS2 & PS3 <-- " ONNX operation " --> ONNX
end
C[Client trying to use pg_onnx] ==> PS3
-- Create a test tableCREATETABLEtrigger_test
(
id SERIALPRIMARY KEY,
value1 INT,
value2 INT,
value3 INT,
prediction FLOAT
);
-- Create a trigger functionCREATE OR REPLACEFUNCTIONtrigger_test_insert()
RETURNS TRIGGER AS
$$
DECLARE
result jsonb;
BEGIN
result := pg_onnx_execute_session(
'sample_model', 'v20230101',
JSONB_BUILD_OBJECT(
'x', ARRAY [[NEW.value1]],
'y', ARRAY [[NEW.value2]],
'z', ARRAY [[NEW.value3]]));
-- output shape: float[-1,1]-- eg: {"output": [[0.6492120623588562]]}NEW.prediction := result ->'output'->0->0;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
-- Create a triggerCREATETRIGGERtrigger_test_insert
BEFORE INSERT
ON trigger_test
FOR EACH ROW
EXECUTE PROCEDURE trigger_test_insert();
Functions
Provides several functions for importing ONNX file and executing and managing it.