VACUUM Simulatorを使って VACUUMをもっと理解しよう Exploring Postgres VACUUM with the VACUUM Simulator PostgreSQL Conference Japan 2023 November 24, 2023 Keiko Oda - pganalyze

● 織田敬子 (Keiko Oda) ● Product Engineer at pganalyze ● Using Postgres ~10 years (with 4 years gap) ● 金沢市在住 Kanazawa, Ishikawa Speaker introduction

● スライドは以下からも見れます https://speakerdeck.com/keiko713/va cuum-simulator ● 話したいことがたくさん！なので抽 象的または概要しか話せないことも ○ おまけスライドたくさんついてます ● 日本語がメイン、下に英語のサブタ イトルが付いています Notes Learn More Link もっと深く学べるコンテンツ へのリンク Link to learn more

Today’s Goal 01 What is VACUUM? 02 Autovacuums 03 Pitfalls Autovacuumのつまずきポイ ントを学ぶ Learn what could go wrong with autovacuums VACUUMとは何か、なぜ必要 なのかを理解する Understand what VACUUM is and why it’s needed Autovacuumはどのタイミン グで起きるのかを理解する Understand what triggers autovacuums 04 Tips 今日から使えるautovacuum まわりの小技を学ぶ Learn tips related autovacuum that you can use from today

What is VACUUM? VACUUMとは何か、なぜ 必要なのかを理解する Understand what VACUUM is and why it’s needed 01

What is VACUUM? ● VACUUMは、Postgresのアーキテクチャ (MVCC) によっ て生ずるさまざまな「ゴミ」を再利用可能にしてくれ る VACUUM makes all kinds of “garbage” generated by Postgres due to its architecture (MVCC) reusable ● MVCC (Multiversion Concurrency Control) ○ トランザクションの分離を行うことで、たくさんの読み 手と書き手がいてもお互いをブロックしない By providing transaction isolation, readers never block writers, and writers never block readers ○ Readers never block writers, and writers never block readers Learn More: 2-4, 2-5

MVCC in 3 minutes 読み手と書き手は一つのデータをみんなで参照するのではな く、それぞれデータのスナップショットを元にクエリを走ら せる Readers and writers are not referring the single source of data, but running queries with a snapshot of the data INSERT SELECT SELECT もし一つのデータをみんなで参照していたら… If everyone is looking at the same data… Learn More: 2-4, 2-5

スナップショットはトランザクションごとに発行されるメガ ネのようなもの Snapshots are like a pair of glasses, issued per transaction MVCC in 3 minutes INSERT SELECT メガネによって同一のデータを見ても見え方が変わる Different pairs of glasses (snapshots) can show different views of the same scene (database) Learn More: 2-4, 2-5 UPDATE snapshot

MVCC in 3 minutes 行が削除されても、他のスナップショットによってまだ参照 されている可能性があるため、物理的に削除できない Even if the row is deleted, it can’t be deleted physically as it could be referred by some snapshot Learn More: 2-4, 2-5 SELECT 🍎🍊 DELETE 🍎 🍎🍊 SELECT 🍎🍊 🍎🍊 Can't be deleted physically and only marked as deleted この物理的に削除されないデータが「ゴミ」となる These data that cannot be deleted physically becomes “garbage”

What is VACUUM? ● ゴミの種類 Kinds of garbage ○ 削除された行（ゴミタプル） Dead rows/tuples ○ 古いトランザクションID Old Transaction IDs ● VACUUMのその他の役割 Other roles of VACUUM ○ VACUUM ANALYZEで統計情報の更新 Update data statistics with VACUUM ANALYZE ○ Index-only scanで使うVisibility Mapの更新 Update the visibility map used by index-only scans Learn More: Visibility Map

What is VACUUM? ● ゴミが放置されると… When garbage is left uncleared ○ 不要なデータが残り続けてクエリの際に余計なI/O をしなければならず、パフォーマンスが低下する Unremoved dead data remains in the database, requiring additional I/O during queries, which results in performance degradation ○ トランザクションIDが枯渇して新規に発行できず アプリケーション障害に繋がる（トランザクショ ンID周回） There will be no new transaction ID to issue, resulting in the application down (transaction ID wraparound failures) ● これらを防ぐためにもVACUUMは大切！ It is critical to run VACUUM to prevent these issues from happening!

What is VACUUM? ● ゴミの種類 Kinds of garbage ○ 削除された行（ゴミタプル） Dead rows/tuples ○ 古いトランザクションID Old Transaction IDs ● VACUUMのその他の役割 Other roles of VACUUM ○ VACUUM ANALYZEで統計情報の更新 Update data statistics with VACUUM ANALYZE ○ Index-only scanで使うVisibility Mapの更新 Update the visibility map used by index-only scans

Dead rows ● 行が削除されるとき、Postgresでは行を物理的に削除 せず、行が削除されたとマークされる When rows are deleted, Postgres doesn’t delete them but marks them as deleted UPDATEも 削除と挿入 UPDATE is also “DELETE” and “INSERT”

Dead rows ● このように削除された行をdead rows（不要行）、また はdead tuples（不要タプル・ゴミタプル）という These deleted rows are called “dead rows” or “dead tuples” ○ 本スライドでは今後dead rowsまたはゴミタプルと呼ぶ In this slide, we will use “dead rows” moving forward ○ Postgresのドキュメントや統計ビューなどではdead row versionsや dead tuplesと呼ばれている In Postgres documentation or system column, it’s often called dead row versions or dead tuples dead row ゴミタプル

Dead rows ● VACUUMはこれらのゴミタプルを再利用可能にしてくれ る VACUUM makes these dead rows reusable ○ 再利用可能となったスペースはOSには返されずに次回の INSERTやUPDATEに使用される Reusable spaces won’t be returned to the OS and will be used for the future UPDATEs and INSERTs (on the same table) dead row ゴミタプル テーブル Table ヒープページ Heap page 行 Row

Dead rows ● VACUUMが走ったからといってテーブルサイズが減るわ けではない Running VACUUM doesn’t mean that the table size will decrease ○ pg_repackを使用すると実際に減らすことが可能 Using pg_repack will actually reduce the size of the table ○ VACUUM FULLも同様のことをするが、非常に強いロックを要する ため実用使用向けではない VACUUM FULL also does the same, though it requires a heavyweight lock and can’t be used in the most cases

What is VACUUM? ● ゴミの種類 Kinds of garbage ○ 削除された行（ゴミタプル） Dead rows/tuples ○ 古いトランザクションID Old Transaction IDs ● VACUUMのその他の役割 Other roles of VACUUM ○ VACUUM ANALYZEで統計情報の更新 Update data statistics with VACUUM ANALYZE ○ Index-only scanで使うVisibility Mapの更新 Update the visibility map used by index-only scans

Old Transaction IDs ● PostgresではトランザクションIDは有限で再利用され ている (32-bit ≒ 40億) Transaction ID in Postgres is finite and old ones need to be reused (32-bit ≈ 4 billion) ● 40億のうち半数の20億は過去のトランザクションID、残 りの半数は未来のトランザクションIDとしている Among this 4 billion, the half of them are assigned as the past transaction IDs, and the other half are assigned as the future transaction IDs ① ② ③ ④ future past ① Future (invisible) XID: 1234 Age: -1234 ② Current XID: 1234 Age: 0 ③ Old XID: 1234 Age: 5000 ④ To be reused XID: 1234 Age: 2B ① Future (invisible) XID: 1234 Age: -1234

