SQL

「ファイルベースだから遅い」は本当か SQLite をサーバー用途に使う話をすると、たいてい「やめておいたほうがいい」と言われます。これ自体はでたらめな脅しではなく、SQLite の公式ドキュメント（Appropriate Uses For SQLite ）自身が、同時書き込みの多いサービスにはクライアント/サーバー型のデータベースを勧めています。では、なぜ駄目なのか。理由を考え始めたとき、まず思い浮かぶのはこんな筋書きではないでしょうか。SQLite の実体はただのファイルで、ファイルの読み書きはディスク I/O。Redis のようなキャッシュ（メモリ上でキーと値を預かる、キャッシュ用途の定番サーバーです）が「メモリで持つから速い」と説明されるのなら、その裏返しで、ディスクに読み書きする SQLite は遅いはずだ——一見、筋が通って聞こえます。 本当にそうか、まず手元で測ってみます。10 万行のテーブルに主キーで 1 件ずつ問い合わせる、キャッシュ用途を想定したいちばん素朴な読み取りです。コードは Python の標準ライブラリだけで動きます。実験はすべて使い捨ての Docker コンテナ（Linux）上で実行していて、再現手順は記事末尾にまとめました。 import sqlite3, time, random con = sqlite3.connect("cache.db") con.execute("CREATE TABLE kv (k TEXT PRIMARY KEY, v TEXT)") with con: con.executemany("INSERT INTO kv VALUES (?, ?)", ((f"k{i}", "x" * 100) for i in range(100_000))) keys = [f"k{random.randrange(100_000)}" for _ in range(200_000)] cur = con.cursor() t0 = time.perf_counter() for k in keys: cur.execute("SELECT v FROM kv WHERE k = ?", (k,)) cur.fetchone() print(200_000 / (time.perf_counter() - t0)) 手元（Apple シリコンの Mac 上の Linux コンテナ、SSD）ではこうなりました。 ...

COMMIT した瞬間、PostgreSQL は本体に書いていない PostgreSQL を運用していると、少し不思議に思う場面があります。大量の書き込みが走っているのに、一件の COMMIT はほとんど待たされずに返ってきます。それでいて、サーバーの電源が突然落ちても、再起動すればコミット済みのデータはちゃんと残っています。速さと壊れにくさは、普通なら両立しにくいものです。それでもデータベースは、平然と両方をこなしているように見えます。 この不思議は、COMMIT を「変更をデータファイルへ書き終えた合図」だと思っていると説明できなくなります。実際には、PostgreSQL は COMMIT の時点でテーブルの本体（データファイル）を書き換えていません。先にすることは別にあります。入口で図にすると、素朴なイメージと実際の動きはこう違います。 PostgreSQL は、データ本体を書き換えるより先に、その変更内容をまず追記専用の日誌へ書き終えます。この日誌が WAL（Write-Ahead Logging、先行書き込みログ）です。日誌への追記が終わった時点で COMMIT は完了として返り、本体のデータファイルへの反映はあとでまとめて行われます。 順序を言葉にすると「ログが先、本体はあと」です。たったこれだけの取り決めが、クラッシュ耐性・レプリケーション・任意時点への復元（PITR）という、一見別々に見える機能をまとめて支えています。この記事では、その日誌が実際に伸びていく様子を psql で覗き、kill -9 でサーバーを殺してから自動で復旧するところまでを、使い捨ての PostgreSQL で目の前で起こしてみます。 先にログ、あとで本体 なぜ「先にログ」だと速くて壊れにくいのか、先に理屈を押さえておきます。 テーブルの本体は、行がどのページに入るかがばらばらに決まります。あちこちのページを更新するたびにディスクの離れた場所へ書きにいくのは、順番の定まらない書き込みで、ディスクにとっては不得意な動きです。一方 WAL は、変更内容を起きた順に末尾へ足していくだけの一本のログです。同じ場所へ続けて書き足していく動きはディスクが最も得意とするもので、しかも「末尾まで書き終えた」ことさえ保証できれば、その時点でコミットを確定してよくなります。だから COMMIT は本体の更新を待たずに素早く返せます。 壊れにくさも同じ仕組みから来ます。WAL には「どの行をどう変えたか」が起きた順に残っています。もし本体への反映が途中で電源断に巻き込まれても、再起動後に、直近のチェックポイント以降の WAL を読み直して同じ変更をなぞり直せば、落ちる直前のコミット済みの状態へたどり着けます。復旧は、いまディスクに残っている本体のデータファイルへ、まだ反映しきれていない分の WAL を継ぎ足し、最新の状態へ組み立て直す形で進みます。 ここで見落としやすい点があります。COMMIT が返っても、本体のデータファイルはその場では書き換わりません。変更が当たったページはいったんメモリ上（共有バッファ）に汚れたページ（dirty page）として残り、ディスクへ確実に書き切られるのは WAL のほうだけです。本体ファイルへの反映は、あとから動くチェックポイントやバックグラウンドの書き出しに任され、状況によっては数分遅れて行われます。コミットのたびに本体のあちこちへ書きにいく遅い動きを避けるための、意図的な後回しです。 図にすると、コミットの瞬間とチェックポイントの瞬間とで、三つの置き場所（メモリ・WAL・本体）の状態はこう食い違います。 この「コミット済みだが本体はまだ古い」という状態は一瞬ではありません。次のチェックポイントまで、ふつうは数秒から数分のあいだ続きます。ただし本当の勘所は「窓が広いから狙わなくてよい」ではなく、そもそも復旧が「どこまで本体へ書けていたか」に左右されない作りだ、という点です。redo は各ページに刻まれた適用済みの位置を見て、まだ当たっていない変更だけを足し直します。だから本体がどれだけ古くても、あるいは一部が先に書けていても、行き着く先は同じコミット済みの状態になります。あとで kill -9 の実験がタイミング合わせ抜きで成立するのは、このためです。 ここで出てくる用語を先に一つだけ紹介しておきます。WAL の中の位置を指す番地を LSN（Log Sequence Number）と呼びます。0/1514378 のように書かれる、ログの先頭からの通し番号だと思ってください。書き込みが進むと LSN は増えていきます。以降の実験は、この LSN が動くのを見るところから始めます。 ...

