How we keep Azure Database for PostgreSQL free of bloat to maximize disk space | Citus Con 2023

Video recording of a conference talk about Postgres table bloat given at Citus Con: An Event for Postgres 2023, by Bob Wuisman and Eleni Siampali of Ebiquity. The team at Ebiquity was facing an issue with bloat not being removed effectively, resulting in poor database and server performance. Now they automatically updates the autovacuum settings every week on their Azure Database for PostgreSQL flexible servers. Each week Bob and Eleni add new client database servers to our Azure Database for PostgreSQL Flexible Server subscription. Each database is different in size and activity. Some are less than 300GB with daily insert, update, and delete activities; some are more than 10 GB with little frequent changes; and then we have everything in between. This changes per table in the databases as well. Bob’s and Eleni’s automation of autovacuum has improved and stabilized Postgres query performance significantly—and has saved consistently more than one TB of server disk space. The weekly automatic updates to the following autovacuum parameters, based on segmenting the database tables in different clusters: ■ Autovacuum_vacuum_cost_limit (increase) ■ Autovacuum_vacuum_cost_delay (reduce) They discuss the 7 steps of their workflow: 1. Design the solution 2. Collect database statistics 3. Segment the database tables 4. Determine the autovacuuming factors 5. Automatically update the factors on each table 6. Return diskspace back to the server 7. Analyze results and adjust where needed Bob Wuisman has successfully built business intelligence environments in various businesses. With a holistic vision and process driven mindset, Bob thrives on building teams and sustainably growing data driven operations.. Eleni Siampali is a Data Engineer who has years of experience in automating data processes and running data science projects. She thrives in solving complex projects and trying out new applications by combining and integrating different technologies. At Ebiquity, Eleni has automated many processes and enabled automated query tasks across multiple Postgres databases. ► Video bookmarks: ⏩ 00:00 Introducing Bob and Eleni ⏩ 01:00 Talk begins: Keeping Azure Database for PostgreSQL free of bloat ⏩ 02:31 About Ebiquity ⏩ 03:12 Challenges: scope change, deletes, inserts, and bloat ⏩ 04:57 Client workload types ⏩ 06:21 Ineffective autovacuum settings cause massive bloat ⏩ 08:13 Structural performance improvements ⏩ 09:25 Design principles: automation, monitoring, and vacuum ⏩ 12:06 End-to-end pipeline in Airflow (DAG) ⏩ 16:10 Database table segmentation ⏩ 18:52 Bloat & vacuum learnings ⏩ 21:39 Q&A ✅ Learn more: Watch more Citus Con talks: https://aka.ms/cituscon-playlist 📕 Everything you need to know about Citus Con: An Event for Postgres can be found at: https://aka.ms/cituscon 📌 Let’s connect:   Twitter – @CitusCon,   / cituscon   #CitusCon #PostgreSQL #Database