Optuna の SQLite ストレージから最後の COMPLETE 以降の Trial を削除する

Optuna で、中断 / 失敗した Trial を SQLite ストレージから削除した上で探索を再開したいこともあると思います (残した方が記録にはなりますが)。本記事では、最後の COMPLETE 以降の Trial を削除するスクリプトを記します。

中断 Trial が残ると再開時に採番が進む

Optuna によるハイパーパラメータ探索を trial 9 まで試行するつもりが、Windows Update を先送りにしていたのを忘れており、trial 5 の最中に PC が落ちてしまった、ということはあると思います。このとき、SQLite ストレージの中身は以下のようになります。

- trial 0: COMPLETE
- trial 1: COMPLETE
- trial 2: COMPLETE
- trial 3: COMPLETE
- trial 4: COMPLETE
- trial 5: RUNNING

ここで、「5 回分は正常終了しているから、もう 5 回探索しよう」と思って再実行すると、trial 5 をやり直してくれるということはなく (仕様として当然ですが)、trial 6 が新規採番されます。これじゃないと思って強制終了すると、trial 6 は例外で FAIL になります。

- trial 0: COMPLETE
- trial 1: COMPLETE
- trial 2: COMPLETE
- trial 3: COMPLETE
- trial 4: COMPLETE
- trial 5: RUNNING
- trial 6: FAIL

やりたいこと

上のように、SQLite ストレージの最後に正常終了でない trial があるとき、予め最後の COMPLETE 以降の Trial を削除したいです (以下のようにしたい)。

- trial 0: COMPLETE
- trial 1: COMPLETE
- trial 2: COMPLETE
- trial 3: COMPLETE
- trial 4: COMPLETE

最後の COMPLETE 以降の Trial を削除するスクリプト

以下の関数 delete_trailing_incomplete_trials で、最後の正常終了以降を削除できます。
※ sqlalchemy は optuna と一緒にインストールされています。

• SQLite ストレージ内の trial_params, trial_values, trials テーブルから最後の COMPLETE 以降の Trial の ID を抹消していますが (私の場合は失敗 ID の痕跡がこれらだけでした)、Optuna のご利用方法によっては他テーブルにも痕跡があるかもしれません。気になる場合はお手元でストレージの中身をみてください。
• これによって 5 回目までで落ちた探索をもう 5 回探索しても、サンプラーの状態がずれるので、連続で 10 回探索した場合と結果はずれます (失敗 Trial を削除してもしなくても)。再現性が必要な場合は、中断地点を記録してください。
• 公式機能が用意されていない操作なので、その他何か影響がある可能性を排除できません。

from sqlalchemy import create_engine, text, bindparam
import optuna


def delete_trailing_incomplete_trials(study, storage):
    incomplete_trial_ids = []
    for trial in reversed(study.trials):
        if trial.state.name == 'COMPLETE':
            break
        incomplete_trial_ids.append(trial._trial_id)
    if len(incomplete_trial_ids) == 0:
        return
    engine = create_engine(storage)
    with engine.connect() as conn:
        for table_name in ['trial_params', 'trial_values', 'trials']:
            stmt = f'DELETE FROM {table_name} WHERE trial_id IN :trial_ids'
            stmt = text(stmt).bindparams(bindparam('trial_ids', expanding=True))
            conn.execute(stmt, {'trial_ids': incomplete_trial_ids})
        conn.commit()


if __name__ == '__main__':
    storage = 'sqlite:///bench/20260601/jma11_uni_o00/optuna_adam/optuna_.db'

    # study のロードと中身の表示
    study = optuna.create_study(
        direction='minimize', sampler=optuna.samplers.TPESampler(seed=0),
        storage=storage, study_name='Optuna Adam', load_if_exists=True,
    )
    for trial in study.trials:
        print(f'- trial {trial.number}: {trial.state.name}')

    # 最後の COMPLETE 以降の Trial を削除
    delete_trailing_incomplete_trials(study, storage)

    # study のリロードと中身の表示
    study = optuna.create_study(
        direction='minimize', sampler=optuna.samplers.TPESampler(seed=0),
        storage=storage, study_name='Optuna Adam', load_if_exists=True,
    )
    for trial in study.trials:
        print(f'- trial {trial.number}: {trial.state.name}')

最後の COMPLETE 以降の Trial を削除するスクリプト (SQLite ストレージの中身も表示する版)

以下は削除前後の SQLite ストレージの中身も表示する版です (参考)。

from sqlalchemy import create_engine, inspect, text, bindparam
import optuna


