From a 27-Hour HNSW Index to 3 Minutes: Upgrading pg_bio with Tiger Data’s pg_vectorsearch (PRISM), RaBitQ 1-Bit Error Bounds, and 1.2ms Dark Proteome Homology

Back in September, I wrote one of my favorite war stories of building pg_bio: The 27-Hour Index: Database Optimization and Indexing the Dark Proteome.

We had just ingested 2,340,766 1280-dimensional ESM-2 protein embeddings (575,000 curated Swiss-Prot proteins + 1.76 million uncharacterized extremophile archaeal proteins from UniProt TrEMBL) into PostgreSQL 18 on a local MacBook. When we ran CREATE INDEX idx_protein_embedding ON proteins USING hnsw (embedding halfvec_cosine_ops), the multi-layered HNSW graph outgrew maintenance_work_mem around the 1.3-million-protein mark, began spilling random graph edges to SSD, and took 27 hours to finish—leaving behind an 8,772.13 MB (8.57 GiB) index for a main heap table of just 1,053.66 MB.

Even after the 27-hour build completed, querying that 8.77 GB HNSW graph alongside 6.11 GB of TOASTed halfvec(1280) chunks (15.72 GiB total footprint) meant every multi-worker discovery wave in our 67.5-Million-Edge Swarm had to contend with shared-buffer evictions (3,304 to 12,123 8 KB pages touched per query, averaging 197 ms to 417 ms under full-proteome random access).

Then my former colleagues at Tiger Data (formerly Timescale, where I spent four wonderful years learning PostgreSQL internals from real-world community workloads) dropped a major open-source announcement: Announcing pg_vectorsearch: Scalable Open-Source Vector Search in PostgreSQL (timescale/pg_vectorsearch, released under The PostgreSQL License).

Instead of another memory-hungry HNSW graph, pg_vectorsearch introduces PRISM (Partitioned Routing Index for Similarity Matching)—a native PostgreSQL Index Access Method inspired by Google’s ScaNN/SOAR for AlloyDB, Microsoft’s SPANN, and SIGMOD 2024 RaBitQ 1-bit binary quantization with theoretical error bounds.

We immediately asked a systems question: Should pg_bio replace pgvector with pg_vectorsearch, or combine them?

Today we benchmarked both extensions head-to-head on all 2,340,766 1280-dimensional UniProt proteins in PostgreSQL 18.3 and opened a PR upgrading pg_bio to combine pgvector (halfvec(1280) STORAGE PLAIN) + pg_vectorsearch (USING prism) + pg_bio (<~> Smith-Waterman hybrid re-ranking & 3D Kabsch/RangeFinder GiST). Here are the live numbers on the exact same 2.34M-protein dataset:

  • Index Build Time (2,340,766 $\times$ 1280-D proteins): 27 hours $\rightarrow$ 184.68 seconds (3.08 minutes — 526× faster!)
  • Vector Index Footprint: 8,772.13 MB (HNSW) $\rightarrow$ 902.26 MB (PRISM) — 9.72× smaller!
  • Total Table + TOAST + Index Disk Footprint: 16,101.81 MB (15.72 GiB) $\rightarrow$ 8,266.57 MB (8.07 GiB) — 7.65 GiB saved (48.7% reduction)!
  • The Exact P0DPB7 Query from The 27-Hour Index: 185.320 ms (HNSW) $\rightarrow$ 1.225 ms (PRISM warm) / 20.26 ms (PRISM p50 across random 2.34M lookups)—with RaBitQ pruning 99.30% (23,266 / 23,429) of posting-list candidates via 1-bit error bounds before touching the heap!
  • Full 2.34M-Proteome Random ANN Throughput (Recall@10 = 98.4%): 7.49× to 11.26× faster than HNSW (26.33 ms vs 197.22 ms at iso-distance 0.06794, and 37.05 ms vs 417.31 ms at high-precision 0.06732).
  • Combined pg_bio <~> Hybrid Smith-Waterman + Vector Discovery: 2.22× to 4.16× faster end-to-end (141.56 ms vs 313.60–589.52 ms) while uncovering even better (lower) hybrid <~> homology scores (0.27906 vs 0.28165)!

