Comparative analysis of indexing strategies in PostgreSQL under various load scenarios
The subject of the study is the effectiveness of various indexing strategies implemented in PostgreSQL and their impact on the performance of SELECT, UPDATE and INSERT operations in conditions of different data scales. The object of the study are the B-Tree, GIN and BRIN indexes used to optimize the...
Uložené v:
| Vydané v: | Программные системы и вычислительные методы číslo 1; s. 21 - 31 |
|---|---|
| Hlavný autor: | |
| Médium: | Journal Article |
| Jazyk: | English |
| Vydavateľské údaje: |
01.01.2025
|
| ISSN: | 2454-0714, 2454-0714 |
| On-line prístup: | Získať plný text |
| Tagy: |
Pridať tag
Žiadne tagy, Buďte prvý, kto otaguje tento záznam!
|
| Abstract | The subject of the study is the effectiveness of various indexing strategies implemented in PostgreSQL and their impact on the performance of SELECT, UPDATE and INSERT operations in conditions of different data scales. The object of the study are the B-Tree, GIN and BRIN indexes used to optimize the operation of databases. The author examines in detail such aspects of the topic as the time characteristics of operations, the size of indexes and their resource intensity. Special attention is paid to the impact of data volume on the performance of indexes and their suitability for working with various types of data, including JSONB. The research is aimed at systematizing knowledge about the use of indexes to improve the efficiency of highly loaded systems, which require optimization of access operations, updating and inserting data, as well as analysis of resource consumption. The leading research method is an empirical approach, which includes the development of a test database with orders, customers, and products tables. Experiments were conducted for SELECT, UPDATE, and INSERT operations on small, medium, and large amounts of data. The metrics of query execution time and index size obtained using PostgreSQL tools were used for the analysis. The novelty of the research lies in conducting a comprehensive performance analysis of the B-Tree, GIN, and BRIN indexes in PostgreSQL when performing typical operations on various amounts of data. The main conclusions of the conducted research are recommendations on the choice of indexes depending on the types of queries and their execution conditions. B-Tree indexes have proven their versatility, demonstrating high performance for SELECT and UPDATE operations. GIN indexes have shown advantages for working with JSONB data, but their use is limited by high resource intensity. BRIN indexes have proven to be effective for large amounts of data, especially for SELECT operations, due to their compactness and low overhead. A special contribution of the author to the research of the topic is the creation of recommendations for database developers, which makes it possible to improve application performance by choosing an optimal indexing strategy. |
|---|---|
| AbstractList | The subject of the study is the effectiveness of various indexing strategies implemented in PostgreSQL and their impact on the performance of SELECT, UPDATE and INSERT operations in conditions of different data scales. The object of the study are the B-Tree, GIN and BRIN indexes used to optimize the operation of databases. The author examines in detail such aspects of the topic as the time characteristics of operations, the size of indexes and their resource intensity. Special attention is paid to the impact of data volume on the performance of indexes and their suitability for working with various types of data, including JSONB. The research is aimed at systematizing knowledge about the use of indexes to improve the efficiency of highly loaded systems, which require optimization of access operations, updating and inserting data, as well as analysis of resource consumption. The leading research method is an empirical approach, which includes the development of a test database with orders, customers, and products tables. Experiments were conducted for SELECT, UPDATE, and INSERT operations on small, medium, and large amounts of data. The metrics of query execution time and index size obtained using PostgreSQL tools were used for the analysis. The novelty of the research lies in conducting a comprehensive performance analysis of the B-Tree, GIN, and BRIN indexes in PostgreSQL when performing typical operations on various amounts of data. The main conclusions of the conducted research are recommendations on the choice of indexes depending on the types of queries and their execution conditions. B-Tree indexes have proven their versatility, demonstrating high performance for SELECT and UPDATE operations. GIN indexes have shown advantages for working with JSONB data, but their use is limited by high resource intensity. BRIN indexes have proven to be effective for large amounts of data, especially for SELECT operations, due to their compactness and low overhead. A special contribution of the author to the research of the topic is the creation of recommendations for database developers, which makes it possible to improve application performance by choosing an optimal indexing strategy. |
| Author | Zolotukhina, Daria |
| Author_xml | – sequence: 1 givenname: Daria surname: Zolotukhina fullname: Zolotukhina, Daria |
| BookMark | eNpNkN1KAzEQhYMoWGtfQfICu-Z3s7mU4h8UVCzehmkyWwLtpiRtsW_vrop4NWfOGQbOd0XO-9QjITec1Ubo5lYorSpmuKoFE7rmtZFctmdk8hec_9OXZFZKXDGljDassRPyMU_bHWTYxyNS6GFzKrHQ1NHYB_yM_ZqW_ZDiOmIZPPqayn6d8f1tQQ_DRaZHyDEdCt0kCLR47Me9XJOLDjYFZ79zSpYP98v5U7V4eXye3y0qb3Vb6bYRppXeigAYghk88K3RgTdBNZxbq72UoFAYlFp1FgOHFQrbSQ_AjJyS5uetz6mUjJ3b5biFfHKcuZGPG6u7sbob-TjuvvnIL8ijW8A |
| Cites_doi | 10.1088/1742-6596/944/1/012022 |
| ContentType | Journal Article |
| DBID | AAYXX CITATION |
| DOI | 10.7256/2454-0714.2025.1.73138 |
| DatabaseName | CrossRef |
| DatabaseTitle | CrossRef |
| DatabaseTitleList | CrossRef |
| DeliveryMethod | fulltext_linktorsrc |
| EISSN | 2454-0714 |
| EndPage | 31 |
| ExternalDocumentID | 10_7256_2454_0714_2025_1_73138 |
| GroupedDBID | AAYXX ALMA_UNASSIGNED_HOLDINGS CITATION M~E |
| ID | FETCH-LOGICAL-c958-5862783c92daedd7958ac875d16d4611995c33a4e27e354f9ed1abe29f3caa073 |
| ISSN | 2454-0714 |
| IngestDate | Sat Nov 29 08:02:37 EST 2025 |
| IsDoiOpenAccess | false |
| IsOpenAccess | true |
| IsPeerReviewed | false |
| IsScholarly | false |
| Issue | 1 |
| Language | English |
| LinkModel | OpenURL |
| MergedId | FETCHMERGED-LOGICAL-c958-5862783c92daedd7958ac875d16d4611995c33a4e27e354f9ed1abe29f3caa073 |
| OpenAccessLink | https://doi.org/10.7256/2454-0714.2025.1.73138 |
| PageCount | 11 |
| ParticipantIDs | crossref_primary_10_7256_2454_0714_2025_1_73138 |
| PublicationCentury | 2000 |
| PublicationDate | 2025-1-00 |
| PublicationDateYYYYMMDD | 2025-01-01 |
| PublicationDate_xml | – month: 01 year: 2025 text: 2025-1-00 |
| PublicationDecade | 2020 |
| PublicationTitle | Программные системы и вычислительные методы |
| PublicationYear | 2025 |
| References | ref13 ref12 ref11 ref10 ref2 ref1 ref8 ref7 ref9 ref4 ref3 ref6 ref5 |
| References_xml | – ident: ref13 – ident: ref1 – ident: ref4 – ident: ref2 – ident: ref5 – ident: ref6 – ident: ref7 – ident: ref3 doi: 10.1088/1742-6596/944/1/012022 – ident: ref9 – ident: ref8 – ident: ref10 – ident: ref11 – ident: ref12 |
| SSID | ssib044757069 |
| Score | 1.8943626 |
| Snippet | The subject of the study is the effectiveness of various indexing strategies implemented in PostgreSQL and their impact on the performance of SELECT, UPDATE... |
| SourceID | crossref |
| SourceType | Index Database |
| StartPage | 21 |
| Title | Comparative analysis of indexing strategies in PostgreSQL under various load scenarios |
| hasFullText | 1 |
| inHoldings | 1 |
| isFullTextHit | |
| isPrint | |
| journalDatabaseRights | – providerCode: PRVHPJ databaseName: ROAD: Directory of Open Access Scholarly Resources customDbUrl: eissn: 2454-0714 dateEnd: 99991231 omitProxy: false ssIdentifier: ssib044757069 issn: 2454-0714 databaseCode: M~E dateStart: 20170101 isFulltext: true titleUrlDefault: https://road.issn.org providerName: ISSN International Centre |
| link | http://cvtisr.summon.serialssolutions.com/2.0.0/link/0/eLvHCXMwtR3LThsx0OLRAxfUilYF2soHcmmUEHvtdXxsHqgHQFSNKm4rZ-2VKtAGhRBx6rd3xo43BkVVOXCZ2LP2rHdn4vF4PTOEnPQqoWQlbEdV3HSEsQ4_EjL4uzNQlgrjvVifbEJdXvavr_XV1vbX6AuzvFV13X981HevymrAAbPRdfYF7G6IAgLKwHSAwHaA_8X4YRLP2yQhR3xcxLB7EMND4GYHZusFm_vnj3OfEnfeXoL1jOdib2fGtjHWE9bv00Vsa9Rr6bPWiOExCSgPxh5mCSbAYQJH_urAl2XbV5iv9GMZIA-XYzfs0A6Nwg-PRACqp72hPEgwKaXQfvMwmvHJpFt4HhGaNlvroCYWDzeYbzw4A4QD2s2eCZfJnomfWrmQwrtuBS24AZdKfZzak0VC0FzP1Y-C9SN62ERKXbx3l3VVxkIIm6fxvp_p4eZ0JNhlSKlAOgXSKZBOwQpPZ5vsciU1Hl-8-DOOcyfGbVQ9n8GxuX1wiEdSp5uHlKzFkkXV5C3ZX1lD9FuQ4ndky9UH5FciwTRKMJ1VNEowXUsw4OhagqmXYLqSYIoSTBsJfk8mZ-PJ8Htnlf6jU2qJ7oA5ZoEpNbfGWasAZ0qwri3LrcgZhhYos8wIx5XLpKi0s8xMHddVVhoDmusD2alntftIqIC3o-Dh-xV-ZZ_m2oIVn5uKS1vajE0PyWl8D8VdCPJS_JsJRy_ucUz21pL4iews5g_uM3lTLhe_7-dfPC__Ahw1fIY |
| linkProvider | ISSN International Centre |
| openUrl | ctx_ver=Z39.88-2004&ctx_enc=info%3Aofi%2Fenc%3AUTF-8&rfr_id=info%3Asid%2Fsummon.serialssolutions.com&rft_val_fmt=info%3Aofi%2Ffmt%3Akev%3Amtx%3Ajournal&rft.genre=article&rft.atitle=Comparative+analysis+of+indexing+strategies+in+PostgreSQL+under+various+load+scenarios&rft.jtitle=%D0%9F%D1%80%D0%BE%D0%B3%D1%80%D0%B0%D0%BC%D0%BC%D0%BD%D1%8B%D0%B5+%D1%81%D0%B8%D1%81%D1%82%D0%B5%D0%BC%D1%8B+%D0%B8+%D0%B2%D1%8B%D1%87%D0%B8%D1%81%D0%BB%D0%B8%D1%82%D0%B5%D0%BB%D1%8C%D0%BD%D1%8B%D0%B5+%D0%BC%D0%B5%D1%82%D0%BE%D0%B4%D1%8B&rft.au=Zolotukhina%2C+Daria&rft.date=2025-01-01&rft.issn=2454-0714&rft.eissn=2454-0714&rft.issue=1&rft.spage=21&rft.epage=31&rft_id=info:doi/10.7256%2F2454-0714.2025.1.73138&rft.externalDBID=n%2Fa&rft.externalDocID=10_7256_2454_0714_2025_1_73138 |
| thumbnail_l | http://covers-cdn.summon.serialssolutions.com/index.aspx?isbn=/lc.gif&issn=2454-0714&client=summon |
| thumbnail_m | http://covers-cdn.summon.serialssolutions.com/index.aspx?isbn=/mc.gif&issn=2454-0714&client=summon |
| thumbnail_s | http://covers-cdn.summon.serialssolutions.com/index.aspx?isbn=/sc.gif&issn=2454-0714&client=summon |