【SQLite】VACUUMで困ったら読む記事：よくあるエラーと解決策

2025-06-06

一時的なデータベースの作成: まず、SQLiteは現在のデータベースの内容を、新しい一時的なデータベースファイルにコピーします。この際、未使用のスペースや断片化されたページはコピーされず、データが連続的に格納されます。これにより、論理的にデータが整理されます。
データベースの再構築: 一時的なデータベースにすべての有効なデータがコピーされた後、元のデータベースファイルは上書きされます。このとき、一時的なデータベースの内容が元のファイルに書き戻されます。これにより、データベースファイルから不要な領域が削除され、ファイルサイズが小さくなります。
インデックスの再構築: VACUUMは、テーブルだけでなく、それに付随するインデックスもすべて再構築します。これにより、インデックスも最適化され、クエリのパフォーマンスが向上する可能性があります。

VACUUMの種類と使い方

SQLiteには、いくつかのVACUUMの実行方法があります。

VACUUM <schema-name> INTO <filename>;
- SQLite 3.27.0以降で導入されたコマンドです。
- これは元のデータベースファイルを変更せずに、最適化された内容を新しいファイルに書き出すことができます。
- これにより、元のデータベースはそのまま残り、新しい最適化されたデータベースファイルを使用するかどうかを選択できます。バックアップと最適化を同時に行いたい場合などに便利です。
PRAGMA auto_vacuum = FULL; または PRAGMA auto_vacuum = INCREMENTAL;
- これらは自動VACUUMモードを設定するPRAGMA文です。
- FULLモードでは、データが削除されるたびに自動的にVACUUMが試みられますが、これは完全なVACUUMと同じくコストがかかります。
- INCREMENTALモードでは、データベースのページが削除された際に、そのスペースを再利用できるようにマークするだけで、実際のスペース解放は明示的にPRAGMA incremental_vacuum;を実行したときに行われます。これは、より軽量なVACUUM処理を必要とする場合に便利です。
VACUUM; (Full VACUUM)
- これは最も一般的なVACUUMコマンドで、データベース全体を再構築します。
- データベースファイルが大幅に縮小され、断片化が解消されます。
- しかし、データベース全体のコピーと再構築が行われるため、処理に時間がかかり、多くのディスクIOが発生します。
- VACUUM実行中はデータベースへの書き込み操作がロックされるため、アイドル状態の時に実行するのが望ましいです。

VACUUMを行うタイミング

定期的なメンテナンス: データベースの健全性を保つために、定期的にVACUUMを実行することを検討する場合があります。
クエリのパフォーマンスが低下した場合: データベースの断片化が進むと、データの読み取り効率が低下し、クエリの速度が遅くなることがあります。VACUUMは、データの物理的な配置を最適化し、パフォーマンスを向上させる可能性があります。
データベースファイルサイズが大きくなりすぎた場合: データの挿入・更新・削除を繰り返すと、ファイルサイズは増大し続けることがあります。VACUUMは、不要なスペースを解放し、ファイルサイズを削減します。

注意点

VACUUMはROWIDの値を変更する場合があります（INTEGER PRIMARY KEYとして定義されたカラムは変更されません）。ROWIDに依存するアプリケーションでは注意が必要です。
VACUUMは、データベース全体を再構築するため、多くの時間とリソースを必要とします。特に大規模なデータベースでは、実行中にデータベースが一時的に利用できなくなる可能性があるため、運用環境での実行には注意が必要です。

データベースがロックされる/ビジーエラー (Database locked / busy error)

原因
VACUUMコマンドは、データベース全体のコピーと再構築を行うため、その実行中はデータベースファイルが排他的にロックされます。もし、他のプロセスやスレッドが同時にデータベースにアクセスしようとすると、ロック競合が発生し、「database is locked」または「database is busy」というエラーが発生します。これは、VACUUMが長時間かかる場合に特に起こりやすいです。

トラブルシューティング