🐯 1. Why Combining pgvector + pg_vectorsearch + pg_bio Beats Replacing Either One

When engineers hear about a new vector extension, the knee-jerk reaction is “rip and replace.” But when we inspected sql/pg_vectorsearch.sql, we realized the Tiger Data engineering team did something extraordinarily smart:

  1. Identical varlena Memory Layout & Zero-Overhead Binary Casts (WITHOUT FUNCTION):
    pg_vectorsearch’s native vec32 and vec16 types share the exact same C binary representation (4-byte varlena header + int16 dim + int16 unused + float32[] / float16[]) as pgvector’s vector and halfvec. When both extensions are installed, pg_vectorsearch_setup_pgvector_compat() creates WITHOUT FUNCTION binary casts (RelabelType in the PostgreSQL planner, costing zero CPU cycles) and registers pgvector’s <->, <#>, and <=> operators directly into prism’s operator families (vec32_cosine_ops, vec16_cosine_ops).
  2. Permissive PostgreSQL License Everywhere:
    Unlike older source-available database extensions, pg_vectorsearch is licensed under The PostgreSQL License—the exact same OSI-approved, permissive (MIT/BSD-equivalent) license used by PostgreSQL itself and pgvector. Every C, ARM64 NEON, AVX-512, and RaBitQ source file in timescale/pg_vectorsearch is 100% open-source and compatible with pg_bio.
  3. Why We Keep pgvector Alongside pg_vectorsearch:
    pg_vectorsearch (0.2.0-dev) is an alpha index engine purpose-built for PostgreSQL 18+ dense vec32/vec16 ANN search. Keeping requires = 'vector, pg_vectorsearch' in pg_bio.control lets pg_bio retain pgvector’s vector/halfvec column types, real[] array casts (used by interface_complementarity and <~>), bit vectors, and sparsevec, while delegating multi-million-row proteome indexing to pg_vectorsearch’s PRISM access method!
flowchart TD Q["Uncharacterized Archaeal Sequence / Bait Protein"] --> E["1280-D ESM-2 Embedding\npgvector halfvec(1280) STORAGE PLAIN (2,564 B inline, 0 TOAST)"] E --> TIER{"pg_bio Tier Router\nevaluate_vector_index_tier()"} TIER -->|"< 10k rows (Small Assay / Pocket Table)"| HNSW["pgvector HNSW Index\n(halfvec_cosine_ops)"] TIER -->|">= 10k to 100M+ rows (2.34M UniProt Proteome)"| PRISM["pg_vectorsearch PRISM Index (vec16_cosine_ops)\n1. Hierarchical Centroid Tree (nlist=9216, nlevels=2)\n2. 1-Bit RaBitQ NEON Fastscan (23,429 entries in 0.81ms)\n3. Theoretical Error-Bound Pruning (99.3% pruned!)\n4. Inline Heap Rerank (163 tuples in 0.11ms)"] HNSW --> CAND["Top-K Oversampled Structural Candidates (K = 50)"] PRISM --> CAND CAND --> PGBIO["pg_bio In-Database Rust Biophysical Pipeline\n• sequence_entropy(seq) >= 2.0 & Length Tolerance\n• <~> Hybrid Operator (0.7 Cosine + 0.3 Smith-Waterman)\n• 3D Kabsch SVD Superposition (sc_rmsd, tm_score)\n• RangeFinder 10 Å GiST Pocket IoU & Chaperone H-Bonds"] PGBIO --> HIT["Validated Functional & 3D Structural Discovery"]

🧮 2. Inside PRISM: How RaBitQ 1-Bit Error Bounds & STORAGE PLAIN Crush the 8.77 GB HNSW Bottleneck

Why did pgvector’s HNSW index bloat to 8,772.13 MB on 2.34 million halfvec(1280) rows, whereas pg_vectorsearch’s PRISM index takes only 902.26 MB (9.72× smaller)?

A. The Mathematics of RaBitQ 1-Bit Quantization with Error Bounds

