ReptiDex launched nine days ago, and already 50 breeders pay to track lineages for 200 animals.
It’s a mobile app that doesn’t mess around—real parent-offspring links, multi-generation trees, all humming in production on PostgreSQL.
Building a multi-generation pedigree tree in PostgreSQL? That’s the crux here, adjacency lists with self-referential keys, recursive CTEs clawing up the family graph. No toys. This handles binary trees where every animal points to a sire and dam, doubling width per generation.
Picture a gecko enthusiast in Texas, importing a clutch from Europe. Grandparents documented, but the dam? MIA. Postgres shrugs—nullable foreign keys terminate the tree gracefully, no query crashes.
The Schema That Won’t Let You Screw Up Lineage
Self-referential foreign keys. Non-negotiable.
Here’s the table:
CREATE TABLE animals ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(255) NOT NULL, species VARCHAR(50) NOT NULL, sex VARCHAR(10), date_of_birth DATE, sire_id UUID REFERENCES animals(id), dam_id UUID REFERENCES animals(id), created_at TIMESTAMPTZ DEFAULT now() );
Postgres rejects bad sire_ids before your app even blinks. Orphan references? Forget breeding decisions on shaky data. This enforces integrity at the metal.
Nested sets flop here—pedigrees aren’t strict hierarchies; one sire spans dams, sprouting subtrees. Materialized paths? A 10-gen string hits 1,023 nodes, absurd. Closure tables tempted, but more on that flop later.
Adjacency list wins. Simple writes for new births, queries fan out via recursion.
That Core Recursive CTE—Does It Scale?
Hand an animal_id, cap at N generations, get the full ancestor graph.
WITH RECURSIVE pedigree AS ( – Base case: the animal we start from SELECT id, name, sire_id, dam_id, 0 AS generation FROM animals WHERE id = :animal_id UNION ALL – Recursive case: walk up to both parents SELECT a.id, a.name, a.sire_id, a.dam_id, p.generation + 1 FROM animals a JOIN pedigree p ON a.id = p.sire_id OR a.id = p.dam_id WHERE p.generation < :max_generations ) SELECT * FROM pedigree;
Base: your starter animal, gen 0. Recurse: snag parents, increment gen, guard at max. Four gens? Up to 31 rows—1 + 2 + 4 + 8 + 16. Binary explosion, 2^(n+1)-1 nodes.
Duplicates? Keep ‘em. That’s inbreeding signal—same ancestor on sire/dam lines fuels COI calcs. DISTINCT kills the insight Wright’s formula craves.
In practice, breeders demand sides: paternal grandpa? Maternal great-granny? Extend with path tracking or side flags. Production tweak.
But here’s my take—ReptiDex denormalizes for cache. Full trees materialized on birth inserts. Why? Breeders query pedigrees constantly, writes rarer. Postgres loves that read bias.
Why Ditch Closure Tables for This Postgres Tree?
Closure tables shine for arbitrary graphs—every ancestor link pre-stored. Insert offspring? Add rows to every ancestor’s descendant table. Fast queries, sure.
But pedigrees? Predictable binary up-trees. Closure bloats on every birth: new animal inherits all ancestor paths, duplicating across siblings. Storage balloons, especially incomplete records (unknown dams skip paths).
ReptiDex’s 200 animals, say average 5 gens known: adjacency recursion flies sub-10ms. Closure? Rewrite frenzy on imports. Nah.
Historical parallel: think early MySQL forum trees. Adjacency lists ruled until nested sets hype—then everyone regretted insert costs. Postgres recursion matured that debate; it’s adjacency’s revenge for read-mostly graphs.
Indexing seals it. Composite on (sire_id, dam_id), GIN on paths if you extend. Vacuum analyze post-launch kept ReptiDex zippy.
Real-World Hits: 50 Subs, What’s the Market Play?
Animal breeding niches—reptiles, dogs, horses—crave this. ReptiDex proves: solve pedigree pain, monetize fast. 200 animals tracked? That’s 9 days of viral word-of-mouth in Facebook groups.
Market dynamics: genealogy apps like Ancestry.com hit billions tracing humans. Pets? Fragmented. No dominant player. ReptiDex slips in via mobile-first, Postgres backbone.
Bold prediction: niche lineage trackers explode with AI breeding sims. COI prereqs like this tree? Table stakes. But hype alert—COI’s no silver bullet; bad data still breeds duds.
Performance truth: test your N. 10 gens? 1,023 rows max, but real trees sparse. Incomplete lines cap at 100-200 rows. Postgres eats it.
Siblings? Lateral joins on sire/dam pairs. Tricky, but doable.
Denorm decision smart—cache trees as JSONB blobs. Query once, serve forever till next birth ripples up.
Why Does Postgres Pedigree Matter for Devs?
Not just breeders. Org charts double like this? Rare, but bill-of-materials, category trees—adapt it.
Postgres recursion beats app-layer loops. No N+1 hell. Database owns the graph walk.
Critique the spin: author’s “not a toy” flex? Fair, but 200 animals ain’t petabyte scale. Still, principles scale.
Extend: add COI. Path counting via window funcs on the CTE. Traversal depth unlocks it.
🧬 Related Insights
- Read more: Axios Maintainer Hacked: NPM’s Latest Supply Chain Nightmare
- Read more: GitHub Copilot’s New Appetite: Devs’ Code Snacks Fuel Smarter AI
Frequently Asked Questions
How do you build a multi-generation pedigree tree in PostgreSQL?
Use recursive CTEs on an adjacency list with self-referential sire_id/dam_id keys—base case your animal, recurse on parents up to N gens.
Is adjacency list better than closure table for pedigrees?
Yes for write-light, predictable binary ups—cheaper inserts, fast recursion; closure overkills storage.
Can Postgres handle inbreeding detection in trees?
Absolutely—keep duplicate ancestors in the CTE; they signal shared paths for COI calcs.
