PostgreSQL Table Partitioning Strategies: Handling Billions of Rows Efficiently
DOI:
https://doi.org/10.63282/3050-9246.IJETCSIT-V5I3P103Keywords:
PostgreSQL, Table Partitioning, Range Partitioning, List Partitioning, Hash Partitioning, Query Optimization, Big Data ManagementAbstract
Another important issue is how to effectively perform data storage and querying especially when data reaches billions of rows. Table partitioning is an otherwise well-developed technique employed by the sophisticated open source relational database called PostgreSQL. The present paper describes different partitioning options, their applicability and impact on query response time, data storage and management. There is emphasis on range, list and hash partitioning methods; The study provides real life examples and the findings of experiments conducted. This paper concludes with the implementation and evaluation of database architecture of big data, the challenges, and the recommendation guidelines for database architects/administrators who deal with large dataset
Downloads
References
[1] Eltabakh, M. Y., Eltarras, R., & Aref, W. G. (2006, April). Space-partitioning trees in postgresql: Realization and performance. In 22nd International Conference on Data Engineering (ICDE'06) (pp. 100-100). IEEE.
[2] Martins, P., Tomé, P., Wanzeller, C., Sá, F., & Abbasi, M. (2021). Comparing oracle and postgresql, performance and optimization. In Trends and Applications in Information Systems and Technologies: Volume 2 9 (pp. 481-490). Springer International Publishing.
[3] Viloria, A., Acuña, G. C., Franco, D. J. A., Hernández-Palma, H., Fuentes, J. P., & Rambal, E. P. (2019). Integration of data mining techniques to PostgreSQL database manager system. Procedia Computer Science, 155, 575-580.
[4] Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387.
[5] Carlota Soto, PostgreSQL Table Partitioning: Boosting Performance and Management, Hackernoon. 2023. online. https://hackernoon.com/postgresql-table-partitioning-boosting-performance-and-management
[6] PostgreSQL: The World's Most Advanced Open Source Relational Database, 2024. online. https://www.postgresql.org/
[7] Herodotou, H., Borisov, N., & Babu, S. (2011, June). Query optimization techniques for partitioned tables. In Proceedings of the 2011 ACM SIGMOD International Conference on Management of data (pp. 49-60).
[8] Juba, S., Vannahme, A., & Volkov, A. (2015). Learning PostgreSQL. Packt Publishing Ltd.
[9] Stones, R., & Matthew, N. (2006). Beginning databases with postgreSQL: From novice to professional. Apress.
[10] Krunal Suthar, Unlocking Performance: A Deep Dive into Table Partitioning in PostgreSQL, 2024. online. https://medium.com/simform-engineering/unlocking-performance-a-deep-dive-into-table-partitioning-in-postgresql-3f5b8faa025f
[11] Hans-Jürgen Schönig, Killing performance with PostgreSQL partitioning, 2023. online. https://www.cybertec-postgresql.com/en/killing-performance-with-postgresql-partitioning/
[12] Ahmed, I., Fayyaz, A., & Shahzad, A. (2015). PostgreSQL Developer's Guide (Vol. 1). Packt Publishing.
[13] Böszörményi, Z., & Schönig, H. J. (2013). PostgreSQL Replication. Packt Publishing.
[14] Guide to PostgreSQL Table Partitioning, Medium, 2023. https://rasiksuhail.medium.com/guide-to-postgresql-table-partitioning-c0814b0fbd9b
[15] Abbasi, M., Bernardo, M. V., Váz, P., Silva, J., & Martins, P. (2024). Adaptive and Scalable Database Management with Machine Learning Integration: A PostgreSQL Case Study. Information, 15(9), 574.
[16] Yedilkhan, D., Mukasheva, A., Bissengaliyeva, D., & Suynullayev, Y. (2023, May). Performance analysis of scaling NoSQL vs SQL: A comparative study of MongoDB, Cassandra, and PostgreSQL. In 2023 IEEE International Conference on Smart Information Systems and Technologies (SIST) (pp. 479-483). IEEE.
[17] Güney, E., & Ceylan, N. (2022, February). Response Times Comparison of MongoDB and PostgreSQL Databases in Specific Test Scenarios. In International Congress of Electrical and Computer Engineering (pp. 178-188). Cham: Springer International Publishing.
[18] Schönig, H. J. (2023). Mastering PostgreSQL 15: Advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications. Packt Publishing Ltd.
[19] Ferrari, L., & Pirozzi, E. (2020). Learn PostgreSQL: Build and manage high-performance database solutions using PostgreSQL 12 and 13. Packt Publishing Ltd.
[20] Coulon, C., Pacitti, E., & Valduriez, P. (2005, July). Consistency management for partial replication in a high performance database cluster. In 11th International Conference on Parallel and Distributed Systems (ICPADS'05) (Vol. 1, pp. 809-815). IEEE.
[21] K. Patibandla and R. Daruvuri, "Reinforcement deep learning approach for multi-user task offloading in edge-cloud joint computing systems," International Journal of Research in Electronics and Computer Engineering, vol. 11, no. 3, pp. 47-58, 2023.
