7月 6, 2020
Articles
コメントはまだありません

大きなテーブルに新しい列を追加する最良の方法は？

Postgresには2.2GBのテーブルがあり、7,801,611行が含まれています。 uuid / guid列を追加していますが、その列にデータを入力するための最良の方法は何でしょうか（NOT NULL制約を追加したいため）。

Postgresを正しく理解している場合、更新は技術的には削除と挿入であるため、基本的に2.2GBのテーブル全体が再構築されます。また、スレーブが実行されているので、それが遅れないようにします。

時間の経過とともにゆっくりとデータを投入するスクリプトを作成するよりも良い方法はありますか？

コメント

  • すでにALTER TABLE .. ADD COLUMN ...を実行しましたか、それともその部分も回答する必要がありますか？
  • 実行していませんテーブルの変更はまだ計画段階にあります。以前は、列を追加してデータを入力し、制約またはインデックスを追加してこれを行っていました。ただし、このテーブルはかなり大きく、ロード、ロック、レプリケーションなどが心配です。 …

回答

設定と要件の詳細によって大きく異なります。

Postgres 11以降、揮発性DEFAULT列のみを追加することに注意してください。 div> は引き続きテーブルの書き換えをトリガーします。残念ながら、これはあなたのケースです。

If ディスクに十分な空き容量があります- pg_size_pretty((pg_total_relation_size(tbl)) -そして共有ロックをしばらくの間、排他ロックを非常に短時間使用してから、新しいテーブル uuid 列を含む CREATE TABLE AS を使用します。なぜですか？

以下のコードは、追加のuuid-ossモジュールの関数を使用しています。

  • SHAREモードでの同時変更に対してテーブルをロックします（引き続き同時読み取りを許可します）。テーブルに書き込もうとすると、待機して最終的に失敗します。以下を参照してください。

  • 新しい列にその場でデータを入力しながら、テーブル全体をコピーします。おそらく、テーブルにいるときに行を順番に並べます。
    If 行を並べ替える場合は、必ず work_mem をRAMで並べ替えるのに十分な高さまたは同じ高さに設定してください。余裕がある場合（グローバルではなく、セッション専用）。

  • 次に、制約、外部キー、インデックス、トリガーなどを新しいものに追加します。テーブル。テーブルの大部分を更新する場合、行を繰り返し追加するよりも、インデックスを最初から作成する方がはるかに高速です。 マニュアルの関連アドバイス

  • 新しいテーブルの準備ができたら、古いテーブルを削除して新しいテーブルの名前を変更しますドロップインの代替品にします。この最後のステップだけが、トランザクションの残りの部分で古いテーブルの排他ロックを取得します。これは今では非常に短いはずです。
    また、テーブルタイプ（ビュー、テーブルタイプを使用する関数）に応じてオブジェクトを削除する必要があります。署名で…）、後で再作成します。

  • 不完全な状態を回避するために、すべてを1つのトランザクションで実行します。

BEGIN; LOCK TABLE tbl IN SHARE MODE; SET LOCAL work_mem = "???? MB"; -- just for this transaction CREATE TABLE tbl_new AS SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order> FROM tbl ORDER BY ??; -- optionally order rows favorably while being at it. ALTER TABLE tbl_new ALTER COLUMN tbl_uuid SET NOT NULL , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1() , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid); -- more constraints, indices, triggers? DROP TABLE tbl; ALTER TABLE tbl_new RENAME tbl; -- recreate views etc. if any COMMIT;

これが最速のはずです。その場で更新する他の方法は、より高価な方法で、テーブル全体を書き直す必要があります。ディスクに十分な空き領域がない場合、またはテーブル全体をロックしたり、同時書き込みの試行でエラーを生成したりする余裕がない場合にのみ、そのルートに進みます。

同時書き込みはどうなりますか？

INSERT / UPDATE / DELETEを試行している他のトランザクション（他のセッション内）トランザクションがSHAREロックを取得した後、同じテーブルで、ロックが解放されるか、タイムアウトが開始されるまで、どちらか早い方で待機します。 fail どちらの方法でも、書き込みを試みていたテーブルが下から削除されているためです。

新しいテーブルには新しいテーブルがありますテーブルOIDですが、並行トランザクションはすでにテーブル名を前のテーブルのOIDに解決しています。ロックが最終的に解放されると、書き込み前にテーブル自体をロックしようとし、それを見つけます。」なくなった。Postgresは答えます：

ERROR: could not open relation with OID 123456

123456は古いテーブルのOIDです。その例外をキャッチし、それを回避するためにアプリコードでクエリを再試行する必要があります。

それを発生させる余裕がない場合は、元のテーブルを保持する必要があります。

既存のテーブルを保持し、代替案1