In an HNSW index (m = 16), every single vector is stored inside the index at full 16-bit precision ($1280 \times 2 = 2,560\text{ B}$) alongside bidirectional multi-layer graph adjacency lists (~1,200+ B of neighbor pointers and page alignment overhead), totaling ~3,930 bytes/protein in the index alone.

PRISM replaces the monolithic neighbor graph with a 2-level hierarchical centroid tree (nlist = 9,216 clusters, fan_out = 96, nlevels = 2) backed by SOAR spill-orthogonal cluster assignment (soar_lambda = 1.0, boundary_epsilon = 0.35) and RaBitQ binary quantization (Gao & Long, SIGMOD 2024):

  1. Random Orthogonal Rotation & Residual Binarization:
    For a database vector $x \in \mathbb{R}^{D}$ assigned to cluster centroid $c$, PRISM projects the normalized residual $(x - c) / |x - c|$ through a random orthogonal matrix $P$ and quantizes each coordinate to a single sign bit $\bar{x}_b \in {-1/\sqrt{D}, +1/\sqrt{D}}^D$ (1280 bits = 160 bytes!), plus two scalar correction factors (f_add and f_rescale).
  2. SIMD Fastscan (VPSHUFB / ARM64 NEON vtbl) with Theoretical Error Bounds:
    During a query $q$, PRISM traverses the 2-level centroid tree in 0.259 ms to pick the top nprobe clusters, then scans 32-vector packed Fastscan groups using 4-bit SIMD lookup tables. Crucially, RaBitQ provides an unbiased distance estimator $\hat{d}(x, q)$ along with a provable lower error bound: \(d_{\text{exact}}(x, q) \ge \hat{d}_{\text{RaBitQ}}(x, q) - \epsilon_{\text{bound}}(x, q)\) If \(\hat{d}_{\text{RaBitQ}}(x, q) - \epsilon_{\text{bound}}(x, q)\) is already greater than the $k$-th best exact distance in the top-$k$ heap, PRISM mathematically eliminates candidate $x$ without ever fetching the 2,564-byte full vector from the table heap!

B. Discovering & Fixing the Hidden 6.11 GB PostgreSQL TOAST Tax on halfvec(1280)

While benchmarking PRISM’s exact reranking stage (Rerank Time), we uncovered a classic PostgreSQL storage-engine trap that affects every 1280-dimensional ESM-2 proteomics table:

  • A halfvec(1280) datum occupies $4\text{ B (varlena header)} + 2\text{ B (dim)} + 2\text{ B (unused)} + 1280 \times 2\text{ B} = \mathbf{2,568\text{ bytes}}$.
  • PostgreSQL’s TOAST_TUPLE_THRESHOLD is 2,000 bytes (~$\frac{1}{4}$ of an 8 KB heap page). Because halfvec defaults to STORAGE EXTERNAL, PostgreSQL slices every single 1280-D protein embedding out of the main heap and pushes it into an out-of-line TOAST table (pg_toast)!
  • In our original proteins table (2.34M rows), the main heap was 1,053.66 MB, while the out-of-line TOAST heap was 6,112.26 MB! Every time HNSW or PRISM fetched a candidate vector from the heap to compute an exact cosine distance, PostgreSQL had to traverse a separate TOAST B-Tree index + TOAST heap page!

By declaring embedding halfvec(1280) STORAGE PLAIN on our parallel proteins_prism table, PostgreSQL keeps the 2,568-byte halfvec(1280) inline inside the 8 KB main heap page (fitting 2–3 complete protein rows per 8 KB page with zero TOAST lookups for vectors). On our 50,000-protein controlled benchmark, switching halfvec(1280) from STORAGE EXTERNAL to STORAGE PLAIN accelerated PRISM query p50 by 2.89× (4.83 ms $\rightarrow$ 1.67 ms) and cut index build time by 2.16× (6.87 s $\rightarrow$ 3.18 s)!


⚡ 3. The Rematch: Re-Running the Exact P0DPB7 Query from The 27-Hour Index (185.32 ms $\rightarrow$ 1.225 ms)

In The 27-Hour Index, our hero query searched for the closest structural homologs of P0DPB7 across all 2,340,766 proteins. Back then, the 8.77 GB HNSW index executed the query in 185.320 ms (Buffers: shared hit=123 read=1439).

