

PostgreSQL のデータ削除に関して、大量のデータの削除が出来ず困っています。

毎日2万件ほどデータが追加されるアプリケーションで、ストレージを圧迫しないようある程度過去のデータはcronを使用して定期的に削除をしています。  
削除はpsqlコマンドを使用し、SQLを直接実行しています。  
テーブルはT1,T2,T3があり、設計は以下のようになっています。

  * リレーションはT1:T2,T1:T3 が1:Nの関係になっています。
  * インデックスは作成しておりません。
  * 各テーブルのidをプライマリーキーに設定しています。
  * `created_at` はtimestamp型です。

[![E-R図](https://i.stack.imgur.com/obEhO.png)](https://i.stack.imgur.com/obEhO.png)

削除では、T1,T2のデータを削除するが、T3に存在する場合は削除しないように、以下のSQLで実行しています。  
T2の削除は行えるのですが、T1だけなぜか3時間以上経過しても終了していませんでした。  
この時のT1で削除対象となっていたレコードは40万件ほどありました。

[code]

    WITH delete_id (
      SELECT 
        id
      FROM
        T1
      WEHRE
        id NOT IN (
          SELECT
            T1_id
          FROM
            T3
        ) AND
        created_at < "2023-01-16"
    )
    DELETE FROM
      T2
    WHERE
      T1_id IN (
        SELECT
          id
        FROM
          delete_id 
      )
    
    DELETE FROM 
      T1
    WHERE 
      id NOT IN (
        SELECT id
        FROM T3
      ) AND
      created_at < "2023-01-16"
    
[/code]

また、`created_at``T1_id`にインデックスを作成していないためかと考えたのですが、DETELE⇒SELECT
に変えた場合数秒でクエリが実行できたので、インデックスが原因ではないと考えています。

[code]

    WITH delete_id (
      SELECT 
        id
      FROM
        T1
      WEHRE
        id NOT IN (
          SELECT
            T1_id
          FROM
            T3
        ) AND
        created_at < "2023-01-16"
    )
    DELETE FROM
      T2
    WHERE
      T1_id IN (
        SELECT
          id
        FROM
          delete_id 
      )
    
    SELECT
      * 
    FROM 
      T1
    WHERE 
      id NOT IN (
        SELECT id
        FROM T3
      ) AND
      created_at < "2023-01-16"
    
[/code]

環境  
PostgreSQL 12


CC BY-SA 4.0
2023-02-16T12:54:27.233
93874
2023-03-13T00:42:05.490
2023-02-16T14:27:51.577
57138
57138
question
sql postgresql
PostgreSQL で大量のデータ削除を行いたい


状況を把握するために[`EXPLAIN`](https://www.postgresql.jp/document/14/html/sql-
explain.html)を実行して、どのような実行計画になっているか確認するのが良いと思います。  
(可能であれば、`EXPLAIN (ANALYZE)` を実行したいですが、実際にSQL文を実行する必要があるので、ひとまずは`EXPLAIN`だけでも)

シーケンシャルアクセス等、不自然な実行計画が出力されるようなら、統計情報がおかしいことが考えられるので、[`pg_stat_user_tables`](https://www.postgresql.jp/document/14/html/monitoring-
stats.html)を確認する、統計がおかしいなら[`VACUUM
ANALYZE`](https://www.postgresql.jp/document/14/html/sql-
vacuum.html)を実行することを検討するのが良いと思います。


CC BY-SA 4.0
2023-02-19T04:59:39.523
93905
2023-02-19T04:59:39.523
20098
93874
answer


まず可能性が高い

  * ただ処理に時間がかかっている
  * ロックが外れるのを待っているかなにかで止まっている

を切り分けましょう。簡単には`top`やパフォーマンスモニタで高い負荷がかかっている様子が見えたら前者、そうでなければ後者が疑わしいということになります。


CC BY-SA 4.0
2023-02-19T06:38:17.573
93907
2023-02-19T06:38:17.573
5793
93874
answer


PostgreSQLでも同様かは分かりませんがOracleの場合で  
(NOT) IN句を(NOT) EXISTS句に書換を行った場合に  
かなりのパフォーマンス改善した経験があります。  
特にデータ量が多いようなので試してみてはいかがでしょうか。  
使用する際はNULLには気を付けてください。


CC BY-SA 4.0
2023-03-13T00:42:05.490
94145
2023-03-13T00:42:05.490
29342
93874
answer