Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Ryu-990(Ryu Hazako)

DBのJsonカラムへ体系的にアクセスできるようにしよう

Posted at

PostgreSQL JSON/JSONBカラムとマテリアライズドビュー 完全ガイド

目次

  1. 概要
  2. JSONとJSONBの違い
  3. マテリアライズドビューの作成
  4. インデックス戦略
  5. ビューのメンテナンス
  6. Prismaとの統合
  7. リレーションの設定
  8. ベストプラクティス

概要

PostgreSQLのJSON/JSONB型カラムを効率的に扱うために、マテリアライズドビューを活用する方法を体系的に解説します。

マテリアライズドビューの利点

  • パフォーマンス向上: JSONデータの繰り返し解析を回避
  • インデックス活用: 抽出したカラムに対して効率的なインデックスを作成可能
  • クエリの簡素化: 複雑なJSON構造をフラットなテーブル構造に変換
  • データアクセスの高速化: 事前に計算された結果を保存

JSONとJSONBの違い

比較表

特徴 JSON JSONB
保存形式 テキスト形式（そのまま保存） バイナリ形式（分解して保存）
インデックス 限定的 GINインデックスに完全対応
処理速度 解析が必要で遅い 高速（事前に分解済み）
重複キー 保持される 最後の値のみ保持
ディスク容量 やや少ない やや多い（インデックス構造含む）
書き込み速度 速い やや遅い（分解処理が必要）
読み込み速度 遅い 速い

推奨事項

JSONB型を使用すべきケース:

  • データを頻繁に検索・クエリする場合
  • インデックスを活用したい場合
  • パフォーマンスが重要な場合

JSON型を使用すべきケース:

  • ログデータなど、保存するだけで検索しないデータ
  • 元のJSON構造を完全に保持したい場合

マテリアライズドビューの作成

1. 基本的なフィールド抽出

CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS name,
    (data->>'age')::int AS age
FROM
    users;

重要な演算子:

  • ->: JSON オブジェクトを返す
  • ->>: テキストとして値を返す
  • ::type: 型キャスト

2. 複数フィールドの抽出

CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS name,
    (data->>'age')::int AS age,
    data->>'email' AS email,
    data->>'phone' AS phone,
    (data->>'active')::boolean AS active
FROM
    users;

3. ネストされたJSONの抽出

CREATE MATERIALIZED VIEW mv_user_profile AS
SELECT
    id,
    data->>'name' AS name,
    data->'address'->>'city' AS city,
    data->'address'->>'country' AS country,
    data->'address'->>'postal_code' AS postal_code,
    (data->'preferences'->>'newsletter')::boolean AS newsletter_subscribed
FROM
    users;

4. 配列要素の抽出

CREATE MATERIALIZED VIEW mv_user_tags AS
SELECT
    id,
    data->>'name' AS name,
    jsonb_array_elements_text(data->'tags') AS tag
FROM
    users
WHERE
    jsonb_typeof(data->'tags') = 'array';

インデックス戦略

マテリアライズドビューのインデックス

基本的なインデックス

-- 単一カラムのインデックス
CREATE INDEX idx_mv_user_name ON mv_user_data(name);
CREATE INDEX idx_mv_user_age ON mv_user_data(age);
CREATE INDEX idx_mv_user_email ON mv_user_data(email);

複合インデックス

-- 複数カラムを組み合わせたインデックス
CREATE INDEX idx_mv_user_name_age ON mv_user_data(name, age);

部分インデックス

-- 条件付きインデックス（アクティブユーザーのみ）
CREATE INDEX idx_mv_active_users ON mv_user_data(name)
WHERE active = true;

元テーブルのJSONBインデックス

GINインデックス（汎用）

-- 標準的なGINインデックス（すべてのキーと値をインデックス化）
CREATE INDEX idx_data_gin ON users USING gin (data);

-- jsonb_path_ops（より高速だが@>演算子のみサポート）
CREATE INDEX idx_data_path ON users USING gin (data jsonb_path_ops);

使い分け:

  • gin (data): 柔軟な検索が必要な場合
  • gin (data jsonb_path_ops): @>演算子を使った包含検索のみの場合（より高速）

特定フィールドのインデックス

-- 単一フィールドのインデックス
CREATE INDEX idx_jsonb_name ON users((data->>'name'));
CREATE INDEX idx_jsonb_age ON users(((data->>'age')::int));

-- 部分インデックス
CREATE INDEX idx_active_users ON users((data->>'name'))
WHERE (data->>'active')::boolean = true;