WALモードの活用: SQLiteのWAL (Write-Ahead Logging) ジャーナルモードを使用すると、書き込みと読み込みの同時実行性が向上します。WALモードでは、VACUUM中に読み込み操作がブロックされることはありませんが、VACUUM自体は書き込み操作として排他的ロックを必要とします。WALモード中でも、VACUUM中は書き込みはブロックされますが、データベースの利用可能性は向上します。
アプリケーションのアイドル時に実行する: ユーザーからの操作が少ない時間帯や、アプリケーションの起動時・終了時など、データベースへのアクセスが集中しないタイミングでVACUUMを実行することを検討します。
ビジーハンドラ (Busy Handler) の設定: SQLiteには、データベースがロックされている場合に、指定された時間だけ待機してから再試行する「ビジーハンドラ」を設定する機能があります。アプリケーション側でこれを適切に設定することで、ロックエラーを軽減できます。
- C/C++の場合: sqlite3_busy_timeout()
- Pythonの場合: conn.execute("PRAGMA busy_timeout = 5000;") (5000ミリ秒待機)
他のアプリケーションやプロセスを終了する: データベースにアクセスしている可能性のある、他のすべてのアプリケーションやプロセスを停止してからVACUUMを実行します。

ディスク容量不足 (Disk full error)

原因
VACUUMは、新しいデータベースファイルを作成し、元のデータベースの内容をそれにコピーしてから、元のファイルを新しいファイルで上書きします。このため、VACUUMを実行するには、元のデータベースファイルと同じか、それ以上の空きディスク容量が必要です。もし十分な空き容量がない場合、「database or disk is full」といったエラーが発生します。

トラブルシューティング

データベースファイルを別のドライブに移動: もし可能であれば、より多くの空き容量があるドライブにデータベースファイルを移動してからVACUUMを実行し、完了後に元の場所に戻すことも検討できます。
不要なファイルの削除: ディスクの空き容量を増やすために、不要なファイルやデータを削除します。
空きディスク容量の確認: VACUUMを実行する前に、対象のデータベースファイルが存在するドライブの空きディスク容量を確認します。データベースファイルの少なくとも2倍程度の空き容量があると安全です。

データベースファイルの破損 (Database file corruption)

原因
稀に、ファイルシステムの問題、ハードウェアの故障、予期せぬシャットダウン、あるいはSQLiteの操作中に他のプロセスがデータベースファイルを不正に書き込むなどの理由で、データベースファイルが破損することがあります。破損したデータベースに対してVACUUMを実行しようとすると、エラーが発生するか、VACUUMが正常に完了しない場合があります。

トラブルシューティング

ファイルシステムやハードウェアの確認: ファイルシステムの破損やハードウェアの故障が原因である可能性もあるため、それらのチェックも行います。
部分的な復旧: sqlite3_deserialize()や他のツールを使って、破損したデータベースから可能な限りデータを抽出し、新しいデータベースにインポートすることを試みる場合があります。ただし、これは高度な作業であり、すべてのデータを復旧できるとは限りません。
バックアップからの復元: 最善の解決策は、破損する前の正常なバックアップからデータベースを復元することです。定期的なバックアップは非常に重要です。
PRAGMA integrity_check;の実行: VACUUMを実行する前に、データベースの整合性をチェックするためにPRAGMA integrity_check;を実行します。これにより、破損箇所が特定できる場合があります。

VACUUM INTOの制限 (Limitations of VACUUM INTO)

原因
SQLite 3.27.0以降で導入されたVACUUM INTO <filename>;コマンドは、元のデータベースを変更せずに最適化された内容を新しいファイルに書き出せる便利な機能ですが、いくつか制限があります。例えば、:memory:データベースに対しては使用できない、一時ファイルとして開かれたデータベースに対しては期待通りに動作しない、といった報告があります。

トラブルシューティング

ファイルパスの指定: 新しいファイルパスが適切に指定されているか確認します。相対パスの場合は、実行コンテキストからのパスが正しいかを確認します。
ドキュメントの確認: 使用しているSQLiteのバージョンで、VACUUM INTOに関する特定の制限がないか、公式ドキュメントを確認します。

原因
PRAGMA auto_vacuum = FULL;またはPRAGMA auto_vacuum = INCREMENTAL;を設定している場合、アプリケーションの動作やデータ削除の頻度によっては、期待通りのパフォーマンスやファイルサイズ削減効果が得られない場合があります。

INCREMENTALモードは、より軽量な自動最適化ですが、実際にディスクスペースを解放するには明示的にPRAGMA incremental_vacuum;を実行する必要があります。
FULLモードは、削除されたページが再利用されるようにマークし、必要に応じて自動的に最適化を試みますが、これは完全なVACUUMと同様にコストがかかることがあります。

