ハクソク

世界を動かす技術を、日本語で。

SQLiteのファイルフォーマット

263日前原文(sqlite.org)

概要

  • SQLiteデータベースファイル形式 の全体像解説
  • メインデータベースファイル と ジャーナル/WALファイル の役割
  • ページ構造 と ファイルヘッダー の詳細
  • 各種バージョン番号 や フリーページリスト の意味
  • スキーマフォーマット番号 の違いと互換性

SQLite データベースファイル形式の概要

  • SQLite のデータベース状態は、通常 単一のファイル （メインデータベースファイル）で管理
  • トランザクション中は ロールバックジャーナル または WALファイル に追加情報を保存
  • ホットジャーナル や ホットWALファイル はクラッシュ回復時にのみ必要となる特別ファイル
  • 本ドキュメントは 主にメインデータベースファイルのフォーマット を解説

ページ構造とサイズ

  • データベースファイルは ページ単位 で構成、ページサイズは 512～65536バイト の2のべき乗
  • ページサイズは、ファイル先頭から 16バイト目の2バイト整数値 で決定
  • ページ番号は 1から開始、最大4294967294ページ
  • 最小データベースサイズ は512バイト、最大は約281TB
  • 実運用では数KB～数GBが一般的、まれにTB級事例も存在

ページ種別

  • 各ページは以下いずれかの用途
    • B-treeページ（テーブル/インデックスの内部・リーフ）
    • フリーリストページ（トランク/リーフ）
    • ペイロードオーバーフローページ
    • ポインタマップページ
    • ロックバイトページ

データベースファイルヘッダー

  • ファイル先頭 100バイト がヘッダー領域
  • ビッグエンディアン 形式で多バイト値を格納
  • 主なフィールド（オフセット:サイズ:内容）
    • 0:16: "SQLite format 3\000"（マジックナンバー）
    • 16:2: ページサイズ（512～32768、または1で65536）
    • 18:1: ファイルフォーマット書き込みバージョン（1=レガシー, 2=WAL）
    • 19:1: ファイルフォーマット読み取りバージョン（1=レガシー, 2=WAL）
    • 20:1: 各ページ末尾の予約バイト数（拡張用、通常0）
    • 21:1/22:1/23:1: ペイロード比率（固定値64/32/32）
    • 24:4: ファイル変更カウンター
    • 28:4: データベースサイズ（ページ数）
    • 32:4: フリーリスト最初のページ番号
    • 36:4: フリーリスト合計ページ数
    • 40:4: スキーマクッキー（スキーマ変更時に更新）
    • 44:4: スキーマフォーマット番号（1～4）
    • 48:4: デフォルトページキャッシュサイズ
    • 56:4: テキストエンコーディング（1=UTF-8, 2=UTF-16le, 3=UTF-16be）
    • 60:4: ユーザーバージョン（PRAGMA user_versionで管理）
    • 64:4: インクリメンタルバキュームモードフラグ
    • 68:4: アプリケーションID（PRAGMA application_idで設定）
    • 72:20: 予約領域（ゼロ固定）
    • 92:4: version-valid-for番号
    • 96:4: SQLITE_VERSION_NUMBER

ページサイズの詳細

  • オフセット16の2バイト値でページサイズ決定
    • 512～32768 はビッグエンディアン整数
    • 65536バイト は0x00 0x01（ビッグエンディアンで1、マジックナンバー扱い）

ファイルフォーマットバージョン

  • 書き込み/読み取りバージョン （オフセット18,19）は将来拡張用
  • 現行SQLiteでは 1=ロールバックジャーナル、2=WAL
  • 読み取りバージョンが2超の場合は読み書き不可

ページ末尾の予約バイト

  • 拡張機能用に 各ページ末尾に追加バイト を予約可能
  • 通常0、SQLite Encryption Extension等で利用
  • ページ有効サイズは「ページサイズ-予約バイト数」
  • 有効サイズは 480バイト以上 必要（例：512バイトページなら予約最大32バイト）

ペイロード比率

  • 最大/最小/リーフペイロード比率 はそれぞれ 64/32/32 で固定
  • 本来はチューニング用だったが、現状は変更不可