UPDATE しかしていないのに、なぜテーブルは太るのか PostgreSQL を運用していると、「DELETE はほとんどしていないのにテーブルの物理サイズが増え続ける」「夜間バッチで大量 UPDATE が走っているのに、別画面の SELECT は思ったほど待たされない」という現象に出会います。 この不思議は、PostgreSQL の UPDATE を「今ある行の値を書き換える操作」だと思っていると説明しにくくなります。入口で先に図にすると、実際の動きは次のようになります。 PostgreSQL の UPDATE は上書きではなく追記です。新しい行バージョンを追加し、古い行バージョンには「このトランザクションで見えなくなった」という死亡印を付けます。DELETE も物理的にその場で消すのではなく、対象の行バージョンに死亡印を付ける操作です。 この記事では、この一点から実務でよく見る挙動をつなげます。SELECT / UPDATE など SQL の基本操作そのものを確認したい場合は、先に PostgreSQLと向き合うための 現場で使えるデータベース操作・SQLノート を見ておくと、以降の psql ログを追いやすくなります。 行バージョンを見分ける xmin と xmax PostgreSQL は MVCC、つまり Multi-Version Concurrency Control という仕組みで同時実行を制御します。日本語にすると「複数バージョンを使った同時実行制御」です。ひとつの論理的な行に対して、必要に応じて複数の行バージョンを持ち、読み取り側は自分のスナップショットから見えるバージョンだけを読みます。 スナップショットとは、ある時点でどのトランザクションが完了済みで、どのトランザクションがまだ実行中だったかを判断するための見取り図です。どの範囲を一つのスナップショットで通すかは、トランザクション分離レベルという設定で決まります。既定の READ COMMITTED では SQL 文ごとに新しいスナップショットを取り直すため、実行中に別のトランザクションが確定した変更は次の文から見えます。一段上の REPEATABLE READ にすると、トランザクションが最初に読んだ時点のスナップショットが最後まで固定され、同じ SELECT を何度実行しても結果が変わりません。この記事の後半では、この固定が効く様子も実際に見ます。 行バージョンには、通常の id や name のような列とは別に、PostgreSQL が内部的に持つシステム列があります。この記事で見るのは次の 3 つです。 列 役割 xmin その行バージョンを作ったトランザクション ID xmax その行バージョンを削除または更新で見えなくしたトランザクション ID。何もなければ 0 ctid その行バージョンがテーブル内のどこにあるかを示す物理位置 トランザクション ID は、PostgreSQL が書き込みトランザクションを識別するために付ける番号です。記事中では XID とも書きます。xmin は「この XID が作った」、xmax は「この XID が死亡印を付けた」と読むと理解しやすいです。 ...