適切なモードの選択: アプリケーションの特性（データの更新・削除頻度、パフォーマンス要件など）に基づいて、VACUUMの実行方法（手動、FULL、INCREMENTAL）を適切に選択します。
パフォーマンスの監視: AUTO_VACUUMモードを使用している場合でも、定期的にデータベースのファイルサイズとクエリパフォーマンスを監視し、必要に応じて手動でVACUUMを実行するか、INCREMENTALモードの場合はPRAGMA incremental_vacuum;を実行することを検討します。

VACUUMコマンドは、データベースファイルの最適化とサイズ縮小を行うために使用されます。ここでは、Pythonのsqlite3モジュールを使って、基本的なVACUUMの実行、ビジーハンドリング、および自動VACUUMの設定例を示します。

基本的なVACUUMの実行

この例では、まずデータベースを作成し、データを挿入・削除して、その後VACUUMを実行してファイルサイズの変化を確認します。

import sqlite3
import os

# データベースファイル名
DB_FILE = "my_database.db"

def get_file_size(filepath):
    """ファイルのサイズをバイト単位で取得するヘルパー関数"""
    if os.path.exists(filepath):
        return os.path.getsize(filepath)
    return 0

def create_and_populate_db():
    """データベースを作成し、データを挿入・削除する"""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    # テーブルの作成
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY,
            name TEXT,
            email TEXT
        )
    """)
    
    # 大量のデータを挿入
    print("データを挿入中...")
    for i in range(1, 10001):
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", (f"User{i}", f"user{i}@example.com"))
    conn.commit()
    print(f"挿入後のデータベースサイズ: {get_file_size(DB_FILE)} バイト")

    # 大量にデータを削除
    print("データを削除中...")
    cursor.execute("DELETE FROM users WHERE id % 2 = 0") # 偶数IDのレコードを削除
    conn.commit()
    print(f"削除後のデータベースサイズ (VACUUM前): {get_file_size(DB_FILE)} バイト")
    
    conn.close()

def run_vacuum():
    """VACUUMを実行する"""
    print("\nVACUUMを実行中...")
    conn = sqlite3.connect(DB_FILE)
    try:
        conn.execute("VACUUM")
        conn.commit()
        print(f"VACUUM実行後のデータベースサイズ: {get_file_size(DB_FILE)} バイト")
    except sqlite3.OperationalError as e:
        print(f"VACUUMエラー: {e}")
    finally:
        conn.close()

if __name__ == "__main__":
    # 古いデータベースファイルを削除
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)

    create_and_populate_db()
    run_vacuum()

解説

create_and_populate_db():
- sqlite3.connect(DB_FILE) でデータベースファイルに接続します。ファイルが存在しない場合は新規作成されます。
- CREATE TABLE IF NOT EXISTS users (...) でテーブルを作成します。
- forループで10000件のレコードを挿入します。この時点でファイルサイズが大きくなります。
- DELETE FROM users WHERE id % 2 = 0 で半分のレコードを削除します。この時点では、ファイルサイズはほとんど変化しません。 削除されたスペースは「空き領域」としてデータベースファイル内に残っているためです。
run_vacuum():
- conn.execute("VACUUM") を実行します。これにより、データベースが再構築され、削除によって生じた空き領域が解放されます。
- VACUUM実行後にファイルサイズを確認すると、大幅に縮小されていることがわかります。
get_file_size(): ファイルサイズを取得するためのユーティリティ関数です。

ビジーハンドラ (Busy Handler) の設定

VACUUMはデータベースをロックするため、他のアクセスと競合する可能性があります。ビジーハンドラを設定することで、ロックが発生した場合に一定時間待機し、再試行させることができます。

import sqlite3
import threading
import time
import os

DB_FILE = "my_busy_db.db"

def writer_thread():
    """データベースに書き込み続けるスレッド"""
    conn = sqlite3.connect(DB_FILE)
    print("Writer: 書き込みスレッド開始...")
    for i in range(5):
        try:
            conn.execute("INSERT INTO data (value) VALUES (?)", (f"data_{i}",))
            conn.commit()
            print(f"Writer: データ挿入 {i+1}")
        except sqlite3.OperationalError as e:
            print(f"Writer: 書き込みエラー (おそらくロック): {e}")
        time.sleep(0.5) # 0.5秒ごとに書き込みを試みる
    conn.close()
    print("Writer: 書き込みスレッド終了。")

def vacuum_thread():
    """VACUUMを実行するスレッド"""
    conn = sqlite3.connect(DB_FILE)
    # ビジータイムアウトを設定 (5秒間待機)
    conn.execute("PRAGMA busy_timeout = 5000") 
    
    print("\nVacuum: VACUUMスレッド開始...")
    try:
        start_time = time.time()
        conn.execute("VACUUM")
        end_time = time.time()
        print(f"Vacuum: VACUUM完了。所要時間: {end_time - start_time:.2f}秒")
    except sqlite3.OperationalError as e:
        print(f"Vacuum: VACUUMエラー: {e}")
    finally:
        conn.close()
    print("Vacuum: VACUUMスレッド終了。")

if __name__ == "__main__":
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)

    # 初期データベースの作成
    conn = sqlite3.connect(DB_FILE)
    conn.execute("CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY, value TEXT)")
    conn.commit()
    conn.close()

    # VACUUMスレッドをバックグラウンドで開始
    v_thread = threading.Thread(target=vacuum_thread)
    v_thread.start()

    # 少し遅れて書き込みスレッドを開始 (ロック競合を発生させるため)
    time.sleep(0.01) 
    w_thread = threading.Thread(target=writer_thread)
    w_thread.start()

    # 両方のスレッドの完了を待つ
    w_thread.join()
    v_thread.join()
    print("\nすべてのスレッドが終了しました。")

解説

writer_thread(): データベースに継続的にデータを書き込むスレッドです。
vacuum_thread():
- conn.execute("PRAGMA busy_timeout = 5000") で、データベースがロックされていた場合に最大5000ミリ秒 (5秒) 待機するように設定します。
- この設定により、VACUUMが実行されたときに、writer_threadが一時的にブロックされたとしても、すぐにエラーになるのではなく、待機して再試行する機会を得ることができます。
if __name__ == "__main__":ブロックで、両方のスレッドを同時に開始し、ロック競合の状況をシミュレートします。VACUUMはWriterスレッドからの書き込みを一時的にブロックする可能性がありますが、busy_timeoutのおかげで、Writerスレッドはエラーではなく待機し、VACUUM完了後に書き込みを再開できる場合があります。

自動VACUUM (AUTO_VACUUM) の設定

VACUUMを手動で実行する代わりに、データベースの作成時にAUTO_VACUUMモードを設定することができます。これにより、削除されたスペースが自動的に再利用されたり、INCREMENTALモードの場合は部分的なVACUUMが可能になります。

注意点
AUTO_VACUUMモードは、データベースの作成時に設定する必要があります。既存のデータベースのモードを変更するには、一度VACUUMを実行してデータベースを再構築するか、新しいデータベースにデータをダンプする必要があります。

import sqlite3
import os

DB_FILE_AUTO = "my_auto_vacuum.db"

def setup_auto_vacuum_db(auto_vacuum_mode="FULL"):
    """
    AUTO_VACUUMモードを設定してデータベースを作成する。
    mode: "NONE", "FULL", "INCREMENTAL"
    """
    print(f"\nAUTO_VACUUMモード '{auto_vacuum_mode}' でデータベースを作成中...")
    conn = sqlite3.connect(DB_FILE_AUTO)
    
    # AUTO_VACUUMモードを設定
    # このPRAGMAはデータベース接続の最初のステートメントとして、または新規作成時に実行されるべきです。
    conn.execute(f"PRAGMA auto_vacuum = {auto_vacuum_mode};")
    
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY,
            name TEXT,
            price REAL
        )
    """)
    conn.commit()
    conn.close()