ビューのメンテナンス

ビューのリフレッシュ

マテリアライズドビューは元データの変更を自動的に反映しません。手動でリフレッシュが必要です。

-- 通常のリフレッシュ（ロックあり）
REFRESH MATERIALIZED VIEW mv_user_data;

-- 並行リフレッシュ（ロックなし、ユニークインデックスが必要）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_data;

並行リフレッシュの要件:

-- ユニークインデックスを作成
CREATE UNIQUE INDEX idx_mv_user_id ON mv_user_data(id);

-- これで並行リフレッシュが可能に
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_data;

リフレッシュの自動化

cron拡張を使用

-- pg_cron拡張のインストール
CREATE EXTENSION pg_cron;

-- 毎日午前2時にリフレッシュ
SELECT cron.schedule('refresh-user-view', '0 2 * * *', 
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_data');

ビューの再定義（カラムの追加・削除・変更）

PostgreSQLではビューの構造を直接変更できません。再作成が必要です。

カラムを追加する場合

-- 1. 既存のビューを削除
DROP MATERIALIZED VIEW IF EXISTS mv_user_data;

-- 2. 新しいカラムを含むビューを作成
CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS name,
    (data->>'age')::int AS age,
    data->>'email' AS email  -- 新しいカラム
FROM
    users;

-- 3. インデックスを再作成
CREATE INDEX idx_mv_user_name ON mv_user_data(name);
CREATE INDEX idx_mv_user_age ON mv_user_data(age);
CREATE INDEX idx_mv_user_email ON mv_user_data(email);

カラムを削除する場合

-- 1. 既存のビューを削除
DROP MATERIALIZED VIEW IF EXISTS mv_user_data;

-- 2. 不要なカラムを除外したビューを作成
CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS name
    -- ageカラムを削除
FROM
    users;

カラム名を変更する場合

-- 1. 既存のビューを削除
DROP MATERIALIZED VIEW IF EXISTS mv_user_data;

-- 2. 新しいカラム名でビューを作成
CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS full_name,  -- 'name' から 'full_name' に変更
    (data->>'age')::int AS user_age  -- 'age' から 'user_age' に変更
FROM
    users;

Prismaとの統合

重要な制約

Prismaのmigrateやdb pushコマンドでは、ビューを直接作成・管理できません。
手動でSQLを実行する必要があります。

統合手順

1. ビューを手動で作成

CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS name,
    (data->>'age')::int AS age,
    data->>'email' AS email
FROM
    users;

2. Prismaスキーマにビューを定義

// schema.prisma

model ViewUserData {
  id    Int    @id
  name  String
  age   Int
  email String

  @@map("mv_user_data")  // 実際のビュー名にマッピング
}

重要な注意点:

  • ビューは読み取り専用
  • @default(autoincrement())は不要
  • 書き込み操作（INSERT、UPDATE、DELETE）はできない

3. Prismaクエリでビューからデータを取得

// ビューからデータを取得
const users = await prisma.viewUserData.findMany();

// フィルタリング
const adults = await prisma.viewUserData.findMany({
  where: {
    age: {
      gte: 18
    }
  }
});

// ソート
const sortedUsers = await prisma.viewUserData.findMany({
  orderBy: {
    name: 'asc'
  }
});

マイグレーションへの組み込み

Prismaの標準マイグレーション機能では対応していませんが、カスタムSQLを組み込むことは可能です。

カスタムマイグレーションファイルの作成

# マイグレーションを作成
npx prisma migrate dev --name add_user_view --create-only

生成されたマイグレーションファイルを編集:

-- prisma/migrations/XXXXXX_add_user_view/migration.sql

-- マテリアライズドビューの作成
CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS name,
    (data->>'age')::int AS age,
    data->>'email' AS email
FROM
    users;

-- インデックスの作成
CREATE INDEX idx_mv_user_name ON mv_user_data(name);
CREATE INDEX idx_mv_user_age ON mv_user_data(age);
CREATE UNIQUE INDEX idx_mv_user_id ON mv_user_data(id);

マイグレーションを適用:

npx prisma migrate dev

リレーションの設定

重要な制約

  • ビューには外部キー制約を設定できない
  • ビューは読み取り専用
  • しかし、Prismaのリレーション機能は使用可能

リレーションの例

スキーマ定義

// ビューのモデル定義
model ViewUserData {
  id    Int    @id
  name  String
  age   Int
  email String
  
  posts Post[]  // リレーション定義
  
  @@map("mv_user_data")
}

// 関連テーブル
model Post {
  id      Int    @id @default(autoincrement())
  title   String
  content String
  userId  Int
  
  user    ViewUserData @relation(fields: [userId], references: [id])
  
  @@map("posts")
}

SQL側の準備

-- ビューの作成
CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS name,
    (data->>'age')::int AS age,
    data->>'email' AS email
FROM
    users;

-- postsテーブル（userId は users.id を参照）
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

リレーションを使ったクエリ

// ユーザーと投稿を一緒に取得
const usersWithPosts = await prisma.viewUserData.findMany({
  include: {
    posts: true
  }
});

// 特定のユーザーの投稿を取得
const userPosts = await prisma.viewUserData.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      orderBy: {
        id: 'desc'
      },
      take: 10
    }
  }
});

