Schedule - PGConf.EU 2022

Understanding Postgres HOT Updates plus using Prometheus and Grafana to track and tune issues

Date: 2022-10-26
Time: 14:00–14:50
Room: Zurich
Level: Intermediate

At Adyen we have invested heavily and built upon the Postgres database engine, generally this has worked very well for us.

Occasionally Postgres Write Amplification can be technical challenge at Adyen, fortunately this does not happen often, unfortunately when it does happen it is around critical, high volume workloads.

So we have been working on using Prometheus and Grafana to track and tune issues around Write Amplification, which can normally be relatively easily addressed by tuning FillFactor and/or tweaking application design.

As well as the tooling (Prometheus and Grafana based dashboards), we have developed "awareness training" for our Developer teams, to ensure we proactively tackle any Write Amplification issues.

This presentation take about 45 mins (plus 5-10 mins for Q&A) and is in two 20 minute parts * We start with technical background, covering why Postgres Write Amplification can happen, the sorts of workloads which are likely to hit this sort of issue and how we can sometimes mitigate this by tuning FillFactor and/or tweaking application design. * Next we talk about monitoring, specifically how to proactively monitor for this, starting with the Postgres standard metrics pg_stat_user_tables and then using the Prometheus Postgres exporter and Grafana to track key long term trends

The aim of the talk is to share best practice and lessons learnt for managing Postgres from Adyen. We want to share this with the broader Postgres community, as we think our DBAs will find this useful and also we're looking for feedback and further possible enhancements.

This a joint presentation * derk.vanveen@adyen.com - covers the technical background * dave.pitts@adyen.com - covers the monitoring via Prometheus and Grafana

Finally in terms of coverage, this talk relates to 5 of the topics

• PostgreSQL internals hacking << The 1st half of the presentation dives into Postgres internals
• Tools and utilities << The 2nd half of the presentation is about using open-source monitoring tools (Prometheus and Grafana) to proactively and iteratively monitor and tune Postgres.
• Tuning and performance improvements << better tuning FillFactor and awareness of how HOT Updates work, has been a major win for us in 2022, we are pushing Postgres to it limits and beyond!
• Administering large scale PostgreSQL installations << tables with a billions of UPDATEs per day?
• Case studies and/or success stories of PostgreSQL deployments (or interesting failures) << vacuum was really struggling to keep up, we have massively cut the vacuum costs through tuning fillfactor and/or tweaking application design

Speaker