部分インデックス（Partial Index）について
目次
部分インデックスとは
部分インデックスは、テーブルの一部の行のみにインデックスを作成する機能です。条件に合致する行のみがインデックスに含まれるため、ストレージ使用量を削減し、クエリパフォーマンスを向上させることができます。
基本的な構文
CREATE INDEX index_name ON table_name (column_name)
WHERE condition;
基本的な構文と実用例
1. アクティブユーザーのみ
-- アクティブユーザーのメールアドレスにユニークインデックス
CREATE UNIQUE INDEX index_users_email_active
ON users (email)
WHERE deleted_at IS NULL;
2. 特定のステータスのみ
-- 公開済み記事のタイトル検索用インデックス
CREATE INDEX index_posts_title_published
ON posts (title)
WHERE status = 'published';
3. 条件付きユニーク制約
-- 同じカテゴリ内での順序の一意性
CREATE UNIQUE INDEX index_items_order_unique
ON items (category_id, display_order)
WHERE deleted_at IS NULL;
Railsでの実装方法
1. マイグレーション
class AddPartialIndexToUsers < ActiveRecord::Migration[7.0]
def change
add_index :users, :email,
unique: true,
where: "deleted_at IS NULL",
name: "index_users_email_active"
end
end
2. 複数カラムの部分インデックス
add_index :orders, [:user_id, :created_at],
where: "status = 'pending'",
name: "index_orders_pending_by_user"
3. 実際の例（冒頭のコード）
add_index :users, :email,
unique: true,
where: "soft_destroyed_at IS NULL",
name: "index_users_on_email_where_not_soft_destroyed",
algorithm: :concurrently
このインデックスの意味：
-
usersテーブルのemailカラムにユニークインデックス - ソフトデリートされていないユーザーのみ対象
- 非ブロッキング作成でダウンタイム回避
部分インデックスの利点
1. ストレージ効率
-- 全レコードのインデックス（100万件）
CREATE INDEX idx_all_users_email ON users (email);
-- 部分インデックス（アクティブユーザー10万件のみ）
CREATE INDEX idx_active_users_email ON users (email) WHERE deleted_at IS NULL;
2. パフォーマンス向上
- インデックスサイズが小さくなる
- インデックススキャンが高速化
- メモリ使用量の削減
3. メンテナンス効率
- インデックス更新の負荷軽減
- バックアップサイズの削減
実用的なパターン
1. ソフトデリート対応
# 削除されていないレコードのみ
add_index :products, :sku,
unique: true,
where: "deleted_at IS NULL"
2. ステータス別インデックス
# 公開済み記事の検索用
add_index :articles, [:category_id, :published_at],
where: "status = 'published'"
3. 条件付きユニーク制約
# 同じプロジェクト内でのタスク順序
add_index :tasks, [:project_id, :position],
unique: true,
where: "archived_at IS NULL"
存在しない場合のリスク
1. データ整合性の破綻
-- インデックスなしの場合、以下のような状況が発生可能
INSERT INTO products (sku, deleted_at) VALUES ('ABC123', NULL); -- 成功
INSERT INTO products (sku, deleted_at) VALUES ('ABC123', '2024-01-01'); -- 成功（削除済み）
INSERT INTO products (sku, deleted_at) VALUES ('ABC123', NULL); -- 失敗（ユニーク制約違反）
問題: 同じSKUで削除済みとアクティブなレコードが混在
2. ビジネスロジックの破綻
# アプリケーションコードでの問題
class Product < ApplicationRecord
# このバリデーションが期待通り動作しない
validates :sku, uniqueness: true, unless: :deleted?
def deleted?
deleted_at.present?
end
end
# 実際の動作
Product.create(sku: 'ABC123') # 成功
Product.create(sku: 'ABC123', deleted_at: Time.current) # 成功
Product.create(sku: 'ABC123') # 失敗（ユニーク制約違反）
3. クエリパフォーマンスの劣化
-- インデックスなしの場合
SELECT * FROM products WHERE sku = 'ABC123' AND deleted_at IS NULL;
-- 実行計画: 全テーブルスキャン（遅い）
-- インデックスありの場合
SELECT * FROM products WHERE sku = 'ABC123' AND deleted_at IS NULL;
-- 実行計画: インデックススキャン（高速）
4. 具体的な問題シナリオ
商品再登録時の問題
# 問題のあるシナリオ
product1 = Product.create(sku: 'ABC123', name: '商品A')
product1.update(deleted_at: Time.current) # 削除
# 同じSKUで新しい商品を登録しようとする
product2 = Product.create(sku: 'ABC123', name: '商品B') # 失敗！
データ移行時の問題
# 大量データ移行時の問題
products_data.each do |data|
Product.create!(
sku: data[:sku],
name: data[:name],
deleted_at: data[:deleted] ? Time.current : nil
)
# 同じSKUで削除済みとアクティブが混在すると失敗
end
まとめ
部分インデックスは、特に大量のデータを扱うシステムで、効率的なインデックス戦略を実現する強力なツールです。適切に使用することで、ストレージ使用量とパフォーマンスの両方を最適化できます。
しかし、部分インデックスが存在しないと、データ整合性、パフォーマンス、ビジネスロジックの全てに深刻な問題が発生する可能性があります。
重要なポイント
- PostgreSQLでのみ完全サポート
- インデックス条件とクエリ条件の一致が重要
- データ整合性の確保が最優先
- 定期的な監視とチェックが必須
部分インデックスを適切に活用して、効率的で安全なデータベース設計を実現したいですね！