Here is that exact same query running today on PostgreSQL 18.3 against our 902 MB idx_proteins_prism_embedding PRISM index (SET prism.nprobe = 40):

SET prism.nprobe = 40;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT p.uniprot_id,
       (p.embedding <=> (SELECT embedding FROM proteins_prism WHERE uniprot_id = 'P0DPB7')) AS distance
FROM proteins_prism p
WHERE p.uniprot_id != 'P0DPB7'
ORDER BY p.embedding <=> (SELECT embedding FROM proteins_prism WHERE uniprot_id = 'P0DPB7') ASC
LIMIT 5;

Look at the telemetry that pg_vectorsearch emits inside EXPLAIN (ANALYZE, BUFFERS, VERBOSE):

                                                                                 QUERY PLAN                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1710.45..1710.46 rows=5 width=17) (actual time=1.203..1.211 rows=5.00 loops=1)
   Output: p.uniprot_id, ((p.embedding <=> (InitPlan 1).col1))
   Buffers: shared hit=1069
   InitPlan 1
     ->  Index Scan using proteins_prism_pkey on public.proteins_prism  (cost=0.43..8.45 rows=1 width=2568) (actual time=0.006..0.007 rows=1.00 loops=1)
           Output: proteins_prism.embedding
           Index Cond: ((proteins_prism.uniprot_id)::text = 'P0DPB7'::text)
           Index Searches: 1
           Buffers: shared hit=4
   ->  Index Scan using idx_proteins_prism_embedding on public.proteins_prism p  (cost=1702.00..7602.12 rows=2340765 width=17) (actual time=1.202..1.210 rows=5.00 loops=1)
         Output: p.uniprot_id, (p.embedding <=> (InitPlan 1).col1)
         Order By: (p.embedding <=> (InitPlan 1).col1)
         Filter: ((p.uniprot_id)::text <> 'P0DPB7'::text)
         Rows Removed by Filter: 1
         Index Searches: 1
         Buffers: shared hit=1069
         Top-K: 12
         Posting Lists Scanned: 40
         Centroid Pages Read: 243
         Posting Pages Read: 573
         Posting Pages Scanned: 573
         Posting Dead Pages Skipped: 0
         Posting Entries Scanned: 23429
         Rerank Candidates: 163
         Rerank Results: 12
         Storage Reads: 896
         Centroid Search Time: 0.259 ms
         Posting Scan Time: 0.817 ms
         Rerank Time: 0.107 ms
 Planning Time: 0.063 ms
 Execution Time: 1.225 ms

Read those last 11 lines of the PRISM scan node carefully:

  1. Centroid Search Time: 0.259 ms — Descending the 2-level hierarchical centroid tree (9,216 leaf clusters, 243 centroid pages) to identify the 40 closest protein clusters takes 259 microseconds.
  2. Posting Entries Scanned: 23429 in 0.817 ms — Scanning 23,429 1-bit RaBitQ-quantized 1280-D vectors across 573 sequentially packed posting pages via ARM64 NEON SIMD takes 817 microseconds (~35 nanoseconds per 1280-D protein!).
  3. Rerank Candidates: 163 (99.30% Pruned!) in 0.107 ms — Out of 23,429 candidate proteins in those 40 clusters, RaBitQ’s theoretical error bounds mathematically ruled out 23,266 proteins (99.30%) without touching the table heap. Only 163 inline STORAGE PLAIN heap tuples were fetched and reranked at full precision, taking 107 microseconds!
  4. Execution Time: 1.225 ms — A 151.3× speedup over our original 185.320 ms HNSW query!

📊 4. Full Benchmark Suite: 50,000-Protein Recall Curve & 2,340,766-Protein Parallel Shootout

To verify that PRISM doesn’t trade away biological recall for speed, we ran two reproducible benchmark suites in our worktree (scripts/benchmark_pg_vectorsearch_vs_pgvector.py and scripts/setup_and_benchmark_parallel_2m_proteome.py):

A. Controlled 50,000-Protein Ground-Truth Recall Benchmark (1280-D halfvec STORAGE PLAIN)

