PGConf.Russia 2018

Developers are often challenged to deliver results that are hard to implement using simple SQL queries. Fortunately, complex SQL capabilities exist in the SQL standards — common table expressions and window functions.

SQL is a declarative language, meaning the user submits an SQL command and the database determines the optimal execution. Common Table Expressions (CTEs) allow queries to be more imperative, allowing looping and processing hierarchical structures that are normally associated only with imperative languages.

Normal SQL queries return rows where each row is independent of the other returned rows. SQL window functions allow queries to return computed columns based on values in other rows in the result set.

This tutorial will help developers use CTE queries in their applications and allow operations that normally could only be done in application code to be done via SQL queries. It also explains the many window function facilities and how they can be used to produce useful SQL query results.

Video

Part I «Programming the SQL Way with CTE»

Part II «Postgres Window Magic»

It is often required to asynchronously perform several transactions in a strictly defined sequence, not just a single transaction. There are several ways to achieve this, and one of the solutions available is the pgpro_scheduler module.

Postgres has a nice feature called Point-in-time Recovery (PITR) that would allow you to go back in time. In this talk, we will discuss what are the use-cases of PITR, how to prepare your database for PITR by setting good base backup and WAL shipping setups, with some examples. We will expand the discussion with how to achieve PITR if you have a distributed and sharded Postgres setup by mentioning challenges such as clock differences and ways to overcome them, such as two-phase commit and pg_create_restore_point.

If you honestly evaluate most of our business applications, you will see that they first collect and import the data into a database and then send the same data in the opposite direction.

What if we don't build an ORM wall between the application and the database, but try using the symbiosis of their strong points and special features instead?

I will tell you how we use PostgreSQL and Clojure for building data-intensive medical applications. We will cover the following topics:

• functional relational programming
• jsonb for modeling complex data domains
• functional indexes and json-knife extension for jsonb search
• graphql implementation on PostgreSQL
• logical replication for building reactive integrations
• asynchronous JDBC-free connector to PostgreSQL on netty