Movatterモバイル変換

[0]ホーム
URL:

Future Tech Blog
フューチャー技術ブログ
DBカテゴリ

pg_dumpによる統計情報ダンプ検証

PostgreSQL18連載の4本目の記事です。

この度、PosgreSQLメジャーバージョンアップに伴い、pg_dumpに統計情報のバックアップ・リストアが追加されました。

PostgreSQLのpg_dumpは、データ削除前のバックアップや他環境へのデータ移行などで広く利用されている機能だと思います。しかし、PosgreSQL18以前のpg_dumpには統計情報が含まれないため、あくまでオブジェクト・データのバックアップとして利用されるが多かったのではないかと考えます。

サードパーティ系の拡張モジュールを利用することで、PosgreSQL18以前のバージョンにおいても統計情報のバックアップできます。

今回、PosgreSQLメジャーバージョンアップで、バックアップ・リストアに統計情報が含まれたことにより、より高い精度で本番環境等の実環境を再現できるようになり、pg_dumpを活用できるシーンも増えるのではないかと考えます。

本記事では統計情報を含むpg_dumpの有用性について検証してまいります。

検証①：pg_dumpに統計情報を含むことによる影響は？

pg_dumpに統計情報が含まれることは活用の幅も広がり、メリットではありますが、実行時間やバックアップファイルサイズが著しく増加すれば、有用性に欠けると考えます。

PosgreSQL18とPosgreSQL18以前（今回はPosgreSQL 16）でpg_dumpによるバックアップ・リストアを実施し、実行時間・ファイルサイズの比較検証を行います。

検証条件

シナリオ

バージョン間の条件を近づけるため、バージョンごとに以下のシナリオで検証を行います。
また、各バージョンごとに「a.10テーブル」「b.50テーブル」「c.100テーブル」の3パターンのテーブル数で検証を行います。

【PosgreSQL18】

  1. pg_dump：スキーマレベル論理バックアップ（対象：スキーマ・オブジェクト・データ・統計情報）
  2. restore：バックアップファイルによるスキーマリストア

【PosgreSQL18以前】

  1. pg_dump：論理バックアップ（対象：スキーマ・オブジェクト・データ）
  2. restore：バックアップファイルによるスキーマリストア
  3. analyze：統計情報取得

テスト用オブジェクト

バックアップ・リストア対象として用意するオブジェクトは以下とします。
なお、データは1テーブルあたり100万件とします。

テーブル

カラム名 (列名)データ型PKNULL許容カーディナリティ
id01bigint〇NO1,000,000
id02textNO2
id03textNO3
id04textNO4
id04textNO5
検証手順
  1. データベース作成
create database {データベース名};
  1. スキーマ作成
create schema {スキーマ名};
  1. テーブル・PK作成

意図しないタイミングで統計情報が更新されるのを避けるため、テーブルレベルでautovacuumを無効化しています。

createtable {テーブル名} (id01bigintnotnull , id02 textnotnull , id03 textnotnull , id04 textnotnull , id05 textnotnull)with(autovacuum_enabled=false, toast.autovacuum_enabled=false);
altertable table01addconstraint pk_table01primary key (id01);
  1. データ作成
insertinto table01 (select generate_series(1,1000000),generate_series(1,1000000)%2,generate_series(1,1000000)%3,generate_series(1,1000000)%4,generate_series(1,1000000)%5);
  1. 統計情報取得※【PosgreSQL18】のみ

autovacuumを無効化しているため、バックアップ対象となる統計情報を取得させます。

analyze {対象オブジェクト全てを指定};
  1. pg_dump
    windows上にインスタンスを立てているため、Powershellよりpg_dumpを実施しています。
    今回は論理バックアップの内容がわかるよう平文にてバックアップを取得します。

【PosgreSQL18】

powershell -Command"Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose --statistics }"

【PosgreSQL18以前】

--statisticsは付与できない。

powershell -Command"Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose }"

今回は実行時間を取得したいのでMeasure-Commandを使用しています。

Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 5
Milliseconds      : 676
Ticks             : 56764202
TotalDays         : 6.56993078703704E-05
TotalHours        : 0.00157678338888889
TotalMinutes      : 0.0946070033333333
TotalSeconds      : 5.6764202
TotalMilliseconds : 5676.4202
  1. スキーマ削除
