Optimasi Kueri pada Very Large Database untuk Analisis Transaksi E-Commerce Real-Time Menggunakan Hybrid Indexing dan Partitioning

Abstract

The growth rate of e-commerce transactions generates enormous data volumes (Very Large Database/VLDB), creating significant performance challenges for real-time analytical queries. Traditional optimization techniques are often inadequate when applied separately. This study proposes and tests a hybrid optimization strategy that integrates Range Partitioning and Partial Indexing in a PostgreSQL database system. An experimental research method was conducted by building a database simulation containing 100 million rows of synthetic transaction data, then comparing performance between a baseline configuration and an optimized configuration. Test results show significant improvement. The complex aggregation query (Q1) experienced an 86.6% acceleration in execution time (from 14,200 ms to 1,900 ms), while the specific search query (Q2) improved by 89.1% (from 8,500 ms to 930 ms). Query plan analysis proves the effectiveness of the partition pruning mechanism and the use of more efficient partial indexes. It is concluded that this hybrid strategy is effective in optimizing VLDB performance for e-commerce analytical workloads and is recommended for adoption in production environments.