PostgreSQL教程（14）：データベースのメンテナンス

一、ディスク領域を復元する:
    PostgreSQLでは、deleteとudate文を使用して削除または更新されたデータ行は実際に削除されておらず、古いバージョンのデータ行の物理アドレスだけでその行の状態を削除または期限切れにしています。したがって、データテーブル内のデータの変化が非常に頻繁である場合、一定時間後には、テーブルによって占有される空間は大きくなるが、データ量は大きく変化しない可能性がある。この問題を解決するには、定期的にデータの変化が激しいデータテーブルに対してVACUUM動作を実行する必要があります。
    VCUUMコマンドには、VCUUMとVCUUM FULLの2つの形態があります。その違いは、次の表のようです。
 
VAICUUMがありません
VAICUUM
VCUUM FULL
大量のデータを削除した後
削除したデータの状態を削除したままにしておくだけで、この空間は記録し直して使用することができません。
削除された記録が表の端にある場合、占有された空間は物理的に解放され、オペレーティングシステムに返却されます。エンドデータでない場合、このコマンドは、指定テーブルまたはインデックスから削除されたデータの占有空間を利用可能な状態に再設定します。今後、新しいデータが挿入された場合は、再利用された空間が全部使い切るまで優先的にこの空間を使用して、新たな磁気ディスクページを使用することを考慮します。
削除されたデータがデータテーブルの終端にあるかどうかに関わらず、これらのデータが占有する空間は物理的に解放され、オペレーティングシステムに返却されます。後に新しいデータが挿入されると、新しいディスクページが割り当てられます。
実行効率
 
状態だけを操作にするので、効率が高いです。
現在のバージョンのPostgreSQL（v 9.1）では、指定されたテーブルまたはインデックスのためにデータファイルを再生成し、既存のファイルで利用可能なデータを新しいファイルに導入し、元のデータファイルを削除します。このため、現在のディスクはこの操作に使用できる空間が多いことが必要です。このことから、このコマンドの実行効率は比較的低い。
削除されたデータが占有する物理空間はオペレーティングシステムに再設計されているかどうか。
できません
できません
はい、
VALCUUMコマンドを実行する場合、このテーブルに対する他の動作を同時に実行できますか？
 
この操作は共有ロックであるため、他の動作と並行して行うことができる。
この操作は指定されたテーブルにロックを適用する必要があるので、この操作を実行する間、テーブルに基づいた操作はいずれも保留され、操作が完了したことが分かります。
おすすめの使い方
データクリア時には、truncateを使用して動作することができます。この操作は物理的にデータテーブルを空にし、その占有空間をオペレーティングシステムに直接返します。
データテーブルのディスクページ数を比較的安定した値に維持できるようにするために、毎日または毎週のようにデータの動作が比較的少ない期間に、定期的に実行することができる。
この操作のオーバーヘッドと他のエラーに対する反発を考慮して、定期的にデータ量の変化が大きいテーブルを監視し、ディスクページの占有量が臨界値に近いことを確認した場合にのみ、この操作を実行することを考慮することが推奨されている。それでも、データ動作が少ない時間帯をできるだけ選択して、この動作を完了させる必要があります。
実行後の他の操作の効率
クエリには、ディスクページの破片が大量に存在するため、効率は徐々に低下します。
VAICUUM動作を実行しないよりも効率が高いですが、挿入の効率は低下します。
この操作を実行した後、この表に基づく操作効率はすべて大きく向上します。
二、計画器の統計を更新する：
    PostgreSQLクエリ計画器は、最適なパスを選択するには、関連データテーブルの統計情報を参照し、クエリとして最も合理的な計画を生成する必要があります。これらの統計はANALYZE命令によって得られたもので、直接にこのコマンドを呼び出すことができます。あるいはVACUUM命令の中の任意のステップとして呼び出します。例えば、VACCUUM ANAYZE table able。name，このコマンドはまずVALUUMを実行してANALYZEを実行します。回収スペース（VACUUM）と同様に、データの更新が頻繁なテーブルに対して一定の頻度のANALYZEを保持することで、このテーブルの統計情報を常に比較的新しい状態にすることが、このテーブルに基づくクエリー最適化にとって極めて有利である。しかしながら、頻繁でないデータテーブルを更新するためには、この動作を実行する必要はない。
    私たちは特定のテーブル、さらにはテーブル内の特定のフィールドに対してANALYZE命令を実行することができます。このように、私たちは実際の状況に応じて、更新が頻繁な部分情報に対してだけANALYZE操作を実行することができます。これは統計情報によって占有される空間を節約するだけでなく、今回のANALYZE操作の実行効率を高めることもできます。ここでは、ANALYZEはかなり速い動作であり、データ量が大きいテーブルであっても、統計学上のランダムサンプリングの方法を用いて行サンプリングを行うので、各行のデータを読み込んで分析するのではなく、統計学上のランダムサンプリングを行う。したがって、このコマンドは、定期的にデータベース全体に対して実行されることが考えられます。
    実際には、下記のコマンドにより、指定されたフィールドのサンプリングレートを調整することもできます。
 

    ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200

 

    注意：この値の取値範囲は0-1000で、値が低いほどサンプリング比率が低くなり、分析結果の精度も低いですが、ANALYZEコマンドの実行速度はより速いです。この値を−1に設定すると、このフィールドのサンプリング比はシステムの現在のデフォルトのサンプル値に戻り、次のコマンドで現在のシステムのデフォルトサンプル値を取得することができます。
 

    postgres=# show default_statistics_target;

     default_statistics_target

    ---------------------------

     100

    (1 row)

 

    上記の結果から、このデータベースのデフォルトサンプル値は100（10%）であることがわかった。
