Egor Rogov

Postgres Professional

Expert

Tutorial: More indexes, good and various

"And telling GIN from SP-GIST was quite beyond his wit, we found", said the classic. Can you? This masterclass is about not-so-often used index types (compared to conventional B-tree) which however can do a great job for you. We will look into internal mechanics of these indexes and discuss cases where they can be successfully applied. Also we will talk about some peculiarities of PostgreSQL index access. To spend time efficiently, listeners are required to have basic knowledge of PostgreSQL and should be used to read plans of simple queries.

Materials of the master class

Backup copy of the database with demo data can be downloaded here:

Slides

Gregory Smolkin

Postgres Professional

Maintenance Engineer

Pg_probackup backup tool for PostgreSQL: high performance and severe paranoia

Ivan Panchenko

Postgres Professional

Deputy Director General

Server side programming in PL/Perl, PL/Python, PL/v8

Tutorial will show specifics of server programming in these languages. I will present practical examples and compare the features of these languages in PostgreSQL environment from the viewpoint of solving practical tasks.

Slides

Ivan Panchenko

Postgres Professional

Deputy Director General

Full text search from A to Ω

Tutorial on Full Text Seach in PostgreSQL, containing all recent improvemets. All recipies necessary for building an application will be given: dictionary and parser configuration, faceted search, fuzzy search, multilanguage search, ranking etc. Participants will be provided with a test database for exercises.

Slides

Olivier Courtin

DataPink

Owner & DataScientist

Tutorial: Advanced spatial analysis with PostgreSQL, PostGIS and Python

  • Spatial and advanced spatial analysis with pure PostGIS (including cutting edge PostGIS functions available)
  • How could we mix and tied efficiently PostgreSQL and Python data types (as NumPy ndarray, and Pandas DataFrames)
  • Tools to improve our data manipulation environment (Jupyter tricks, easy dataviz...)
  • How to go further throught GeoDataScience, with Python libs and framework tied with PostgreSQL/PostGIS (including Machine and DeepLearning)

Alexey Klyukin

Zalando SE

Database Engineer

Alexander Kukushkin

Zalando SE

Database Engineer

Tutorial: Management of High-Availability PostgreSQL clusters with Patroni

Patroni is a Python application to create high-availability PostgreSQL clusters based on the streaming replication. It is used by Red Hat, IBM Compose, Zalando and many other companies. This tutorial will highlight Patroni architecture, provide attendees with hands-on experience of configuring high-availability PostgreSQL clusters with Patroni, describe how to take advantage of numerous additional features and give an opportunity to learn more about common mistakes related to running Patroni and its troubleshooting.

In order to take most out of the Patroni tutorial one needs a laptop with git, vagrant and virtual box installed.

Vagrant can be obtained from https://www.vagrantup.com Virtualbox is at https://www.vagrantup.com

Alternatively, one can install your Linux distribution packages (or use homebrew on Mac).

Once Vagrant and Virtualbox are installed one can run the Patroni VM by issuing the following commands:

$ git clone https://github.com/alexeyklyukin/patroni-training
$ cd patroni-training
$ vagrant up

When the setup concludes Patroni box can be accessed via ssh using vagrant ssh command.

Ivan Frolkov

Postgres Professional

consulting engineer

pgpro_scheduler and cryptocurrency transactions

Apart from its main purpose of scheduling tasks, pgpro_scheduler can also deal with chained transactions. It can be used in various scenarios of asynchronous data processing.

This tutorial demonstrates pgpro_scheduler features that ensure secure processing of chained transactions. We'll be using cryptocurrency transactions as an example.

pgpro_scheduler is included into Postgres Pro Enterprise as an extension.

Slides

Bruce Momjian

EnterpriseDB

Senior Database Architect

Exploring Common Table Expressions and Window Functions

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»


Slides

Slides