def demonstrate_auto_vacuum(db_file):
    """AUTO_VACUUMの挙動をデモンストレーションする"""
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()

    # AUTO_VACUUMの状態を確認
    cursor.execute("PRAGMA auto_vacuum;")
    current_auto_vacuum = cursor.fetchone()[0]
    print(f"現在のAUTO_VACUUMモード: {current_auto_vacuum} (0:NONE, 1:FULL, 2:INCREMENTAL)")

    # データを挿入
    print("データを挿入中...")
    for i in range(1, 1001):
        cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (f"Product{i}", i * 10.0))
    conn.commit()
    print(f"挿入後のデータベースサイズ: {get_file_size(db_file)} バイト")

    # 大量のデータを削除
    print("データを削除中...")
    cursor.execute("DELETE FROM products WHERE id % 5 = 0") # 5の倍数IDを削除
    conn.commit()
    print(f"削除後のデータベースサイズ (AUTO_VACUUMの反映前/後): {get_file_size(db_file)} バイト")

    # もしINCREMENTALモードの場合、明示的なVACUUMが必要
    if current_auto_vacuum == 2: # INCREMENTAL
        print("\nINCREMENTAL AUTO_VACUUMモードのため、PRAGMA incremental_vacuumを実行...")
        conn.execute("PRAGMA incremental_vacuum;")
        conn.commit()
        print(f"INCREMENTAL VACUUM実行後のデータベースサイズ: {get_file_size(db_file)} バイト")

    conn.close()

