title

text

Egor Rogov
Egor Rogov Postgres Professional
14:00 05 February
90 мин

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:

Слайды

Видео

Другие доклады

  • Bruce Momjian
    Bruce Momjian EnterpriseDB
    180 мин

    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»


  • Michael Balayan
    Michael Balayan Acronis
    45 мин

    MVCC in pictures and when long transactions create problems

    Many of us know that it is MVCC that provides concurrency access to data in many relational databases that guarantee transactions consistency and isolation. But only deep understanding of the implementation of this mechanism in PostgreSQL allows us to better understand the processes in the database, to design the application logic and table structures to be the most effective in a high-load world. We'll take one of the processes in our product to understand how MVCC is implemented in PostgreSQL and we'll explain one of the peculiarity when seemingly unrelated activities can affect each other.

  • Alexander Korotkov
    Alexander Korotkov Postgres Professional
    45 мин

    Credereum – blockchain-enabled Postgres

    Bringing the provability and immutability of blockchain to performance and efficiency of traditional DBMS.

    Blockchain technology has several unique properties including provability and immutability. Every blockchain transaction is signed by its author, and it could be verified by any blockchain network member. Also, once data is stored in blockchain, it can't be altered in the future. Many databases operating traditional DBMS would also benefit from provability and immutability properties. However, inclusion of all the transaction data in the public blockchain is very expensive.

    Credereum is the platform, which allows creation and maintaining of databases, whose contents and history are provable and immutable without sacrifice the performance and efficiency of traditional DBMS. Thanks to Credereum, database owner can prove the validity of query results, while users can verify them. Database owner don't have to reveal the whole database contents or full history of transactions to provide the proof of database query results. Therefore, Credereum database may contain private sensitive information. Credereum utilized bleeding-edge technologies including, but not limited to decentralized cloud, public blockchain with sharding. Credereum is an emerging technology of trusted and private databases.

    We will explain why PostgreSQL is suitable database for Credereum and what we need to develop in Postgres to support signed transactions and cryptographic storage.

  • Andrey Borodin
    Andrey Borodin Яндекс
    45 мин

    Development of delta-backups in WAL-G

    WAL-G is simple and effective disaster recovery tool for PostgreSQL using cloud storages. In its core functionality, WAL-G is the successor of WAL-E rewritten in Go. But there is one new neat feature - delate-backups. WAL-G delta-backups, whenever possible, stores only pages, changed since the previous backup. In this talk, I'm going to describe development process of this feature.

    Surprisingly, most important and complicated question was the design of the interface: WAL-e is simple and comprehensive, keeping these properties was goal #1. Technical details of implementation were covering some underwater stones too. Besides these, I want to discuss the perspective of technological development and future coordination of recovery tools developers.