Learn More: 2-3 Old Transaction IDs ● 行ごとに行に関するメタデータがあり、その行が作ら れたトランザクションID (xmin) が書かれている Every row contains some associated metadata, including information about the transaction ID of the transaction that created it (xmin) ● VACUUMは行を走査し、凍結可能な行のメタデータに凍 結フラグをたてる VACUUM will go through rows and update a row metadata to note that the row has been frozen

Old Transaction IDs ● 凍結されることにより、テーブル毎に使用している最 古のトランザクションIDがより新しいものとなる By freezing rows, the oldest transaction ID used by each table will be updated to “newer” transaction IDs ○ 例では、凍結前は1212が最古のトランザクションIDであっ たものが、凍結後は1717になっている In the example, the oldest transaction ID before freezing was 1212, but it became 1717 after the freezing Learn More: 2-3 凍結されていない最古の トランザクションID the oldest unfrozen transaction ID 凍結されていない最古の トランザクションID the oldest unfrozen transaction ID

● トランザクションIDのage（齢）は現トランザクションIDとの 差で表される The age of the transaction ID is calculated by the difference from the current transaction ID ● Example: the current transaction ID is 5,000 ○ Before freezing: the oldest unfrozen TXID 1212, age 3,788 ○ After freezing: the oldest unfrozen TXID 1717, age 3,283 凍結されていない最古の トランザクションID the oldest unfrozen transaction ID 凍結されていない最古の トランザクションID the oldest unfrozen transaction ID Old Transaction IDs - Age

Old Transaction IDs ● VACUUMはこうして古いトランザクションIDを凍結に よって開放することにより再利用可能にしてくれる VACUUM will release the old Transaction IDs and make them reusable for the future transactions by freezing them ● 何もしていないテーブルにも凍結(=VACUUM)は必要 Tables without any activities still require freezing ○ 何もしていなくても、テーブル最古のトランザクション IDは現在のトランザクションIDと比べて相対的に古くなっ ていき凍結が必要となる Even nothing is done with the table, the oldest transaction ID of the table is getting older relatively compared to the current transaction ID, and requires freezing

VACUUM Phases ● VACUUMはいくつかのフェーズに分かれている VACUUM has several phases ○ pg_stat_progress_vacuumビューで今走ってい るVACUUMのフェーズと進捗を見ることができる You can check the progress of VACUUM using a pg_stat_progress_vacuum view

VACUUM Phases - terminology 用語 テーブル・ヒープ Table/Heap ヒープページ Heap page 行・タプル Row/Tuple ● Heap: テーブルのこと。特に、インデックスなどではな くテーブルの実際のデータ部分を指す Heap refers to the table itself, where the actual data rows are stored

VACUUM Phases - example ● Flowers table: DELETEとUPDATEによってdead rowsが2 つある状態 Flowers table: 2 dead rows due to DELETE and UPDATE operations ● Primary keyにインデックスflowers_pkeyがあり、イ ンデックスにもゴミがたまっている Primary key index flowers_pkey also contains some garbage

● Scanning heapフェーズでは テーブルを走査してdead rows を見つける During this phase, VACUUM goes through the table to find dead rows ● 見つけたdead rowsはVACUUM TODOリストに入れておく Put found dead rows into the VACUUM TODO list ● 凍結する場合はこのフェーズで 行う Freezing is done during this phase VACUUM Phases - scanning heap

● Scanning heapフェーズで作っ たVACUUM TODOリストを元に インデックスを掃除する Clean up indexes based on the VACUUM TODO list made in the previous scanning heap phase ● 一番時間のかかるフェーズ The most time consuming phase ● インデックスが複数あるときは すべてをひとつずつ掃除する When there are multiple indexes, VACUUM needs to clean up indexes one by one ○ [Postgres 13+] 複数のインデッ クスを並列に掃除可能 [Postgres 13+] Vacuuming indexes can be run parallel VACUUM Phases - vacuuming indexes

● Scanning heapフェーズで作っ たVACUUM TODOリストを元に ヒープを掃除する Clean up dead rows based on the VACUUM TODO list made in the previous scanning heap phase ● インデックスによって参照され ていないことを保証しなければ ならないため、必ずvacuuming indexesフェーズの後に来る This phase must run after the vacuuming indexes phase since heap blocks to be cleaned are needed to be guaranteed not being referred by any indexes VACUUM Phases - vacuuming heap

VACUUM Phases ● VACUUM TODOリストの大きさは決まっている The size of VACUUM TODO list is limited ○ デフォルトではautovacuum_work_mem (maintenance_work_mem)の64MBで、最大は1GB The default size is 64MB from autovacuum_work_mem (maintenance_work_mem), then it can be the maximum 1GB ○ もし最初のscanning heapフェーズでヒープページ を全部走査する前に埋まってしまった場合は、全部 走査ができるまで三つのステップを繰り返す If it’s filled during the first scanning heap phase before scanning all heap pages, these 3 phases will be repeated until all heap pages are scanned

VACUUM Phases - 小ネタ ● [Postgres 12+] VACUUMには一番時間のかかるフェー ズ、vacuuming indexesを飛ばすオプション INDEX_CLEANUP offがある [Postgres 12+] You can use INDEX_CLEANUP off to skip the most time consuming phase, the vacuuming indexes phase ● でも…vacuuming indexesを飛ばすとvacuuming heap が出来ないのでは？ Well… if you skip the vacuuming indexes phase, you can’t really run the vacuuming heap page because it’s depending on it? ○ ゴミタプル自体（Item）の掃除はできるが、ヒープに ItemIDのゴミが残る You can clean up most of the heap, but some uncleaned bits will remain ○ 詳しくはINDEX_CLEANUP offの副作用を参照 Check out the article “the side effect of INDEX_CLEANUP off” by Masahiko Sawada for the detail

Autovacuums Autovacuumは どのタイミングで起きるのか を理解する Understand what triggers autovacuums 02

● パラメータに応じてPostgresが自動でVACUUMを走らせ てくれる Postgres will automatically run VACUUM based on a set of predefined parameters Autovacuum launcher worker1 worker2 worker3 VACUUMが必要か チェック Needs VACUUM? I’m VACUUMing table2! I’m VACUUMing table1! worker3さん、VACUUM お願いします！ Please run VACUUM with table3, worker3! は〜い

● パラメータは、サーバーレベルのものをデフォルトと して、テーブル毎にも上書きできる There are server-level default parameters, which can be overwritten by the table-level settings, allowing you to define unique settings for each table ● テーブル毎のチューニングも非常に有効 It is effective to tune parameters per table as each table has different needs Autovacuum launcher Table3のdead tuplesが 閾値を超えている、 VACUUMしないと！ Table 3 is exceeding the threshold, need to VACUUM!

What triggers autovacuums? ● Autovacuumのトリガー要因（閾値） What triggers autovacuums? (thresholds) ○ Dead rows ■ どれだけのdead rowsがテーブルにあるか How many dead rows in the table ○ Freeze age ■ 最古の凍結されていないトランザクションIDのage （現トランザクションIDとの差）はどれだけか How old is the oldest unfrozen transaction ID of the table ○ Inserts [Postgres 13+] ■ 最後のVACUUMからどれだけの行が挿入されたか How many rows are inserted since the last VACUUM

