PostgreSQLの大規模データセットに対する効率的なサンプリング戦略
背景
数十億レコードを含む大規模なPostgresのデータセットが更新処理のない環境にありました。
これらのレコードを効果的に検証する方法が必要でした。
いくつかのレコードをサンプリングして検証することを考えましたが、すでにカーソルパターンの実装を広範囲に使用していたため、同じ手法を用いることにしました
サンプリングの課題とid % 10 == 0の問題点
id % 10 == 0のようなクエリを用いてサンプリングを行なうと、大量のデータに対しては非効率的です。
主な理由は、インデックスが効かないことと、偏ったデータ分布により、id % 10 == 0に該当するデータが少ない可能性があることです。
TABLESAMPLE句の基本
この問題を解決するために、TABLESAMPLE句に目を向けました。
この句は、指定されたテーブルからランダムなサンプルを取得するために使用されます。例えば、50億レコードに対して1%のサンプルを取得する場合、500万レコードをランダムに取得できます。
TABLESAMPLE句を使うと、必要なサンプルを効率的に取得できます。
ただし、BERNOULLI方式はフルスキャンを行うためパフォーマンスが悪いです。大量のレコードがある場合は、SYSTEM方式を使用することが推奨されます。
TABLESAMPLE SYSTEMとREPEATABLEの組み合わせ
特に重要なのが、TABLESAMPLE SYSTEMとREPEATABLEオプションの組み合わせです。この組み合わせは、優れたパフォーマンスを提供し、何度実行しても同じ結果セットを得ることができます。
これにより、カーソルパターンを併用することができます。
カーソルを使った効率的なサンプリング戦略
カーソルパターンを用いて、1万件ずつデータをメモリに読み込むことで、大量のデータを効率的に処理することにしました。この方法では、メモリ使用量を抑えつつ、必要なデータを段階的に検証できます。
実際に、数億レコードに対して500万件のサンプリングを行った例を挙げます。以下のクエリは、TABLESAMPLE SYSTEM(1) REPEATABLE(1)を使用して、特定の範囲のIDに対して10,000レコードを取得するものです。
SELECT * FROM hoge_table TABLESAMPLE SYSTEM(1) REPEATABLE(1) WHERE id >= 53434 LIMIT 10000
このクエリは、一貫したパフォーマンスを持ちながら、必要なサンプリングデータを効率的に取得することができました。
結論
TABLESAMPLEとカーソルパターンを組み合わせることで、大量のデータに対して高いパフォーマンスでサンプリングを行うことができました。数万件ずつのデータを読み込むことが可能であり、これはサンプリング比率と回数の調整が面倒な場合に特に便利です。他の方法と比較しても、このアプローチは特に大量データの処理において効果的であり、カーソルパターンの実装を既に行っている場合には、より一層のメリットがあります。
Discussion