質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Q&A

解決済

4回答

23330閲覧

PostgreSQLで大量Delete

redara

総合スコア344

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

0グッド

1クリップ

投稿2017/04/05 01:26

PostgreSQLで数百万件のデータをSQL文で削除したいと思ったのですが、カスケードしている関連テーブルやインデックスもあるため削除に時間が掛かって終わりません。処理中ロックも掛かっているようでDBサーバのCPU使用率も100%になってしまいます。インデックスを削除しても良いかもしれませんが、インデックスの削除自体にも時間が掛かりそうですし、その間検索のパフォーマンスが低下するのも避けたいです。

10件づつや1000件づつであれば削除処理が完了して返ってきますが、どちらも100時間くらい掛かりそうなペースです。
SQL文ではなくPHP側から処理を行っても削除速度は同じくらいです。

できれば数時間~10時間くらいで処理を完了させたいのですが、何か良い方法はあるでしょうか。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

SVC34

2017/04/05 01:58 編集

サービス停止時間を確保すること、もしくは予定されているサービス停止時間まで実施を待つことは可能でしょうか?
redara

2017/04/05 02:08

停止しないのがベストですが、良い方法があれば最悪何時間か停止させることも検討したいです。
guest

回答4

0

サービス停止が可能な場合、以下の手段が良いと思われます。

  1. バックアップを取得しておく
  2. 残すレコードをcopyコマンドでエクスポートして退避しておく(関連テーブルも含める)
  3. truncateをcascadeオプションつきで実行する
  4. copyコマンドで退避したデータを再度投入する

3の前にwal_levelをminimumにしておき、3と4を同じトランザクション内で実行すると、walの量が削減されより高速化が可能です。残すレコードの数が少なければ、保管用テーブルを作って退避しそこから普通にinsertでも良いでしょう。

14.4. データベースへのデータ投入

サービス停止が不可能な場合、トランザクションを分割して一定件数ずつcommitしながらdeleteしていくしかありません。

その際、対象レコードの抽出に索引が使用できず、残すレコードも大量に存在するケースでは、削除対象のレコードを見つけるまで残すレコードも含め何度も表スキャンを行うことになるため、処理がさらに遅くなります。この場合の対処方法として考えられるのは以下の2つです。

  1. 削除対象の抽出に使用できる索引を一時的に追加する(create indexにconcurrentlyオプションを指定すると更新をブロックせずに作成できる)
  2. cursorを使用したdeleteを行う(with holdオプションを指定することで、途中でcommitしてもcursorをクローズさせず、続きから処理継続できる)

1は索引の追加にもそれなりの時間を要すると思われることと、サービスのSQLの実行計画変動のリスクがあることから2の方が確実と思われます。

投稿2017/04/05 03:05

編集2017/04/05 03:14
SVC34

総合スコア1149

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

自己解決

一時的にサービスを停止させ、関連するCASCADEを一旦切ってから大量削除するという方法で
時間短縮することができ、解決いたしました。
回答ありがとうございました。

投稿2017/04/13 06:24

redara

総合スコア344

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

トランザクションをかけていないと(delete 1回ごとにcommitしていると)てきめんに遅いです。インデックスの再構築が激しく発生するためです。
かといって大量に delete して一気に commit すると、redo ログがあふれます。

  • 削除対象を全件一回でやるのではなく、1000件程度づつで行う
  • トランザクションを適時かける(1000回ごとにcommit~begin transaction など)

などをやるとだいぶ違います。

投稿2017/04/05 02:17

tacsheaven

総合スコア13703

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

全体の母数がいくつか分かりませんが、
必要なデータをdumpするか一時的なテーブルに退避させて、
truncate(drop)して、insertすると早くなると言われています。

あとは大量件数の場合、
auto commitでやったり、redoログ切ったりで全然違ったりもします。

投稿2017/04/05 02:08

szk.

総合スコア1400

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問