PgConf.Russia 2020

Will be demonstrated in the master-class the following: 1). The benefits of declarative programming. Rapid creation of complex LUI-forms using only SQL. 2). Support for long transactions and automatic locks. An example of a wizard with a record directly in the database. 3). The multilingual fields and columns. Rapid inclusion of multilingualism in existing forms. 4). Control the coloring of data in fields and columns of the form. Typical examples. 5). Live search in input fields using the example of address input. 6). How to configure the LUI to enter and display of application-specific data. 7). Design form Master Detail with retrieving data from two different databases. 8). How end-user builds of complex data queries without using SQL.

In this tutorial, we will solve several programming problems using JSON functionality that extends SQL capabilities and virtually turns a relational database into an almost ready-to-use application server. :)
We will learn how to use aggregates, both standard and custom, as well as review possible benefits of JSON recursive structure.

I'll show an example of solving the problem of searching "similar" texts for one given text in big array using GiST index. The problem itself is not much important, but it is easy to understand. Using this problem as example, I'll show one of many methods of adapting GiST index for custom search problems. Maybe this talk will help you to solve other search problems.

Locking is critical for providing high concurrency for any database — you cannot fully utilize your hardware if locking is throttling its use. This talk explores all aspects of locking in Postgres by showing queries and their locks; covered lock types include row, table, shared, exclusive, and advisory lock types. The high concurrency provided by Multiversion Concurrency Control (MVCC) is also covered.

Slides are at https://momjian.us/main/writings/pgsql/locking.pdf