ファイル変更カウンター

  • 4バイト整数（オフセット24） で管理
  • 他プロセスによる変更検知やキャッシュ無効化に利用
  • WALモードでは使用されない場合あり

データベースサイズの判定

  • オフセット28の4バイト値 が有効ならそれを使用
  • 無効時は実ファイルサイズ参照
  • 有効性判定は「変更カウンター」と「version-valid-for番号」が一致しているかで判断

フリーページリスト

  • 未使用ページ はフリーページリストで管理
  • オフセット32: 最初のフリーページ番号
  • オフセット36: フリーページ総数

スキーマクッキー

  • 4バイト整数（オフセット40） で管理
  • スキーマ変更時にインクリメント
  • プリペアドステートメントの再準備判別に利用

スキーマフォーマット番号

  • 4バイト整数（オフセット44）
  • 現在サポートされる番号は 1～4
    • 1: 最初期フォーマット
    • 2: ALTER TABLE ... ADD COLUMNサポート
    • 3: 非NULLデフォルト値付き追加カラムサポート
    • 4: インデックスDESCサポート等（デフォルト）
  • 新規ファイルは フォーマット4 が標準
  • sqlite3_db_config() のオプションでフォーマット1の作成も可能

この内容はSQLiteデータベースファイルの 物理構造理解や互換性管理、 エンジニアリング用途 に必須の情報。

Hackerたちの意見

ここのファイルフォーマットの内部構造がしっかりドキュメント化されてるのは本当にありがたいね。SQLiteの内部動作についてたくさんの情報がわかるし、読むことを強くおすすめするよ。実際、雨の日用にコピーを保存しておいたんだけど、すごく勉強になったし、将来SQLiteを使う際のデザイン決定にも影響を与えたよ。

└

フォーマット自体はアメリカの連邦標準で、変更できません。それには利点と欠点があります。 https://www.sqlite.org/locrsf.html

公式のSQLiteデータベースファイルフォーマットのページから。最大サイズのデータベースは4294967294ページ、1ページあたり65536バイト、つまり281,474,976,579,584バイト（約281テラバイト）になる。通常、SQLiteは自分の内部サイズ制限に達する前に、基盤となるファイルシステムやディスクハードウェアの最大ファイルサイズ制限に達しちゃうんだよね。

└

「通常」？確かに、単一のファイルシステムでこれだけのスペースを持つコンピュータが存在しないわけじゃないけど…実際に誰かが単一のSQLiteファイルでこの制限に達したケースってあったのかな？

└

KioxiaのLC9は最大245TBの容量で売られてるから、281TBを超える単一ディスクが登場するのも、あと1年くらいの話だね。

└

HPCシステムでより大きなファイルを見たことあるよ。確かに、これらは意図的に生成されたわけじゃないけど、でも実際にあったんだよね。

見た中で一番面白いのは、SQLiteのデータベースをHTTPサーバーに置いて、範囲リクエストを使って効果的に読み取れるってことだね。

└

つまり、HTTPサーバーをランダムアクセスできるデータストアとして使うってこと？それは正しい感じだね。

└

俺の経験では、データベースが読み取り専用のときにこれがうまくいく。そういう読み取り専用の場合は、Duckdb Wasmでクエリをかけたパーケットファイルを使うかな。

└

それに対する実装があるよ。例えば、https://github.com/psanford/sqlite3vfshttp や https://github.com/phiresky/sql.js-httpvfs みたいな。

└

そのリクエストのレイテンシは重要だよね。できるだけ大きなページサイズを使った方がいいし、keep aliveも活用した方がいいよ。でもそれでも、リクエストごとに最大64 KiBしか引き出せないんだ。もし応答時間が10msだったら、最大で52Mbpsしか出ない。だから、クエリが数ページだけを読むなら問題ないけど、全テーブルスキャンが必要なら、レイテンシを隠すために賢いプリフェッチングやキャッシングが必要だね。

たまに、もっと良いファイルフォーマット、例えばパーケットみたいな行指向のものができないかなって考えることがある。

SQLiteは、単一の要素が他のすべてを上回る良い例だと思う。単一のファイルに含まれるデータベースってアイデアが素晴らしくて、設計が悪いストレージレイヤーやカラムフォーマット、ひどいSQL実装を上回ってる。もしクラフトマンシップが、何かを洗練された感じにするための良い選択の長い尾で測られるなら、SQLiteはそのどれも持ってない。でも、それでもデータベースが必要なプロジェクトには間違いなく最初の選択肢としてベストだね。ほとんどのプロジェクトは、もっと別のものに切り替える必要がないだろうし。