drop schema {スキーマ名} cascade;
  1. リストア

windows上にインスタンスを立てているため、PowershellよりDBにログインし、リストアを実施しています。

psql -h {ホスト名} -p {ポート} -U {ユーザ名} -d {データベース名}
\timing
\i {バックアップファイル名};

今回は実行時間を取得したいので\timingを使用しています。

（略）
CREATE TABLE
時間: 3.321 ミリ秒
ALTER TABLE
時間: 0.479 ミリ秒
CREATE TABLE
時間: 4.801 ミリ秒
ALTER TABLE
時間: 0.641 ミリ秒
COPY 1000000
時間: 816.210 ミリ秒
COPY 1000000
時間: 721.280 ミリ秒
COPY 1000000
（略）
  1. 統計情報取得※【PosgreSQL18以前】のみ
analyze {対象オブジェクト全てを指定};

検証①結果

実行時間

  • pg_dumpの実行時間はPostgres18が優位である。
  • restore（+analyze）の実行時間にも致命的な差はない。
バージョンパターン
(総データ量)		pg_dump
(ms)		restore
(ms)		analyze
(ms)		total
(ms)
PosgreSQL1610テーブル
(1,000万件)		10,292.24819,134.7551,179.92030,606.923
PosgreSQL1650テーブル
(5,000万件)		38,700.804113,306.8617,801.671159,809.336
PosgreSQL16100テーブル
(10,000万件)		147,478.958318,141.06819,085.712484,705.738
PosgreSQL1810テーブル
(1,000万件)		5,676.42019,507.097-25,183.517
PosgreSQL1850テーブル
(5,000万件)		24,826.907195,836.231-220,663.138
PosgreSQL18100テーブル
(10,000万件)		69,300.5253341,310.349-410,610.8743

バックアップファイルサイズ

  • 統計情報データの分ファイルサイズとしては純増していると思われるが、軽微な範囲であると考える。
バージョンパターン
(総データ量)		ファイルサイズ(byte)
Postgres1610テーブル
(1,000万件)		148,897,705
Postgres1650テーブル
(5,000万件)		744,485,345
Postgres16100テーブル
(10,000万件)		1,488,969,904
Postgres1810テーブル
(1,000万件)		148,951,114
Postgres1850テーブル
(5,000万件)		744,657,089
Postgres18100テーブル
(10,000万件)		1,489,313,091

反省点（まずは反省から。今後、アップデートしていきます）

  • 純粋なバージョンアップによる比較を行うためには、Postgres17を採用すべきであった。これではPostgres17アップデートによる影響なのか、Postgres18アップデートによる影響なのか判断ができない
  • 値の比較を行うには、試行回数が足りていない

pg_dump考察

Postgres18バックアップファイルを確認すると、以下のように統計情報が含まれていることがわかる。

また、テーブルレベルの統計情報はpg_restore_relation_stats、カラムレベルの統計情報はpg_restore_attribute_statsにより、統計情報が更新されている。

（略）
SELECT*FROM pg_catalog.pg_restore_relation_stats(
'version','180000'::integer,
'schemaname','test',
'relname','table01',
'relpages','5406'::integer,
'reltuples','1e+06'::real,
'relallvisible','0'::integer,
'relallfrozen','0'::integer
);
SELECT*FROM pg_catalog.pg_restore_attribute_stats(
'version','180000'::integer,
'schemaname','test',
'relname','table01',
'attname','id01',
'inherited','f'::boolean,
'null_frac','0'::real,
'avg_width','8'::integer,
'n_distinct','-1'::real,
'histogram_bounds','{56,10524,21232,30718,41153,50377,60264,70120,80019,89398,99574,109434,119523,129696,139684,149877,161659,171036,181245,191695,201052,211025,220695,231533,241040,251121,261035,272801,282385,291622,302144,311922,320997,331276,341649,352215,362449,372253,383367,393098,402659,412876,422222,431857,441934,452435,462050,471877,481111,491169,502147,512482,522629,533297,542697,552015,562928,573165,583118,592605,602734,612603,622786,632162,641168,651381,660752,671539,681549,691029,701074,711518,721946,732664,743058,752998,763483,773288,783463,793519,802897,812524,822721,832245,841333,851908,860870,870866,880738,889926,898894,909459,919903,929828,939844,949636,960208,970440,980545,989514,999967}'::text,
'correlation','1'::real
);
（略）

