Dynamic Compilation of User-Defined Functions in PL/pgSQL Language

Many modern RDBMS provide procedural extensions for SQL programming language, which allow users to perform server-side complex computations. Use of such extensions improves modularity and code reuse, simplifies programming of application logic, and helps developers to avoid network overhead and enhance performance. Interpretation is mostly used to execute SQL queries and procedural extensions code, resulting in significant computational overhead because of indirect function calls and performing of generic checks. Moreover, most RDBMS use different engines for SQL queries execution and procedural extensions code execution, and it is necessary to perform additional computations to switch between different engines. Thus, interpretation of SQL queries combined with interpretation of procedural extensions code may drastically degrade performance of RDBMS. One solution is to use a dynamic compilation technique. In this paper, we describe the technique of dynamic compilation of PL/pgSQL procedural language for the PostgreSQL database system using LLVM compiler infrastructure. Dynamic compiler of PL/pgSQL procedural language is developed as part of PostgreSQL queries dynamic compiler. Proposed technique helps to get rid of computational overhead caused by interpretation usage. Synthetic performance tests show that the developed solution speeds up SQL queries execution by several times.

1. PostgreSQL official site. Available at: https://www.postgresql.org/, accessed: 20.10.2020.

2. Karthik Ramachandra, Kwanghyun Park, K. Venkatesh Emani, Alan Halverson, César A. Galindo-Legaria, Conor Cunningham Froid. Optimization of Imperative Programs in a Relational Database. Proceedings of the VLDB Endowment, vol. 11, no. 4, 2017, pp. 432-444.

3. Christian Duta, Denis Hirn, and Torsten Grust. Compiling PL/SQL Away. In Proc. of the 10th Annual Conference on Innovative Data Systems Research (CIDR’20), 2020, 8 p.

4. Denis Hirn and Torsten Grust. PL/SQL Without the PL. In Proc. of the 2020 ACM SIGMOD International Conference on Management of Data (SIGMOD’20), 2020, pp. 2677–2680.

5. PL/pgSQL – SQL Procedural Language. Available at: https://www.postgresql.org/docs/9.6/plpgsql-overview.html, accessed: 20.10.2020

6. The LLVM Compiler Infrastructure. Available at: http://llvm.org/, accessed: 20.10.2020.

7. Шарыгин Е.Ю., Бучацкий Р.А., Скворцов Л.В., Жуйков Р.А., Мельник Д.М. Динамическая компиляция выражений в SQL-запросах для СУБД PostgreSQL. Труды ИСП РАН, том 28, вып. 4, 2016 г., стр. 217-240. DOI: 10.15514/ISPRAS-2016-28(4)-13 / Sharygin E.Y., Buchatskiy R.A., Skvortsov L.V., Zhuykov R.A., Melnik D.M. Dynamic compilation of expressions in SQL queries for PostgreSQL. Trudy ISP RAN/Proc. ISP RAS, vol. 28, issue 4, 2016. pp. 217-240 (in Russian).

8. Бучацкий Р.А., Шарыгин Е.Ю., Скворцов Л.В., Жуйков Р.А., Мельник Д.М., Баев Р.В. Динамическая компиляция SQL-запросов для СУБД PostgreSQL. Труды ИСП РАН, том 28, вып. 6, 2016, стр. 37-48. DOI: 10.15514/ISPRAS-2016-28(6)-3 / Buchatskiy R.A., Sharygin E.Y., Skvortsov L.V., Zhuykov R.A., Melnik D.M., Baev R.V. Dynamic compilation of SQL queries for PostgreSQL. Trudy ISP RAN/Proc. ISP RAS, vol. 28, issue 6, 2016, pp. 37-48 (in Russian).

9. E. Sharygin, R. Buchatskiy, R. Zhuykov, and A. Sher. Runtime specialization of postgresql query executor. Lecture Notes in Computer Science, vol. 11, 2018, pp. 375–386.

10. Пантилимонов М.В., Бучацкий Р.А., Жуйков Р.А. Кэширование машинного кода в динамическом компиляторе SQL-запросов для СУБД PostgreSQL. Труды ИСП РАН, том 32, вып. 1, 2020, стр. 205-220. DOI: 10.15514/ISPRAS-2020-32(1)-11 / Pantilimonov M.V., Buchatskiy R.A., Zhuykov R.A. Machine code caching in PostgreSQL query JIT-compiler. Trudy ISP RAN/Proc. ISP RAS, vol. 32, issue 1, 2020. pp. 205-202 (in Russian).

11. ISP RAS website. Available at: https://www.ispras.ru/en/, accessed: 20.10.2020.

12. Graefe G. Volcano – an extensible and parallel query evaluation system. IEEE Transactions on Knowledge and Data Engineering, vol. 6, no. 1, 1994, pp. 120-135.

13. Perf profiler website. Available at: https://perf.wiki.kernel.org/index.php/Main_Page, accessed: 20.10.2020.

14. PostgreSQL SPI Memory Management. Available at: https://www.postgresql.org/docs/9.6/spi-memory.html, accessed: 20.10.2020.

15. PLV8 – A Procedural Language in Javascript powered by V8. Available at: https://github.com/plv8/plv8, accessed: 20.10.2020.

16. PL/Python – Python Procedural Language. Available at: https://www.postgresql.org/docs/9.6/plpython.html, accessed: 20.10.2020.

17. PL/Perl – Perl Procedural Language, Available at: https://www.postgresql.org/docs/9.6/plperl.html, accessed: 20.10.2020.