Scaling a database is rarely a linear journey. It usually involves hitting a wall at full speed, diagnosing the crash, and rebuilding the engine.
Yesterday, we successfully mapped the Swiss-Prot database (575,000 proteins) into PostgreSQL to discover novel Fanzor enzymes. Emboldened, we decided to plunge into the “Dark Proteome” by ingesting the UniProt TrEMBL Archaea dataset—adding 1.76 million extremophile proteins to our local machine.
Suddenly, we were dealing with 2.34 million 1280-dimensional structural embeddings. And almost immediately, my laptop ground to an absolute halt. Here is the story of how a database traffic jam taught us the brutal reality of high-dimensional indexing, and how combining different indexing strategies is the key to synthetic biology.
The Great PostgreSQL Traffic Jam
Without an index, querying a 1280-dimensional vector space requires a Sequential Scan. To find a structural match, PostgreSQL has to calculate the cosine distance against all 2.34 million arrays. It’s the computational equivalent of reading every book in a library to find a specific quote.
Take a look at the query execution plan for a single vector search before indexing:
EXPLAIN WITH target AS (
SELECT embedding as emb FROM proteins WHERE uniprot_id = 'P0DPB7'
)
SELECT p.uniprot_id, (p.embedding <=> t.emb) as distance
FROM proteins p, target t
ORDER BY distance ASC LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=165027.14..165027.26 rows=1 width=17)
-> Gather Merge (cost=165027.14..437564.87 rows=2340053 width=17)
Workers Planned: 2
-> Sort (cost=164027.11..166464.67 rows=975022 width=17)
Sort Key: ((p.embedding <=> proteins.embedding))
-> Nested Loop (cost=0.43..159152.00 rows=975022 width=17)
-> Parallel Seq Scan on proteins p (cost=0.00..144518.23 rows=975022 width=41)
-> Materialize (cost=0.43..8.45 rows=1 width=32)
-> Index Scan using proteins_pkey on proteins (cost=0.43..8.45 rows=1 width=32)
Index Cond: ((uniprot_id)::text = 'P0DPB7'::text)
Notice the Parallel Seq Scan and the massive execution cost.
We fired off a CREATE INDEX using pgvector’s HNSW (Hierarchical Navigable Small World) algorithm to fix this. But at the same time, we had background scripts running INSERT statements, and a rogue query from an entirely different project consuming 94% of the CPU.
Because index creation requires locks, the database completely deadlocked. The INSERT scripts froze, waiting for the index. The index froze, starved of CPU by the rogue queries. We had to mercilessly clear the battlefield using pg_cancel_backend() just to let the database breathe.
The Memory Ceiling and Disk Spilling
With the CPU freed, the HNSW index began to build. But HNSW is a beast.
HNSW works like Google Maps for mathematics. It builds a multi-layered graph: a top layer with a few “super-hubs”, middle layers for regional neighborhoods, and a bottom layer connecting all 2.34 million proteins to their local neighbors. When querying, it hops down these layers, dropping query times from minutes to exactly 2 milliseconds.
But building this graph requires holding millions of edges in RAM.
My Mac has 16GB of RAM. We tuned PostgreSQL’s maintenance_work_mem to 4GB. It wasn’t enough. Around the 1.3 million protein mark, the graph grew too large for RAM. PostgreSQL had to gracefully “spill” to disk, reading and writing graph edges to the SSD.
This IO thrashing turned what should be a 20-minute index build into a 10-hour marathon.
(Update pending: We will post the exact size of the final index using pg_relation_size and the new lightning-fast EXPLAIN ANALYZE query plan as soon as the index build finishes!)
How Our Indices Complement Each Other
The magic of pg_bio isn’t just in the vectors; it’s how we combine completely different mathematical paradigms to complement each other’s weaknesses.
-
HNSW (The Global Compass): HNSW is strictly for structural discovery. It navigates the 1280-dimensional folded universe to answer the question: “Which proteins in this database share a similar global 3D fold?” It acts as our incredibly fast macro-filter.
-
Z-Order Curves (The Atomic Microscope): Once HNSW identifies a novel protein, we need to look at its active binding pocket.
pgvectorcannot index 3D space. Instead,pg_biouses Morton Coding (Z-Order Curves) to index the X, Y, and Z coordinates of millions of individual atoms into a single B-Tree. When HNSW hands us a protein, our Z-Order index allows us to instantly query a 5-Angstrom spherical radius around a specific residue to map its atomic interactions. -
B-Trees (The Anchors): Standard B-Trees anchor our biological metadata (UniProt IDs, taxonomies, entropy scores).
By chaining these indices together, we replicate the workflow of an entire molecular biology lab inside a single SQL query. HNSW gives us the structural match, B-Trees filter out the known biology, and Z-Order Curves extract the binding pocket.
It took 10 hours of heavy computational lifting to build the map, but now that we have it, the Dark Proteome is fully illuminated.
