PostgreSQL でテーブルのレコード件数を誤差なく確認する方法

バックアップからデータをリカバリした際やデータ移行後など、テーブルのレコード件数を確認して作業が無事に完了しているかどうかを判断することがあります。
本記事では、pgbench で作成されるテーブルを例にして、PostgreSQL でテーブルのレコード件数を確認する方法を紹介します。

テーブルの確認方法

PostgreSQL でテーブル件数を確認するには下記の 3 通りの方法があります。

1. COUNT 関数を実行する
2. pg_class を確認する
3. pg_stat_user_tables を確認する

結論としては

• pg_class, pg_stat_user_tables は推定値
• 正確な件数を確認したい場合は COUNT 関数を実行する
• テーブルのレコード件数によっては、COUNT 関数の実行に時間がかかる

となります。
公式ドキュメントにもあるように pg_class や pg_stat_user_tables については推定値なので、実際のレコード件数と誤差が発生する可能性があります。
正確な件数を正しく知りたい場合には、やはり COUNT 関数を実行するしかなさそうです。

下記で、それぞれの方法について、pgbench のテスト用テーブルを利用して確認していきます。

実際に検証

検証用テーブルの作成

まずは pgbench のテスト用テーブルの作成です。
pgbench コマンドに -i オプションをつけて実行することで、テスト用のテーブル群を作成できます。
-s オプションでテーブルの行数（の倍率）を指定できます。

$ pgbench -i -s 100
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
2022-08-27 18:00:16.083 JST [98046] LOG:  checkpoints are occurring too frequently (10 seconds apart)
2022-08-27 18:00:16.083 JST [98046] HINT:  Consider increasing the configuration parameter "max_wal_size".
10000000 of 10000000 tuples (100%) done (elapsed 8.02 s, remaining 0.00 s)
vacuuming...
creating primary keys...
2022-08-27 18:00:23.963 JST [98046] LOG:  checkpoints are occurring too frequently (7 seconds apart)
2022-08-27 18:00:23.963 JST [98046] HINT:  Consider increasing the configuration parameter "max_wal_size".
done in 13.06 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 8.09 s, vacuum 2.66 s, primary keys 2.30 s).

• -s に 100 を指定したので、「10000000 of 10000000 tuples (100%) done」 のとおり、1,000 万行 のレコードが作成されました。

COUNT 関数で確認

postgres=# SELECT COUNT(1) FROM pgbench_accounts;
  count   
----------
 10000000
(1 row)

• COUNT 関数では問題なく 1,000 万件と表示されました。

pg_class を確認

postgres=# SELECT relname, reltuples::INTEGER FROM pg_class WHERE relname = 'pgbench_accounts';
     relname      | reltuples 
------------------+-----------
 pgbench_accounts |  10000000
(1 row)

• pg_class の reltuples がテーブルの有効行数を表します。
• reltupels も問題なく 1,000 万件と表示されました。

pg_stat_user_tables を確認

postgres=# SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts';
     relname      | n_live_tup 
------------------+------------
 pgbench_accounts |   10000035
(1 row)

• pg_stat_user_tables の n_live_tup がテーブルの有効行数を表します。
• n_live_tup は 1,000 万 35 件と表示されました。

pgbench_accounts を更新したらどうなるか

早速、pg_stat_user_tables の n_live_tup は、正確な値とはなりませんでしたが、同じく推定値である pg_class の reltuples は、正確な値でした。
次に pgbench_accounts を更新して、それぞれの値がどうなるかを確認します。

pgbench_accounts から 9,999 件のデータを削除する

postgres=# DELETE FROM pgbench_accounts WHERE aid < 10000;
DELETE 9999

• pgbench_accounts から 9,999 件のデータを削除しました。

COUNT 関数で確認

postgres=# SELECT COUNT(1) FROM pgbench_accounts;
  count  
---------
 9990001
(1 row)

• COUNT 関数では 無事に 1,000 万件 - 9,999 件 = 9,990,001 件のレコード件数が表示されました。

pg_class を確認

次に pg_class を確認します。

postgres=# SELECT relname, reltuples::INTEGER FROM pg_class WHERE relname = 'pgbench_accounts';
     relname      | reltuples 
------------------+-----------
 pgbench_accounts |  10000035
(1 row)

