PgConf.Russia 2016

Java is the most used programming language in the world. Yet how is it supported in PostgreSQL? What are the gotchas and the best practices? Now that Java is evolving significantly, how will PostgreSQL follow?

Despite Java's age, language is stronger than ever. It's the de facto programming language in the enterprise world. And since Java 8, it is having a come back in the startup and open source world. PostgreSQL is accessed more from Java than any other interface but, how's Java supported in PostgreSQL?

This talk will analyze how it has been in the past, but more importantly how can you use it and what can you do today. JDBC drivers, best practices, pl/java and other less frequently used tools will be presented and discussed.

And then we will look into the future, to see what is currently under development. Like Phoebe, a new Java Reactive Driver for PostgreSQL that targets clusters, pipelined queries and non-JDBC interface for fully asynchronous operation. And also what needs to be done in areas like server-side Java, to bring Java to a fully advanced first-level language within PostgreSQL.

CitusDB is an extension for PostgreSQL that can distribute tables across a cluster of PostgreSQL servers. Data is stored in shards that can use append-partitioning for bulk-loading of time series data or hash-partitioning for real-time data ingestion. SELECT queries on distributed tables are transparently parallelised across the cluster, using all available cores. Distributed tables can also be joined in parallel, even if they are not partitioned along the same column. CitusDB is especially suitable for real-time analytics use-cases such as dashboards which require fast analytical queries over live data, and can simultaneously act as a scalable operational database. This talk will describe the internals of CitusDB and give a live demo of a large-scale CitusDB cluster.

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.

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.