テーブルレベル統計情報（pg_class）

列説明
relpagesテーブルのディスク上のページ表現のサイズ
reltuplesテーブル内の有効な行数
relallvisibleテーブルの可視性マップですべて可視とマークされているページの数
relallfrozenテーブルの可視性マップで「すべて凍結」とマークされているページの数

参考：52.11. pg_class

カラムレベル統計情報（pg_stats）

列説明
null_fracNULLの列エントリの割合
avg_width列のエントリの平均幅（バイト単位）
n_distinct列内の固有値の推定数
histogram_bounds列の値をほぼ均等な母集団のグループに分割する値のリスト
correlation物理的な行順序と列値の論理的な順序との間の統計的な相関関係

参考：53.29. pg_stats

データベースオブジェクト統計操作関数

関数名説明
pg_restore_relation_statsテーブルレベルの統計情報を更新
pg_restore_attribute_stats列レベルの統計情報を作成または更新

参考：9.28. System Administration Functions

restore考察

リストアフローを分解し、各所の実行時間を洗い出す。※環境変数設定・スキーマ作成は含まない

  • テーブル・カラム統計情報更新、インデックス統計情報更新は純増している。
  • インデックス作成時にも実行時間の増加傾向が見受けられる。★今後の検証課題とする。
バージョン
パターン		TABLE
作成
(ms)		データ
作成
(ms)		統計情報
更新
TABLE
(ms)		INDEX
作成
(ms)		統計情報
更新
INDEX
(ms)
PosgreSQL16
10テーブル		82.9829950.638-9092.244-
PosgreSQL16
50テーブル		316.99558,569.127-54,412.186-
PosgreSQL16
100テーブル		746.560133,915.813-183,468.043-
PosgreSQL16
10テーブル		58.6798059.094527.81310846.5125.288
PosgreSQL16
50テーブル		286.43663196.114989.952131313.68639.404
PosgreSQL16
100テーブル		640.463134743.7671298.477204548.49563.719

検証①結論

  • pg_dumpに統計情報を含む形でも実行時間・バックアップファイルサイズが著しく増加することはないため、今後問題なく活用できると考える

検証②：オブジェクト・データ・統計情報をリストアすれば、バックアップ元の実行計画は再現される？

オブジェクト・データ・統計情報が再現されれば、理論上プランナは同じ実行計画を生成すると想定できる。
しかし、あくまで理論上であるため、検証を行います。

検証条件

シナリオ

以下シナリオの②と⑤の実行計画を比較する。

①オブジェクト作成・データ作成・統計情報を取得
②実行計画を取得
③論理バックアップ（対象：スキーマ・オブジェクト・データ） を取得
④スキーマを削除・バックアップファイルをリストア
⑤実行計画を取得

テスト用オブジェクト

バックアップ・リストア対象として用意するオブジェクトは以下とします。
なお、データは1テーブルあたり100万件とします。

テーブル（table01）

カラム名 (列名)データ型PKNULL許容カーディナリティ
id01bigint〇NO1,000,000
id02textNO1,000,000
id03textNO1,000,000
id04textNO1,000,000
id04textNO1,000,000

インデックス

インデックス名データ型
table01_idx1id02

クエリ
table01_idx1を利用するようなクエリとします。

select*from table01where id02='1';
検証手順
  1. データベース作成
create database {データベース名};
  1. スキーマ作成
create schema {スキーマ名};
  1. テーブル・PK作成
    意図しないタイミングで統計情報が更新されるのを避けるため、テーブルレベルでautovacuumを無効化しています。
createtable {テーブル名} (id01bigintnotnull , id02 textnotnull , id03 textnotnull , id04 textnotnull , id05 textnotnull)with(autovacuum_enabled=false, toast.autovacuum_enabled=false);
altertable table01addconstraint pk_table01primary key (id01);
  1. データ作成
insertinto table01 (select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000));
  1. 統計情報取得
    autovacuumを無効化しているため、バックアップ対象となる統計情報を取得させます。
analyze {対象オブジェクト全てを指定};
  1. 実行計画取得