if __name__ == "__main__":
    # AUTO_VACUUM NONE の場合
    if os.path.exists("my_none_vacuum.db"): os.remove("my_none_vacuum.db")
    setup_auto_vacuum_db(auto_vacuum_mode="NONE")
    demonstrate_auto_vacuum("my_none_vacuum.db")

    # AUTO_VACUUM FULL の場合
    if os.path.exists("my_full_vacuum.db"): os.remove("my_full_vacuum.db")
    setup_auto_vacuum_db(auto_vacuum_mode="FULL")
    demonstrate_auto_vacuum("my_full_vacuum.db")

    # AUTO_VACUUM INCREMENTAL の場合
    if os.path.exists("my_incremental_vacuum.db"): os.remove("my_incremental_vacuum.db")
    setup_auto_vacuum_db(auto_vacuum_mode="INCREMENTAL")
    demonstrate_auto_vacuum("my_incremental_vacuum.db")

setup_auto_vacuum_db():
- PRAGMA auto_vacuum = {mode}; を使って、データベース作成時にauto_vacuumモードを設定します。
- "NONE": 自動VACUUMは行われません。手動VACUUMが必要です。
- "FULL": データが削除されると、可能な限り自動的にスペースが解放されますが、これは完全なVACUUMと同様にコストがかかります。
- "INCREMENTAL": 削除されたページは再利用可能としてマークされますが、実際のディスクスペースの解放はPRAGMA incremental_vacuum;が明示的に実行されたときに行われます。
demonstrate_auto_vacuum():
- 各モードでのデータの挿入と削除を行い、ファイルサイズの変化を観察します。
- INCREMENTALモードの場合、DELETE直後にはファイルサイズが減らないが、PRAGMA incremental_vacuum;を実行するとサイズが減少することを示します。

VACUUMコマンドは強力ですが、データベース全体のロックや時間のかかる処理といったデメリットもあります。そのため、アプリケーションの要件によっては、以下のような代替手段を検討することが有効です。

PRAGMA auto_vacuum = INCREMENTAL; と PRAGMA incremental_vacuum; の活用

これはVACUUMの一種ですが、データベース全体を再構築するのではなく、部分的な最適化を行う点で代替手段として重要です。

PRAGMA incremental_vacuum(N); の実行: このコマンドをプログラムから定期的に実行することで、指定した数（N）のページ、またはすべての再利用可能なページを解放します。これにより、完全なVACUUMよりもはるかに高速に、かつデータベース全体をロックする時間を短縮してスペースを回収できます。

import sqlite3
import os

DB_FILE = 'my_incremental_db.db'

def run_incremental_vacuum():
    conn = sqlite3.connect(DB_FILE)

    # 現在の再利用可能なページ数を確認（オプション）
    cursor = conn.execute("PRAGMA freelist_count;")
    freelist_count = cursor.fetchone()[0]
    print(f"再利用可能なフリーページ数: {freelist_count}")

    if freelist_count > 0:
        # 例えば、フリーリストの1/4のページを解放
        pages_to_vacuum = freelist_count // 4 
        if pages_to_vacuum == 0 and freelist_count > 0: # 少なくとも1ページは解放
            pages_to_vacuum = 1

        print(f"インクリメンタルVACUUMで {pages_to_vacuum} ページを解放中...")
        conn.execute(f"PRAGMA incremental_vacuum({pages_to_vacuum});")
        conn.commit()
        print(f"インクリメンタルVACUUM後のデータベースサイズ: {os.path.getsize(DB_FILE)} バイト")
    else:
        print("解放するフリーページがありません。")

    conn.close()

