Benchmarking Relational and Array-Based Models for Genealogical Data Storage in PostgreSQL

Abstract

Genealogical information systems manage inherently hierarchical data structures that represent family relationships across multiple generations. Traditional implementations predominantly rely on normalized relational database designs using junction tables to model parent–child relationships. While this approach ensures strong referential integrity, it often incurs substantial performance overhead due to complex join operations during deep hierarchical traversal. Recent versions of PostgreSQL provide native support for array data types This study compares two genealogical database models implemented in PostgreSQL: a normalized relational model using a junction table and a denormalized model that stores child identifiers directly as UUID arrays. To evaluate their performance, we conducted controlled benchmarking experiments using synthetically generated genealogical datasets with varying generational depth and branching patterns. The comparison focuses on storage efficiency, recursive traversal performance, and write operation costs under realistic hierarchical workloads. Results obtained from a large-scale dataset containing more than 7 million individual records show that the UUID array–based model reduces disk space usage by 31%. During deep recursive traversal involving over 12 million nodes at the tenth generation, the array-based model demonstrates improved data locality, leading to a 5.2% reduction in execution latency and 7% fewer shared buffer accesses compared to the relational model. Interestingly, contrary to common expectations in normalized database design, the array-based model achieves 22% faster single-insert performance because it avoids foreign key validation and multiple index updates. This improvement comes with slightly higher write amplification, reflected in a 6.6% increase in buffer usage due to PostgreSQL’s multi-version concurrency control mechanism. These findings contribute to the field of Informatics by providing empirical evidence on how database internal mechanisms influence performance trade-offs in hierarchical data management, offering guidance for designing scalable and read-efficient information systems beyond genealogical applications.