└

もしクラフトマンシップが、何かを洗練された感じにするための良い選択の長い尾で測られるなら、SQLiteはそのどれも持ってない。どうやら、広範で徹底したテストスイートがあるみたいだね。それは他の多くのプロジェクトが学ぶべき素晴らしい設計選択で、成功の鍵となる要素かもしれない。時には、設計が悪いけど優れたドキュメントと徹底したテストがあるものの方が、設計が素晴らしいけどそれが欠けているものよりも良いことがある。実際、もしそのもののユーザー数が1（クリエイター）でない限り、前者の方がすべての可能なユースケースにおいてより良い選択肢になる可能性が高い。これを一般化して、「決定論 > パレート最適性」と言えるかもしれないね。

└

デジタル・エクイップメント・コーポレーションは、RdbというSQLデータベースを販売していて、単一ファイルとしても動作しました。コストベースのオプティマイザーを初めて導入したデータベースで、VMSとDigital UNIXの両方で動作しました。Oracleが買収して、VMSバージョンは今でもサポートされています。 https://www.oracle.com/database/technologies/related/rdb.htm... https://en.m.wikipedia.org/wiki/Oracle_Rdb （私の雇い主は今でもVMSバージョンを使っています。）

└

設計が悪いストレージレイヤー、設計が悪いカラムフォーマット、ひどいSQL実装 これって他の人も同じ意見なの？

└

Tcl用のDBとして設計されたもので、その言語には型付きオブジェクトがなかった時代のことです。そのSQL実装もそれを反映しています。PythonやPerl、JSのDBはどこにあるんだろう？

└

Firebirdも条件に合うと思うけど、あんまり普及しなかったね。Firebirdはクライアントサーバーの展開もサポートしてるし。

└

なんか不必要にネガティブなコメントだね。私はSQLiteを20年以上使ってるけど（時間が経つの早い！）、君が言う「洗練されていない」っていうのは、Dr. Hippが長期的な互換性を維持することに気を使っているからだと思うよ。図書館協会がデータの長期保存に推奨するくらいだからね。長期的な互換性（つまり、ユーザーのニーズを優先することと、洗練されたものや完璧なものを追い求めることのバランス）、テストと品質への近い狂信的な献身、数十年にわたる持続的な改善 - これこそがエンジニアリングプロジェクトにおける真の職人技の証だよ。（それに、ストレージ層やカラムフォーマット、SQLの実装が悪いとは思わないな。）

└

MS AccessやInterbase/Firebird、dBase IIと全く同じだね。

└

poorly designed storage layer, poorly designed column formats, and a terrible SQL implementation それについて詳しく説明してほしいんだけど、何のことを言ってるのか全然わからないし、ここにいる誰もわからないみたいだよ。これは主に単一ユーザー向けのリレーショナルデータベースだし、SQLだよ。ちゃんと機能するし、パフォーマンスもいい。驚くほど信頼性が高い。知っている限りでは、カラムがタイプを混ぜられるという明らかに疑わしい設計決定があるけど、それは「悪い設計」というより「違った設計」って感じで、小さな整数の自動的なスペース節約には素晴らしいんだよね。ALTER TABLEが制限されているのも事実だけど、回避策もあるし、実際の運用ではそんなに頻繁にやることじゃないし。具体的にどんな問題があるの？

└

テストカバレッジや互換性、持続可能なサポートに関しては、いい仕事をしていると思う。他の多くのハイプなデータベース、特にフォーチュン500企業が作って3年後に閉鎖されるようなものには言えないけどね。

データベースのページサイズ（バイト単位）。512から32768の間の2の累乗でなければならないか、65536のページサイズを表す値1でなければならない。なんて奇妙なデザインなんだろう。ページサイズの2進数対数を値にすればよかったのに、つまり9から16の間の値で。

└

予想するに、このフィールドは65536のページサイズがサポートされる前に指定されたんだと思います。その時点では、65536のページサイズに対して値1を使うのが最も理にかなっていました。

