Re: Need an idea to operate massive delete operation on big size table. - Mailing list pgsql-admin

1. **Batch Deletion**: Delete in small chunks using `LIMIT` or `CTE`.
   ```sql
   DELETE FROM your_table WHERE <condition> LIMIT 10000;
   ```

2. **Drop and Recreate Indexes**: Drop indexes before deletion, then recreate them.
   ```sql
   DROP INDEX idx_name;
   DELETE FROM your_table WHERE <condition>;
   CREATE INDEX idx_name ON your_table(column_name);
   ```

3. **Partitioning**: Use table partitioning to drop entire partitions instead of deleting rows.
   ```sql
   DROP TABLE your_table_partition_name;
   ```

4. **Archive and Truncate**: Copy rows to keep into a temp table, truncate the original, then reinsert.
   ```sql
   CREATE TABLE temp_table AS SELECT * FROM your_table WHERE <condition_to_keep>;
   TRUNCATE TABLE your_table;
   INSERT INTO your_table SELECT * FROM temp_table;
   DROP TABLE temp_table;
   ```

5. **Disable Triggers**: Temporarily disable triggers during deletion.
   ```sql
   ALTER TABLE your_table DISABLE TRIGGER ALL;
   DELETE FROM your_table WHERE <condition>;
   ALTER TABLE your_table ENABLE TRIGGER ALL;
   ```

6. **Optimize Settings**: Increase `work_mem` and `maintenance_work_mem` for the session.
   ```sql
   SET work_mem = '1GB';
   SET maintenance_work_mem = '2GB';
   ```

7. **Vacuum and Analyze**: Clean up after deletion.
   ```sql
   VACUUM ANALYZE your_table;
   ```

8. **Soft Deletes**: Use an `is_deleted` flag to mark rows for later cleanup.

By batching, partitioning, and optimizing settings, you can make large deletes faster and less disruptive.