Generating Vector Embeddings Directly Inside PostgreSQL with pg_embedder

Modern enterprise applications rely heavily on high-dimensional vector embeddings to power artificial intelligence capabilities. From semantic search engines and retrieval-augmented generation (RAG) pipelines to recommendation systems, document classification, and real-time entity resolution, dense vector representations of textual data have become a foundational building block for contemporary software infrastructure. Historically, computing these numerical vector representations required extracting plaintext from primary relational databases, transmitting it over external network connections to third-party cloud APIs or internal Python microservices, and writing the returned floating-point arrays back into database storage. This multi-step out-of-database processing pattern introduces substantial network overhead, operational complexity, synchronization risks, and recurring API expenses.

The pg_embedder postgresql extension, developed by riclab, presents a fundamental shift in how PostgreSQL handles machine learning inference. By integrating local model inference capabilities directly into PostgreSQL backend worker processes, pg_embedder allows standard SQL queries to execute text tokenization and model execution natively within the database engine. This transformation turns PostgreSQL into a self-contained AI operational hub where data ingestion, vector generation, indexing, and similarity search occur entirely within the database security perimeter.

What is pg_embedder?

The pg_embedder postgresql extension is an open-source tool designed to execute lightweight machine learning embedding models directly within PostgreSQL server processes. Written in Rust using the pgrx extension development framework, pg_embedder bridges standard relational database tables with neural network inference engines. It exposes simple, deterministic SQL functions that accept raw text inputs and generate dense mathematical vectors compatible with PostgreSQL vector extensions such as pgvector.

At its core, pg_embedder leverages the Open Neural Network Exchange (ONNX) Runtime alongside native Rust subword tokenization libraries. This architecture enables PostgreSQL to run popular transformer models—such as MiniLM, BGE, and E5 variants—without requiring an external Python interpreter, Node.js sidecar service, or outbound network connection. Because inference runs within local CPU and RAM memory space on the database host, software developers can compute embeddings dynamically inside standard SELECT queries, bulk UPDATE operations, and automated transactional database triggers.

The Architectural Imperative for In-Database Embedding Generation

Building production-ready vector search and RAG systems requires navigating trade-offs between architectural complexity, query latency, data security, operational costs, and transactional consistency. Traditional out-of-process embedding pipelines force software engineers to maintain background job queues, manage third-party API rate limits, and write complex retry logic. Running inference directly inside PostgreSQL resolves these structural bottlenecks across four major dimensions:

1. Eliminating Network Latency and Microservice Overhead

In standard vector processing pipelines, text data traverses network boundaries twice: first from PostgreSQL to an application server or background worker process, and second from that worker process to an external embedding API endpoint. HTTP connection handshakes, JSON serialization, payload compression, and network congestion introduce latencies ranging from tens to hundreds of milliseconds per batch. The pg_embedder postgresql extension executes model inference inside local server memory space, completely eliminating network latency during vector calculation.

2. Complete Data Sovereignty and Zero Third-Party Data Egress

For organizations operating in regulated sectors—such as healthcare, finance, defense, and legal services—transmitting sensitive text records (e.g., electronic health records, financial transactions, or internal legal contracts) to external AI endpoints creates regulatory compliance risks under frameworks like HIPAA, GDPR, SOC2, and PCI-DSS. Utilizing pg_embedder guarantees that raw unstructured text and calculated vectors never leave your infrastructure perimeter, enabling secure deployment in fully isolated or air-gapped environment configurations.

3. Eliminating Variable Token-Based Billing Models

Commercial embedding APIs charge software teams based on the total number of processed tokens. As document repositories grow into millions of records—or require complete re-indexing whenever embedding models are updated—API token costs scale linearly. Deploying pg_embedder alongside open-weights ONNX models converts variable, consumption-based operational expenses into predictable compute hardware utilization.

4. Guaranteed Transactional Consistency with SQL Triggers

When embedding generation depends on asynchronous external background workers, a delay between text creation and vector generation is unavoidable. This delay creates window conditions where records exist without corresponding vectors, leading to incomplete or stale search results. Because pg_embedder operates inside standard PostgreSQL transactions, vector embeddings can be calculated and saved within a BEFORE INSERT or BEFORE UPDATE database trigger. This guarantees that source text data and its mathematical vector representation remain synchronized at all times under strict ACID guarantees.

Core Technical Architecture and Capabilities

The riclab/pg_embedder project provides low-level and high-level capabilities engineered specifically for high-throughput database operations. Key technical components of the extension include:

