Episode
Optimizing Postgres for write heavy workloads ft. Checkpoint and WAL configs | Citus Con 2023
with Samay Sharma
Video of a conference talk from Citus Con: An Event for Postgres 2023 about what checkpoints are, when they are triggered and how they impact database performance. We’ll also go over the most important configs which impact checkpoints and WAL generation, recommend how to set them and how to monitor and adapt them as your workload changes.
As you ramp up the write load on your Postgres database, you will often benefit from tuning checkpoint and WAL related configs for better performance. In the last decade, I’ve personally recommended adjusting parameters like max_wal_size, checkpoint_timeout and checkpoint_completion_target to dozens of customers. However, what they’ve found more useful is understanding the concepts so that they can reason on their own on how to tune these for their workload and also understand how other parameters (eg. full_page_writes) 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 an extension developer, solutions engineer, and an ardent fan of PostgreSQL. Over the last few years, he has been working directly with PostgreSQL customers to improve and optimize their databases. He has a keen interest in making it easier for users to understand PostgreSQL performance.
Recommended resources
- Watch more Citus Con 2023 talks
- Everything you need to know about Citus Con: An Event for Postgres 2023
- Star the GitHub repo for the Citus database extension, to show your support of Citus open source
Connect
- Citus Con An Event for Postgres | Twitter: @CitusCon
Video of a conference talk from Citus Con: An Event for Postgres 2023 about what checkpoints are, when they are triggered and how they impact database performance. We’ll also go over the most important configs which impact checkpoints and WAL generation, recommend how to set them and how to monitor and adapt them as your workload changes.
As you ramp up the write load on your Postgres database, you will often benefit from tuning checkpoint and WAL related configs for better performance. In the last decade, I’ve personally recommended adjusting parameters like max_wal_size, checkpoint_timeout and checkpoint_completion_target to dozens of customers. However, what they’ve found more useful is understanding the concepts so that they can reason on their own on how to tune these for their workload and also understand how other parameters (eg. full_page_writes) 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 an extension developer, solutions engineer, and an ardent fan of PostgreSQL. Over the last few years, he has been working directly with PostgreSQL customers to improve and optimize their databases. He has a keen interest in making it easier for users to understand PostgreSQL performance.
Recommended resources
- Watch more Citus Con 2023 talks
- Everything you need to know about Citus Con: An Event for Postgres 2023
- Star the GitHub repo for the Citus database extension, to show your support of Citus open source
Connect
- Citus Con An Event for Postgres | Twitter: @CitusCon
Have feedback? Submit an issue here.