NOT NULL制約を追加する前に、適切な場所で更新します（おそらく、一度に小さなセグメントで更新を実行します）。 。 NULL値を持ち、NOT NULL制約のない新しい列を追加するのは安価です。
Postgres以降 9.2 CHECK制約を

：

制約は引き続き適用されます後続の挿入または更新に対して強制されます

これにより、行を更新できますpeuàpeu–複数の個別のトランザクション。これにより、行ロックを長時間保持することが回避され、デッド行を再利用することもできます。 （自動バキュームを開始するのに十分な時間がない場合は、手動でVACUUMを実行する必要があります。）最後に、NOT NULL制約を適用し、NOT VALID CHECK制約を削除します：

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID; -- update rows in multiple batches in separate transactions -- possibly run VACUUM between transactions ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL; ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null;

NOT VALIDの詳細：

既存のテーブルを保持し、代替案2

一時テーブルで新しい状態を準備します、 TRUNCATE 元の refill 。すべて 1つのトランザクションで。あなた SHAREロック 前を取得する必要がありますpr同時書き込みが失われないように新しいテーブルを準備します。

SOに関するこれらの関連する回答の詳細：

コメント

  • 素晴らしい答えです！まさに私が探していた情報。 2つの質問1.このようなアクションにかかる時間をテストする簡単な方法について何か考えがありますか？ 2.たとえば5分かかる場合、その5分間にそのテーブルの行を更新しようとするアクションはどうなりますか？
  • @CollinPeters：1。ライオン'の時間のシェアは、大きなテーブルのコピーに費やされ、場合によってはインデックスと制約の再作成に費やされます（状況によって異なります）。ドロップと名前の変更は安価です。テストするには、準備したSQLスクリプトをLOCKなしでDROPまで実行できます。私は野蛮で役に立たない推測しか発することができませんでした。 2.については、私の回答の補遺を検討してください。
  • @ErwinBrandstetterビューの再作成を続行します。したがって、テーブルの名前を変更した後も古いテーブル（oid）を使用するビューが多数ある場合。ビュー全体の更新/作成を再実行するのではなく、ディープリプレースを実行する方法はありますか？
  • @CodeFarmer：テーブルの名前を変更しただけの場合、ビューは名前が変更されたテーブルで機能し続けます。代わりに new テーブルを使用してビューを作成するには、新しいテーブルに基づいてビューを再作成する必要があります。 （また、古いテーブルを削除できるようにするためです。）それを回避する（実用的な）方法はありません。
  • 9.2postgresは'をスローしないためcould not open relation with OID 123456

回答

「最良の」回答はありませんが、かなり速く物事を成し遂げることができるかもしれない「最も悪い」答えがあります。

テーブルに2MM行があり、デフォルトで最初の2番目のタイムスタンプ列を追加しようとすると更新パフォーマンスが低下していました。 。

ALTER TABLE mytable ADD new_timestamp TIMESTAMP ; UPDATE mytable SET new_timestamp = old_timestamp ; ALTER TABLE mytable ALTER new_timestamp SET NOT NULL ;

40分間ハングした後、これにかかる時間を把握するために、これを小さなバッチで試しました。 8時間。

受け入れられた答えの方が間違いなく優れていますが、このテーブルは私のデータベースで頻繁に使用されています。FKEYが適用されるテーブルは数十あります。非常に多くのテーブルで外部キーを切り替えないようにしたかったのです。 。そして、ビューがあります。

ドキュメント、ケーススタディ、StackOverflowを少し検索すると、「A-Ha！」の瞬間がありました。コアのUPDATEではなく、すべてのINDEX操作にありました。私のテーブルには12個のインデックスがありました。いくつかは一意の制約用、いくつかはクエリプランナーの高速化用、そしていくつかは全文検索用です。

更新されたすべての行は、DELETE / INSERTだけでなく、各インデックスの変更と制約のチェックのオーバーヘッドも処理していました。

私の解決策は、すべてのインデックスを削除し、制約、テーブルを更新してから、すべてのインデックス/制約を追加し直します。

次のようなSQLトランザクションを作成するのに約3分かかりました。

  • BEGIN;
  • 削除されたインデックス/制約
  • テーブルの更新
  • インデックス/制約の再追加
  • COMMIT;

スクリプトの実行には7分かかりました。

受け入れられた回答は間違いなくより適切で適切です…そしてダウンタイムの必要性を事実上排除します。しかし、私の場合は、かなり多くの時間がかかりました。開発者」はそのソリューションを使用するために作業し、それを達成できるスケジュールされたダウンタイムの30分のウィンドウがありました。私たちのソリューションは10でそれに対処しました。

コメント

  • 言うまでもなく、同時書き込みが可能です。インデックスがないと、同時読み取りが遅くなる可能性があります。 sしかし:)他のオプションと比較してベンチマークを行うのは興味深いでしょう…

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です