● Normal ○ Dead rowsまたはinsertsによってトリガーされたもの Triggered by dead rows or inserts ○ 凍結は行われず、dead rowsの掃除またはvisibility map の更新のみが行われる No freezing, only cleaning up dead rows or updating the visibility map ● Aggressive（凍結するぞモード） ○ Anti-wraparound autovacuum（Freeze ageによってトリ ガーされたもの）、またはNormalのうち、freeze ageが vacuum_freeze_table_ageに達したもの Anti-wraparound autovacuums (triggered by freeze age), or those with a freeze age exceeding the vacuum_freeze_table_age among the normal modes ○ 凍結が行われる Perform freezing Autovacuum Modes

デフォルトでは、だいたいテーブルの20%がdead rowsとな るとautovacuumがトリガーされる ● autovacuum_vacuum_threshold: 50 ● autovacuum_vacuum_scale_factor: 0.2 Parameters: triggered by dead rows Threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltuples Dead rows threshold pg_class.reltuples: テーブルの行数 (number of rows of the table)

デフォルトでは、テーブルごとに凍結されていない最古のト ランザクションIDのageが2億を超えるとautovacuumがトリ ガーされる ● autovacuum_freeze_max_age: 200,000,000 ● autovacuum_multixact_freeze_max_age: 400,000,000 Parameters: triggered by freeze age Threshold = autovacuum_freeze_max_age OR autovacuum_multixact_freeze_max_age Freeze age threshold

デフォルトでは最初の1,000行が挿入されるとautovacuumが トリガーされる ● Autovacuum_vacuum_insert_threshold: 1,000 ● Autovacuum_vacuum_insert_scale_factor: 0.2 Parameters: triggered by inserts Threshold = autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor * pg_class.reltuples Inserts threshold pg_class.reltuples: テーブルの行数 (number of rows of the table)

● 過去のテーブルに関する統計情報を元に、autovacuum がどのトリガー要因をもとにいつ発生したかをシミュ レーションしグラフを描画する From the historical statistic of the table, draw simulation graphs of what triggered autovacuums and when ● Autovacuumのトリガー要因に関係するパラメータを調 節することによって、autovacuumの発生タイミングが どのように変わるのかをシミュレーションする Simulate how the timing of autovacuums will change by tweaking the Postgres setting related to autovacuums ● https://vacuum-simulator.netlify.app VACUUM Simulator

● Table: public.issue_references ● UPDATEとDELETEが一日に一度、大量に起こっている。 それ以外は常時UPDATEと少なめのINSERTが起こってい る Lots of UPDATEs and DELETEs are happening once a day. Other than that, there are steady UPDATEs and a little bit low volume of INSERTs ● 現状では、dead rows VACUUMが一日に一度かそれ以上 起こっている With the current configuration, dead rows VACUUMs are happening at least once a day ● Dead rows VACUUMがなくなったらどれほどのdead rowsが発生するのかをみてみよう See how much dead rows will be accumulated when VACUUMs weren’t happening Simulation 1: dead rows VACUUMs

Simulation 2: freeze age VACUUMs ● Table: public.postgres_roles ● DELETEが一日に一度大量に起こっているが、トータル 行数に比べると多くない。それ以外は常時少なめの UPDATEとINSERTが起こっている Lots of DELETEs are happening once a day, though it’s not that much compares to the total rows. Other than that, there are a low volume of UPDATEs and DELETEs ● 現状では、dead rows VACUUMは起こっていなく、 freeze age VACUUMのみ起こっている With the current configuration, there is no dead rows VACUUMs and only freeze age VACUUMs are happening ● どうやったらdead rows VACUUMを起こせるか試してみ よう See how we can trigger VACUUMs by dead rows

● Table: public.schema_table_stats_35d ● パーティションテーブルなのでパーティションの日に しか編集アクティビティがなく、しかも挿入のみ Since this is a partition table, table modifying activities are only happening on the day of the partition, also INSERTs only ● Inserts VACUUMが無くなったらどうなるのか試してみ よう See what happens when there is no inserts VACUUMs Simulation 3: inserts VACUUMs

● Table: public.servers ● トータルの行数に対してUPDATE数が大きい。INSERTと DELETEはほぼ行われていない Compares to the total row count, UPDATEs per minute is quite high. There is almost no INSERTs or DELETEs ● autovacuumの数をシミュレーションで減らしてみよう Let’s try decreasing the number of autovacuums with VACUUM Simulator Simulation 4: too many VACUUMs

Pitfalls Autovacuumの つまずきポイントを学ぶ 　Learn what could go wrong with autovacuums 03

Review: What is VACUUM? - おさらい ● ゴミが放置されると… When garbage is left uncleared ○ 不要なデータが残り続けてクエリの際に余計なI/O をしなければならず、パフォーマンスが低下する Unremoved dead data remains in the database, requiring additional I/O during queries, which results in performance degradation ○ トランザクションIDが枯渇して新規に発行できず アプリケーション障害に繋がる（トランザクショ ンID周回） There will be no new transaction ID to issue, resulting the application down (transaction ID wraparound failures) ● これらを防ぐためにもVACUUMは大切！ It is critical to run VACUUM to prevent these issues from happening!

Meaning… Autovacuumが走ってない Autovacuum is not running ゴミタプルが掃除されない No one is cleaning up dead rows テーブルがゴミで埋まって いく（肥大化・bloat） Tables becomes bloated with dead rows いつものクエリが遅くなる Query performance degrades アプリがダウンする Application goes down トランザクションIDの凍結 がされない No one is freezing older transaction IDs 発行できるトランザクショ ンIDが少なくなってくる Only a few TXIDs are left to be issued データベースがread-only になる Database becomes read-only mode

Meaning… Autovacuumが走ってない Autovacuum is not running ゴミタプルが掃除されない No one is cleaning up dead rows テーブルがゴミで埋まって いく（肥大化・bloat） Tables becomes bloated with dead rows いつものクエリが遅くなる Query performance degrades アプリがダウンする Application goes down トランザクションIDの凍結 がされない No one is freezing older transaction IDs 発行できるトランザクショ ンIDが少なくなってくる Only a few TXIDs are left to be issued データベースがread-only になる Database becomes read-only mode 一度ゴミが大量に溜まると掃除しにくい・しても 使うディスク容量自体は変わらず大きいままで元 のパフォーマンスに戻らない Once a table is bloated, VACUUM will take longer time to finish, and even if it’s VACUUMed, the total table size won’t shrink and the performance kept degraded Learn More: 4-1

Meaning… Autovacuumが走ってない Autovacuum is not running ゴミタプルが掃除されない No one is cleaning up dead rows テーブルがゴミで埋まって いく（肥大化・bloat） Tables becomes bloated with dead rows いつものクエリが遅くなる Query performance degrades アプリがダウンする Application goes down トランザクションIDの凍結 がされない No one is freezing older transaction IDs 発行できるトランザクショ ンIDが少なくなってくる Only a few TXIDs are left to be issued データベースがread-only になる Database becomes read-only mode トランザクションID周回問題によるデータ破 壊を防ぐため、新規トランザクションが必要 なクエリ（挿入・更新等）を許可しない To prevent TXID wraparound from happening, any new queries require a new transaction will raise errors この時点でPostgresが頑張って色々 してくれる Postgres does its best to prevent the next step from happening Learn More: 1-3, 1-4

Running autovacuums is important Autovacuumが走ってない Autovacuum is not running アプリがダウンする Application goes down ● Autovacuumがちゃんと走っていないと問題が起きる Autovacuums not running properly => problems! ● どのようなときにautovacuumがうまく走らないのか？ When and which situation autovacuums stop running properly? ？？？

