SQLiteにもRustスタイルのエディションが必要

 (mort.coffee)
3 ポイント 投稿者 GN⁺ 4 시간 전 | 2件のコメント | WhatsAppで共有
  • SQLiteはローカルストレージや組み込みプロジェクトで広く使われる自己完結型RDBMSだが、後方互換性のために安全性と性能に不利なデフォルト設定を維持している
  • デフォルトでは外部キー制約を強制せず、ROWIDを再利用できるため、削除されたユーザーの投稿が同じIDを割り当てられた別のユーザーに結び付くデータ整合性の問題が起こりうる
  • 通常のテーブルは宣言された列と異なるデータ型も保存でき、STRICTテーブルはこれを防ぐが、テーブルごとにstrictを指定する必要があり、ユーザー定義型名を活用していたやり方と衝突する
  • 同時書き込み時に即座に発生するSQLITE_BUSY、無効化されたWAL、保守的な同期設定は、それぞれbusy_timeout、journal_mode、synchronousプラグマで改善できる
  • PRAGMA edition = 2026のような年ベースのエディションで安全なデフォルト設定の束を選べるようにすれば、既存の挙動を壊さずにSQLiteのデフォルト設定を継続的に進化させられる

SQLiteの強みと古いデフォルト設定

  • SQLiteは組み込みプロジェクトのデータベースであり、ローカルデータ保存の業界標準と呼べる存在で、一部のサーバーソフトウェアであるlobste.rsもSQLiteで動作している
  • 別プロセスとして動作する従来のRDBMSとは異なり、ライブラリ形式のRDBMSなのでソフトウェアを自己完結型のまま保てる
  • 一般的なファイル形式のようにユーザー定義のシリアライザやパーサーを書く必要がなく、RDBMSとファイルベース保存方式の利点をあわせ持つ
  • しかし、データ整合性、同時実行性、性能に影響するいくつものデフォルト設定が不適切という問題が残っている

外部キーを強制しないことで生じる整合性問題

  • 外部キーはデータベースの一貫性を保ち、切れた参照を防ぐための中核的な手段だが、SQLiteはデフォルトではこれを強制しない
  • posts.user_idがusers.idを参照するよう宣言しても、デフォルト設定では次の動作を防げない
    • 有効なユーザーIDを持たない投稿の作成
    • そのユーザーの投稿を残したままユーザーを削除
  • INTEGER PRIMARY KEYはテーブルのROWIDの別名となり、SQLiteのROWID割り当てアルゴリズムは一部の状況でIDを再利用する
    • BobがID 1で投稿を作成したあと、アカウントだけを削除しても外部キーエラーは発生しない
    • その後Aliceが再利用されたID 1を受け取ると、Bobの既存投稿がAliceに結び付く
    • クエリ自体は正常に成功するため、単純な切れた参照よりも問題を発見しにくい
  • 接続ごとに次のプラグマを有効にすれば外部キーを強制できる
PRAGMA foreign_keys = ON;
  • これを最初から適用していれば、投稿を残したユーザーの削除はFOREIGN KEY constraint failedエラーで中断される

宣言と異なるデータ型を許容する通常のテーブル

  • SQLiteの値はNULL、INTEGER、REAL、TEXT、BLOBのいずれかだが、通常のテーブルの列宣言は保存可能なデータ型を厳密に制限せず、型親和性(type affinity) を決める
  • INTEGER親和性の列には次の保存規則が適用される
    • 整数として解釈できるTEXTはINTEGERに変換される
    • 実数として解釈できるTEXTは倍精度浮動小数点のREALに変換される
    • それ以外の値は元のデータ型のまま保存される
  • 他の親和性にも別の変換規則がある
    • BLOB親和性は値をそのまま保存する
    • TEXT親和性はBLOB、TEXT、NULLをそのままにし、数値をTEXTへ変換する
    • REAL親和性はINTEGER親和性のように動作するが、整数をREALへ変換する
  • このため、duration_sec INTEGER列にも数値ではない'Way too long, I mean come on'のような文字列を保存できる
  • 実際のプロジェクトでは、ブール値用の数値1、0ではなく文字列'1'、'0'が記録され、データ整理やデバッグが必要になった例もあった

STRICTテーブルとユーザー定義型の衝突

  • STRICTテーブルは、列に誤ったデータ型を入れるとcannot store TEXT value in INTEGER columnのような型エラーを発生させる
