PgConf.Russia 2017

Streaming replication has been introduced in 2010 and quickly became one of the most popular features of PostgreSQL. Today, it is hard to imagine PostgreSQL installation without streaming replication. With its stability, high efficiency and ease of configuration one would have thought it is an optimal feature. However, while using it you might sometimes enter murky waters. This often can be resolved by using a combination of built-in and third party troubleshooting tools. In my talk I will provide an overview of these tools and explain how with their help one can diagnose, understand and eliminate problems related to streaming replication. I will also go through the most frequent issues occurring when streaming replication is used and will propose possible solutions. This talk is primarily aimed at DBAs and system administrators who use PostgreSQL in their day-to-day.

VIDEO

Real-word data often need cryptographycal protection. The presentation describes typical problems that can be solved using cryptographycal methods and the right ways to use cryptography with RDBMS. The newest solutions suggested for usage with PostgreSQL are described.

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.

< Query failed: ERROR: deadlock detected
< DETAIL: Process 17371 waits for ShareLock on transaction 102733872; Blocked by process.
< Process 10414 waits for ShareLock on transaction 102733874; Blocked by process 17371.

Such "unpleasant" messages from the server can seriously puzzle the developer. When working with locks, in particular, with transactions in general, it is necessary to take into account the features of the implementation of client libraries, which can cause the above exception.

In the short talk, the mechanics of the interaction of locks will be explained, main attention being paid to causes of deadlocks. References to the relevant documentation pages will be given. A technique of "bypassing" this problem of concurrent data access will be described and illustrated with some generalized examples from practice are shown. The talk will be interesting to database developers and administrators as well as the client-side application developers.