• pg_class の reltuples の値が、なぜか 1,000 万 35 件となりました。

pg_stat_user_tables を確認

同じく pg_stat_user_tables も確認します。

postgres=# SELECT relname, n_live_tup::INTEGER FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts';
     relname      | n_live_tup 
------------------+------------
 pgbench_accounts |   10000035
(1 row)

• pg_stat_user_tables の n_live_tup の値は、変わらず 1,000 万 35 件です。

結果

DELETE 前後の件数を表にまとめると、下記となります。

pg_stat_user_tables の値が DELETE 前後で変わっていない理由は、pg_stat_user_tables の値は ANALYZE で更新されるためです（pg_class の値が変わっている理由は不明ですが・・）。
というわけで早速 VACUUM ANALYZE して、値がどう変化するかを確認します。

統計情報を更新するため VACUUM ANALYZE を実行

postgres=# VACUUM ANALYZE pgbench_accounts ;
VACUUM

pg_class を確認

VACUUM ANALYZE で pg_class の値がどう変化したのかを確認します。

postgres=# SELECT relname, reltuples::INTEGER FROM pg_class WHERE relname = 'pgbench_accounts';
     relname      | reltuples 
------------------+-----------
 pgbench_accounts |   9991035
(1 row)

• ANALYZE の結果、pg_class の値が 9991035 に更新されました。

pg_stat_user_tables を確認

同じく pg_stat_user_tables も確認します。

postgres=# SELECT relname, n_live_tup::INTEGER FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts';
     relname      | n_live_tup 
------------------+------------
 pgbench_accounts |    9991035
(1 row)

• pg_stat_user_tables の値も ANALYZE の結果、9991035 に更新されました。

値は更新されましたが、pg_class も pg_stat_user_tables も正確なレコード件数ではありませんでした。

ANALYZE のサンプリング精度が原因

pgbench_accounts の正しいレコード件数は 9990001 ですので、pg_class および pg_stat_user_tables ともに値が正確ではありません。
ANALYZE を実行しても正しいレコード数とならないのは、その ANALYZE のサンプリング精度に理由があります。
公式ドキュメントの ANALYZE の説明(https://www.postgresql.jp/document/current/html/sql-analyze.html) に

巨大なテーブルでは、ANALYZEは、全ての行を検査するのではなく、テーブルの中からランダムにサンプルを取り出して使用します。

とあるとおり、巨大なテーブルではレコード行数についても推定値となります。

このことについて確証を得るために、サンプリング精度を高くして再度レコード件数を確認します。

ANALYZE のサンプリング精度を変更

ANALYZE のサンプリング精度は default_statistics_target パラメータで制御されています。

postgres=# SET default_statistics_target = 10000;
SET

• データベース全体の ANALYZE のサンプリング精度を最大値の 10000 としました。

設定後のサンプリング精度で ANALYZE を実行

この状態でもう一度 ANALYZE をしてからレコード件数を確認します。

postgres=# ANALYZE pgbench_accounts;
ANALYZE

• サンプリング精度を高くしたので、ANALYZE の実行時間も長くなります。

pg_class と pg_stat_user_tables を確認

postgres=# SELECT relname, n_live_tup::INTEGER FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts';
     relname      | n_live_tup 
------------------+------------
 pgbench_accounts |    9990001
(1 row)

postgres=# SELECT relname, reltuples::INTEGER FROM pg_class WHERE relname = 'pgbench_accounts';
     relname      | reltuples 
------------------+-----------
 pgbench_accounts |   9990001
(1 row)

• pg_class および pg_stat_user_tables の両方で、無事に正確なレコード数が表示されるようになりました。

まとめ

以上が PostgreSQL でテーブルのレコード件数を確認するための方法の紹介となります。
pg_class の reltuples や pg_stat_user_tables の n_live_tup は、あくまで推測値となりますので、テーブルが巨大になればなるほど、正確なレコード数と乖離するようになります。
レコード件数の少ないテーブルでは pg_class や pg_stat_user_tables を確認するだけで問題ないと思いますが、実行時間がかかっても COUNT 関数を使って確認するのが理想です。
特にデータ移行が正しく完了しているかの判断基準にする際など、正確なレコード件数の確認が求められる際には、COUNT 関数を実行する方が安全です。