A COMPARATIVE STUDY OF INDEXING STRATEGIES FOR BOOSTING POSTGRESQL QUERY PERFORMANCE

In the digital age, the exponential growth of data volumes in modern information systems has made query performance a critical challenge for organizations relying on relational database management systems (RDBMS). While indexing is widely recognized as an effective optimization strategy, the comparative impact of different index types on PostgreSQL performance across multiple operation categories remains insufficiently studied in the literature. This study provides an experimental evaluation of three indexing strategies - simple index, composite index, and text index - applied to PostgreSQL 18.0 on a synthetic dataset of 500,000 records. Performance is assessed across three fundamental data manipulation operations (retrieval, updating, and deletion) using four metrics: execution time, CPU utilization, memory consumption, and index storage size. Results demonstrate substantial performance gains for retrieval and update operations across all index types: 96.5% and 98.0% for the simple index, 96.3% and 98.7% for the composite index, and 98.8% and 99.9% for the text index. Deletion operations showed more moderate gains (51.4%-60.3%), attributed to index maintenance overhead. These findings offer concrete guidelines for practitioners seeking to optimize large-scale data management in PostgreSQL-based information systems.