CREATE TABLE music (
  id INTEGER PRIMARY KEY,
  name TEXT,
  duration_sec INTEGER
) strict;

  • すべてのテーブルをグローバルに厳格化するプラグマはないため、各CREATE TABLE文にstrictを漏れなく追加しなければならない

  • SQLiteは柔軟な型付けが有用だという立場だが、STRICTテーブルでもANYデータ型を明示すればあらゆる種類の値を保存する列を作れる

    • 任意のデータ型を許容する列と、特定のデータ型を強制する列を明示的に区別できる

  • より現実的な互換性問題は、STRICTテーブルが単にデータ型を強制するだけでなく、型指定子の解釈規則まで変えてしまう点にある

  • 通常のテーブルが型名を解釈する方法

    • 通常のSQLiteテーブルは宣言された型文字列に応じて親和性を決める
    • "INT"が含まれていればINTEGERとして扱う
    • "CHAR"、"CLOB"、"TEXT"のいずれかが含まれていればTEXTになる
    • "BLOB"が含まれるか型を省略した場合はBLOBとして扱う
    • "REAL"、"FLOA"、"DOUB"のいずれかが含まれていればREALになる
    • どの条件にも当てはまらなければNUMERICとして扱う
    • 緩い型付けとこの規則を組み合わせれば、DATETIME、KEY_VALUE_SET、COLORのようなユーザー定義型名を列に付けられる
    • データベースコネクタやラッパーは型名を見て自動でシリアライズ・デシリアライズできる
    • 特別な処理機能がなくても、型名そのものが列の想定データを文書化する
    • STRICTをそのままデフォルトにするとこの活用法を捨てることになるため、明示的なユーザー定義型エイリアスが必要になる
    CREATE TYPE KEY_VALUE_SET = TEXT;
    • 厳格なテーブルでKEY_VALUE_SETのようなエイリアスを使えれば、アプリケーション側で解析すべきTEXT列の想定データパターンもスキーマに記録できる
    • ユーザー定義型にCHECK制約を結び付ける機能も有用だ
    • SQL:1999標準には、制約までサポートする型エイリアス構文である**CREATE DOMAIN** がすでにあるため、SQLiteはこの標準構文をサポートする形で実装できる

同時書き込みとSQLITE_BUSY

  • SQLiteは複数の同時読み取りを許可するが、書き込みは一度に1つしか処理しない
  • デフォルト設定では、2つのプロセスが同時に書き込みロックを取得しようとすると、一方は待たずに即座に**SQLITE_BUSYエラー**を受け取る
    • ディスクI/Oを伴う書き込みはもともと遅くなりうるため、一定時間ロック解除を待つほうが自然だ
    • 実際のシステムが断続的に停止し、手作業で再試行ループを書く必要があった例もある
  • 次の設定では、最大5秒間ロック取得を再試行した後にのみSQLITE_BUSYを返す
PRAGMA busy_timeout = 5000;
  • 通常運用では、すべての書き込みを単一プロセス、できれば単一スレッドに集約するのが適切であり、同時書き込みが高速になることはない
  • ただし、同時書き込みが必要な正当な運用例もある
    • sqlite3コマンドラインツールでデータベースを対話的に整理する
    • 専用フロントエンドのないまれな管理作業用スクリプトを実行する
  • こうした作業はデフォルト設定のせいで予期しないSQLITE_BUSYを引き起こし、実行中のソフトウェアを停止させる可能性がある

WALと性能のデフォルト設定

  • SQLiteは正しく設定すれば、PostgreSQLやMySQLのような大型サーバーが担っていた役割の一部もこなせるほど高速になりうるが、デフォルト性能は良くない
  • サーバー環境向けの詳細なチューニングはOptimizing SQLite for serversでさらに確認できる
  • 最も重要なデフォルト設定の問題は、Write-Ahead Log、すなわちWALが無効化されている点だ
PRAGMA journal_mode = WAL;
  • WALはほとんどの状況で書き込み速度を大きく向上させる
  • 次の設定をあわせて使えば、データ破損のリスクを増やさずにディスク同期回数を大幅に減らせる
PRAGMA synchronous = NORMAL;

年ベースのSQLiteエディション

  • 既存のデフォルト設定を今すぐ直接変更すると、古いソフトウェアが壊れ、ユーザーが今後のSQLiteアップグレードを恐れるようになるかもしれない
  • 後方互換性を保ちながら改善されたデフォルト設定を選べるよう、1つの上位プラグマ(super pragma) を追加できる
