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

Experience we've got after 5 years of developing, deploying and improving BI system http://colibri365.ru used in government. I would talk about government IT reality and our way over it. Postgres performance improvements, using of latest features, overwriting of user generated queries to help query optimizer and other tweaks and hacks to tackle limited hardware problems. These lead us to number of computer science papers and (now committed) patches to Postgres (see Andrey Borodin talks for details).

VIDEO

Citus allows you to distribute postgres tables across many servers. It extends postgres to transparently delegate or parallelise work across a set of worker nodes, enabling you to scale out the CPU and memory available for queries.

One year ago, we began a long journey to allow Citus to scale out another dimension: write throughput. With writes being routed through a single postgres node, write throughput in Citus was ultimately bottlenecked on the CPUs of a single node. Citus MX is a new edition of Citus which allows distributed tables to be used from from any of the nodes, enabling NoSQL-like write-scalability.

Input-output (IO) performance issues have been on DBAs’ agenda since the beginning of databases. The volume of data grows rapidly and time is of an essence when one needs to get necessary data fast from the disk and, more importantly, to the disk.

For most databases it is relatively easy to find checklist of recommended Linux settings to maximize IO throughput and, in most cases, this checklist is indeed good enough. It is however essential always to understand how the optimisation of those settings actually works, especially, if you run into corner cases.