First, we computed exact brute-force sequential-scan top-10 and top-50 neighbors (p50 = 118.44 ms) across 50,000 UniProt proteins and measured exact Recall@10 and Recall@50:

Index Engine (50,000 Proteins, 1280-D halfvec) Build Time Index Size Query Parameter p50 Latency Throughput (QPS) Exact Recall@10 Exact Recall@50
pg_vectorsearch PRISM (Default: SOAR + Boundary) 3.18 s 19.87 MB nprobe = 20 1.67 ms 599.9 88.40% 78.08%
pg_vectorsearch PRISM (Default: SOAR + Boundary) 3.18 s 19.87 MB nprobe = 40 1.90 ms 462.6 98.40% 93.52%
pg_vectorsearch PRISM (Default: SOAR + Boundary) 3.18 s 19.87 MB nprobe = 60 2.75 ms 339.2 99.20% 97.92%
pg_vectorsearch PRISM (Compact: soar=0, eps=0) 2.70 s 11.38 MB nprobe = 40 3.85 ms 240.0 85.20% 76.64%
pgvector HNSW (m = 16, ef_construction = 64) 63.23 s 195.16 MB ef_search = 10 3.78 ms 274.3 86.80% 18.08%
pgvector HNSW (m = 16, ef_construction = 64) 63.23 s 195.16 MB ef_search = 40 8.75 ms 122.7 96.00% 77.84%
pgvector HNSW (m = 16, ef_construction = 64) 63.23 s 195.16 MB ef_search = 100 13.56 ms 75.6 98.40% 94.80%
pgvector HNSW (m = 16, ef_construction = 64) 63.23 s 195.16 MB ef_search = 200 26.83 ms 37.4 99.60% 98.72%
pgvector IVFFlat (lists = 316) 44.89 s 131.88 MB probes = 40 22.43 ms 43.3 80.80% 70.80%

At iso-recall (98.40% Recall@10), pg_vectorsearch PRISM (nprobe = 40) is 7.14× faster in p50 latency (1.90 ms vs 13.56 ms), 9.82× smaller (19.87 MB vs 195.16 MB), and builds 19.88× faster (3.18 s vs 63.23 s) than pgvector HNSW (ef_search = 100).

B. Full 2,340,766-Proteome Parallel Shootout (proteins [HNSW] vs. proteins_prism [PRISM])

Next, we ran both full 2.34-million-row tables side-by-side in bio_demo:

Full 2.34M-Proteome Metric proteins (pgvector HNSW + EXTERNAL) proteins_prism (pg_vectorsearch PRISM + PLAIN) Measured Improvement
Index Build Time (2,340,766 rows) ~27 hours (disk-spilling) 184.68 s (3.08 min) ~526× faster build
Main Heap / TOAST Heap 1,053.66 MB / 6,112.26 MB 6,095.75 MB / 1,105.83 MB (seq only) Zero vector TOAST fetches
Vector Index Size 8,772.13 MB (8.57 GiB) 902.26 MB (0.88 GiB) 9.72× smaller index
Total Table + TOAST + Index Disk 16,101.81 MB (15.72 GiB) 8,266.57 MB (8.07 GiB) 7.65 GiB (48.7%) saved
Fast Screening Latency (p50 / QPS) 197.22 ms / 5.3 QPS (ef=40) 9.27 ms / 116.8 QPS (nprobe=10) 21.28× faster (525 buffers)
Standard Discovery Latency (p50 / Avg Dist) 197.22 ms / 0.06798 (ef=40) 26.33 ms / 0.06794 (nprobe=auto=48) 7.49× faster & closer neighbors
High-Precision Latency (p50 / Avg Dist) 417.31 ms / 0.06733 (ef=100) 37.05 ms / 0.06732 (nprobe=80) 11.26× faster & 2.3× fewer buffers
Combined pg_bio <~> Hybrid Search (p50 / Top-1 Score) 313.60 ms (ef=40, score 0.28165)
589.52 ms (ef=100, score 0.28068)
141.56 ms (nprobe=40, score 0.27931)
141.71 ms (nprobe=48, score 0.27906)
2.22×–4.16× faster & superior <~> hybrid matches!