def delete_trailing_incomplete_trials(study, storage):
    incomplete_trial_ids = []
    for trial in reversed(study.trials):
        if trial.state.name == 'COMPLETE':
            break
        incomplete_trial_ids.append(trial._trial_id)
    if len(incomplete_trial_ids) == 0:
        return
    engine = create_engine(storage)
    with engine.connect() as conn:
        for table_name in ['trial_params', 'trial_values', 'trials']:
            stmt = f'DELETE FROM {table_name} WHERE trial_id IN :trial_ids'
            stmt = text(stmt).bindparams(bindparam('trial_ids', expanding=True))
            conn.execute(stmt, {'trial_ids': incomplete_trial_ids})
        conn.commit()


def print_storage(storage):
    engine = create_engine(storage)
    inspector = inspect(engine)
    with engine.connect() as conn:
        for table_name in inspector.get_table_names():
            n_rows = conn.execute(text(f'SELECT COUNT(*) FROM "{table_name}"')).scalar_one()
            rows = conn.execute(text(f'SELECT * FROM "{table_name}" LIMIT 10')).fetchall()
            print(f'\n===== {table_name} ({n_rows} rows) =====')
            for row in rows:
                print(dict(row._mapping))


if __name__ == '__main__':
    storage = 'sqlite:///bench/20260601/jma11_uni_o00/optuna_adam/optuna_.db'

    # study のロードと中身の表示 (SQLite ストレージの中身も表示)
    study = optuna.create_study(
        direction='minimize', sampler=optuna.samplers.TPESampler(seed=0),
        storage=storage, study_name='Optuna Adam', load_if_exists=True,
    )
    for trial in study.trials:
        print(f'- trial {trial.number}: {trial.state.name}')
    print_storage(storage)

    # 最後の COMPLETE 以降の Trial を削除
    delete_trailing_incomplete_trials(study, storage)

    # study のリロードと中身の表示 (SQLite ストレージの中身も表示)
    study = optuna.create_study(
        direction='minimize', sampler=optuna.samplers.TPESampler(seed=0),
        storage=storage, study_name='Optuna Adam', load_if_exists=True,
    )
    for trial in study.trials:
        print(f'- trial {trial.number}: {trial.state.name}')
    print_storage(storage)

最後の COMPLETE 以降の Trial を削除する前

[I 2026-06-01 10:02:47,649] Using an existing study with name 'Optuna Adam' instead of creating a new one.
- trial 0: COMPLETE
- trial 1: COMPLETE
- trial 2: COMPLETE
- trial 3: COMPLETE
- trial 4: COMPLETE
- trial 5: RUNNING
- trial 6: FAIL

===== alembic_version (1 rows) =====
{'version_num': 'v3.2.0.a'}

===== studies (1 rows) =====
{'study_id': 1, 'study_name': 'Optuna Adam'}

===== study_directions (1 rows) =====
{'study_direction_id': 1, 'direction': 'MINIMIZE', 'study_id': 1, 'objective': 0}

===== study_system_attributes (0 rows) =====

===== study_user_attributes (0 rows) =====

===== trial_heartbeats (0 rows) =====

===== trial_intermediate_values (0 rows) =====

===== trial_params (21 rows) =====
{'param_id': 1, 'trial_id': 1, 'param_name': 'batch_size_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 4}}'}
{'param_id': 2, 'trial_id': 1, 'param_name': 'lr_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 3, 'trial_id': 1, 'param_name': 'gamma_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 4, 'trial_id': 2, 'param_name': 'batch_size_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 4}}'}
{'param_id': 5, 'trial_id': 2, 'param_name': 'lr_index', 'param_value': 1.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 6, 'trial_id': 2, 'param_name': 'gamma_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 7, 'trial_id': 3, 'param_name': 'batch_size_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 4}}'}
{'param_id': 8, 'trial_id': 3, 'param_name': 'lr_index', 'param_value': 3.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 9, 'trial_id': 3, 'param_name': 'gamma_index', 'param_value': 3.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 10, 'trial_id': 4, 'param_name': 'batch_size_index', 'param_value': 1.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 4}}'}

===== trial_system_attributes (0 rows) =====

===== trial_user_attributes (0 rows) =====

===== trial_values (5 rows) =====
{'trial_value_id': 1, 'trial_id': 1, 'objective': 0, 'value': 1.6313042873179942, 'value_type': 'FINITE'}
{'trial_value_id': 2, 'trial_id': 2, 'objective': 0, 'value': 1.638494750084785, 'value_type': 'FINITE'}
{'trial_value_id': 3, 'trial_id': 3, 'objective': 0, 'value': 1.659784386924694, 'value_type': 'FINITE'}
{'trial_value_id': 4, 'trial_id': 4, 'objective': 0, 'value': 1.6681359793866626, 'value_type': 'FINITE'}
{'trial_value_id': 5, 'trial_id': 5, 'objective': 0, 'value': 1.6528374752018118, 'value_type': 'FINITE'}