• Native ONNX Runtime Integration: Incorporates the C-based ONNX Runtime engine directly inside PostgreSQL backend worker processes, executing optimized neural network computational graphs on host CPU hardware.
• Embedded Tokenization Pipeline: Integrates native Rust subword tokenization libraries directly into the binary extension, executing text token splitting, vocabulary lookups, and input tensor padding without invoking external helper scripts.
• Direct Type Integration with pgvector: Converts output dynamic float arrays directly into PostgreSQL’s native vector data type, allowing immediate indexing with Hierarchical Navigable Small World (HNSW) or Inverted File Flat (IVFFlat) access methods.
• SQL-First User Interface: Exposes clean SQL scalar functions such as pg_embedder_embed(), enabling embedding calculations inside standard SQL queries, database views, stored procedures, and trigger functions.
• Zero Runtime Scripting Dependencies: Operates as a compiled, dynamic library (.so on Linux or .dylib on macOS). It requires no local Python installation, PyTorch, TensorFlow, or containerized sidecar runtimes.
• Dynamic Model Path Management: Supports configurable global system paths using PostgreSQL Grand Unified Configuration (GUC) parameters, allowing model selection across different schemas or database instances.

Architectural Comparison: In-Database vs. External Options

The table below highlights the operational, technical, and structural differences between using the pg_embedder postgresql extension directly within PostgreSQL versus relying on cloud APIs or dedicated Python microservices:

While external APIs allow offloading raw compute workloads to cloud providers, they introduce third-party network dependencies, potential data privacy risks, and ongoing consumption costs. Self-hosted Python services offer internal security control, but introduce operational friction by requiring queue workers, container management, and custom synchronization logic. The pg_embedder postgresql extension removes these extra operational layers by consolidating model execution and data storage within PostgreSQL.

System Prerequisites and Infrastructure Requirements

Before installing and configuring the pg_embedder postgresql extension, verify that your target host system meets all operating system, database engine, and compiler dependencies:

• PostgreSQL Engine: PostgreSQL version 14, 15, 16, or newer installed with system development header files for building C and Rust extensions (e.g., postgresql-server-dev-16 on Debian or Ubuntu distributions).
• pgvector Extension: The pgvector extension must be installed in the target PostgreSQL database instance to store and index high-dimensional vector outputs.
• Rust Compilation Toolchain: A stable Rust toolchain (including rustc and cargo) alongside the pgrx compilation framework configured for your target PostgreSQL version.
• ONNX Runtime Libraries: Shared C library binaries for ONNX Runtime (e.g., libonnxruntime.so on Linux or libonnxruntime.dylib on macOS) placed within standard system library discovery paths.
• ONNX Model Artifacts: Pre-trained transformer models exported to the ONNX standard format (model.onnx) alongside their matching Hugging Face tokenizer configuration files (tokenizer.json).

Step-by-Step Installation and Deployment Guide

Setting up pg_embedder involves installing system build dependencies, retrieving ONNX Runtime shared libraries, compiling the extension from source code using cargo-pgrx, provisioning local filesystem model directories, and registering the extension inside PostgreSQL.

Step 1: Install Operating System Build Packages

On Ubuntu or Debian Linux environments, update the system package repository index and install required build tools, C compilers, OpenSSL development libraries, Git, and PostgreSQL development headers:

sudo apt-get update
sudo apt-get install -y build-essential clang libssl-dev pkg-config postgresql-server-dev-16 curl git

Step 2: Download and Link ONNX Runtime Shared Libraries

Fetch the official ONNX Runtime C library binaries from the project release page and copy the shared library files into standard system library paths so the dynamic linker can locate them during runtime model execution:

# Download the ONNX Runtime release tarball
curl -LO https://github.com/microsoft/onnxruntime/releases/download/v1.17.1/onnxruntime-linux-x64-1.17.1.tgz
tar -xvf onnxruntime-linux-x64-1.17.1.tgz

# Move dynamic shared libraries to local system library folder
sudo cp onnxruntime-linux-x64-1.17.1/lib/libonnxruntime.so* /usr/local/lib/
sudo ldconfig

Step 3: Clone Repository and Compile pg_embedder

Clone the official riclab/pg_embedder source repository. Initialize the pgrx toolchain against your installed PostgreSQL binary path and compile the extension in release mode:

git clone https://github.com/riclab/pg_embedder.git
cd pg_embedder

# Install pgrx cargo utility if not previously installed
cargo install cargo-pgrx --version 0.11.3 --locked

# Initialize pgrx against the system PostgreSQL installation
cargo pgrx init --pg16 $(which pg_config)

# Build and install the extension binary into PostgreSQL system directories
cargo pgrx install --release

Step 4: Configure Filesystem Model Storage Directory

Create a dedicated directory on the local filesystem to store ONNX model files and tokenizer configurations. Ensure the directory and its enclosed model files are owned by the system user running the PostgreSQL service (typically postgres):

sudo mkdir -p /var/lib/postgresql/models/bge-small
# Place your model.onnx and tokenizer.json files into this model directory
sudo chown -R postgres:postgres /var/lib/postgresql/models