Why does combining PRISM Stage-1 (LIMIT 50) + pg_bio <~> Stage-2 Smith-Waterman Re-Ranking find better (lower) hybrid scores (0.27906 vs 0.28165) than default HNSW (ef_search = 40)?

  • In pgvector HNSW, hnsw.ef_search defaults to 40. When you oversample $K=50$ candidates for Smith-Waterman <~> re-ranking, HNSW’s greedy graph beam saturates (Recall@50 = 77.84% at ef_search = 40, and even worse—18.08%—at ef_search = 10).
  • In pg_vectorsearch PRISM (nprobe = 40..48), scanning 40–48 complete SOAR-replicated posting lists evaluates ~23,000+ RaBitQ codes across diverse structural sub-clusters, feeding pg_bio’s Rust Smith-Waterman <~> operator a broader, higher-diversity candidate pool that surfaces remote twilight-zone homologs HNSW’s narrow beam missed!

🦀 5. Upgrading pg_bio in Rust (pgrx): evaluate_vector_index_tier & pg_bio_search_homologs_prism

In our upgrade PR (src/lib.rs), we also fixed a subtle planner cast trap in pg_bio_search_homologs and added an automated vector tier recommender:

  1. Eliminating the Left-Hand halfvec::vector Cast Trap:
    Previously, pg_bio_search_homologs declared v_target_emb vector; and ran ORDER BY p.embedding <=> v_target_emb ASC where p.embedding was halfvec(1280). In PostgreSQL, comparing halfvec <=> vector forces an implicit cast on the column (p.embedding::vector <=> v_target_emb), which can prevent the planner from choosing a halfvec/vec16 index! Our upgraded pg_bio_search_homologs and pg_bio_search_homologs_prism keep v_target_emb halfvec during Stage-1 index retrieval (p.embedding <=> v_target_emb) and cast c.embedding::vector only on the 50 surviving rows in Stage 2 when constructing the bio_feature tuple for <~>:
-- Upgraded Two-Stage PRISM + pg_bio <~> Hybrid Homology Function in src/lib.rs:
CREATE OR REPLACE FUNCTION pg_bio_search_homologs_prism(
    p_target_id text,
    p_max_distance float8 DEFAULT 0.35,
    p_len_tolerance float8 DEFAULT 0.3,
    p_min_entropy float8 DEFAULT 2.0,
    p_limit integer DEFAULT 5,
    p_oversample_factor integer DEFAULT 10,
    p_prism_nprobe integer DEFAULT 20
)
RETURNS TABLE(uniprot_id text, name text, distance float8, hybrid_score float8)
LANGUAGE plpgsql
AS $$
DECLARE
    v_target_emb halfvec;
    v_target_vec vector;
    v_target_seq text;
    v_target_len float8;
    v_oversample integer := GREATEST(p_limit * p_oversample_factor, 10);
BEGIN
    -- Dynamically tune PRISM nprobe within the local transaction
    BEGIN
        PERFORM set_config('prism.nprobe', p_prism_nprobe::text, true);
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;

    SELECT embedding::halfvec, embedding::vector, sequence, LENGTH(sequence)::float8
    INTO v_target_emb, v_target_vec, v_target_seq, v_target_len
    FROM proteins_prism WHERE proteins_prism.uniprot_id = p_target_id;

    IF v_target_emb IS NULL THEN
        RETURN;
    END IF;

    -- Stage 1: Zero-cast halfvec <=> halfvec PRISM index scan (vec16_cosine_ops)
    -- Stage 2: Shannon sequence_entropy filter + Rust Smith-Waterman <~> hybrid re-rank
    RETURN QUERY
    WITH closest AS (
        SELECT p.uniprot_id, p.name, p.embedding, p.sequence, (p.embedding <=> v_target_emb) as dist
        FROM proteins_prism p
        ORDER BY p.embedding <=> v_target_emb ASC
        LIMIT v_oversample + 1
    )
    SELECT c.uniprot_id::text, c.name, c.dist,
           (ROW(c.embedding::vector, c.sequence)::bio_feature <~> ROW(v_target_vec, v_target_seq)::bio_feature) as h_score
    FROM closest c
    WHERE c.uniprot_id != p_target_id
      AND c.dist <= p_max_distance
      AND LENGTH(c.sequence) BETWEEN v_target_len * (1.0 - p_len_tolerance) AND v_target_len * (1.0 + p_len_tolerance)
      AND sequence_entropy(c.sequence) >= p_min_entropy
    ORDER BY h_score ASC
    LIMIT p_limit;
