PgConf.Russia 2016

Postgresql's buffer manager has parts where it's showing its age. We'll discuss how it currently works, what problems there are, and what attempts are in progress to rectify its weaknesses.

• Lookups in the buffer cache are expensive
• The buffer mapping table is organized as a hash table, which makes efficient implementations of prefetching, write coalescing, dropping of cache contents hard
• Relation extension scales badly
• Cache replacement is inefficient
• Cache replacement replaces the wrong buffers

Currently, PostgreSQL uses the interpreter to execute SQL-queries. This yields an overhead caused by indirect calls to handler functions and runtime checks, which could be avoided if the query were compiled into the native code "on-the-fly" (i.e. JIT-compiled): at a run time the specific table structure is known as well as data types used in the query. This is especially important for complex queries, which performance is CPU-bound. At the moment there are two major projects that implement JIT-compilation in PostgreSQL: a commercial database Vitesse DB and an open-source project PGStorm. The former uses LLVM JIT to achieve up to 8x speedup on selected TPC-H benchmarks, while the latter JIT-compiles the query using CUDA and executes it on GPU, which allows to speed up execution of specific query types by an order.

Our work is dedicated to adding support for SQL query JIT-compilation to PostgreSQL using LLVM compiler infrastructure. In the presentation we'll discuss how JIT-compilation can be used to speed up various stages of query execution in PostgreSQL, and the specifics of translating an SQL query into LLVM bitcode to achieve good performing native code. Also we'll present preliminary results for our JIT-compiler on TPC-H benchmark.

Common Java wisdom is to use PreparedStatements and Batch DML in order to achieve top performance. It turns out one cannot just blindly follow the best practices. In order to get high throughput, you need to understand the specifics of the database in question, and the content of the data.

In the talk we will see how proper usage of PostgreSQL protocol enables high performance operation while fetching and storing the data. We will see how trivial application and/or JDBC driver code changes can result in dramatic performance improvements. We will examine how server-side prepared statements should be activated, and discuss pitfalls of using server-prepared statements.

The talk describes performance benchmarking results of PostgreSQL on modern Hi-End servers. The main attention was paid to the locks for shared data access and associated bottlenecks. The testing propose was to test the linear read scalability limits with an increase of cores number allocated for PostgreSQL. Testing was performed for different postgres versions (9.4, 9.5, 9.6) to check new features designed to increase performance on multiprocessing architectures.