3 cases of autovacuums go wrong Case 1. Bloat: テーブルが肥大化している The table is bloated Case 2. Performance: Autovacuumの処理時間が長い Autovacuum is taking too much time Case 3. Locks: Autovacuumがブロックしたりブロックさ れたりする Autovacuum is blocking or being blocked

● 閾値の設定がテーブルに合ってい なくautovacuumの頻度が低く、 ゴミが溜まる Autovacuum is running infrequently and dead rows are accumulated due to improper thresholds ● Xmin horizonのせいでゴミタプ ルの掃除がうまくできていない Unable to clean up dead rows due to the xmin horizon ● クエリの実行の際により多くの データを読まなければならなくな りパフォーマンスが低下する Autovacuum is running infrequently ● ゴミのためより多くのI/Oやディ スク容量が必要となる（コスト 増） Increased I/O and/or disk usage due to dead rows (might increase operating costs) Case 1: Bloated - 肥大化 Causes 原因 Consequences 結果

● 閾値の設定がテーブルに合ってい なくautovacuumの頻度が低く、 ゴミが溜まる Autovacuum is running infrequently and dead rows are accumulated due to improper thresholds ● Xmin horizonのせいでゴミタプ ルの掃除がうまくできていない Unable to clean up dead rows due to the xmin horizon ● クエリの実行の際により多くの データを読まなければならなくな りパフォーマンスが低下する Autovacuum is running infrequently ● ゴミのためより多くのI/Oやディ スク容量が必要となる（コスト 増） Increased I/O and/or disk usage due to dead rows (might increase operating costs) Case 1: Bloated - 肥大化 Causes 原因 Consequences 結果

Case 1: Bloated - 肥大化 ● 閾値がテーブルのゴミ蓄積パターンに合っていないと ゴミを適切なタイミングで掃除できず、不必要に新し い容量を使い始めてテーブルが肥大化してしまう When thresholds are not matching to the pattern of dead rows accumulating, autovacuum is unable to clean up them in appropriate timing, resulting using new spaces unnecessary and causing the table bloat ● 例えば閾値が20%の場合、100行のテーブルだと20行の 更新・削除でautovacuumがトリガーされるが、1億行 のテーブルだと2000万行が更新・削除されないとトリ ガーしない For example, with 20% threshold, 20 dead rows will trigger autovacuum with a 100 rows table. However, 20M dead rows are required to trigger autovacuum for a 100M rows table

● 閾値の設定がテーブルに合ってい なくautovacuumの頻度が低く、 ゴミが溜まる Autovacuum is running infrequently and dead rows are accumulated due to improper thresholds ● Xmin horizonのせいでゴミタプ ルの掃除がうまくできていない Unable to clean up dead rows due to the xmin horizon ● クエリの実行の際により多くの データを読まなければならなくな りパフォーマンスが低下する Autovacuum is running infrequently ● ゴミのためより多くのI/Oやディ スク容量が必要となる（コスト 増） Increased I/O and/or disk usage due to dead rows (might increase operating costs) Case 1: Bloated - 肥大化 Causes 原因 Consequences 結果

Case 1: Bloated - 肥大化 ● ゴミタプルの中でも、この時期以前に作られたものし か掃除ができないといった境界“xmin horizon”がある Among the dead rows, there is a horizon called “xmin horizon”, which indicates that VACUUM can clean up only the dead rows created before this point ● Autovacuumがしっかり走っていてもxmin horizonが古 いとゴミを掃除できずテーブルが肥大化する Even though autovacuum is running properly, the “old” xmin horizon might be affecting how much can be cleaned and causing the table bloat Learn More: 3-1 1111 2222 3333 4444 5555

tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended Case 1: Bloated - 肥大化 Learn More: 3-1 小さいほうがいい Smaller is better 大きいほうがいい Bigger is better

Case 1: Bloated - 肥大化 ● Xmin horizonは「一番古いバージョンを見ている人 （トランザクション）」によって進まない The xmin horizon can’t advance because of “the person (transaction) who is watching the oldest version” Learn More: 3-1 あ、ぼくまだここ見てるので 掃除しないでください〜 Oh, I’m still watching this part, please don’t clean up

Case 1: Bloated - 肥大化 ● たいていはxmin horizonのageの分だけ生きているロン グトランザクションが見ている Usually, the long-running transaction that has been running as the same length/age as the xmin horizon is “watching” the oldest dead tuple Learn More: 3-1 3日前に BEGIN した人 Ran BEGIN 3 days ago 3日分のゴミ 3 days worth garbage

Solution 解決 Xmin horizonが古くなっている原因のロングトランザクションを取り除く Remove the long-running transaction that is causing the xmin horizon to be held back ● 長く走っているクエリ・トランザクション Long-running queries/transactions ● ロジカルレプリケーションの遅延または不備 Lagging or stale logical replication slots ● ストリーミングレプリケーションの遅延または不備(hot_standby_feedback on) Lagging or stale physical (streaming) replication slots with hot_standby_feedback on ● スタンバイサーバでの長く走っているクエリ(hot_standby_feedback on) Long-running queries on standbys with hot_standby_feedback on ● 放置された二相コミット用のトランザクション Abandoned prepared transactions Case 1: Bloated - 肥大化

● 適切にI/Oが割り当てられていな くI/Oがボトルネックになり時間 がかかる I/O is not allocated well and being a bottleneck ● 適切にメモリが割り当てられてい なくVACUUMが非効率になってい て時間がかかる Not assigned enough memory to perform VACUUM efficiently ● Autovacuumが途中でDDLなどに より中断されてしまい終わらない Things like DDL could interrupt autovacuums and they won’t finish ● VACUUM待ちテーブルが発生し、 肥大化の原因となる There will be backlogs of autovacuums, causing table bloat ● 重要なVACUUMが実行されずアプ リダウンの危険がある Important VACUUMs (like anti-wraparound) won’t get executed and could cause the application down Case 2: Performance - パフォーマンス Causes 原因 Consequences 結果

Case 2: Performance - パフォーマンス Autovacuumを効率よく走らせるにはパラメータとデータ ベースの作業負荷の絶妙なバランスが重要 Having a good balance between parameters and database workload is important for running autovacuums effectively launcher worker1 worker2 worker3

launcher worker1 worker2 worker3 Case 2: Performance - パフォーマンス Autovacuumのパフォーマンス関連のパラメータ Autovacuum parameters related to performance 最大何人ワーカーがいるか How many workers concurrently run max autovacuum_max_workers ワーカーあたり使用できるメモリ How many workers concurrently run max autovacuum_work_mem autovacuum要否チェックの間隔 Delay between autovacuum runs check autovacuum_naptime 64MB 1 min 3 workers

Case 2: Performance - パフォーマンス Autovacuumのパフォーマンス関連のパラメータ Autovacuum parameters related to performance Cost limitに達したときにどれだけ待つか Delay when the cost limit is reached autovacuum_vacuum_cost_delay 一度にVACUUMを進められるコスト量 The cost limit for how much VACUUM can be progressed at one time autovacuum_vacuum_cost_limit 200 200 2ms

● 適切にI/Oが割り当てられていな くI/Oがボトルネックになり時間 がかかる I/O is not allocated well and being a bottleneck ● 適切にメモリが割り当てられてい なくVACUUMが非効率になってい て時間がかかる Not assigned enough memory to perform VACUUM efficiently ● Autovacuumが途中でDDLなどに より中断されてしまい終わらない Things like DDL could interrupt autovacuums and they won’t finish ● VACUUM待ちテーブルが発生し、 肥大化の原因となる There will be backlogs of autovacuums, causing table bloat ● 重要なVACUUMが実行されずアプ リダウンの危険がある Important VACUUMs (like anti-wraparound) won’t get executed and could cause the application down Case 2: Performance - パフォーマンス Causes 原因 Consequences 結果

