With 20+ years of experience, I specialize in database optimization, system architecture, and performance tuning large-scale applications.
Currently working as a Senior Software Engineer at Hubstaff, and former Developer Advocate at Timescale.
I'm also a community builder, a cyclist and a permaculture enthusiast.
Featured Content
Latest Talks
Explore my recent presentations at conferences and meetups around the world.
View TalksInteractive Hub & Tools
Apps & Playgrounds
Check out the interactive tools directory, featuring the Semantic Learning journey, full-screen Drawing Recorder, 7m Geodesic Dome Builder, and the new Truck Camper Bamboo Builder.
Explore AppsMandala Drawings
Explore the Mandala Synth and Playground. Draw mandalas, sonify visuals, and experiment with complex geometries mapped to audio.
Launch PlaygroundLatest Post
Analyzing 3D Genomic Architecture and Hi-C Contact Maps with SQL
September 29, 2026 genomics postgresql data-engineering deep-learning
The human genome is not a flat, 2D string of letters. Inside the nucleus, DNA twists, folds, and loops back on itself in complex 3D topological structures. This architecture is critical: a piece of DNA (an Enhancer) can physically loop backward in 3D space to touch a gene (a Promoter) located millions of base pairs away, turning that gene on or off.
To study this, geneticists use Hi-C Sequencing or deep learning prediction models like ORCA, which output massive “Contact Maps”.
The $N \times N$ Matrix Problem
A Hi-C Contact Map is a giant $N \times N$ matrix tracking the physical distance between every single base pair in a chromosome. For large genomes, these matrices are gigabytes in size. Most of the matrix is empty (zeros), because most base pairs never touch each other.
Loading .hic or .cool files into R or Python to find specific enhancer-promoter loops requires massive amounts of RAM.
Enter pg_bio: Relational Topologies
Instead of treating the genome as a flat file, we can treat it as a relational graph using pg_bio.
pg_bio introduces the SparseAttentionMap PostgreSQL type. Under the hood, this uses a Compressed Sparse Row (CSR) structure natively in Rust. It entirely discards the empty space (the zeros) and only stores the exact base pairs that are physically interacting.
Mining for Genomic Loops in SQL
By storing the genomic architecture in PostgreSQL, finding complex 3D DNA loops becomes a trivial SQL query.
If we want to ask the database: “Which base pairs are physically wrapping around and touching the Promoter located at base pair 25?”, we can use the get_top_interacting_residues function:
SELECT
sequence_name,
get_top_interacting_residues(orca_hic_map, 25, 5) as closest_physical_dna_contacts
FROM genomic_predictions
WHERE sequence_name = 'Synthetic_Plasmid_Construct';
Output:
sequence_name | closest_physical_dna_contacts
---------------------------+-------------------------------
Synthetic_Plasmid_Construct | {1300, 1301, 1302, 26, 24}
In a fraction of a second, the database proves that base pair 1300 is physically touching base pair 25.
By pushing 3D genomic matrices into a highly optimized PostgreSQL extension, computational biologists can instantly join topological data against their existing gene annotation tables, mapping the regulatory network of the cell using nothing but standard SQL.