Posted inデータベース

多言語データベース設計：PostgreSQLとMySQLにおける3つの実証済み戦略

Database tutorial - IT technology blog
Database tutorial - IT technology blog

グローバル展開の厳しい現実

UI文字列を単一の言語でハードコーディングするのは、週末の個人プロジェクトなら問題ありません。しかし、アプリケーションが日本やドイツなどの市場で普及し始めると、その固定されたnameカラムがボトルネックになります。国際化（i18n）は単にテキストを翻訳することではありません。5つのタイムゾーンにまたがる10万人のユーザーを抱えても破綻しないようにデータを設計することです。

私は、ローカライズされたコンテンツをより効率的に処理するために、MySQLからPostgreSQLへの移行プロジェクトを管理したことがあります。あるケースでは、設計の不備により、4つ目の言語を追加した途端にクエリのレイテンシが3倍になりました。早期に適切な構造を選択することで、99.9%の稼働率を維持しながら500万行の商用テーブルをリファクタリングするという悪夢を避けることができます。

このガイドでは、大規模な多言語データを扱うための3つの実証済み戦略を紹介します。

サンドボックスのセットアップ

テストにはPostgreSQLまたはMySQLのインスタンスが必要です。基本ロジックは両方に適用できますが、PostgreSQLはJSONBのような特殊なツールを提供しており、非構造化データのローカライズにおいてわずかに有利です。

デモ環境を初期化しましょう。ターミナルで以下のコマンドを実行して開始します。

-- PostgreSQL用
CREATE DATABASE i18n_lab;
\c i18n_lab;

-- MySQL用
CREATE DATABASE i18n_lab;
USE i18n_lab;

ここではEコマースの「商品（Products）」テーブルをモデル化します。各アイテムには、英語、ベトナム語、フランス語のタイトルと説明文が必要です。

アーキテクチャの選択

万能な設計図はありません。「最善」のアプローチは、サポートする言語が2つなのか20なのかによって異なります。

1. 静的カラムアプローチ

これは「手っ取り早く簡単」な方法です。単にメインテーブルに言語ごとの新しいカラムを直接追加します。

CREATE TABLE products_simple (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE,
    name_en TEXT,
    name_vi TEXT,
    name_fr TEXT
);

効果的なケース: 要件が確定しており、2〜3言語しかサポートしない場合、これは非常に高速です。読み取りにJOINが必要なく、データに直接アクセスできます。

リスク: 新しい言語を追加するにはALTER TABLEが必要です。1000万行のテーブルでは、データベースが数分間ロックされる可能性があります。また、バックエンド開発者は適切なカラム名を選択するためだけに、複雑な条件分岐ロジックを書かざるを得なくなります。

2. 翻訳テーブル（業界標準）

これは、この問題を解決するための古典的なリレーショナル手法です。価格やSKUなどの静的データと、翻訳されたコンテンツを分離します。

-- 商品の基本データ
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 翻訳データの保存
CREATE TABLE product_translations (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id) ON DELETE CASCADE,
    lang_code VARCHAR(5), -- 'en'（英語）、'vi'（ベトナム語）、'ja'（日本語）など
    name TEXT,
    description TEXT,
    UNIQUE(product_id, lang_code)
);

効果的なケース: これはMySQLユーザーにとって最も柔軟なオプションです。スキーマを変更することなく、明日からでも50言語を追加できます。メインテーブルを軽量で整理された状態に保てます。

トレードオフ: 「JOINのコスト」が発生します。100件の商品リストを取得するには、100行の翻訳行を結合する必要があります。(product_id, lang_code)に複合インデックスがないと、テーブルの肥大化に伴いクエリのパフォーマンスが急激に低下します。

3. JSONBの活用（PostgreSQL限定）

PostgreSQLを使用している場合、これが優れた選択肢になることが多いです。すべての翻訳を単一のバイナリJSONカラムに保存します。

CREATE TABLE products_json (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50),
    translations JSONB
);

データの挿入はクリーンで読みやすくなります。

INSERT INTO products_json (sku, translations) VALUES 
('MACBOOK-PRO', '{"en": "MacBook Pro", "vi": "Máy tính MacBook Pro"}');

効果的なケース: 単一テーブルの速度と、別テーブルの柔軟性の両方が欲しい場合に適しています。迅速なプロトタイピングや、読み取り頻度の高い環境に最適です。

パフォーマンスチューニングと監査

戦略は、その実行精度が重要です。負荷がかかってもクエリの高速性を維持する方法を見ていきましょう。

翻訳テーブルの最適化

ベトナム語の商品情報を取得するには、標準的なJOINを使用します。これを10ミリ秒未満に抑えるには、外部キーにインデックスを貼る必要があります。

SELECT p.id, t.name, p.price
FROM products p
JOIN product_translations t ON p.id = t.product_id
WHERE t.lang_code = 'vi';

このクエリでEXPLAIN ANALYZEを実行してください。大きなテーブルで「Sequential Scan」が表示される場合は、インデックスが機能していません。複合インデックスを追加して修正しましょう。

JSONBのインデックスによる高速化

JSON形式だからといって侮ってはいけません。インデックスを貼ることが可能です。PostgreSQLでは、GINインデックスを使用することで、ネイティブカラムとほぼ同等の速さで検索が可能になります。

CREATE INDEX idx_products_json_translations ON products_json USING GIN (translations);

これにより、特定のキーを効率的にクエリできます。

SELECT id, translations->>'en' as title 
FROM products_json 
WHERE translations ? 'en';

コンテンツの欠落の特定

国際化における最大の悩みの種の一つは、翻訳の欠落です。LEFT JOINを使用すれば、フランス語の翻訳が欠けている箇所を簡単に監査できます。

SELECT p.id, p.sku
FROM products p
LEFT JOIN product_translations t ON p.id = t.product_id AND t.lang_code = 'fr'
WHERE t.name IS NULL;

翻訳テーブルアプローチでは、こうした監査が容易になります。JSONBは開発スピードは速いですが、キーの存在を保証するためにアプリケーション側でより多くのバリデーションロジックが必要になります。チームの規模とデータベースの強みに合った道を選んでください。

Share:
Tags: