Optimization of Memory Operations in Generalized Search Trees of PostgreSQL

  • Andrey Borodin
  • Sergey Mirvoda
  • Ilia Kulikov
  • Sergey Porshnev
Conference paper
Part of the Communications in Computer and Information Science book series (CCIS, volume 716)

Abstract

Our team is working on new algorithms for intra-page indexing in PostgreSQL generalized search trees. During this work, we encountered that slight modification of the algorithm for modification of a tuple on a page can significantly affect the performance. This effect is caused by optimization of page compaction operations and speeds up inserts and updates of a data. Most important performance improvement is gained using sorted data insertion, time to insert data into an index can be reduced by a factor of 3. For a randomized data performance increase is around 15%. Size of the index also significantly reduced. This paper describes implementation and evaluation of the technique in PostgreSQL codebase. Proposed patch is committed to upstream and expected to be released with the PostgreSQL 10.

Keywords

PosgreSQL GiST Memory operations Multidimensional index 

References

  1. 1.
    Bayer, R.: Binary b-trees for virtual memory. In: Proceedings of the 1971 ACM SIGFIDET (Now SIGMOD) Workshop on Data Description, Access and Control, pp. 219–235. ACM (1971)Google Scholar
  2. 2.
    Beckmann, N., Seeger, B.: A revised r*-tree in comparison with related index structures. In: SIGMOD 2009 Proceedings of the 2009 ACM SIGMOD International Conference on Management of Data (2009)Google Scholar
  3. 3.
    Borodin, A., Kiselev, Y., Mirvoda, S., Porshnev, S.: On design of domain-specific query language for the metallurgical industry. In: Kozielski, S., Mrozek, D., Kasprowski, P., Małysiak-Mrozek, B., Kostrzewa, D. (eds.) BDAS 2015. CCIS, vol. 521, pp. 505–515. Springer, Cham (2015). doi: 10.1007/978-3-319-18422-7_45 Google Scholar
  4. 4.
    Borodin, A., Mirvoda, S., Porshnev, S.: Database index debug techniques: a case study. In: Kozielski, S., Mrozek, D., Kasprowski, P., Małysiak-Mrozek, B., Kostrzewa, D. (eds.) BDAS 2015-2016. CCIS, vol. 613, pp. 648–658. Springer, Cham (2016). doi: 10.1007/978-3-319-34099-9_50 CrossRefGoogle Scholar
  5. 5.
    Borodin, A.: Gist inserts optimization with pageindextupleoverwrite. https://commitfest.postgresql.org/10/661
  6. 6.
    Borodin, A.: [PoC] GiST optimizing memmoves in gistplacetopage for fixed-size updates. https://www.postgresql.org/message-id/CAJEAwVGQjGGOj6mMSgMwGvtFd5Kwe6VFAxY%3DuEPZWMDjzbn4VQ%40mail.gmail.com
  7. 7.
  8. 8.
    Chilingarian, I., Bartunov, O., Richter, J., Sigaev, T.: Postgresql: the suitable DBMS solution for astronomy and astrophysics. In: Astronomical Data Analysis Software and Systems (ADASS) XIII, vol. 314, p. 225 (2004)Google Scholar
  9. 9.
    Garcia-Molina, H., Ullman, J.D., Widom, J.: Database System Implementation, vol. 654. Prentice Hall, Upper Saddle River (2000)Google Scholar
  10. 10.
    Guttman, A.: R-trees: A Dynamic Index Structure for Spatial Searching, vol. 14. ACM, New York (1984)Google Scholar
  11. 11.
    Hameurlain, A., Morvan, F.: Big data management in the cloud: evolution or crossroad? In: Kozielski, S., Mrozek, D., Kasprowski, P., Małysiak-Mrozek, B., Kostrzewa, D. (eds.) BDAS 2015-2016. CCIS, vol. 613, pp. 23–38. Springer, Cham (2016). doi: 10.1007/978-3-319-34099-9_2 CrossRefGoogle Scholar
  12. 12.
    Hellerstein, J.M., Naughton, J.F., Pfeffer, A.: Generalized search trees for database systems, September 1995Google Scholar
  13. 13.
    Kornacker, M., Mohan, C., Hellerstein, J.M.: Concurrency and recovery in generalized search trees. In: ACM SIGMOD Record, vol. 26, pp. 62–72. ACM (1997)Google Scholar
  14. 14.
    Korotkov, A.: A new double sorting-based node splitting algorithm for r-tree. In: Proceedings of the Spring/Summer Young Researchers Colloquium on Software Engineering. vol. 5 (2011)Google Scholar
  15. 15.
    Krechowicz, A., Deniziak, S., Łukawski, G., Bedla, M.: Preserving data consistency in scalable distributed two layer data structures. In: Kozielski, S., Mrozek, D., Kasprowski, P., Małysiak-Mrozek, B., Kostrzewa, D. (eds.) BDAS 2015. CCIS, vol. 521, pp. 126–135. Springer, Cham (2015). doi: 10.1007/978-3-319-18422-7_11 Google Scholar

Copyright information

© Springer International Publishing AG 2017

Authors and Affiliations

  • Andrey Borodin
    • 1
  • Sergey Mirvoda
    • 1
  • Ilia Kulikov
    • 2
  • Sergey Porshnev
    • 1
  1. 1.Ural Federal UniversityYekaterinburgRussia
  2. 2.RWTH AachenAachenGermany

Personalised recommendations