home-docker/scripts/batch_delete.sql

-- First, create indexes if they don't exist
CREATE INDEX IF NOT EXISTS idx_view_records_created_at ON view_records(created_at);
CREATE INDEX IF NOT EXISTS idx_view_records_id ON view_records(id);

-- Analyze the table to update statistics
ANALYZE view_records;

\echo 'Starting optimized batch deletion...'

DO $$
DECLARE
  batch_size INTEGER := 50000; -- Increased batch size
  min_id INTEGER;
  max_id INTEGER;
  current_id INTEGER;
  deleted INTEGER;
  total_deleted INTEGER := 0;
  start_time TIMESTAMP;
  batch_start_time TIMESTAMP;
BEGIN
  start_time := clock_timestamp();
  
  -- Get ID range to work with
  SELECT MIN(id), MAX(id) INTO min_id, max_id
  FROM view_records
  WHERE created_at <= '2024-03-20';
  
  RAISE INFO 'Starting batch deletion process at %', start_time;
  RAISE INFO 'Processing ID range: % to %', min_id, max_id;
  
  current_id := min_id;
  
  -- Disable autovacuum temporarily for this table
  ALTER TABLE view_records SET (autovacuum_enabled = false);
  
  LOOP
    EXIT WHEN current_id > max_id;
    
    batch_start_time := clock_timestamp();
    
    -- Delete in ID ranges for better performance
    DELETE FROM view_records
    WHERE id >= current_id
      AND id < current_id + batch_size
      AND created_at <= '2024-03-20';
    
    GET DIAGNOSTICS deleted = ROW_COUNT;
    total_deleted := total_deleted + deleted;
    
    RAISE INFO 'Batch stats: % records deleted, % total deleted. Batch took % seconds. Current ID: %', 
      deleted,
      total_deleted,
      EXTRACT(EPOCH FROM (clock_timestamp() - batch_start_time))::INTEGER,
      current_id;
    
    current_id := current_id + batch_size;
    
    COMMIT;
  END LOOP;
  
  -- Re-enable autovacuum
  ALTER TABLE view_records SET (autovacuum_enabled = true);
  
  -- Final vacuum to reclaim space
  RAISE INFO 'Running VACUUM ANALYZE...';
  VACUUM ANALYZE view_records;
  
  RAISE INFO 'Deletion complete! Total records deleted: %. Total time: % seconds',
    total_deleted,
    EXTRACT(EPOCH FROM (clock_timestamp() - start_time))::INTEGER;
END $$;
  END LOOP;
  
  RAISE NOTICE 'Deletion complete! Total records deleted: %. Total time: % seconds',
    total_deleted,
    EXTRACT(EPOCH FROM (clock_timestamp() - start_time))::INTEGER;
END $$;