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.

Method of automated refresh of preprocessed results of analytis reports is provided. Preprocessing and caching of reports allows ability for fast response for big data reports. Author describes the way of reports cache refreshing with minimum server loads and tuned actualization rate.

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.

We'll discuss how does Linux work with virtual memory. The following topics will be covered: * x86-64 page table, context switch and page fault; * internals of virtual memory management (VMM) in Linux; * page eviction methods in Linux, page cache and anonymous pages; * huge and gigantic pages, transparent huge pages; * how mmap(2) works and what madvise(2), msync(2) etc. provide; * why large databases don't use mmap(2), but rather implement buffer pool on their own; * ans surely how to tune Linux VMM using sysctl.