"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:
- Recovery with pg_restore (338 MB)
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.
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.
- 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)
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.
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.
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.
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.