はじめに このブログの記事Aと記事Bがどれだけ類似しているかを検出して、関連記事を自動化したいと思った。そこで簡単に思いつく方法は、それぞれの記事のキーワードを抽出し、お互いどれだけ共通のキーワードを持っているか調べれば良さそう。 そこでJaccard係数という手法を見つけた。 Jaccard係数とは Jaccard係数は、2つの集合 ( A ) と ( B ) の類似度を測る数学的なアプローチ。 $$ J(A,B)=\frac{|A\cap B|}{|A\cup B|} $$ 記事Aと記事Bのキーワードに当てはめて集合を考えると、 重複を取り除いた記事Aと記事Bのキーワードの合計が分母 重複する部分のキーワードだけを抽出したのが分子 となる。そしてこの割り算の結果をJaccard係数とし、 係数が大きいほど類似度が高いことを意味する。 Jaccard係数はsqliteで簡単に計算できる 次は、実際にこのブログサイトで関連記事を作成するために使っているSQLである。 全ての記事に対してJaccard係数を計算し、係数が0.1以上のものを関連記事として抽出する処理をさせている。 WITH target_words AS ( SELECT word FROM keywords WHERE filename = '__FILENAME__' ), intersections AS ( SELECT k.filename, COUNT(*) AS intersect_count FROM keywords k JOIN target_words t ON k.word = t.word WHERE k.filename != '__FILENAME__' GROUP BY k.filename ), unions AS ( SELECT k.filename, COUNT(DISTINCT k.word) + (SELECT COUNT(DISTINCT word) FROM target_words) - COUNT(DISTINCT CASE WHEN k.word IN (SELECT word FROM target_words) THEN k.word END) AS union_count FROM keywords k WHERE k.filename != '__FILENAME__' GROUP BY k.filename ) SELECT i.filename, CAST(i.intersect_count AS REAL) / u.union_count AS jaccard FROM intersections i JOIN unions u ON i.filename = u.filename WHERE CAST(i.intersect_count AS REAL) / u.union_count >= 0.1 ORDER BY jaccard DESC; 複雑に入り組んでいて読みにくいが、Jaccard係数を計算するために共通部分の要素を求めるintersectionsと、キーワードの和集合を求めるunionsがポイントである。 ...

iOSアプリ開発でSQLiteを使う FMDB はじめに iOSアプリ開発で、SQLiteを使ってデータ管理する方法を調べてみました。SQLiteをそのまま使うよりは、SQLiteをラッパーしたFMDBライブラリを使うとより便利です。そちらもご紹介いたします。 iOSアプリ開発ではじめてSQLiteを使う libsqlite3.tbdをプロジェクトに追加 libsqlite3.tbdをプロジェクトに追加します。 Xcodeのプロジェクトナビゲータでプロジェクト名を選択し、「Build Phases」タブに移動します。「Link Binary With Libraries」セクションで、+ボタンを押して、libsqlite3.tbdを追加します。 ソースコード // // ViewController.swift // SampleSqlite // // Created by Toshihiko Arai on 2024/10/11. // import UIKit import SQLite3 // 日本語文字を登録するために必要な定数 let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self) class ViewController: UIViewController { var db: OpaquePointer? override func viewDidLoad() { super.viewDidLoad() // データベースの準備 if let databasePath = createDatabase() { openDatabase(path: databasePath) createTable() insertData(name: "空条承太郎", age: 40) insertData(name: "空条徐倫", age: 17) fetchData() } } // データベースのパスを取得して作成 func createDatabase() -> String? { let fileURL = try? FileManager.default .url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false) .appendingPathComponent("sample.sqlite") return fileURL?.path } // データベースのオープン func openDatabase(path: String) { if sqlite3_open(path, &db) != SQLITE_OK { print("データベースを開くことができません") } } // テーブルの作成 func createTable() { let createTableString = """ CREATE TABLE IF NOT EXISTS Person( Id INTEGER PRIMARY KEY AUTOINCREMENT, Name CHAR(255), Age INTEGER); """ var createTableStatement: OpaquePointer? if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK { if sqlite3_step(createTableStatement) == SQLITE_DONE { print("テーブルの作成に成功しました") } else { print("テーブルの作成に失敗しました") } } else { print("テーブル作成の準備に失敗しました") } sqlite3_finalize(createTableStatement) } // データの挿入 func insertData(name: String, age: Int32) { let insertStatementString = "INSERT INTO Person (Name, Age) VALUES (?, ?);" var insertStatement: OpaquePointer? if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK { sqlite3_bind_text(insertStatement, 1, name, -1, SQLITE_TRANSIENT) sqlite3_bind_int(insertStatement, 2, age) if sqlite3_step(insertStatement) == SQLITE_DONE { print("データの挿入に成功しました") } else { print("データの挿入に失敗しました") } } else { print("挿入ステートメントの準備に失敗しました") } sqlite3_finalize(insertStatement) } // データの取得 func fetchData() { let queryStatementString = "SELECT * FROM Person;" var queryStatement: OpaquePointer? if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK { while sqlite3_step(queryStatement) == SQLITE_ROW { let id = sqlite3_column_int(queryStatement, 0) let name = String(cString: sqlite3_column_text(queryStatement, 1)) let age = sqlite3_column_int(queryStatement, 2) print("Query Result:") print("ID: \(id), Name: \(name), Age: \(age)") } } else { print("クエリの準備に失敗しました") } sqlite3_finalize(queryStatement) } } SQLiteのデータ型 SQLiteには4つの基本的なストレージクラス（データ型）があります。 ...

