PGConf.Russia 2018

The speaker will share his experience of transferring high-loaded game projects that were originally developed to work with MySQL into PostgreSQL. Problems that had been expected and ones that were actually faced. Tricky bugs that were found in production servers after the migration. Solutions that were used to deal with problems. PostgreSQL features that were very useful and desirable ones that were missing.

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»

This talk explores the ways attackers with no authorized database access can steal Postgres passwords, see database queries and results, and even intercept database sessions and return false data. Postgres supports features to eliminate all of these threats, but administrators must understand the attack vulnerabilities to protect against them. This talk covers all known Postgres external attack methods.

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.