PRAGMA edition = 2026;
  • 2026エディションは少なくとも次の設定のエイリアスとして動作する
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
  • このエディションでは新しいテーブルのstrictモードもデフォルトで適用される
  • Rust editionsのように、既存コードの挙動を保ちながら新しいデフォルト動作を明示的に選択する方式だ
  • JavaScriptの"use strict";のような単一モードとは異なり、年ベースのエディションは時代に応じて合理的なデフォルト設定の束を変えられる
  • たとえばHctreeのWAL2が2034年にメインブランチへ入るなら、将来のPRAGMA edition = 2034がjournal_mode = WAL2を選ぶように構成できる
  • エディション体系は、既存プログラムのデフォルト挙動を壊さずに、SQLiteが過去の設定に縛られず安全性と性能のデフォルト設定を進化させるための折衷案になりうる

関連記事

2件のコメント

 
tomskang 2 시간 전

理論としてはよさそうですが、互換性の衝突やオーバーヘッドを考えるとマイナスになりそうです。
SQLite はイージスのような組み込み環境で動作するために生まれた DBMS ですし、
void** をばんばん叩いて __weak__ を宣言した後に関数を上書きするのが組み込みの世界ですよね。そんな世界でこれをあえてやる必要があるのか、という気がします。
 
GN⁺ 4 시간 전
Lobste.rs の意見

  • 昨日、lobste.rs が SQLite に移行したという話（https://lobste.rs/s/ko1ji1/…
    そのときのやり取りを読みながら、SQLite のデフォルト設定にある細かな不便さを思い出し、ブログ記事として整理した

    • 私もそのやり取りを読んで、同じ結論に達した
      余力があれば、この文章を SQLite フォーラムにも共有する価値がある: https://sqlite.org/forum/forum

  • SQL で CREATE TYPE は複合型を作るときに使い、Postgres はここで範囲、列挙、配列、かなり複雑な基底型までサポートしている
    欲しい機能は CREATE DOMAIN に近い。基底となる具体型から新しい型（newtype）を作り、デフォルト値や制約条件も指定できるので、複数のテーブルで同じ CHECK 制約を繰り返すスキーマがずっと簡潔になる。たとえば create domain mything as text; や create domain mything as integer not null check (VALUE < 5); のように書ける

    • 既存の 型エイリアス機能があるか先に確認すべきだった。記事に関連内容を追記する

  • busy_timeout だけでは、即座に発生する SQLITE_BUSY エラーを防げないという、かなり危険な落とし穴も扱う必要がある
    読み取り専用クエリでトランザクションを開始したあと、保持しているロックを昇格させるために書き込みクエリを実行しようとして、他の書き込み処理があると、busy_timeout の設定に関係なく即座に失敗する。解決するには begin immediate を使う必要がある

    • 実際のバグではないのは分かるが、正直かなり バグのように感じる挙動ではある

  • 提案されたエディションにひも付く設定の一部は SQLite のモデルでは 接続属性で、残りはデータベースファイルや個々のテーブルの属性である点も考慮する必要がある
    PRAGMA journal_mode = WAL はデータベースファイルに永続化されるが、PRAGMA foreign_keys = ON は接続ごとに毎回設定しなければならない。STRICT は個々のテーブルの属性なので、1つのデータベース内に厳格なテーブルとそうでないテーブルを混在させることもできる。したがって、この 統合 PRAGMA をデータベースファイルと接続のどちらの属性として定義するのか決める必要がある。旧バージョンのライブラリが、自分の知らない機能を含むファイルを変更して壊してしまわないよう警告する組み込みの仕組みがあるのかも気になる

  • エディション方式は、基本的に下位互換性を維持しようとする SQLite 開発陣の目標と、最新の推奨設定を望むユーザーにとって分かりやすいデフォルト値との良い折衷案に見える

  • 最近 RocksDB にかなりはまっている。SQLite のような組み込みデータベースライブラリだが、SQL がないことがむしろ大きな利点だと思う
    すべてを SQL で表現しなければならないという制約は、データベースを扱ううえで邪魔にしかならないと感じる

    • RocksDB は キー・バリュー型データベースで、SQLite は リレーショナルデータベースなので、別の目的のために作られた別物の概念だと思う
      lobste.rs のように SQL を使っているシステムを、RocksDB や他のキー・バリューデータベースへどう移行するのか想像しにくい

  • busy_timeout を設定することが解決策だという点には同意しない。SQLite は 単一の書き込みスレッドを置いて、互いに無関係な論理トランザクションをまとめて処理すると、性能が非常に高い
    具体的な方法は https://andersmurphy.com/2025/12/… で説明されている

    • 一般的なワークロードでは、書き込み処理を1つに制限し、複数の書き込み処理が常に競合する構造を作らないほうがよい
      ただし、整理作業のためにデータベースを直接変更したり、専用の管理画面を作るほどではない断続的な管理作業をスクリプトで実行したりすることもよくある。このとき、たまたま実行タイミングが重なったというだけでサーバーやアプリケーションがランダムに停止しうるのは好ましくない