PgConf.Russia 2017

I love complex knowledge domains, strong typing in applications, and 3NF, but I hate ORM. That is why I’ve been actively storing serialized structures in JSON fields (even before the JSON type was introduced). In this talk, I will tell you about some specifics of storing complex structures within DBMS fields, what problems can arise, and how to cushion the blow.

VIDEO

1. 1C:Enterprise 8 and PostgreSQL 9 interoperability 1.1 Changes in new 1C platform versions 1.2 v81c_data and v81c_index schemas 1.3 Sending 1C queries to SQL 1.4 Using 1C technological log events for PostgreSQL diagnostics
2. Analyzing queries that affect PostgreSQL performance 2.1 A free tool for automating log parsing 2.2 Pareto principle in action 2.3 Installation and configuration of the tool 2.4 A case study of query optimization 2.4.1 An issue in a PostgreSQL query 2.4.2 Finding non-optimal operations in a query 2.4.3 Resolving inefficiencies
3. PostgreSQL statistics for performance diagnostics 3.1 Comparing Postgres with MS SQL Server 3.2 Troubleshooting locks 3.3 Operating load diagnostics 4 Case studies by the gilev.ru team

Sets are apparently a useful data type for many kinds of applications. While PostgreSQL offers no built-in set data type, sets may be emulated to some degree with its built-in array and JSONB data types. Also, acceleration of respective containment (subset) queries is readily available as a built-in feature of the GIN index type.

Starting with the above, we will then explore the performance gains enabled by custom set data types, and especially by customisation code in C ("operator classes") for the GIN and GiST index types.

MongoDB is a successful database in the NoSQL space, mostly used for OLTP-type workloads. However, due to the lack of ACID (transactions in particular) and significant performance issues with OLAP/DW workloads, more and more MongoDB users are considering migrating off of MongoDB to a RDBMS, where PostgreSQL is the usual choice. This represents a significant opportunity for the PostgreSQL ecosystem, to "bring NoSQL to SQL". This talk will present the challenges that MongoDB users are facing and the state of the art of the available tools and open source solutions available to perform ETL and live migrations to PostgreSQL. In particular, ToroDB Stampede will be discussed, an open source solution that replicates live from MongoDB, transform JSON documents into relational tables, and stores the data in PostgreSQL.

VIDEO