Step 5: Initialize Extensions and Set Configuration in PostgreSQL

Connect to the target PostgreSQL database using psql and enable both the vector and pg_embedder extensions:

CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_embedder;

-- Set the global base path for model discovery
ALTER SYSTEM SET pg_embedder.model_dir = '/var/lib/postgresql/models';
SELECT pg_reload_conf();

SQL API and Configuration Reference

The pg_embedder postgresql extension provides scalar SQL functions and custom database configuration settings to manage model resolution, text processing, and vector output casting.

1. Function Signature: pg_embedder_embed()

The primary SQL function used for text embedding generation is pg_embedder_embed():

pg_embedder_embed(model_name text, input_text text) RETURNS vector

• model_name (text): The directory name corresponding to a subfolder within the configured model base directory containing valid model.onnx and tokenizer.json files (e.g., 'bge-small').
• input_text (text): The text string to tokenize, encode, and process through the ONNX transformer model graph.
• Return Value: A dense floating-point vector typed natively as vector, matching the dimension of the chosen model (e.g., 384 dimensions for BGE-small or MiniLM, 768 dimensions for BGE-base).

2. PostgreSQL Configuration Parameters (GUCs)

Define the root directory for model discovery using PostgreSQL GUC parameters in postgresql.conf, at the database level, or dynamically within an individual SQL session:

-- Set model directory at runtime for the current database connection session
SET pg_embedder.model_dir = '/var/lib/postgresql/models';

-- Verify current configuration parameter setting
SHOW pg_embedder.model_dir;

End-to-End Implementation Workflows and Code Examples

The following technical code examples demonstrate how to integrate the pg_embedder postgresql extension into standard database operational patterns, ranging from simple validation queries to automated database triggers and high-speed similarity search.

1. Ad-Hoc Inference Verification in SELECT Statements

Verify model loading, tokenization, and vector output generation directly within an ad-hoc SQL query:

SELECT pg_embedder_embed('bge-small', 'PostgreSQL powers native machine learning inference.') AS output_vector;

2. Designing a High-Performance Relational Vector Schema

Create a dedicated table structure for technical documentation articles. The schema stores original text content alongside a 384-dimensional vector column generated by the bge-small embedding model:

