PgConf.Russia 2016

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.

PostgreSQL includes several index types: GiST, SP-GiST, GIN, and of course, the regular B-tree. DBAs are familiar with using each of these for specific use cases, GIN for full-text search, GiST for geometrical data, and so on, but how do they work internally? What makes them suitable for the cases they're typically used for?

In this presentation, I will walk through the internal structure of each of these index types, explaining what strengths and weaknesses each one of them have.

In the speech we consider the current PostgreSQL planner model, then the possibilities of applying machine learning methods for planner improvement and the obtained results.

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.