OPTIMASI PROSES DATA WAREHOUSE MENGGUNAKAN PARTISI DAN INDEXING PADA POSTGRESQL UNTUK MENINGKATKAN PERFORMA QUERY
DOI:
https://doi.org/10.23960/jitet.v13i3S1.8012Abstract Views: 194 File Views: 157
Keywords:
Data Warehouse, Optimization, Partition, Indexing, PostgreSQLAbstract
Optimasi query pada data warehouse sangatlah krusial terutama dengan pertumbuhan volume dan kompleksitas analitik. Seiring dengan pertumbuhan volume data, performa query pada data warehouse seringkali mengalami degradasi yang menghambat proses analisis dan pengambilan keputusan. Penelitian ini bertujuan untuk mengoptimalkan performa data warehouse dengan menganalisis dampak dari teknik partisi dan indexing terhadap kinerja query pada PostgreSQL 17.6 menggunakan dataset “Online Retail II” yang telah dibersihkan dan dibuat star schema. Eksperimen dilakukan secara komparatif yaitu tanpa optimasi, partisi saja, indexing saja, dan partisi dengan indexing. Kinerjanya diukur menggunakan dua matriks utama yaitu waktu eksekusi dan I/O. Penelitian menunjukkan bahwa kombinasi partisi dan indexing adalah strategi paling superior, yang mampu mengurangi waktu eksekusi query hingga 55.13% sedangkan partisi efektif untuk query berbasis rentang waktu dan indexing untuk akses selektif. Perancangan data warehouse berbasis PostgreSQL sebaiknya memadukan partisi yang selaras antara pola waktu dengan indexing pada kolom filter/join.
Downloads
References
J. Inukonda, “Leveraging Dimensional Modeling for Optimized Healthcare Data Warehouse Cloud Migration: Data Masking and Tokenization,” International Journal of Science and Research (IJSR), vol. 13, no. 10, pp. 437–441, Oct. 2024, doi: 10.21275/sr241004233606.
S. V. Salunke and A. Ouda, “A Performance Benchmark for the PostgreSQL and MySQL Databases,” Oct. 01, 2024, Multidisciplinary Digital Publishing Institute (MDPI). doi: 10.3390/fi16100382.
O. M. Hashem, K. Rahouma, N. S. Abd, and E. Hameed, “Enhancing Quality Factors in Data Warehousing Through Study and Improvement,” Journal of Advanced Engineering Trends, vol. 44, no. 1, pp. 187–193, Jan. 2025.
W. Wijaya, J. Wiratama, and S. F. Wijaya, “Implementation of Data Warehouse and Star Schema for Optimizing Property Business Decision Making,” G-Tech: Jurnal Teknologi Terapan, vol. 8, no. 2, pp. 1242–1250, Apr. 2024, doi: 10.33379/gtech.v8i2.4091.
M. N. Gundapaneni, “Data Partitioning: Optimizing Performance in Large Database Systems,” European Modern Studies Journal, vol. 9, no. 4, pp. 956–964, Aug. 2025, doi: 10.59573/emsj.9(4).2025.90.
V. C, S. Unnikrishnan, and J. V N, “Basic Indexing Techniques in Relational Database,” INTERNATIONAL JOURNAL OF INNOVATIVE RESEARCH IN TECHNOLOGY, vol. 6, no. 12, May 2020, [Online]. Available: https://www.guru99.com/indexing-in-database.
B. Azizi, A. A. Pratama, G. M. Dysa, and C. Carudin, “ANALISIS PENERAPAN DESIGN PATTERN SINGLETON DALAM PENGELOLAAN KONEKSI DATABASE UNTUK EFISIENSI MEMORI,” Jurnal Informatika dan Teknik Elektro Terapan, vol. 13, no. 3, pp. 2127–2136, Jul. 2025, doi: 10.23960/jitet.v13i3.6705.
J. Mostafa, S. Wehbi, S. Chilingaryan, and A. Kopmann, “SciTS: A Benchmark for Time-Series Databases in Scientific Experiments and Industrial Internet of Things,” Jun. 2022, doi: 10.1145/3538712.3538723.
T. Vu and A. Eldawy, “R*-Grove: Balanced Spatial Partitioning for Large-scale Datasets,” Jul. 2020, [Online]. Available: http://arxiv.org/abs/2007.11651
Vasudevan Senathi Ramdoss, “Optimizing database queries: Cost and performance analysis,” International Journal of Science and Research Archive, vol. 2, no. 2, pp. 293–297, Aug. 2021, doi: 10.30574/ijsra.2021.2.2.0025.
H. Nicholson, P. Chrysogelos, and A. Ailamaki, “HPCache: memory-efficient OLAP through proportional caching revisited,” in VLDB Journal, Springer Science and Business Media Deutschland GmbH, Nov. 2024, pp. 1775–1791. doi: 10.1007/s00778-023-00828-7.
M. Riyaz Ansari, K. Rahim, R. Bhoje, and S. Bhosale, “A STUDY ON RESEARCH DESIGN AND ITS TYPES,” International Research Journal of Engineering and Technology (IRJET), vol. 9, no. 7, pp. 1132–1135, Jul. 2022, [Online]. Available: www.irjet.net
B. All Habsy and M. Nursalim, “Jenis-Jenis Metode Pengumpulan Data (Qualitative Research),” Jurnal Pendidikan Tambusai, vol. 9, pp. 9932–9938, Feb. 2025.
L. Barać, “Research Environment,” Split, 2023, pp. 1–17. doi: 10.1007/978-3-031-22412-6_1.
J. Wehrstein, T. Eckmann, R. Heinrich, and C. Binnig, “JOB-Complex: A Challenging Benchmark for Traditional & Learned Query Optimization,” Jul. 2025, [Online]. Available: http://arxiv.org/abs/2507.07471
Downloads
Published
How to Cite
Issue
Section
License
Copyright (c) 2025 Jurnal Informatika dan Teknik Elektro Terapan
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.