CREATE TABLE documentation_articles (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    category TEXT NOT NULL,
    body_content TEXT NOT NULL,
    embedding vector(384),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

3. Batch Processing and Historical Record Population

Populate missing embeddings for existing database records using a single SQL UPDATE statement:

UPDATE documentation_articles
SET embedding = pg_embedder_embed('bge-small', title || ' - ' || body_content)
WHERE embedding IS NULL;

4. Real-Time Automated Embedding Triggers

To keep vector representations synchronized automatically, build a PL/pgSQL trigger function that computes or updates vector embeddings whenever a row is inserted or updated:

CREATE OR REPLACE FUNCTION trg_compute_documentation_embedding()
RETURNS TRIGGER AS $$
BEGIN
    -- Concatenate title and body text to generate a single composite embedding input
    NEW.embedding := pg_embedder_embed('bge-small', NEW.title || ' ' || NEW.body_content);
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_embed_documentation
BEFORE INSERT OR UPDATE OF title, body_content ON documentation_articles
FOR EACH ROW
EXECUTE FUNCTION trg_compute_documentation_embedding();

5. Executing Fast Vector Similarity Search Queries

Combine pg_embedder with pgvector distance operators (such as cosine distance <=>) to execute semantic search queries directly against database tables:

WITH search_query AS (
    SELECT pg_embedder_embed('bge-small', 'high performance database query optimization') AS query_vec
)
SELECT 
    doc.id,
    doc.title,
    doc.category,
    1 - (doc.embedding <=> sq.query_vec) AS similarity_score
FROM documentation_articles doc, search_query sq
WHERE doc.embedding IS NOT NULL
ORDER BY doc.embedding <=> sq.query_vec ASC
LIMIT 5;

Advanced Performance Tuning and Resource Management

Running neural network inference directly inside database server processes requires resource management to preserve high query throughput and low response latency under concurrent client connections.

1. Model Quantization (FP32 to INT8)

Standard 32-bit floating-point (FP32) ONNX transformer models require significant CPU memory bandwidth and computational cycles. Converting models to quantized 8-bit integer (INT8) format using Hugging Face Optimum or ONNX Runtime quantization tools reduces disk storage requirements and memory consumption by up to 75%. INT8 quantization accelerates CPU inference execution while maintaining semantic search accuracy.

2. Thread Governance and CPU Concurrency Control

By default, ONNX Runtime attempts to use all available CPU cores for intra-op parallelism during model execution. In multi-client PostgreSQL production deployments, unconstrained CPU multithreading across concurrent backend worker processes can lead to CPU core starvation. Configuring ONNX execution thread limits prevents CPU contention during peak database transaction loads.

3. Context Window Truncation and Text Sanitization

Most embedded transformer models enforce maximum token sequence lengths (e.g., 512 tokens for MiniLM and BGE-small variants). Passing text inputs longer than these limits without explicit handling can cause unnecessary compute overhead or sequence truncation errors. Ensure inputs passed to pg_embedder_embed() are sanitized and bounded at the SQL or application layer to maintain consistent execution times.

4. Vector Index Optimization with HNSW

Accelerate similarity search queries on large datasets by pairing vector columns populated by pg_embedder with HNSW indexes configured for cosine distance:

CREATE INDEX idx_doc_embedding_hnsw 
ON documentation_articles 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Enterprise Use Cases and Operational Deployment Patterns

Bringing native machine learning inference into PostgreSQL enables software architecture patterns across several key domain areas:

1. Air-Gapped Enterprise Knowledge Management

Organizations operating under strict security regulations—such as defense contractors, healthcare providers, and financial institutions—can implement semantic search engines and RAG pipelines entirely within isolated, air-gapped server environments. Internal operational manuals, clinical notes, or proprietary contracts are indexed and searched without exposing sensitive enterprise data to third-party endpoints.

2. Natural Language E-Commerce Catalog Search

Online retail platforms can generate vector embeddings for product titles, attributes, and customer reviews upon item creation. When users perform search queries using descriptive phrases (e.g., “waterproof lightweight trail running shoes”), the query string is converted to a vector in-database and matched against product catalog records instantly.

3. Real-Time Support Ticket Triage and Routing

Customer support platforms can use PostgreSQL triggers to calculate vector representations for incoming support tickets in real time. The database matches incoming requests against historical ticket databases or knowledge base articles, automatically suggesting relevant resolution steps or routing tickets to specialized support teams.

4. Automated Document Deduplication during Data Ingestion

High-volume text ingestion applications can calculate document embeddings inside a BEFORE INSERT trigger. By evaluating vector cosine distance against existing database records, the system can identify semantically duplicate documents and reject or merge them before saving them to disk.

Security, Compliance, and Data Governance Advantages

In-database model execution addresses critical security requirements inherent in traditional cloud-based AI workflows:

• Strict Data Boundary Protection: Raw text data remains within PostgreSQL buffer pools and encrypted storage volumes. Plaintext records never cross public networks or external REST API gateways.
• Simplified Compliance Auditing: Because model execution generates zero outbound network traffic, security compliance reviews for standards such as SOC2, ISO 27001, HIPAA, and GDPR are simplified.
• Elimination of Third-Party Vendor Risk: Deploying open-weights ONNX models removes dependencies on external commercial API vendors, protecting systems from unexpected API deprecations, service outages, or pricing updates.

Operational Troubleshooting and Problem Resolution

When deploying the pg_embedder postgresql extension in production environments, administrators may encounter operational issues related to library resolution, permissions, or system resources. Use the following diagnostic steps to resolve common setup challenges:

1. Shared Library Loading Errors (libonnxruntime.so missing)

If PostgreSQL fails to load pg_embedder.so with an error indicating that libonnxruntime.so cannot be found, verify that the ONNX Runtime dynamic library resides in a directory listed in /etc/ld.so.conf or /etc/ld.so.conf.d/, and run sudo ldconfig to refresh the system dynamic linker cache.

2. Permission Denied Errors on Model Directories

If pg_embedder_embed() raises a file access error, confirm that the operating system user running PostgreSQL (typically postgres) possesses read and execute permissions across the target model directory path:

sudo chown -R postgres:postgres /var/lib/postgresql/models
sudo chmod -R 755 /var/lib/postgresql/models

3. Vector Dimension Mismatches with pgvector Columns

Ensure that the output vector dimension generated by your selected ONNX model matches the dimension declared in your table schema (e.g., 384 dimensions for BGE-small). If the table column is declared as vector(768) and a 384-dimensional model is executed, PostgreSQL will return a runtime type mismatch error.

Summary and Ecosystem Outlook

The pg_embedder postgresql extension provides a self-contained solution for running machine learning embedding models natively inside PostgreSQL. By eliminating external HTTP calls and microservice dependencies, it simplifies system architecture, enhances data privacy, and removes token-based API cost constraints. Combined with vector indexing extensions like pgvector, pg_embedder enables engineers to build secure, high-performance semantic search engines and RAG applications directly within PostgreSQL.

Resource Links

• GitHub Repository: riclab/pg_embedder on GitHub
• pgvector Extension: pgvector GitHub Repository
• ONNX Runtime Documentation: ONNX Runtime Official Site
• Hugging Face Tokenizers: Hugging Face Tokenizers Documentation