END;
$$;
  1. In-Database Vector Tier Recommender (evaluate_vector_index_tier):
    Scientists can now query SELECT evaluate_vector_index_tier(2340766, 1280, true, 8192); directly in SQL or via PgBioClient.evaluate_vector_index_tier() in Python to automatically compute TOAST thresholds (requires_storage_plain = true), compare HNSW vs. PRISM memory footprints, and get the optimal nlist and prism.nprobe parameters for their hardware budget.

🧬 6. Interactive 3Dmol.js Validation: Navigating the Dark Proteome in Milliseconds with PRISM + <~>

What does a 902 MB PRISM index + pg_bio <~> hybrid operator + 3D Kabsch + RangeFinder 10 Å GiST pipeline unlock biologically?

Because proteins_prism fits comfortably inside shared buffers on a 16 GB laptop, we can execute multi-stage cross-family twilight-zone queries across all 2.34 million proteins in milliseconds—instantly recovering our top 3D-validated extremophile discoveries:

Discovery Pair (Queried via proteins_prism PRISM Index) Bait Protein & Ecosystem Discovery Partner & Ecosystem PRISM Vector Distance (<=>) & <~> Hybrid Score 3D Core RMSD & TM-Score RangeFinder 10 Å Pocket IoU
1. Solving Dark Proteome DUF2071 $\rightarrow$ YqjF Respiratory Quinol-Redox Barrel M0M3V2 (235 aa, pI 5.80)
Halococcus morrhuae DSM 1307 (Rock-Salt Fluid Inclusion Archaea)
PF09844 / DUF2071
A0ABD5UV63 (231 aa, pI 7.56)
Halopenitus salinus (Solar Saltern Archaea)
YqjF Quinol-Oxidase Family
d_cos = 0.0791
(37.4% SW Sim)
<~> = 0.2724
0.80 Å (221/231 C$\alpha$, 95.7%)
TM = 0.949
98.44% IoU
MW [171, 377] Da
LogP [2.01, 5.89]
2. Archaeal Ether-Lipid Membrane Armor (GGR Hydrogenation Tunnel) A0A1G7GUI6 (411 aa, pI 4.69)
Halorubrum xinjiangense
Digeranylgeranylglycerophospholipid Reductase (EC 1.3.1.101)
A0A7J9SG79 (411 aa, pI 4.59)
Halobellus ruber
Broadly Annotated FAD Oxidoreductase
d_cos = 0.0717
(47.9% SW Sim)
<~> = 0.2581
0.67 Å (405/411 C$\alpha$, 98.5%)
TM = 0.979
95.55% IoU
(rf_score = 0.9634)
534 vs 526 H-Bonds

Explore both PRISM-indexed 3D structural discoveries below! Double-click either viewport to lock cameras (pg_bio_sync.js), or click the chemistry teaching buttons to inspect the conserved 3D catalytic core, RangeFinder 10 Å active cleft, and halophilic Asp/Glu surface charge armor:

🐯 Select PRISM + pg_bio 3D Discovery Pair:
Loading PRISM-indexed 3D Kabsch-superimposed structures from local models...
Bait Reference
PRISM + <~> Discovery

🔭 7. The Horizon: Future Research Ideas & Billion-Scale Metagenomics on a Laptop

