PGCon2019 - 3.4

PGCon 2019
The PostgreSQL Conference

Adam Wołk
Mariusz Zaborski
Day Talks - Day 2 - 2019-05-31
Room DMS 1120
Start time 10:00
Duration 00:45
ID 1308
Event type Lecture
Track Case Studies
Language used for presentation English

Let's (D)Trace Postgres

tracing the madness

There are situations where an existing database mechanism doesn't provide sufficient methods to determine the exact work being performed.

An example of such situation is GIN index rebuilds as a result of the fastupdate mechanism and slow computation of query plans.

Due to wrongly selected systems timers. With the help of DTrace, we will take a look on the Postgres internals and we show how a system administrator can clearly identify the root cause of a hanging insert and the slow computation of the query plans.

DTrace is a dynamic tracing framework created by Sun Microsystem and available in FreeBSD, NetBSD, and MacOS. Dtrace is a powerful tool which allows software developers and administrators measure performance and trace their applications using advanced scripting language - D.

Before entering the beautiful world of Postgres during this talk we will discuses the basics of it.

Gin in Posrgres stands for Generalized Inverted Index and should be considered as a genie, not a drink. Generalized means that the index does not know which operation it accelerates.

Having to do multiple searches/insertions when the same key appears in multiple heaps becomes costly during bulk insertions hence a fast update feature has been developed that maintains a pending list of entries delaying the index rebuilds in time.

Usually, the pending list is merged with the main btree by the vacuum process but it can also happen in other situations. Currently, the PostgreSQL database lacks a mechanism allowing a DBA to know when exactly this situation occurs and how long it took. This can lead to seemingly hanging INSERT and a DBA scratching his head not knowing what causes the stall.

Using DTrace we can show exactly when this rebuild happens, how long it took and how a DBA can script this in D expanding his toolbox for the future - all without rebuilding the database.