# データベースの初期化とデータ操作の例
if __name__ == '__main__':
    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)

    # INCREMENTALモードでデータベースをセットアップ
    conn = sqlite3.connect(DB_FILE)
    conn.execute("PRAGMA auto_vacuum = INCREMENTAL;")
    conn.execute("CREATE TABLE IF NOT EXISTS large_data (id INTEGER PRIMARY KEY, payload TEXT)")
    conn.commit()
    conn.close()

    print(f"初期データベースサイズ: {os.path.getsize(DB_FILE)} バイト")

    # 大量のデータを挿入
    conn = sqlite3.connect(DB_FILE)
    for i in range(1000):
        conn.execute("INSERT INTO large_data (payload) VALUES (?)", ("X" * 1000,)) # 1KBのデータ
    conn.commit()
    conn.close()
    print(f"挿入後のデータベースサイズ: {os.path.getsize(DB_FILE)} バイト")

    # 大量のデータを削除
    conn = sqlite3.connect(DB_FILE)
    conn.execute("DELETE FROM large_data WHERE id % 2 = 0") # 半分を削除
    conn.commit()
    conn.close()
    print(f"削除後のデータベースサイズ (incremental_vacuum前): {os.path.getsize(DB_FILE)} バイト")

    # インクリメンタルVACUUMを実行
    run_incremental_vacuum()

    # もう一度実行して、残りのページを解放する可能性を試す
    run_incremental_vacuum()

利点: 実行時間が短く、データベースの可用性への影響が少ないです。欠点: 完全なVACUUMほどの最適化効果は期待できません。フリーリストのページを解放するだけで、断片化そのものの解消にはつながりません。

PRAGMA auto_vacuum = INCREMENTAL; の設定: データベース作成時にこのPRAGMAを設定することで、削除されたページが「再利用可能」としてマークされますが、すぐにディスクスペースは解放されません。
```
import sqlite3

conn = sqlite3.connect('my_incremental_db.db')
conn.execute("PRAGMA auto_vacuum = INCREMENTAL;")
conn.execute("CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY, value TEXT)")
conn.close()
```

アプリケーションレベルでのデータ整理

SQLiteのVACUUMに頼らず、アプリケーション自身でデータを整理・再構築する方法です。

新しいデータベースへのデータ移行:

新しい空のSQLiteデータベースファイルを作成します。
既存のデータベースから、必要なデータのみをSQLのSELECTステートメントで抽出し、新しいデータベースにINSERTします。
すべてのデータが移行されたら、古いデータベースファイルを削除し、新しいファイルを古いファイル名にリネームします。

import sqlite3
import os

OLD_DB = "old_db.db"
NEW_DB = "new_db.db"

def create_and_populate_old_db():
    conn = sqlite3.connect(OLD_DB)
    conn.execute("CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, name TEXT, status INTEGER)")
    for i in range(1000):
        conn.execute("INSERT INTO items (name, status) VALUES (?, ?)", (f"Item {i}", i % 3))
    conn.execute("DELETE FROM items WHERE status = 0") # 不要なデータを削除
    conn.commit()
    conn.close()
    print(f"古いDB ({OLD_DB}) 作成完了。サイズ: {os.path.getsize(OLD_DB)} バイト")

def migrate_data():
    if os.path.exists(NEW_DB):
        os.remove(NEW_DB)

    old_conn = sqlite3.connect(OLD_DB)
    new_conn = sqlite3.connect(NEW_DB)

    try:
        # 新しいDBにテーブルを作成
        new_conn.execute("CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, name TEXT, status INTEGER)")
        new_conn.commit()

        # 古いDBからデータをSELECTし、新しいDBにINSERT
        cursor = old_conn.execute("SELECT name, status FROM items WHERE status != 0") # 不要なデータを含めない

        print("データ移行中...")
        for row in cursor:
            new_conn.execute("INSERT INTO items (name, status) VALUES (?, ?)", row)
        new_conn.commit()
        print("データ移行完了。")

    except Exception as e:
        print(f"データ移行エラー: {e}")
        new_conn.rollback()
    finally:
        old_conn.close()
        new_conn.close()

    print(f"新しいDB ({NEW_DB}) 作成完了。サイズ: {os.path.getsize(NEW_DB)} バイト")

    # 古いDBを削除し、新しいDBをリネーム
    os.remove(OLD_DB)
    os.rename(NEW_DB, OLD_DB)
    print(f"古いDBを新しいDBで置き換えました。現在のDB ({OLD_DB}) サイズ: {os.path.getsize(OLD_DB)} バイト")


if __name__ == '__main__':
    create_and_populate_old_db()
    migrate_data()

利点: データのフィルタリングやスキーマ変更を同時に行える柔軟性があります。非常に大きなデータベースでも、段階的に処理できます。VACUUMのような排他的ロックは必要なく、データ移行中は古いデータベースにアクセス可能です（ただし、移行中のデータ変更は新しいDBには反映されません）。欠点: データベースファイルサイズが一時的に2倍以上になる可能性があります。移行プロセスをアプリケーション側で実装する必要があります。

