PostgreSQL :大きなテーブルの更新方法

高度なオープンソースデータベース管理システムPostgreSQLの大きなテーブルの更新は簡単ではありません.あなたが列を追加したり、列の種類を変更するなど、これらの単純な操作を見つける行の数百万のテーブルを持っている場合は、タイムリーに行うには難しいです.
ダウンタイムなしでこれらの種類の操作を行うことはさらに困難な課題です.このブログ記事では、大きなデータセットを管理しながら、テーブルの可用性に対する影響を最小限にするためのガイドラインと戦略を概説します.

PostgreSQLテーブル更新の一般的なガイドライン
列内の値を更新すると、Postgresは新しい行をディスクに書き込み、古い行を非推奨にしてから、すべてのインデックスを更新します.このプロセスは、リソースのかなりの量を取る各行に対するINSERTプラスDELETEと等価です.
この他に、大きなテーブルを更新するときに知っておくべきことのリストがあります.

これは、高速から1つの行を更新するよりも新しいテーブルを作成する高速です.シーケンシャル書き込みは、疎な更新よりも高速です.また、最後に死んだ行を取得しないでください.

テーブルの制約とインデックスは、すべての書き込みを大幅に遅らせる.可能であれば、更新プログラムを実行し、最後にそれらを再現しながら、すべてのインデックス、トリガ、および外部キーをドロップする必要があります.

デフォルト値なしでnull可能な列を加えることは安い操作です.列の実際のデータを書き込むには高価な部分です.

トーストに格納されたデータは、行が更新されたときに書き直されません

いくつかのデータ型の間での変換はPostgres 9.2以降の完全なテーブル書き換えを必要としません.: varchar ( 32 )からvarchar ( 64 )への変換.

PostgreSQLのテーブル更新の戦略
これを念頭に置いて、PostgreSQLのテーブル内の多数の行を効果的に更新するためのいくつかの戦略を見てみましょう.

インクリメンタルアップデート
たとえば、シーケンシャルIDを使用してデータをセグメント化する場合は、バッチでインクリメンタルに行を更新できます.あなたが時間の短い期間のロックを維持する必要があるので、これはあなたのテーブルの可用性を最大化します.新しい列を追加するとき、一時的にnullableとして設定することができます.
このアプローチの主な問題はパフォーマンスです.場所の更新が高価であるので、それは非常に遅いプロセスです.また、移行中により複雑なアプリケーションロジックを必要とする可能性があります.

2 .新規表の作成
大きなテーブルを更新する最速の方法は、新しいものを作成することです.
場合は、既存のテーブルを安全にドロップし、十分なディスク領域がある場合.次に、更新を実行する最も簡単な方法は、新しいテーブルにデータを挿入し、その後に名前を変更することです.以下に、この操作の基本構造を示すスクリプトを示します.

CREATE TABLE new_tbl
(
  field1 int,
  field2 int,
  ...
);
INSERT INTO new_tbl(field1, field2, ...)
(
  SELECT FROM ... -- use your new logic here to insert the updated data
)
CREATE INDEX -- add your constraints and indexes to new_tbl
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

既存のテーブルの再作成
上記の最適化でさえ、PostgreSQLのテーブルを再作成するのは、遅い操作です.ライブデータベースでクエリを実行している場合は、同時書き込み要求を処理する必要があります.
これを行う最も簡単な方法は、トランザクション中にテーブルの共有ロックを強制することです.

LOCK TABLE tbl IN SHARE MODE;

すべての書き込み要求はロックが解除されるまで待ちます.タイムアウトしなかったリクエストは、元の親テーブルが削除されなかった場合にトランザクションが終了すると実行されます.ただし、同じ名前のテーブルを作成しても、テーブルoidを使用するため、リクエストが失敗します.
あなたの書き込み要求の性質に応じて、また、カスタムを作成することができます申し込むrules 変更を保存するにはたとえば、データ移行を開始する前に削除された行を記録する規則を設定できます.

CREATE RULE deleted_rule AS ON DELETE
TO tbl
DO INSERT INTO tbl_deletes VALUES
(
  OLD.id
);

移行が終了すると、tblCountからIDを読み込み、新しいテーブルで削除するだけです.同様の方法を使用して他の種類のリクエストを処理できます.

結論
一度特定のサイズに達すると、一度瞬時にされた操作を準備し、実行するには数時間かかることができます.Codacyで、我々は毎分何千もの書き込み要求を受けます、そして、我々は数百ギガバイトのデータを管理します.新機能を開発し、有用性を損なうことなく、データベースのパフォーマンスを向上させる挑戦は、我々は毎日解決しようとする挑戦です.この記事は、これらの問題を扱っている間に学んだことの一部を含んでいます.
The Postgresql documentation とstack exchange answers PostgreSQLの詳細についてはもっと詳しい情報が必要です.
質問をしてコメントのセクションでの提案を自由に感じ、我々はスケールで作業中に物事を行うのより良い方法を学ぶのが大好きです.