===== trials (7 rows) =====
{'trial_id': 1, 'number': 0, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 00:51:50.802866', 'datetime_complete': '2026-06-01 01:06:21.046475'}
{'trial_id': 2, 'number': 1, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 01:06:21.080182', 'datetime_complete': '2026-06-01 01:35:22.614041'}
{'trial_id': 3, 'number': 2, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 01:35:22.638614', 'datetime_complete': '2026-06-01 01:48:34.706374'}
{'trial_id': 4, 'number': 3, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 01:48:34.724641', 'datetime_complete': '2026-06-01 02:03:16.042763'}
{'trial_id': 5, 'number': 4, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 02:03:16.070349', 'datetime_complete': '2026-06-01 02:20:34.373054'}
{'trial_id': 6, 'number': 5, 'study_id': 1, 'state': 'RUNNING', 'datetime_start': '2026-06-01 02:20:34.400944', 'datetime_complete': None}
{'trial_id': 7, 'number': 6, 'study_id': 1, 'state': 'FAIL', 'datetime_start': '2026-06-01 09:05:04.074784', 'datetime_complete': '2026-06-01 09:07:00.796439'}

===== version_info (1 rows) =====
{'version_info_id': 1, 'schema_version': 12, 'library_version': '4.7.0'}

最後の COMPLETE 以降の Trial を削除した後

[I 2026-06-01 10:02:47,726] Using an existing study with name 'Optuna Adam' instead of creating a new one.
  trial 0: COMPLETE
  trial 1: COMPLETE
  trial 2: COMPLETE
  trial 3: COMPLETE
  trial 4: COMPLETE

===== alembic_version (1 rows) =====
{'version_num': 'v3.2.0.a'}

===== studies (1 rows) =====
{'study_id': 1, 'study_name': 'Optuna Adam'}

===== study_directions (1 rows) =====
{'study_direction_id': 1, 'direction': 'MINIMIZE', 'study_id': 1, 'objective': 0}

===== study_system_attributes (0 rows) =====

===== study_user_attributes (0 rows) =====

===== trial_heartbeats (0 rows) =====

===== trial_intermediate_values (0 rows) =====

===== trial_params (15 rows) =====
{'param_id': 1, 'trial_id': 1, 'param_name': 'batch_size_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 4}}'}
{'param_id': 2, 'trial_id': 1, 'param_name': 'lr_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 3, 'trial_id': 1, 'param_name': 'gamma_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 4, 'trial_id': 2, 'param_name': 'batch_size_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 4}}'}
{'param_id': 5, 'trial_id': 2, 'param_name': 'lr_index', 'param_value': 1.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 6, 'trial_id': 2, 'param_name': 'gamma_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 7, 'trial_id': 3, 'param_name': 'batch_size_index', 'param_value': 2.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 4}}'}
{'param_id': 8, 'trial_id': 3, 'param_name': 'lr_index', 'param_value': 3.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 9, 'trial_id': 3, 'param_name': 'gamma_index', 'param_value': 3.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 3}}'}
{'param_id': 10, 'trial_id': 4, 'param_name': 'batch_size_index', 'param_value': 1.0, 'distribution_json': '{"name": "IntDistribution", "attributes": {"log": false, "step": 1, "low": 0, "high": 4}}'}

===== trial_system_attributes (0 rows) =====

===== trial_user_attributes (0 rows) =====

===== trial_values (5 rows) =====
{'trial_value_id': 1, 'trial_id': 1, 'objective': 0, 'value': 1.6313042873179942, 'value_type': 'FINITE'}
{'trial_value_id': 2, 'trial_id': 2, 'objective': 0, 'value': 1.638494750084785, 'value_type': 'FINITE'}
{'trial_value_id': 3, 'trial_id': 3, 'objective': 0, 'value': 1.659784386924694, 'value_type': 'FINITE'}
{'trial_value_id': 4, 'trial_id': 4, 'objective': 0, 'value': 1.6681359793866626, 'value_type': 'FINITE'}
{'trial_value_id': 5, 'trial_id': 5, 'objective': 0, 'value': 1.6528374752018118, 'value_type': 'FINITE'}

===== trials (5 rows) =====
{'trial_id': 1, 'number': 0, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 00:51:50.802866', 'datetime_complete': '2026-06-01 01:06:21.046475'}
{'trial_id': 2, 'number': 1, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 01:06:21.080182', 'datetime_complete': '2026-06-01 01:35:22.614041'}
{'trial_id': 3, 'number': 2, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 01:35:22.638614', 'datetime_complete': '2026-06-01 01:48:34.706374'}
{'trial_id': 4, 'number': 3, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 01:48:34.724641', 'datetime_complete': '2026-06-01 02:03:16.042763'}
{'trial_id': 5, 'number': 4, 'study_id': 1, 'state': 'COMPLETE', 'datetime_start': '2026-06-01 02:03:16.070349', 'datetime_complete': '2026-06-01 02:20:34.373054'}

===== version_info (1 rows) =====
{'version_info_id': 1, 'schema_version': 12, 'library_version': '4.7.0'}