Hierarchical Data in Databases: From Infinite Comments to Product Categories
How do you store a tree in a flat table? Comments nested infinitely like HackerNews, product categories 10 levels deep, org charts, file systems — they’re all trees. This post compares four patterns for hierarchical data: Adjacency List, Nested Set, Materialized Path, and Closure Table. Each with PostgreSQL and MongoDB implementations, real complexity analysis, and when to use what. The Problem A relational table is flat — rows and columns. A tree is recursive — nodes pointing to children pointing to children. There’s no native “tree” column type. You need to encode the tree structure into flat data, and every encoding has trade-offs between read speed, write speed, and storage. ...