Evaluating Field Flexibility Approaches in Relational Databases: A Performance Study of JSON and Column-Oriented Models in Library Systems

This study examines two approaches for achieving field flexibility in library systems using relational databases: column-oriented tables and JSON data types. To evaluate the performance and practicality of flexible schema strategies, a dataset of 41,000 library records was implemented using both column-oriented and JSONB-based schemas in PostgreSQL. Five representative queries based on typical search operations in library applications were executed repeatedly on each model, and average execution times were measured in a controlled environment. Results show that JSONB consistently outperforms the column-oriented approach across all query scenarios, benefiting from reduced structural overhead and more direct access to semi-structured data. However, the flexibility of JSONB introduces risks of inconsistent data structures and reduced schema enforcement compared to the more rigid but uniform column-oriented method. The findings highlight a trade-off between performance and data consistency, suggesting that JSONB is advantageous for dynamic, metadata-rich systems, while column-oriented storage remains preferable when strict structural integrity is required. Future work should explore hybrid models and schema validation layers to combine flexibility with reliable data governance.