└

非2のべき乗のディスクセクターサイズを持つハードウェアが存在するよね。今のSQLiteの実装は2のべき乗を必要とするけど、将来的にはそうじゃなくなる可能性もあるかも。64kの表現は、たぶん後から考えたことなんだろうね。

└

「ページサイズの2の対数を値にすればいいんじゃない？つまり、9から16の間の値で。」そうだね、それがもっと良い選択だったかも。もともと、ファイルフォーマットは512から32768の間のページサイズしかサポートしてなかったから、実際の数値を2バイトの整数に詰め込むのは自然なことに思えたんだ。65536のページサイズ機能は、数年後にクライアントのリクエストで追加されたから、後方互換性を保ちながら65536のページサイズを実装しなきゃいけなかった。デザインは人間が読みやすいとは言えないけど、パフォーマンスの問題や不合理なコードの複雑さはないよ。ページサイズの値だけが奇妙なわけじゃなくて、ファイルフォーマットにはもっと改善できた点がある。でも、何兆ものデータベースが流通してる今、これらの些細な quirks をそのままにしておく方がいいと思う。新しい、もっと完璧だけど互換性のないフォーマットを作ろうとするよりね。

ネットワークストレージに適した、書き込み一回（文字通り一回）のデータストレージフォーマットについて、HNからおすすめはありますか？sqliteのドキュメントではネットワークストレージでの使用を避けるように推奨されていますが、実際に読み取りだけを行うなら問題は少ないみたいです（つまり、ローカルで作成してからネットワークストレージにコピーできる）。Apache Parquetは有望そうで、今はインデックスもサポートしているみたいですね。これは重要な要件です。

└

私の経験では、SQLiteは読み取り専用のNFS上でも問題なく動作します。ただし、不変のコピーで作業し、変更する場合はアプリケーションを再起動してください。アプリケーションが短命で、パス上で不変のコピーしか見えないなら、素晴らしい解決策です。

└

SQLiteはNFSでも読み書きのシナリオで動くよ。偶然発見したけど、俺の言ってることは間違ってない。WALモードはネットワークファイルシステムでは明示的にサポートされてないけど、期待してないよね :)

└

パーケットファイルを使ってるよ。

└

ネットワークストレージでの複数のライターが問題なんだよね。読み取りは全然問題ないはず。

└

squashfsの中の普通のファイル？ https://www.kernel.org/doc/html/latest/filesystems/squashfs....

└

NFS上のSQLiteは、1人のライターと多くのリーダーがいる場合はうまくいくよ。

2025年だよ。ストレージと処理を分けよう。SQLiteは埋め込みデータベースがどれだけエレガントになれるかを示したけど、本当に勝ってるのはParquetみたいなフォーマットだね。つまらないけど耐久性のあるストレージで、どんなエンジンでも読めるやつ。ストレージはシンプルに、計算はスワップ可能に。これが未来だよ。

└

反論: 「Parquetの2つのバージョン」 https://news.ycombinator.com/item?id=44970769 （17日前、50件のコメント）

└

短い説明を読んだ限りでは、Parquetはデータを選択するために作られたカラム指向のフォーマットで、更新には使いにくい（Yandex Clickhouseみたいに）。

SQLiteのファイルフォーマットで唯一気になるのは、ファイルの一部が壊れたら、残りのファイルを簡単に復元できないことだね。何年も前にRichard Hippにこのことを聞いたら、残念ながら問題を修正するとバイナリ互換性が壊れるって言われたよ。

これが数ページに収まっていて、しかもとてもわかりやすいのは、そのシンプルさの証だと思う。例えば、Wordのdoc/docxファイルのファイルフォーマットを理解するのは、もっと難しいと思う。

└

.docと.docxを並べるのはやめた方がいいよ。関係はあるけど、あまりにもかすかなものだからね。最初の.docxの「Hello, World!」を作るのはちょっと面倒かもしれないけど、理解するのは難しくないと思う。ECMA 376 4th Edのブラウザで見れるコピーはすぐには見つからなかったけど、https://github.com/PumasAI/WriteDocx.jl/blob/v1.2.0/docs/src... は、実際の仕様書のどこかで見つけるだろうと思っていたものに近い感じだったよ。