The Bait (pgvector HNSW on STORAGE EXTERNAL tables, and sequence-annotated reference baits like M0M3V2 from *Halococcus morrhuae or A0A1G7GUI6 from Halorubrum xinjiangense) can index tens of thousands of vectors in RAM or anchor single-species annotations, but fails when scaling to millions of 1280-D protein embeddings blows the index to 8.77 GB, triggers 27-hour SSD swap thrashing, and narrows Recall@50 candidate diversity—or when 62.6% sequence divergence leaves PF09844 / DUF2071 unannotated—whereas our combined pg_vectorsearch PRISM + pgvector STORAGE PLAIN + pg_bio <~> engine compresses 2.34 million 1280-D vectors into a 902 MB RaBitQ-quantized index built in 3.08 minutes, scanning 23,429 quantized candidates in 0.817 ms and pruning 99.30% via theoretical error bounds, making it perfect for laptop-scale 100-million-protein metagenomic dark-proteome mining, hypersaline bioelectrochemical quinone shuttles, and cell-free archaeol ether-lipid synthesis.*

  1. Scaling Local Laptop Proteomics from 2.34M (UniProt Archaea + Swiss-Prot) to 50M+ (UniRef50 & ESM Metagenomic Atlas):
    At HNSW’s ~3.83 KB/protein index footprint, indexing 50 million 1280-D ESM-2 vectors requires ~191 GB of index memory, forcing academic labs onto expensive cloud clusters. At PRISM’s ~385 bytes/protein (902 MB for 2.34M proteins, or ~230 bytes/protein in compact mode), 50 million metagenomic proteins fit inside an 11–19 GB on-disk PRISM index whose 2-level centroid tree (< 50 MB) stays permanently pinned in PostgreSQL shared_buffers on a standard workstation!
  2. Real-Time Multi-Stage <~> Hybrid & 3D RangeFinder Screening for Industrial Halophile Biocatalysts (M0M3V2 $\times$ A0ABD5UV63 — DUF2071):
    Because PRISM’s nprobe = 40 posting scan surfaces a broader top-50 structural neighborhood in 1.2–20 ms, chaining Stage-1 PRISM (vec16_cosine_ops) $\rightarrow$ Stage-2 pg_bio <~> Smith-Waterman hybrid re-ranking $\rightarrow$ Stage-3 RangeFinder 10 Å GiST pocket containment (IoU = 98.44%) lets protein engineers screen entire extremophile divisions for menaquinone electron-transfer modules (DUF2071 / YqjF) in a single interactive SQL transaction.
  3. High-Throughput Archaeal Ether-Lipid Reductase Mining for Thermostable mRNA Lipid Nanoparticles (A0A1G7GUI6 $\times$ A0A7J9SG79 — GGR):
    Pairing fast PRISM cluster routing with pg_bio’s 3D Kabsch alignment (0.67 Å RMSD across 405/411 C$\alpha$, TM = 0.979) and Chaperone H-bond auditing (534 vs 526 H-bonds) enables instant discovery of halophilic Digeranylgeranylglycerophospholipid reductases (EC 1.3.1.101) for manufacturing non-hydrolyzable archaeol ether-lipid vesicles.

Huge kudos to the Tiger Data team for open-sourcing pg_vectorsearch under the PostgreSQL License—and showing once again why PostgreSQL’s extensible Index Access Method architecture is the ultimate foundation for scientific computing. 🐯🧬


⚡ Powered by pg_bio on PostgreSQL 18 + Rust (pgrx): This discovery was executed natively inside PostgreSQL shared buffers using zero-copy Rust extensions (smith_waterman_score, 3D Kabsch SVD superposition, Z-Order B-Trees, and pgvector HNSW).

🎬 Watch & Explore in 3D: Interactive pg_bio Video Library & 3D Studio (/pg-bio-videos/) — Navigate our “Hello World, pg_bio!” series with a voiced hovering video companion and live 3Dmol.js molecular playground.

📚 Explore the Systems Architecture: PostgreSQL Query Planner & GiST R-Trees • Introducing pg_bio • Hybrid Operator <~> in Rust • The 27-Hour HNSW Index • More Bioinformatics Discoveries • Systems Research & CV

Jônatas Davi Paganini

Jônatas Davi Paganini

Jônatas Davi Paganini — Systems Researcher & Database Internals Engineer (20+ years experience). Creator of pg_bio (in-database structural biology & chemoinformatics engine for PostgreSQL in Rust) and spiral (geometry-aware multidimensional storage & Z-order indexing). Former Developer Advocate at Timescale.

1/1