Case 2: Performance - パフォーマンス ● cost_limitとcost_delayの設定によって同じテーブ ルを同じだけ掃除するにもかかる時間が変わる Depending on the cost_limit and cost_delay settings, the time spend for cleaning up the exact same table will vary ● 言い換えると、cost_limitとcost_delayは autovacuumが使用できるI/Oを制限している In other words, the cost_limit and cost_delay settings are limiting how much I/O autovacuum can spend ● Example：cost_delay=250 → total 800 costs / sec 250ms 250ms 250ms 250ms 1 second 200 200 200 200

Case 2: Performance - パフォーマンス ディスク性能の進化に伴い、デフォルト値も変わってきた As disk performance increases over time, the default value of these parameters changed cost_limit cost_delay page_hit page_miss page_dirty Max I/O read (hit) Max I/O read (miss) Max I/O write Postgres 11 (2018) 200 20 1 10 20 80MB/s 8MB/s 4MB/s Postgres 12 (2019) 200 2 1 10 20 800MB/s 80MB/s 40MB/s Postgres 14 (2021) 200 2 1 2 20 800MB/s 400MB/s 40MB/s costs/s: 10k costs/s: 100k costs/s: 100k 1 page: 8KB

Case 2: Performance - パフォーマンス ● ディスク性能が悪いとVACUUMに時間がかかるだけでな く、最悪I/OのボトルネックでVACUUMがクエリパ フォーマンスに影響することも When the disk performance is not good, it’s not only affecting the time spend for VACUUMs, but also VACUUMs could affect the ongoing other queries in the worst case, due to the I/O being a bottleneck Example: AWS RDS gp2 storage

Solution 解決 パラメータが現在のディスクに見合ったものかを見直す Revisit cost related parameters to see if it matches to the current disk spec ● 特にPostgres 11以前は要注意 Highly recommended to adjust for Postgres 11 and older ● 古いバージョンの時にパラメータを既にカスタマイズしていた場合、アップグレード後 にカスタマイズした値がデフォルト値より小さくなってしまっている場合も If cost related parameters were previously customized, it’s possible that these values are actually lower than the default values after the upgrade ● cost_limitは上げれば上げるほど、cost_delayは下げれば下げるほどautovacuumの使 用しうるI/Oは大きくなる Both bumping cost_limit and lowering cost_delay will result the I/O usage increase by autovacuums Case 2: Performance - パフォーマンス

● 適切にI/Oが割り当てられていな くI/Oがボトルネックになり時間 がかかる I/O is not allocated well and being a bottleneck ● 適切にメモリが割り当てられてい なくVACUUMが非効率になってい て時間がかかる Not assigned enough memory to perform VACUUM efficiently ● Autovacuumが途中でDDLなどに より中断されてしまい終わらない Things like DDL could interrupt autovacuums and they won’t finish ● VACUUM待ちテーブルが発生し、 肥大化の原因となる There will be backlogs of autovacuums, causing table bloat ● 重要なVACUUMが実行されずアプ リダウンの危険がある Important VACUUMs (like anti-wraparound) won’t get executed and could cause the application down Case 2: Performance - パフォーマンス Causes 原因 Consequences 結果

Review: VACUUM Phases - おさらい ● VACUUM TODOリストの大きさは決まっている The size of VACUUM TODO list is limited ○ デフォルトではautovacuum_work_mem (maintenance_work_mem)の64MBで、最大は1GB The default size is 64MB from autovacuum_work_mem (maintenance_work_mem), then it can be the maximum 1GB ○ もし最初のscanning heapフェーズでヒープページ を全部走査する前に埋まってしまった場合は、全部 走査ができるまで三つのステップを繰り返す If it’s filled during the first scanning heap phase before scanning all heap pages, these 3 phases will be repeated until all heap pages are scanned

● もしメモリが不十分だと… If there is not enough memory… ○ scanning heapとvacuuming heapはTODO リストに載る・載っている分だけ走査すれ ばいいが、vacuuming indexesフェーズは 繰り返しの度に全部を走査しなければなら ない The scanning heap and vacuuming heap phases only need to go through the parts that can be fit in (or already on) the TODO list, however, the vacuuming indexes phase will need to go through all pages of all indexes for each iteration Case 2: Performance - パフォーマンス

Solution 解決 autovacuum_work_memもしくはdead rows関連の他の閾値を見直す Revisit the autovacuum_work_mem setting or other thresholds related to dead rows ● デフォルトでは64MBなのでだいたい1000万のdead rowsまでサポート With default value 64MB, it supports holding ~10 million dead rows ● 大抵のテーブルはdead rowsが1000万たまる前にautovacuumが走る Usually, autovacuums will be triggered before dead rows reaching to 10M ● 常に1000万以上のdead rowsがたまっているテーブルなどは、閾値の調整などで autovacuumの頻度を上げることも大事 For tables that always have more than 10M dead rows, it is also important to revisit thresholds to ensure that autovacuums are running frequently Case 2: Performance - パフォーマンス

● DDLのせいでautovacuumが開始 できない・中断される Autovacuum can’t be started due to DDL, or it will be interrupted ● Anti-wraparound autovacuumが DDLをブロックしてしまう Anti-wraparound autovacuums block DDL ● Autovacuumが適切なタイミング で走らず肥大化の原因となる Autovacuum is not running as needed and causes bloat ● Autovacuum待ちクエリでアプリ がダウンする Application can go down due to queries indirectly waiting for autovacuum Case 3: Locks - ロック Causes 原因 Consequences 結果 Learn More: 3-2

Characteristic of autovacuum - 特性 ● 必要なロック(SHARE UPDATE EXCLUSIVE)を取得できな い場合走るのを諦める When unable to acquire the SHARE UPDATE EXCLUSIVE lock, it gives up running ● もし他のプロセスがSHARE UPDATE EXCLUSIVEと競合 するロックを取得しようとしたら走っている autovacuumを中断する If a process attempts to acquire a lock that conflicts with the SHARE UPDATE EXCLUSIVE lock, autovacuum will be intervened ● ただしanti-wraparound (triggered by freeze age)の場合 は中断せず競合するプロセスをブロックする However, if autovacuum is triggered to prevent wraparound (aka triggered by freeze age), it won’t be interrupted

● SHARE UPDATE EXCLUSIVEロックが競合するロック及 びクエリ例 Locks and example queries that conflict with the SHARE UPDATE EXCLUSIVE lock ○ SHARE UPDATE EXCLUSIVE ■ VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY ○ SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE ■ CREATE INDEX, CREATE TRIGGER, REFRESH MATERIALIZED VIEW CONCURRENTLY ○ ACCESS EXCLUSIVE ■ DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, ALTER TABLE, ALTER INDEX Characteristic of autovacuum - 特性

Characteristic of autovacuum - 特性 走るのを諦める Give up to run 中断する Cancel 他をブロックする Block others VACUUM table1! Can’t VACUUM… ALTER TABLE table1 Oops, needs to cancel anti-wraparound! I need to wait for ALTER TABLE ALTER TABLE table1

