Optimizing Postgres for write heavy workloads ft. Checkpoint and WAL configs | Citus Con 2023

In this video of Samay Sharma’s talk at Citus Con: An Event for Postgres 2023, learn about what checkpoints are in Postgres, when they are triggered, and how they impact database performance. This talk covers the configs that impact checkpoints and WAL generation, how to set them, and how to monitor/adapt the configs as your Postgres workload changes.

As you ramp up the write load on your Postgres database, you can benefit from tuning checkpoint and WAL related configs for improved performance. In the last decade, Samay has worked with dozens of customers to recommend optimizations for Postgres parameters like max_wal_size, checkpoint_timeout and checkpoint_completion_target. However, what most of the Postgres customers found most useful is to understand the concepts so they can reason on their own on how to tune these for their workload—and to understand how other parameters such as full_page_writes can influence checkpoints and WAL generation.

Samay Sharma is a principal engineering manager in the PostgreSQL team at Microsoft. He has been working with PostgreSQL for almost a decade—at Microsoft and at Citus Data prior to that—as a PG extension developer, Citus solutions engineer, and an ardent fan of PostgreSQL. In recent years Samay has worked directly with many PostgreSQL customers to improve and optimize their database workloads. Samay has a keen interest in making it easier for users to understand PostgreSQL performance.

► Video bookmarks:
⏩ 00:00 Introduction
⏩ 01:41 Making sure Postgres setup is good
⏩ 03:31 Writing performant applications for PG
⏩ 06:07 Optimizing writes in Postgres
⏩ 08:10 Autovacuum, Checkpointer, and Bgwriter
⏩ 20:33 WAL tuning parameters
⏩ 24:04 Tuning Background Writer

✅ Learn more:

📌 Let’s connect:  

More resources:

#CitusCon #PostgreSQL #Database

[devihomes]

lots of useful tips and clearer explanation...thanks

[danydanger]

Very helpful session 'Samay Sharma', really appreciate it

[eugenia]

Thanks for the video! Great explanation, it is a shame there's not so many likes.