PgConf.Russia 2016

In late 2006, the 1C company has implemented to work 1C:Enterprise platform with DBMS PostgreSQL, which can operate under the operating systems Windows or Linux. The talks will attempt to summarize the experience of sharing the platform 1C:Enterprise with PostgreSQL database since 2008. Consideration will be given a few success stories, technical features of the work are examples of specific tasks, offers advice on selecting and cons. The talk may be of interest to employees of companies considering the option of using PostgreSQL for the 1C:Enterprise, DBA, professionals interested in the possibility of extensibility PostgreSQL.

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.

We love to stress test software, since we are a performance engineering company. Our friends from a hosting company servers.com provided us with a modern dedicated server so we immediately started to test PostgreSQL in different environments, including SmartOS, DragonFly and Windows. We would like to present our results (and all the gory details) to community.

Whenever multiple users, processes, or threads are concurrently modifying data which is shared among them, problems can occur if race conditions are not handled somehow. These problems are particularly acute in a database which provide ACID semantics. A set of changes grouped into a database transaction must appear atomically, both to concurrent transactions and in terms of crash recovery. Each transaction must move the database from one consistent state (with regard to business rules) to another. For programming efficiency, each transaction must be able to be coded independently of what other transactions may happen to be running at the same time. In the event of a crash, all modifications made by transactions for which the application was notified of successful completion, and all modifications which had become visible to other transactions, must still be completed upon crash recovery. Over the years, various strategies have been employed to provide these guarantees, and sometimes the guarantees have been compromised in one way or another. This talk will cover the approaches taken to provide these guarantees or compromised variations of them, with an emphasis on the Serializable Snapshot Isolation (SSI) technique available in PostgreSQL (and so far not in any other production product). While SSI already performs faster and with higher concurrency than any other technique for managing race conditions with most common workloads, there are many opportunities for further enhancing performance, some of which would require the assistance of people expert in the various index access methods; these issues will be discussed. The talk will also present some rough ideas about how SSI techniques might be used with XTM in a distributed system.

Time will be reserved at the end of the talk for group discussion of optimizations and possible application in distributed environments.