explain analyzeselect*from table01where id02='1';
  1. pg_dump
    windows上にインスタンスを立てているため、Powershellよりpg_dumpを実施しています。
    今回は論理バックアップの内容がわかるよう平文にてバックアップを取得します。
powershell -Command"Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose --statistics }"
  1. スキーマ削除
drop schema {スキーマ名} cascade;
  1. リストア
    windows上にインスタンスを立てているため、PowershellよりDBにログインし、リストアを実施しています。
psql -h {ホスト名} -p {ポート} -U {ユーザ名} -d {データベース名}
\i {バックアップファイル名};
  1. 実行計画取得
explain analyzeselect*from table01where id02='1';

結果

実行計画は概ね同様であると考える。

ただ、同一のオブジェクト・データ・統計情報であるため、再現したわけではなく、ただ同じ条件下で、同じ実行計画を生成したとも考えられる。
よって、バックアップ元の実行計画が再現されているとは言い切れないと判断する。

②バックアップ元実行計画

Index Scan using table01_idx1 on table01  (cost=0.42..8.44 rows=1 width=32) (actual time=0.038..0.039 rows=1.00 loops=1)
  Index Cond: (id02 = '1'::text)
  Index Searches: 1
  Buffers: shared hit=4
Planning:
  Buffers: shared hit=34
Planning Time: 0.107 ms
Execution Time: 0.060 ms

⑤リストア先実行計画

Index Scan using table01_idx1 on table01  (cost=0.42..8.44 rows=1 width=32) (actual time=0.080..0.081 rows=1.00 loops=1)
  Index Cond: (id02 = '1'::text)
  Index Searches: 1
  Buffers: shared read=4
Planning:
  Buffers: shared hit=27 read=1
Planning Time: 1.288 ms
Execution Time: 0.109 ms

検証②追加検証：データ状況の異なるテーブルに統計情報のみリストアすれば、バックアップ元の実行計画は再現される？

バックアップ元のデータ状況とは異なるテーブルに統計情報のみをリストアした場合、理論上はバックアップ元の統計情報を基に実行計画を生成する。

検証条件

シナリオ

以下シナリオの②と⑤と⑦の実行計画を比較する。

①オブジェクト作成・データ作成・統計情報を取得
②実行計画を取得
③論理バックアップ（対象：スキーマ・オブジェクト・データ） を取得
④スキーマを削除・バックアップファイルをリストア
⑤実行計画を取得
⑥統計情報を生成
⑦実行計画を取得

テスト用オブジェクト

今回は統計情報のみバックアップ・リストアするため、バックアップ元・リストア先のテーブル・データそれぞれ用意します。

バックアップ元のテーブル（table01）
データ：2件

カラム名 (列名)データ型PKNULL許容カーディナリティ
id01bigint〇NO2
id02textNO2
id03textNO2
id04textNO2
id04textNO2

リストア元のテーブル（table01）
データ：100万件

カラム名 (列名)データ型PKNULL許容カーディナリティ
id01bigint〇NO2
id02textNO2(内、999,999件の値が1）
id03textNO2
id04textNO2
id04textNO2

インデックス

インデックス名データ型
table01_idx1id02

クエリ
table01_idx1を利用するようなクエリとします。

select*from table01where id02='1';
検証手順
  1. データベース作成
create database {データベース名};
  1. スキーマ作成
create schema {スキーマ名};
  1. テーブル・PK作成
    意図しないタイミングで統計情報が更新されるのを避けるため、テーブルレベルでautovacuumを無効化しています。
createtable {テーブル名} (id01bigintnotnull , id02 textnotnull , id03 textnotnull , id04 textnotnull , id05 textnotnull)with(autovacuum_enabled=false, toast.autovacuum_enabled=false);
altertable table01addconstraint pk_table01primary key (id01);
  1. データ作成
insertinto table01 (select generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000),generate_series(1,1000000));
  1. 統計情報取得
    autovacuumを無効化しているため、バックアップ対象となる統計情報を取得させます。
analyze {対象オブジェクト全てを指定};
  1. 実行計画取得
explain analyzeselect*from table01where id02='1';
  1. pg_dump
    windows上にインスタンスを立てているため、Powershellよりpg_dumpを実施しています。
    今回は論理バックアップの内容がわかるよう平文にてバックアップを取得します。