WAL (Write-Ahead Logging) モードの活用

WALモードは、データ書き込み時のロック粒度を細かくし、読み込みと書き込みの同時実行性を向上させます。これにより、通常の操作中の「database is locked」エラーを減らすことができます。

チェックポイント (Checkpoint): WALモードでは、変更はまずWALファイルに書き込まれ、その後定期的にメインデータベースファイルに「チェックポイント」されます。このチェックポイントプロセスが、事実上、一部の不要なWALエントリーを削除し、メインデータベースの整合性を保ちます。VACUUMほどファイルサイズを縮小するわけではありませんが、ファイルの健全性維持に役立ちます。

import sqlite3

DB_WAL_FILE = 'my_wal_db.db'

def run_checkpoint():
    conn = sqlite3.connect(DB_WAL_FILE)
    # WALモードの確認
    cursor = conn.execute("PRAGMA journal_mode;")
    mode = cursor.fetchone()[0]
    print(f"現在のジャーナルモード: {mode}")

    if mode == 'wal':
        print("WALモードのチェックポイントを実行中...")
        # FULLチェックポイント: WALファイルのすべての内容をメインDBに書き込む
        # PASSIVEチェックポイント: 他のリーダーをブロックせずに可能な限り多くのWALを書き込む
        conn.execute("PRAGMA wal_checkpoint(FULL);") # または PASSIVE, RESTART, TRUNCATE
        print("チェックポイント完了。")
    else:
        print("WALモードではありません。チェックポイントは適用されません。")
    conn.close()

# 実際のアプリケーションでは、定期的に wal_checkpoint を呼び出すか、
# 接続が閉じられたときに自動で行われるようにします。
if __name__ == '__main__':
    # まずWALモードのDBを作成（または既存のDBをWALモードにする）
    conn = sqlite3.connect(DB_WAL_FILE)
    conn.execute("PRAGMA journal_mode = WAL;")
    conn.execute("CREATE TABLE IF NOT EXISTS test_data (id INTEGER PRIMARY KEY, val TEXT)")
    conn.close()

    # 書き込みをシミュレート (WALファイルが大きくなる)
    conn = sqlite3.connect(DB_WAL_FILE)
    for i in range(100):
        conn.execute("INSERT INTO test_data (val) VALUES (?)", (f"Test data {i}",))
    conn.commit()
    conn.close()

    print(f"WALファイルサイズ (wal-log): {os.path.getsize(DB_WAL_FILE + '-wal')} バイト")

    # チェックポイントを実行してWALファイルをクリア
    run_checkpoint()

    if os.path.exists(DB_WAL_FILE + '-wal'):
        print(f"チェックポイント後のWALファイルサイズ (wal-log): {os.path.getsize(DB_WAL_FILE + '-wal')} バイト")
    else:
        print("WALファイルは削除されました。")

利点: 読み込みと書き込みの同時実行性が大幅に向上します。VACUUMのような重い処理を頻繁に実行する必要が減ります。欠点: データベースファイルに加えて.walファイルと.shmファイルが作成されるため、ファイル数が一時的に増えます。.walファイルは大きくなる可能性があり、明示的なチェックポイントが必要な場合があります。VACUUMほどのファイルサイズ縮小効果はありません。

WALモードへの切り替え:

import sqlite3

conn = sqlite3.connect('my_wal_db.db')
conn.execute("PRAGMA journal_mode = WAL;")
conn.execute("CREATE TABLE IF NOT EXISTS log_data (id INTEGER PRIMARY KEY, message TEXT)")
conn.close()

シャード化（Sharding）または複数のデータベースファイルの使用

これは直接VACUUMの代替ではありませんが、単一の巨大なデータベースファイルへの依存を減らすことで、VACUUMが必要になる頻度や影響を軽減します。

欠点: データのクエリが複雑になる（複数のデータベースを結合する必要がある場合）。アプリケーション側での管理が複雑になります。
利点: 各データベースが小さくなるため、個々のVACUUMが高速になります。一部のデータベースに問題が発生しても、全体への影響を限定できます。
概念:
- データを論理的または時間的に分割し、複数の小さなSQLiteデータベースファイルに格納します。
- 例えば、年ごと、月ごと、またはユーザーごとにデータベースファイルを分けるなど。

SQLite's Built-in printf()

