2021年に見つかった、予想外の20GB分の未使用インデックス領域の解放

• データベースでインデックスやデータを削除せずに、ストレージ容量を確保する技術が論じられています。
• 未使用のインデックスを特定し、ストレージ容量を確保するために削除します。
• テーブルとインデックスの不要な領域を整理するために再構築します。
• PostgreSQL 13ではB-Treeインデックスの重複排除機能が導入され、重複値を含むインデックスのサイズを縮小できます。
• ダウンタイムなしでブロートを減らすために、pg_repack拡張機能を使ってテーブルを再構築できます。
• PostgreSQLではNULL値もインデックス化されるため、不要な領域を消費します。
• NULL値を除外してインデックスサイズを縮小するために、部分インデックスを活用します。
• これらの作業を行うためのクエリとコマンドが提供されます。
• 言及された技術は、ストレージ使用量を最適化し、データベース性能を向上させることができます。
• Djangoで構築された大規模アプリケーションでは、ストレージ最適化と性能向上のために部分インデックスが使われます。
• 部分インデックスは、null値だけでなく、頻繁には、あるいはまったくクエリされない値も除外できます。
• 特定のインデックスをnull値を除外する部分インデックスに変換すると、約1.3GBのストレージ容量を節約できます。
• レプリカでインデックスを削除すると、さらに多くのストレージ容量を確保できます。
• Djangoで外部キーに対する暗黙的なインデックス作成を防ぎ、ダウンタイムや性能低下なしに既存の完全インデックスを部分インデックスへ移行するためのヒントも紹介されています。
• インデックス定義を最適化し、ストレージ効率と性能を最大化する重要性が強調されています。

[GN⁺]

Hacker Newsのコメント

• インデックスを小さくすることには、十分なストレージ容量があっても利点がある。
• 自前のハードウェアを持つ人にとっては、より多くのストレージ領域を割り当てることが有効な選択肢になる。
• PostgreSQL 13のB-tree重複排除は、外部キーインデックスのNULL値に役立つ可能性がある。
• この記事は、PostgreSQLの空間効率を最適化するための有用な手法と洞察を提供している。
• 創業初期または初期段階の企業では、サイズを最適化するよりもディスク容量を追加するほうが一般的には望ましい。
• テーブルの列順を並べ替え、スクリプトを使うことで、かなりのストレージ容量を確保できる。
• 非対称なデータ分布は、インデックスの肥大化や、より効率的なバキューム処理の必要性につながる可能性がある。
• この記事はHacker Newsで好意的なフィードバックを受けた。
• 未使用のインデックスや最適化の機会を見つけるために、pganalyze.comが推奨されている。
• あまり一般的でないPostgreSQLのインデックス形式やNULL値インデックスは、広く知られていない。
• データ量を増やすと、挿入時間やパフォーマンスに大きな影響を与える可能性がある。
• その原則は、列に共通値の高い割合がある場合でも、依然として当てはまる可能性がある。