// 投稿からユーザー情報を取得
const postsWithUsers = await prisma.post.findMany({
  include: {
    user: true
  }
});

注意事項

  1. 外部キーの参照先: postsテーブルのuser_idは、ビューではなく元のusersテーブルのidを参照する必要があります

  2. ビューのリフレッシュ: マテリアライズドビューのデータは自動更新されないため、定期的にリフレッシュが必要

  3. 書き込み操作: ビュー経由では書き込みできないため、ユーザーの作成・更新は元のusersテーブルに対して行う

ベストプラクティス

1. JSONB型の使用

-- 推奨: JSONB型を使用
ALTER TABLE users ALTER COLUMN data TYPE jsonb USING data::jsonb;

-- 非推奨: JSON型のまま使用（パフォーマンスが劣る）

2. 必要なフィールドのみ抽出

-- 良い例: 必要なフィールドのみ
CREATE MATERIALIZED VIEW mv_user_summary AS
SELECT
    id,
    data->>'name' AS name,
    (data->>'age')::int AS age
FROM users;

-- 悪い例: JSONデータ全体を含む（意味がない）
CREATE MATERIALIZED VIEW mv_user_all AS
SELECT id, data FROM users;

3. 適切なインデックスの作成

-- ビュー作成後すぐにインデックスを作成
CREATE MATERIALIZED VIEW mv_user_data AS
SELECT id, data->>'name' AS name, (data->>'age')::int AS age
FROM users;

CREATE INDEX idx_mv_user_name ON mv_user_data(name);
CREATE INDEX idx_mv_user_age ON mv_user_data(age);
CREATE UNIQUE INDEX idx_mv_user_id ON mv_user_data(id);  -- 並行リフレッシュ用

4. 定期的なリフレッシュ

-- 並行リフレッシュを使用してロックを最小化
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_data;

5. パフォーマンスモニタリング

-- ビューのサイズを確認
SELECT
    schemaname,
    matviewname,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||matviewname)) AS size
FROM pg_matviews
WHERE matviewname = 'mv_user_data';

-- インデックスの使用状況を確認
SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname LIKE 'idx_mv_%';

6. 型変換の明示

-- 良い例: 明示的な型変換
CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS name,
    (data->>'age')::int AS age,
    (data->>'active')::boolean AS active,
    (data->>'created_at')::timestamp AS created_at
FROM users;

-- 悪い例: 型変換なし（すべてTEXT型になる）
CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    data->>'name' AS name,
    data->>'age' AS age  -- これはTEXT型
FROM users;

7. NULL値の処理

-- COALESCE を使ってデフォルト値を設定
CREATE MATERIALIZED VIEW mv_user_data AS
SELECT
    id,
    COALESCE(data->>'name', 'Unknown') AS name,
    COALESCE((data->>'age')::int, 0) AS age,
    COALESCE((data->>'active')::boolean, false) AS active
FROM users;

8. ビュー命名規則

mv_     : マテリアライズドビュー
v_      : 通常のビュー
idx_    : インデックス

まとめ

マテリアライズドビューを使うべき場合

✅ JSONデータを頻繁にクエリする
✅ 複雑なJSON構造をフラット化したい
✅ パフォーマンスが重要
✅ 読み取り専用のデータ

通常のビューを使うべき場合

✅ リアルタイムデータが必要
✅ ストレージを節約したい
✅ 更新頻度が高い

元テーブルを直接クエリすべき場合

✅ データ量が少ない
✅ クエリ頻度が低い
✅ リアルタイム性が最優先

Prisma統合のポイント

  1. ビューは手動SQLで作成
  2. Prismaスキーマにマッピング
  3. 読み取り専用として扱う
  4. リレーションは設定可能
  5. マイグレーションにカスタムSQLを組み込む

参考リソース

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?