SQLiteのprintf()関数は、1つ目の引数にフォーマット文字列を取り、それに続く引数で指定された値をそのフォーマットに従って文字列に変換して返します。この機能は、もともとformat()という名前でしたが、他のデータベースとの互換性のためにprintf()というエイリアスが残されています。

SQLiteとCSVの連携術：「Comma option」をマスターしてデータ処理を効率化

CSVファイルのエクスポート/インポートにおける区切り文字としてのカンマSQLクエリ内でカンマ区切りの文字列を扱う場合それぞれについて詳しく説明します。SQLiteは、コマンドラインインターフェース（sqlite3）やGUIツール（SQLiteStudio、DB Browser for SQLiteなど）を使って、データベースのデータをCSV（Comma-Separated Values）形式でエクスポートしたり、CSVファイルをデータベースにインポートしたりする機能を提供しています。この際、「comma option」は、CSVファイル内の各データを区切る文字としてカンマ（,）を使用することを指します。

%q

簡単に言うと、%qは以下の処理を行います。シングルクォートのエスケープ: 入力された文字列に含まれるすべてのシングルクォート（'）を、SQLでエスケープするためにダブルシングルクォート（''）に変換します。これにより、文字列内に含まれるシングルクォートがSQL文の区切りとして解釈されるのを防ぎます。

SQLiteのstrftime('%w') は何が違う？曜日処理の正しい理解と実装

SQLiteにおいて、単独で「%w」という特別な意味を持つ書式指定子はありません。おそらく、「%w」という表記は、他のプログラミング言語やライブラリ、あるいは何らかの文脈と混同されている可能性があります。SQLiteで日付や時刻を扱う際に、特定の書式で文字列として取得したり、逆に文字列から日付や時刻に変換したりする関数が存在しますが、それらの書式指定子として一般的なのは以下のようなものです。

SQLiteの%zは非対応？タイムゾーン表示の基本とプログラミングのコツ

SQLiteの strftime() 関数における %z は、タイムゾーンオフセットを表すための書式指定子です。しかし、SQLiteの標準的な strftime() 関数では、%z はサポートされていません。一般的に、C言語の strftime() 関数や他のプログラミング言語の類似関数では、%z はUTCからのオフセットを +HHMM または -HHMM の形式（例: 日本標準時なら +0900）で表示するために使われます。

【2025年版】SQLite 共有キャッシュモードの最新情報と活用事例

SQLiteの共有キャッシュモードは、複数のデータベース接続が同じデータベースファイルを共有して利用する際に、それぞれの接続が持つページキャッシュを共有する仕組みのことです。通常、SQLiteでは、各データベース接続はそれぞれ独立したページキャッシュを持ちます。これは、ある接続がデータベースから読み込んだデータや変更したデータが、他の接続には直接反映されないことを意味します。

sqldiff.exeだけじゃない！SQLiteデータベース比較の代替手法を徹底解説

sqldiff. exe は、SQLiteデータベース間の差分を表示するためのコマンドラインユーティリティです。2つのSQLiteデータベースファイルの内容を比較し、一方のデータベースをもう一方のデータベースに変換するために必要なSQLスクリプトとして差分を出力します。

SQLiteプログラミング必見！一時ファイルの挙動とパフォーマンス最適化

SQLiteは、データベース操作の過程で様々な目的のために一時ファイルを作成することがあります。これらのファイルは通常、操作が完了すると自動的に削除されますが、その存在と役割を理解しておくことは、パフォーマンスやデバッグの観点から重要です。

SQLiteマルチスレッド活用術：安全な並列処理とパフォーマンス最適化

SQLiteは、シングルプロセスで動作するように設計された軽量なデータベースエンジンです。しかし、マルチスレッドアプリケーションでSQLiteを使用する場合、いくつかの重要な考慮事項があります。スレッドセーフモード (Thread Safety Modes)

ネットワーク越しSQLiteプログラミング：セキュリティとパフォーマンスの考慮事項

ファイルロックの問題 (File Locking Issues) SQLiteは、データベースへの同時アクセスを制御するためにファイルロッキング機構を使用します。ネットワークファイルシステム（NFS、SMBなど）は、ローカルファイルシステムと比べてファイルロックの挙動が不安定であったり、完全にサポートしていなかったりする場合があります。複数のクライアントが同時に同じSQLiteデータベースファイルにアクセスしようとすると、データの破損や予期せぬエラーが発生する可能性があります。これは、ネットワーク越しではロックの取得や解放のタイミングが保証されないためです。