三、VACUUMとANALYZEの例：
   

    #1.        。

    postgres=# CREATE TABLE testtable (i integer);

    CREATE TABLE

    #2.         。

    postgres=# CREATE INDEX testtable_idx ON testtable(i);

    CREATE INDEX

    #3.              。

    postgres=# CREATE OR REPLACE FUNCTION test_insert() returns integer AS $$

    DECLARE

        min integer;

        max integer;

    BEGIN

        SELECT COUNT(*) INTO min from testtable;

        max := min + 10000;

        FOR i IN min..max LOOP

            INSERT INTO testtable VALUES(i);

        END LOOP;

        RETURN 0;

    END;

    $$ LANGUAGE plpgsql;

    CREATE FUNCTION

    #4.           (    )

    postgres=# SELECT test_insert();

     test_insert

    -------------

               0

    (1 row)

    #5.            。

    postgres=# SELECT COUNT(*) FROM testtable;

     count

    -------

     40004

    (1 row)

    #6.      ，               PostgreSQL    。

    postgres=# ANALYZE testtable;

    ANALYZE

    #7.                  (       8k)。

    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

        relname       | relfilenode    | relpages

    ---------------+-------------+----------

     testtable        |       17601   |      157

     testtable_idx  |       17604   |       90

    #8.       。

    postgres=# DELETE FROM testtable WHERE i < 30000;

    DELETE 30003

    #9.   vacuum analyze，       ，              。

    #10.           ，                ，          ，

    #       where i > 10000，     VACUUM ANALYZE   ，           。

    postgres=# VACUUM ANALYZE testtable;

    ANALYZE

    #11.             ，   VACUUM ANALYZE            (    )。

    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

        relname      | relfilenode     | relpages

    ---------------+-------------+----------

     testtable        |       17601   |      157

     testtable_idx  |       17604   |       90

    (2 rows)

    #12.          ，               。

    postgres=# SELECT test_insert(); --    。

     test_insert

    -------------

               0

    (1 row)

    postgres=# ANALYZE testtable;

    ANALYZE

    #13.                            ，         

    #                 ，         ，                。

    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

        relname       | relfilenode    | relpages

    ---------------+-------------+----------

     testtable        |       17601   |      157

     testtable_idx  |       17604   |      173

    (2 rows)

    postgres=# SELECT test_insert();

     test_insert

    -------------

               0

    (1 row)

    postgres=# ANALYZE testtable;

    ANALYZE

    #14.                    。

    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

        relname      | relfilenode    | relpages

    ---------------+-------------+----------

     testtable        |       17601   |      157

     testtable_idx  |       17604   |      173

    (2 rows)

    #15.         。

    postgres=# DELETE FROM testtable WHERE i < 30000;

    DELETE 19996

    #16.           ，   VACUUM FULL    ，              

    #           ，                。

    postgres=# VACUUM FULL testtable;

    VACUUM

    postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

        relname      | relfilenode     | relpages

    ---------------+-------------+----------

     testtable        |       17602   |      118

     testtable_idx  |       17605   |       68

    (2 rows)

四、定期的にインデックスを再構築する：
    PostgreSQLでは、頻繁にデータを更新するためのデータテーブルの定期的な再構成インデックス（REINDEX INDEX）が必要です。B-Treeインデックスについては、完全に空のインデックスページだけが重複して使用されます。一部の空間でのみ使用可能なインデックスページは再利用されません。一つのページのほとんどの索引キーが削除され、わずかな部分だけが残ります。このページは解放されて再利用されません。このような極端な場合、各インデックスページの利用率が極めて低いため、データ量が著しく増加するとインデックスファイルが極めて膨大になり、クエリ効率が低下するだけでなく、ディスク領域全体が完全に満たされる危険性がある。
    再構成されたインデックスにはまた別の個性的なエネルギー的な利点があり、論理的に相互に接続されたページは物理的にも接続されているため、ディスクページが連続して読み込まれる確率が高くなり、動作全体のIO効率が向上します。次の例を参照してください
    #1.このテーブルには約6万件のデータが挿入されています。以下のSQL文はインデックスが持つディスク空間を調べます。   
 

    postgres=# SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx';

        relname     | size

    ----------------+------

     testtable_idx | 1240K

    (1 row)

    #2.             。

    postgres=# DELETE FROM testtable WHERE i > 20000;

    DELETE 50006

    #3.       ，      SQL              。

    postgres=# ANALYZE testtable;

    ANALYZE

    #4.           ，             ，          。

    postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;

     size

    ------

     1240K

    (1 row)

    #5.     。

    postgres=# REINDEX INDEX testtable_idx;

    REINDEX

    #6.                ，                 。

    postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;

     size

    ------

     368K

    (1 row)

    #7.           ，              。

    postgres=# ANALYZE testtable;

    ANALYZE

 五、ディスクの使用状況を観察する：
    1.データテーブルのディスクページ数を表示します。
 

    #relpages   VACUUM、ANALYZE   DDL    ， CREATE INDEX。          8K  。

    postgres=# SELECT relfilenode, relpages FROM pg_class WHERE relname = 'testtable';

     relfilenode | relpages

    -------------+----------

           16412 |       79

    (1 row)

    

    2.指定したデータテーブルのインデックス名とインデックスが占有しているディスクのページ数を表示します。
 

    postgres=# SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i

        WHERE c.relname = 'testtable' AND c.oid = i.indrelid AND c2.oid = i.indexrelid

        ORDER BY c2.relname;

        relname    | relpages

    ---------------+----------

     testtable_idx |       46

    (1 row)