● DDLのせいでautovacuumが開始 できない・中断される Autovacuum can’t be started due to DDL, or it will be interrupted ● Anti-wraparound autovacuumが DDLをブロックしてしまう Anti-wraparound autovacuums block DDL ● Autovacuumが適切なタイミング で走らず肥大化の原因となる Autovacuum is not running as needed and causes bloat ● Autovacuum待ちクエリでアプリ がダウンする Application can go down due to queries indirectly waiting for autovacuum Case 3: Locks - ロック Causes 原因 Consequences 結果 Learn More: 3-2

Characteristic of autovacuum - 特性 走るのを諦める Give up to run 中断する Cancel 他をブロックする Block others VACUUM table1! Can’t VACUUM… ALTER TABLE table1 Oops, needs to cancel anti-wraparound! I need to wait for ALTER TABLE ALTER TABLE table1

Case 3: Locks - ロック ● Autovacuumがしっかり走っているかを確認する Check if autovacuum is running ● Autovacuumが頻繁に中断していないか確認する Check if autovacuums are not cancelled frequently ● Autovacuumのログを有効にして状態を確認 Check status by enabling autovacuum logs ○ log_autovacuum_min_duration ○ デフォルトは10分だが、ログボリューム的に制限さえな ければすべてをログする0を推奨 Default logs autovacuums taking more than 10 minutes. Unless there is any restriction on the log volume, it is recommended to set this to 0 to log all autovacuums

Solution 解決 ● 頻繁にautovacuumが中断されている場合は、中断の原因となっているクエリを見直す If autovacuums are cancelled often, find queries that are causing the cancellation and review them ● Autovacuumは中断されてもまた走ってくれるので、たまの中断は無視して大丈夫 Cancelled autovacuums will re-triggered by a launcher, so you can safely ignore non-frequent cancellations ● 中断の原因が解消されないと中断され続ける、もしくは中断後に走れない Unless the root cause of the cancellation is resolved, it’ll keep being cancelled, or unable to run ● Autovacuumがそもそも走っていない場合は、何か特別ブロックするようなクエリがない かを確認する If autovacuums are not running at all, try to find a query that is specifically blocking them ● 長らく走っていない、または頻繁に中断されるテーブルを発見したら一度手動で VACUUMするのも手 For a table that hasn’t been autovacuum-ed for a while or constantly cancelled, it would be good to run VACUUM manually Case 3: Locks - ロック

● DDLのせいでautovacuumが開始 できない・中断される Autovacuum can’t be started due to DDL, or it will be interrupted ● Anti-wraparound autovacuumが DDLをブロックしてしまう Anti-wraparound autovacuums block DDL ● Autovacuumが適切なタイミング で走らず肥大化の原因となる Autovacuum is not running as needed and causes bloat ● Autovacuum待ちクエリでアプリ がダウンする Application can go down due to queries indirectly waiting for autovacuum Case 3: Locks - ロック Causes 原因 Consequences 結果 Learn More: 3-2

Characteristic of autovacuum - 特性 走るのを諦める Give up to run 中断する Cancel 他をブロックする Block others VACUUM table1! Can’t VACUUM… ALTER TABLE table1 Oops, needs to cancel anti-wraparound! I need to wait for ALTER TABLE ALTER TABLE table1

Case 3: Locks - ロック ● Anti-wraparound autovacuumはロックが競合するクエ リが発生しても中断しない Anti-wraparound autovacuums won’t be cancelled due to lock conflicts ● DDLがブロックされてしまう可能性がある DDL will conflict with the lock of autovacuum, therefore DDL can be blocked ● さらにDDLが他の簡単なクエリをブロックしてしまいア プリダウンの危険性 That DDL can block other trivial queries, resulting application down Learn More: 3-2

Case 3: Locks - ロック Anti-wraparound VACUUM ALTER TABLE UPDATE SELECT Waiting for Anti-wraparound VACUUM Waiting for ALTER TABLE

Case 3: Locks - ロック ACCESS SHARE ROW EXCLUSIVE SHARE UPDATE EXCLUSIVE ACCESS EXCLUSIVE Anti-wraparound VACUUM ALTER TABLE UPDATE UPDATE UPDATE SELECT SELECT SELECT UPDATE SELECT

Solution 解決 ● Anti-wraparound autovacuumに時間がかからないようにする Make sure that anti-wraparound autovacuums won’t take time to run ● 閾値が高い = あまり走らない = ブロックする確率が低い Higher threshold = not running frequently = lower chance of blocking DDL ● でも…閾値が高い = 一つ一つが長い = ブロックしたときブロックする時間も長い Higher threshold = each anti-wraparound autovacuums takes more time to run = it’ll block longer time ● 50GBのテーブルを走査するにはPostgres11のデフォルト設定だと最低でも10分はかかる With the default settings of Postgres 11 (80MB/s), it’ll take at least 10 minutes to scan the 50GB table ● 50GBのテーブルを走査するにはPostgres14のデフォルト設定だと最低でも1分はかかる With the default settings of Postgres 14 (800MB/s), it’ll take at least 1 minutes to scan the 50GB table Case 3: Locks - ロック

Solution 解決 ● もし起こってしまったら、速やかにDDLまたはanti-wraparound autovacuumを中断する If anti-wraparound autovacuums are blocking DDL, cancel either anti-wraparound autovacuums or DDL immediately ● 中断後に手動で走らせて凍結を終わらせてしまうのも手 After cancelled, you could run VACUUM FREEZE manually to freeze this table ● その場合は、なるべく早く終わるようにINDEX_CLEANUP offを設定するとよい In that case, passing INDEX_CLEANUP off is recommended to complete VACUUM asap ○ 余裕のある時にINDEX_CLEANUP onで再度手動で走らせよう Make sure to run VACUUM manually with INDEX_CLEANUP on afterwards ● DDLが長くanti-wraparound autovacuumを待つ状況にならないようにする Make sure that DDL won’t wait for anti-wraparound autovacuums (or any locks) forever Case 3: Locks - ロック Learn More: 1-2

Monitoring is important ● autovacuumのデフォルトパラメータは沢山の用途をサ ポートできるように設定されているが、パーフェクト ではないし、保守的であることも多い Autovacuum default parameters are set to support majority of use cases but will never be perfect for your database, or can often be conservative ● モニタリングを通じた個々のデータベースへのチュー ニングが大切 It is important to adjust them through the monitoring ○ マネージドサービスでは独自にパラメータを上書きして いるものもある Some managed services are overwriting the default parameters ■ RDS: autovacuum_vacuum_scale_factor 0.2→0.1

Monitoring is important ● モニタリングサービスを使うのも一つの手 Using a monitoring service is a good way to monitor autovacuums

Tips 今日から使えるautovacuum まわりの小技を学ぶ Learn tips related autovacuum that you can use from today 04

Reduce VACUUM by Partitioning ● 一番いいのはVACUUMの必要がなくなること “No need for VACUUM” is the best ● 例：夜間バッチで保持期間を超えた分を削除している ようなテーブル Example: a table which has a nightly deletion for the data exceeding the retention ○ 削除が走った後にVACUUMが走らないとゴミが溜 まる原因に If no VACUUM happens after the deletion, the garbage can accumulate ○ 日付ごとのパーティショニングによりテーブルご とDROPする By partition per day, the table can be simply dropped instead of a mass deletion

Reduce VACUUM by less UPDATEs ● 不必要なUPDATEをなくすことはそれ自体がアプリパ フォーマンスによいだけでなく、VACUUMも減らせる Reducing unnecessary UPDATEs not only helps to improve application performance, but also helps reducing the number of VACUUMS (by dead rows) ● 例：issue_referencesテーブルは10秒ごとに「経過時 間」を設定するためのUPDATEがあったが、これを見直 して起点時間を設定することにより更新頻度が大幅に 下がった Example: the issue_references table had an UPDATE query that updates the “spend time” every 10 seconds, but after changing this to the “start time”, the UPDATE frequency dramatically decreased ○ VACUUMも週10回から週7回へ Weekly VACUUM frequency reduced from 10 to 7

