PGConf.Russia 2018

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.

PostgreSQL looks very competitive with other mainstream databases on OLTP workload (execution of large number of simple queries). But on OLAP queries, requiring processing of larger volumes of data, DBMS-es oriented on analytic queries processing can provide an order of magnitude better speed. The following factors limit Postgres OLAP performance:

• Unpacking tuple overhead (tuple_deform)
• Interpretation overhead (Postgres executor has to interpret query execution plan)
• Abstraction penalty (support of abstract data types)
• Pull model overhead (operators are pulling tuples from heap page one-by-one, resulting numerous repeated accesses to the page)
• MVCC overhead (extra per-tuple storage + visibility check cost)

All this issues can be solved using vectorized executor, which proceed bulk of values at once. In this presentation I will show how vector operations can be implemented in Postgres as standard Postgres extension, not affecting Postgres core. The approach is based on introducing special types: tile types, which can be used instead of normal (scalar) types and implement vector operations. Postgres extension mechanism, such as UDT (user-defined type), FDW (foreign data wrappers), executor hooks are used to let users work with vectorized tables almost in the same way as with normal tables. But more than 10 times faster because of vector operations.

If you honestly evaluate most of our business applications, you will see that they first collect and import the data into a database and then send the same data in the opposite direction.

What if we don't build an ORM wall between the application and the database, but try using the symbiosis of their strong points and special features instead?

I will tell you how we use PostgreSQL and Clojure for building data-intensive medical applications. We will cover the following topics:

• functional relational programming
• jsonb for modeling complex data domains
• functional indexes and json-knife extension for jsonb search
• graphql implementation on PostgreSQL
• logical replication for building reactive integrations
• asynchronous JDBC-free connector to PostgreSQL on netty

Transition tables, a new feature in PostgreSQL 10, offer broad new capabilities including new ways to maintain materialized views. At the end of this talk, you will have seen new ways to use this feature and have it in your tool chest for the future.