The pg_bio Series This post is part of a 4-part series on building a bioinformatics engine natively in PostgreSQL.
- Part 1: Why Proteins are just High-Dimensional Vectors
- Part 2: Writing a Hybrid Database Operator for Biology (<~>) (You are here)
- Part 3: Indexing 3D Space like a Video Game
- Part 4: Mining the Dark Proteome: Legacy Code with Missing Docs
AI Vector databases are the darling of the tech industry right now. We use them for everything from RAG chatbots to Netflix recommendations. But there is a dirty little secret about AI embeddings: They hallucinate.
In a chatbot, an AI hallucination is a funny screenshot on Twitter. In computational biology, an AI hallucination means a drug binds to the wrong organ.
In our previous post, we showed how we use AI to translate proteins into 1024-dimensional vectors to instantly find structural matches using PostgreSQL’s pgvector. But because we cannot tolerate AI hallucinations in drug discovery, we had to build a deterministic “safety net” natively inside the database.
Here is how we extended PostgreSQL using Rust (pgrx) to fuse bleeding-edge AI spatial searches with a dynamic programming algorithm from the 1980s.
The “Git Diff” of Biology
If the AI vector embedding represents the compiled 3D shape of a protein, the sequence of amino acids (A, C, T, G, etc.) is the raw source code.
When our AI tells us, “Hey, these two proteins have the exact same shape,” we need to mathematically verify that claim by comparing their source code.
To do this, biologists use the Smith-Waterman algorithm. Invented in 1981, it is essentially a highly optimized Levenshtein distance (or a git diff) for biological sequences. It uses dynamic programming to find the optimal local alignment between two strings, applying penalties for mismatched characters or gaps.
We needed this algorithm to run natively inside PostgreSQL memory, so we wrote it in Rust using the pgrx framework:
use pgrx::prelude::*;
/// Core Smith-Waterman local alignment algorithm with O(N) space optimization.
#[pg_extern(immutable, parallel_safe)]
pub fn smith_waterman_score(seq1: &str, seq2: &str, match_score: i32, mismatch: i32, gap: i32) -> i32 {
let b1 = seq1.as_bytes();
let b2 = seq2.as_bytes();
let mut prev = vec![0; b2.len() + 1];
let mut curr = vec![0; b2.len() + 1];
let mut max_score = 0;
for i in 1..=b1.len() {
curr[0] = 0;
for j in 1..=b2.len() {
let score_diag = prev[j - 1] + if b1[i - 1] == b2[j - 1] { match_score } else { mismatch };
let score_up = prev[j] + gap;
let score_left = curr[j - 1] + gap;
curr[j] = 0.max(score_diag).max(score_up).max(score_left);
if curr[j] > max_score { max_score = curr[j]; }
}
prev.copy_from_slice(&curr);
}
max_score
}
By implementing this in Rust as a #[pg_extern], PostgreSQL compiles it into a highly optimized C-compatible shared library. It runs at blistering native speeds.
Creating the Custom Hybrid Operator (<~>)
Now we have our two halves:
- Structural Distance via
pgvector(<=>) - Sequence Alignment via Rust Smith-Waterman
But querying them separately is clunky. PostgreSQL allows developers to define custom operators. We wanted to create a single Hybrid Biological Operator (<~>) that takes two proteins, looks at their vectors, looks at their sequences, and returns a fused “Hybrid Homology Score”.
First, we created a composite type to hold both the AI vector and the raw text sequence:
CREATE TYPE bio_feature AS (
emb vector,
seq text
);
Next, we wrote the mathematical function to fuse the two worlds. We assign a 70% weight to the AI’s structural shape, and a 30% weight to the deterministic sequence alignment:
CREATE OR REPLACE FUNCTION hybrid_bio_distance(a bio_feature, b bio_feature)
RETURNS float8 LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
SELECT
(0.7 * (a.emb <=> b.emb)) +
(0.3 * (100.0 / (100.0 + sequence_alignment_score(a.seq, b.seq)::float8)));
$$;
Finally, we bind it to a custom PostgreSQL operator:
CREATE OPERATOR <~> (
LEFTARG = bio_feature,
RIGHTARG = bio_feature,
PROCEDURE = hybrid_bio_distance,
COMMUTATOR = <~>
);
The “Oversample and Refine” Pipeline
With our operator built, we can now execute extremely complex, hallucination-proof biological searches natively in SQL.
When a pharmaceutical engineer wants to verify that their new drug won’t accidentally bind to an off-target human protein, they run an Oversample-and-Refine query.
First, Postgres uses the lightning-fast pgvector HNSW index to fetch the 50 closest shapes. Then, it uses our <~> operator to re-rank those 50 candidates using the deterministic Smith-Waterman Rust code:
WITH closest AS (
-- 1. Oversample: Use AI to instantly find the 50 closest 3D shapes
SELECT p.uniprot_id, p.embedding, p.sequence, (p.embedding <=> target_emb) as dist
FROM proteins p
ORDER BY p.embedding <=> target_emb ASC
LIMIT 50
)
-- 2. Refine: Re-rank using the Hybrid Operator
SELECT c.uniprot_id, c.dist as structural_distance,
(ROW(c.embedding, c.sequence)::bio_feature <~> ROW(target_emb, target_seq)::bio_feature) as hybrid_score
FROM closest c
ORDER BY hybrid_score ASC;
The Result
We successfully transformed PostgreSQL from a standard relational database into an industrial-grade bioinformatics engine. By combining AI Vector Indexes with deterministic Rust extensions, we get the absolute best of both worlds: Sub-millisecond speed, with mathematical guarantees.
In our next post, we’ll dive into how we use Z-Order curves—the exact same math used to detect bullet collisions in 3D video game engines—to index the physical atoms inside a protein’s active site!
Next up in the series: Part 3: Indexing 3D Space like a Video Game