Upgrading ● VACUUMのエリアはリリースごとに常にパフォーマンス 向上がなされている The improvement of VACUUM is happening in each releases ○ Postgres 16: Improve performance of vacuum freezing ○ Postgres 14: VACUUM automatically becomes more aggressive, and skips inessential cleanup, if the database starts to approach a transaction ID wraparound condition ● パラメータのデフォルト値も見直され続けている The default parameters for the VACUUM/autovacuums are continuously revisited too

Bloat Check ● pgstattupleモジュールを使って調べる Find tables that should be triggering autovacuums (but haven’t) ○ タプルレベルの統計情報 (tuple-level statistics) ○ https://www.postgresql.jp/document/15/html/pg stattuple.html ● PostgreSQL Wikiに載っているSQLを使って調べる Use the SQL on the PostgreSQL Wiki ● https://wiki.postgresql.org/wiki/Show_database _bloat

Bloat Check

Xmin Horizon Check ● Xmin Horizonが現在どれだけかをチェックして、 VACUUMをブロックしていないかを見る Check the current xmin horizon and determinate if it’s blocking VACUUMs ● https://gist.github.com/keiko713/e2cb98a6834c8610 d49a54bc463ec29f backend | replication_slot_xmin | replication_slot_catalog_xmin | prepare_xact | standby -----------+-----------------------+-------------------------------+--------------+--------- 178944984 | 0 | 0 | 0 | 0 (1 row)

Xmin Horizon Check

Freeze Age Check ● 各テーブルをFreeze Ageが大きい順に並べる List up freeze age of each table ● もしageがautovacuum_freeze_max_ageを超えている ものがあれば要注意 You want to look into more if there is any table that is exceeding autovacuum_freeze_max_age SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ORDER BY age(relfrozenxid) DESC; SELECT relname, age(relminmxid) FROM pg_class WHERE relkind = 'r' ORDER BY age(relminmxid) DESC;

Freeze Age Check

CREDITS: This presentation template was created by Slidesgo, including icons by Flaticon and infographics & images by Freepik Thanks Any questions? Social: @keiko713 VACUUM Simulator: https://vacuum-simulator.netlify.app/ pganalyze: https://pganalyze.com/

Learn More 1 1-1. [EN] pganalyze VACUUM Advisor by pganalyze What is Bloat?やWhat is Freezing?のセクションはこれらを学び直すのにおすすめ。またどんなところを注意して見る べきかのヒントになる 1-2. [JP] 最速でVacuumを完了させる方法とその副作用 by Masahiko Sawada これに限らず澤田さんのVACUUMに関するポストは、VACUUMのコードにまさにコントリビュートしている方だから こそのクオリティ 1-3. [EN] How to simulate the deadly, infamous, misunderstood & complex ‘Transaction Wraparound Problem’ in PostgreSQL … by FatDBA 実際にwraparoundさせかけた人のブログ。9時間かけてTXIDを使い切った模様 1-4. [EN] Transaction ID wraparound: a walk on the wild side by Laurenz Albe (CYBERTEC) 実際にwraparoundさせた人のブログ。single-userモードを使ってまでデータ破壊を再現したのは圧巻。これ以外にも LaurenzさんのブログやStack Overflowのポストにはよく助けられる

Learn More 2 2-1. [EN] Cloud SQL for PostgreSQL - A deep dive into VACUUM FAQs by Virender Singla (Google Cloud) VACUUMに関するさまざまなことがQ&A形式で書かれており、テンポよく読みやすい 2-2. [EN] Debugging Postgres autovacuum problems: 13 tips by Samay Sharma (Microsoft) autovacuumに関する問題をカテゴライズして見やすく書かれている 2-3. [EN] PostgreSQL/Wraparound and Freeze by Wikibooks Freezeって結局どういった条件でどうやって走ってるの、という疑問が細かく解消される。このページ以外にも WikibooksのPostgreSQLのセクションはよくまとめられている 2-4. [JA] 同時実行制御 by PostgreSQLドキュメント MVCCの概要とPostgresの同時実行制御を完結に表したもの。MVCCについて詳しくない場合はまずここを読んで、 分からない部分や具体的に知りたい部分を掘り下げるのもよい 2-5. [JA] MVCCの素顔 by Koichi Suzuki (EDB) Bruce Momjian氏のMVCC unmaskedを日本語化したスライド。MVCCについてとことん理解したいときにとても役立 つ。EDBのウェビナーのページではこのスライドのウェビナーも見ることができる

Learn More 3 3-1. [EN] Postgres VACUUM and Xmin Horizon by Keiko Oda 手前味噌ですがxmin horizonにフォーカスを当てて掘り下げて書いてあります 3-2. [EN] Understanding an outage: concurrency control & vacuuming in PostgreSQL by Walter Carvalho, João Bernardo (Duffel) 実際にanti-wraparound autovacuumが原因で起こった障害について書かれてある。ストーリーテリング形式で読みや すい

4-1. [EN] Understanding & Managing Postgres Table Bloat | Citus Con: An Event for Postgres 2023 by Chelsea Dole (Brex) VACUUMでも特に肥大化 (bloat) について学ぶのによい 4-2. [EN] Bloat in PostgreSQL: a taxonomy by Peter Geoghegan VACUUMについてとても深入りしている。また、opportunistic cleanup/pruning（VACUUM以外でのdead tuplesの掃除 法）についてもカバーされている 4-3. [JA] Vacuum徹底解説 by Masahiko Sawada PostgreSQL Conference Japan 2021でのチュートリアルセッション。VACUUMについて知っておくべきことがよくま とめられており、私のトークを聞かなくてもこれを見れば十分 Learn More 4

Appendix おまけ APPENDIX

● VACUUM and Visibility Map ● VACUUM Phases - with logs Appendix Table of Contents APPENDIX

VACUUM and Visibility Map ● Visibility Mapの更新：VACUUMの役割うちの一つ Updating the visibility map: One of VACUUM’s roles ● Visibility Mapが更新されるとうれしいこと Benefit of updating the visibility map ○ Index-only scanが効率的に使えるようになる Improve performance of index-only scans ○ 後続VACUUMの処理時間の短縮 Improve performance for any subsequent VACUUMs APPENDIX

Visibility Map - 可視性マップ ● ヒープページごとに次のどれかの情報を持つ： ”all-visible”, ”all-frozen”, ”unknown” Each heap page contains one of the followings: “all-visible”, “all-frozen”, or “unknown” ● VACUUMによってのみall-visibleまたはall-frozenの情報 がセットされる Only VACUUM can set all-visible or all-frozen information to the visibility map ● ヒープページが挿入・削除など編集されると情報が”未 知”にリセットされる Any data-modifying operations on a heap page will reset this to “unknown” APPENDIX

VACUUM and Visibility Map ①テーブルに行を挿入 Rows are inserted to the table テーブル Table ヒープページ Heap page 行 Row APPENDIX

VACUUM and Visibility Map ②いくつかの行を削除 Some rows are deleted 赤い部分が削除された行 Deleted rows are marked as red APPENDIX