はじめに この記事では、PostgreSQL を実務で使うときに毎回見直したくなる操作を 1 本にまとめています。Rocky Linux と macOS でのインストール手順、LAN 越しの外部接続設定、pg_dump / pg_restore を使ったバックアップ・リストア、そして psql の便利コマンドや SQL の基本までを順に確認できる構成です。 MySQL について同じ粒度でまとめたものは MySQLと向き合うための 現場で使えるデータベース操作・SQLノート をご覧ください。 Rocky Linuxにインストール PostgreSQL 13.15 を Rocky Linux にインストールする手順は以下の通りです。 メモ: 記事執筆時は PostgreSQL 13 系を入れています。PostgreSQL 13 はコミュニティサポートが 2025 年 11 月で終了しているため、新規構築では postgresql16-server など現役メジャーバージョンの利用を検討してください。手順の流れ自体はバージョンとリポジトリの番号を読み替えればそのまま使えます。また、Rocky Linux 9 系を使っている場合は EL-8 を EL-9 に置き換えてください。 リポジトリを有効化する まず、PostgreSQL 13 のリポジトリを追加します。 sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm PostgreSQL 13をインストールする PostgreSQL 13 のインストールを行います。AppStream 側に同梱されている古い postgresql モジュールが優先されないよう、無効化してから入れるのが安全です。 sudo dnf -qy module disable postgresql sudo dnf install -y postgresql13-server PostgreSQLの初期化 PostgreSQLのデータベースを初期化します。 ...

このページは MySQL を使うときに毎回開いて確認している自分用のノートをまとめたものです。CLI のセットアップから root パスワード設定、データベースとテーブルの基本操作、mysqldump でのバックアップと復元、\G や pager less -S といった結果表示の小技、よく使うデータ型、INSERT・UPDATE・JOIN・GROUP_CONCAT などの SQL までを 1 ページに集めています。 特定の章だけ拾い読みできるよう小さなトピック単位で並べているので、目次から目的の項目に飛んで使う想定です。PostgreSQL を使うときの同じ粒度のノートは PostgreSQLと向き合うための 現場で使えるデータベース操作・SQLノート にまとめています。 MySQL開発環境のセットアップ 項目 バージョン インストールコマンド macOS 14.2 (Sonoma) MySQL 8.0 brew install [email protected] 上記は記事執筆時に動作確認した組み合わせです。2026年5月時点では Homebrew で brew install mysql を実行すると MySQL 9.x が入り、長期サポート版の [email protected]（LTS）も選べます。新規に環境を作るならどちらかをおすすめします。バージョンを固定したい場合は [email protected] も引き続き利用できますが、旧安定系列で現在は通常サポートが終了しており、新規環境には [email protected]（LTS）などをおすすめします。macOS 15 (Sequoia) などより新しいバージョンでも、Homebrew を使う限り手順はほぼ変わりません。 インストールが完了したらCLIでmysqlコマンドを使えるように、環境パスを通しておきます。[email protected] 以外を入れた場合は、[email protected] などインストールした formula 名にパスを読み替えてください。 export PATH="/opt/homebrew/opt/[email protected]/bin:$PATH" mysqlの起動と停止 $ mysql.server start # 起動 $ mysql.server stop # 停止 MySQLの初期設定 rootユーザーにパスワードを設定する Homebrew でインストールした直後の MySQL 8.x は、root パスワードが未設定のまま起動するので、まずは無パスワードで入って初期パスワードを設定します。Linux ディストリビューションのパッケージ（apt / dnf など）で入れた場合は、初回起動時に一時パスワードがログ（/var/log/mysqld.log など）に出ているか、mysql_secure_installation で対話的に設定する流れになるので、環境に合わせて読み替えてください。 ...