PgConf.Russia 2019

PostgreSQL provides possibility to create temporary tables. Though a temporary table is accessible only to a single session and is removed at the end of the session, all information about it is stored in the system catalogs of PostgreSQL. This is related to several issues, which make it difficult or impossible to use temporary tables in some cases. There are attempts to solve this feature, including in our company. But they have not yet succeeded, mainly because of the PostgreSQL engine. In the talk I want to tell about simple and small pg_variables extension. It allows you to create table variables along with scalar ones. I will tell how it can replace temporary tables, what advantages and disadvantages it has.

Brendan Gregg’s USE (Utilization, Saturation, Errors) method for monitoring is quite known. There’s also Tom Wilkie’s RED (Rate, Errors, Durations) method, which is suggested to be better suited to monitor services than USE. I want to talk about how we employ these methodologies when we develop our Postgres monitoring in okmeter.io.

JSONB in PostgreSQL has interesting properties for design and development of business systems with heavy domain, helping to fight against complexity and variability. We will discuss the trade-offs of the JSONB approach. Using an open source project for Health IT - fhirbase we will:

• load synthetic medical data into PostgreSQL
• learn how to search and index this data (gin, jsquery, json-knife)
• use JSON aggregation to build complex queries (GraphQL)
• see how this data can be modified and validated
• discuss architecture consequences of JSONB usage

Technical requirements:

• docker
• docker-compose

Detailed prerequisites

Wide usage of Common Table Expression queries considered as a core paradigm for implementing the Business Logic for high loaded web applications development based on PostgreSQL functions.