③VACUUMの実行（一回目） Run VACUUM (1st time) VACUUM and Visibility Map Dead rowsが空スペースに Dead rows become empty spaces 各heap pageにall-visibleと all-frozenがセットされる All-visible and all-frozen are set to each page ヒープによってはall-visibleまたは all-frozenがセットされない場合もある Some heaps may not be flagged as all-visible or all-frozen APPENDIX

VACUUM and Visibility Map ④行を削除したり挿入したり Deleting and inserting some rows 行を削除 Deleting a row 編集が行われたヒープページは Visibility Mapがリセットされる Visibility Maps of the modified heap pages are reset 行を新しく挿入 Inserting new rows APPENDIX

VACUUM and Visibility Map ⑤VACUUMの実行（二回目） Run VACUUM (2nd time) VACUUMをスキップ できる！ VACUUM can be skipped with these pages! APPENDIX

VACUUM and Visibility Map ● All-visibleまたはall-frozenなヒープページはscanning heapフェーズでスキップされる All-visible or all-frozen heap pages will be skipped during the scanning heap phase ○ Aggressive VACUUM（freezeに重きを置いたVACUUM）では all-frozenのみスキップできる All-frozen pages can be only skipped with an aggressive VACUUM (freeze-focused VACUUM) ● このようなヒープページがたくさんあればあるほど後 続のVACUUMでスキップでき処理時間が短縮できる Subsequent VACUUMs can benefit more and more performance improvements when there are many skippable heap pages in the table ○ Inserts-onlyのテーブルでは効果大 Especially works really well with inserts-only tables APPENDIX

Inserts-only table and Visibility Map ①VACUUMの実行（一回目） Run VACUUM (1st time) APPENDIX Note: technically with inserts-only table, Postgres actually won’t freeze (and mark all-frozen) like in the diagram, unless it is an aggressive vacuum

Inserts-only table and Visibility Map ②VACUUMの実行（二回目） Run VACUUM (2nd time) APPENDIX 前にVACUUMしたヒープページ は全部スキップできる！ All heap pages that are VACUUMed previously can be skipped! 挿入のみのテーブルなのでヒープ ページが編集されずVisibility Map が維持される Heap pages won’t be modified because the table is inserts-only, therefore the visibility map will remain set

Benefit of autovacuum triggered by inserts ● Postgres 13から、最後のVACUUMからどれだけの行が 挿入されたかによってautovacuumが走るようになった Starting from Postgres 13, autovacuums can be triggered by how many rows inserted since the last VACUUM ● 従来、挿入のみのテーブルではVACUUMがdead rowsに よってはトリガーされず、freeze ageでのみトリガーさ れていた Before that, autovacuums of inserts-only tables were only triggered by freeze age, as there are no dead rows and dead rows weren’t triggering autovacuums ● autovacuumの間隔が長くなり、一度のautovacuumで 処理すべき部分が大きくなっていた autovacuums weren’t running frequently with these tables and each autovacuum needed to go through a lot of data APPENDIX

Benefit of autovacuum triggered by inserts ● 結果、全体的なパフォーマンスの低下が見られた Resulted performance degradation with these tables or with autovacuum processes ● Visibility Mapがいつまでも更新されず、効果的な index-only scanが使用できない The visibility maps are not updated with these tables and they aren’t able to take advantage of index-only scans fully ● autovacuum一つ一つの処理が重くなりリソース がとられる Each autovacuum is taking potentially lots of resources ● 大量insert後シンプルなSELECT文に時間がかかる Simple SELECT statement takes time to run after massive inserts ● This takes time because there will be writes to update hint bits after inserts. Explained well in “At first, things look pretty normal” part in CYBERTEC’s blog post ● An aws talk explaining how performance degrade when you don’t VACUUM with inserts/read-only tables APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs Postgres version: 15.3 APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs Aggressive mode （凍結するぞモード） How many times below is performed 全体の状況 Overview APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs ヒープページの削除数・残数・スキャン数 How many pages are removed/remain/scanned ヒープページ Heap page 行・タプル Row/Tuple ヒープページ Heap pages APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs タプルの削除数・残数 How many tuples are removed/remain ヒープページ Heap page 行・タプル Row/Tuple タプル Tuples APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs 255,707個のゴミタプルが削除できなかった 255,707 dead tuples were not removable トランザクションIDが568440102より新しく 作られたゴミタプルは削除できなかった Any dead tuples created after XID 568440102 were not removable VACUUM終了時にトランザクションID 568440102 の齢は2,475,189であった When VACUUM was done, the age of transaction ID 568440102 was 2,475,189 ゴミタプル清掃状況 Dead rows cleaning up status Learn More in xmin horizon APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs 凍結の結果、relfrozenxid（凍結されていない最古のトランザク ションID）が260,122,451進んで518,440,452となった With freezing, relfrozenxid (the oldest unfrozen XID) advanced by 260,122,451, became 518,440,452 260,122,451 258,318,001 518,440,452 凍結状況 Freezing status APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs 5,258,716ページ（全体の46.53%）から合計12,925,696個 のゴミタプルがVACUUM TODOリストに追加された Total 12,925,696 dead rows were added to the VACUUM TODO list, from 5,258,716 pages (46.53% of total) Index scan（vacuuming indexesフェーズ）の要否 Needs vacuuming indexes phase or not 0%: not needed（不要）, 0-2%: bypassed（スキップ）, 2%+: needed（必要） Learn More: 4-2 インデックス全体 Indexes Overview APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs 4つのインデックスそれぞれについての vacuuming indexesフェーズのログ Vacuuming indexes phase logs for each 4 indexes 各インデックス Each index APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs ● Newly deleted ○ このVACUUMで削除されたインデックスページ Deleted during this VACUUM ● Currently deleted ○ 現在削除されているインデックスページ（newly deletedも含む） Currently deleted index pages (including ones deleted during this VACUUM) ● Reusable ○ 再利用可能なインデックスページ（currently deletedで再利用可能なものも含む） Reusable index pages (including reusable ones within currently deleted index pages) 各インデックス Each index APPENDIX

automatic aggressive vacuum of table "pgaweb.public.queries": index scans: 1 pages: 0 removed, 11302268 remain, 10786416 scanned (95.44% of total) tuples: 6605628 removed, 140296119 remain, 255707 are dead but not yet removable removable cutoff: 568440102, which was 2475189 XIDs old when operation ended new relfrozenxid: 518440452, which is 260122451 XIDs ahead of previous value index scan needed: 5258716 pages from table (46.53% of total) had 12925696 dead item identifiers removed index "index_queries_on_database_id_and_table_names": pages: 1036814 in total, 2624 newly deleted, 2722 currently deleted, 2806 reusable index "queries_pkey": pages: 1205210 in total, 22261 newly deleted, 23690 currently deleted, 23690 reusable index "index_queries_on_database_id": pages: 382537 in total, 14192 newly deleted, 19379 currently deleted, 19379 reusable index "index_queries_on_last_occurred_at_and_database_id": pages: 386425 in total, 13110 newly deleted, 19324 currently deleted, 19324 reusable I/O timings: read: 224003.288 ms, write: 34770.930 ms avg read rate: 64.077 MB/s, avg write rate: 51.295 MB/s buffer usage: 16212992 hits, 15328879 misses, 12271167 dirtied WAL usage: 23534009 records, 12360914 full page images, 29981773444 bytes system usage: CPU: user: 760.28 s, system: 140.51 s, elapsed: 1868.96 s VACUUM Phases - with logs パフォーマンス・コスト関連のログ Logs related to performance/cost パフォーマンス Performance APPENDIX