powershell -Command"Measure-Command { pg_dump -f {バックアップファイル名} -h {ホスト名} -p {ポート} -U {ユーザ名} -n {スキーマ名} -d {データベース名} -W --format=p -E "UTF8" --verbose --statistics-only }"
  1. スキーマ削除
drop schema {スキーマ名} cascade;
  1. リストア
    windows上にインスタンスを立てているため、PowershellよりDBにログインし、リストアを実施しています。
psql -h {ホスト名} -p {ポート} -U {ユーザ名} -d {データベース名}
\i {バックアップファイル名};
  1. 実行計画取得
explain analyzeselect*from table01where id02='1';
  1. 統計情報取得
analyze {対象オブジェクト全てを指定};
  1. 実行計画取得
explain analyzeselect*from table01where id02='1';

結果

  • ⑤リストア先実行計画（統計情報取得前）を見る限り、プランナは取得されるのは1件であると想定している。しかし、それに対して実際は999,999件取得できているため、indexscanに切り替えて実行している。
    想定通り、バックアップ元の統計情報に基づいて、実行計画を生成するしている様子がうかがえる。

②バックアップ元実行計画

Seq Scanon table01  (cost=0.00..1.02rows=1 width=16) (actualtime=0.022..0.023rows=1.00 loops=1)
Filter: (id02='1'::text)
Rows RemovedbyFilter:1
  Buffers: shared hit=1
Planning:
  Buffers: shared hit=27 read=1
PlanningTime:1.491 ms
ExecutionTime:0.039 ms

⑤リストア先実行計画（統計情報取得前）

Index Scanusing table01_idx1on table01  (cost=0.41..8.43rows=1 width=16) (actualtime=0.678..181.715rows=999999.00 loops=1)
  Index Cond: (id02='1'::text)
  Index Searches:1
  Buffers: shared hit=7352 read=844
Planning:
  Buffers: shared hit=36 read=1
PlanningTime:0.794 ms
ExecutionTime:215.249 ms

⑦リストア先実行計画（統計情報取得後）

Seq Scanon table01  (cost=0.00..19852.00rows=1000000 width=28) (actualtime=0.019..93.264rows=999999.00 loops=1)
Filter: (id02='1'::text)
Rows RemovedbyFilter:1
  Buffers: shared hit=7352
Planning:
  Buffers: shared hit=34 dirtied=2
PlanningTime:1.653 ms
ExecutionTime:125.505 ms

検証②結論

  • 追加検証を見ても、バックアップ元の統計情報がリストア先に反映されていると判断できる

結論

  • pg_dumpに統計情報を含むことによる劣化はなく、問題なく活用できる
  • 統計情報のバックアップ・リストア自体も問題なく機能している
  • あくまで論理バックアップであり、デッドタプル等の物理的な部分まで再現できるわけではないため、リストア先で問題がなかったからバックアップ元でも問題ないと言い切るべきではない
  • ただし、従来のpg_dumpによるバックアップ・リストアと比較して、より高い精度でバックアップ元環境を再現できるようになっているため、”検証” を行う上ではより有用性の高い機能になっていると考える

目次

  1. 検証①：pg_dumpに統計情報を含むことによる影響は？
    1. 検証条件
      1. シナリオ
      2. テスト用オブジェクト
    2. 検証①結果
      1. 実行時間
      2. バックアップファイルサイズ
      3. 反省点（まずは反省から。今後、アップデートしていきます）
      4. pg_dump考察
      5. restore考察
    3. 検証①結論
  2. 検証②：オブジェクト・データ・統計情報をリストアすれば、バックアップ元の実行計画は再現される？
    1. 検証条件
      1. シナリオ
      2. テスト用オブジェクト
    2. 結果
      1. ②バックアップ元実行計画
      2. ⑤リストア先実行計画
  3. 検証②追加検証：データ状況の異なるテーブルに統計情報のみリストアすれば、バックアップ元の実行計画は再現される？
    1. 検証条件
      1. シナリオ
      2. テスト用オブジェクト
    2. 結果
      1. ②バックアップ元実行計画
      2. ⑤リストア先実行計画（統計情報取得前）
      3. ⑦リストア先実行計画（統計情報取得後）
    3. 検証②結論
      1. 結論

カテゴリー

[8]ページ先頭
©2009-2025 Movatter.jp