「インデックスが増えすぎて、どれが必要か分からない…」 「削除したいけど、本番環境で問題が起きたらどうしよう」 「インデックスのメンテナンスでディスク容量が圧迫されている」
PostgreSQLを長期運用していると、こんな悩みが必ず出てきますよね。
実は、不要なインデックスは百害あって一利なしです。ディスク容量を無駄に消費し、INSERT/UPDATE/DELETEの性能を低下させ、バックアップ時間も長くなります。適切にインデックスを削除することで、データベースの性能を20-30%向上させることも可能です。
この記事では、インデックスを安全に削除する方法から、削除前の影響分析、本番環境での実践手順まで、実例とトラブル回避策を交えながら徹底的に解説していきます。
🔍 削除すべきインデックスの特定
未使用インデックスの発見
一度も使われていないインデックス
完全未使用のインデックスを特定：
-- 作成後一度も使われていないインデックス
SELECT
schemaname,
tablename,
indexrelname as index_name,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
pg_stat_get_create_time(indexrelid) as created_time,
idx_scan as total_scans,
'DROP INDEX ' || schemaname || '.' || indexrelname || ';' as drop_command
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey' -- プライマリキーは除外
ORDER BY pg_relation_size(indexrelid) DESC;
-- 実行結果例
schemaname | tablename | index_name | index_size | total_scans | drop_command
-----------|-----------|--------------------|-----------:|------------:|------------------
public | logs | idx_logs_old | 458 MB | 0 | DROP INDEX public.idx_logs_old;
public | archive | idx_archive_temp | 234 MB | 0 | DROP INDEX public.idx_archive_temp;
public | users | idx_users_test | 12 MB | 0 | DROP INDEX public.idx_users_test;
使用頻度が極端に低いインデックス
削除候補の優先順位付け：
-- コスト対効果の悪いインデックスを発見
WITH index_stats AS (
SELECT
schemaname,
tablename,
indexrelname,
idx_scan,
pg_relation_size(indexrelid) as size_bytes,
pg_stat_get_last_scan_time(indexrelid) as last_used,
EXTRACT(EPOCH FROM (NOW() - pg_stat_get_last_scan_time(indexrelid)))/86400 as days_since_last_use
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
)
SELECT
schemaname,
tablename,
indexrelname,
idx_scan as total_scans,
pg_size_pretty(size_bytes) as index_size,
ROUND(days_since_last_use::numeric, 1) as days_unused,
CASE
WHEN idx_scan = 0 THEN 'NEVER_USED'
WHEN days_since_last_use > 90 THEN 'OBSOLETE'
WHEN days_since_last_use > 30 THEN 'RARELY_USED'
WHEN idx_scan < 10 AND size_bytes > 10485760 THEN 'INEFFICIENT'
ELSE 'ACTIVE'
END as recommendation,
CASE
WHEN idx_scan = 0 OR days_since_last_use > 90 THEN 1
WHEN days_since_last_use > 30 THEN 2
WHEN idx_scan < 10 AND size_bytes > 10485760 THEN 3
ELSE 4
END as priority
FROM index_stats
WHERE idx_scan < 100 -- 100回未満の使用
OR days_since_last_use > 30 -- 30日以上未使用
ORDER BY priority, size_bytes DESC;
重複インデックスの検出
完全に重複しているインデックス
同じカラムに複数のインデックス：
-- 重複インデックスを検出して削除候補を提案
WITH index_info AS (
SELECT
n.nspname as schema_name,
t.relname as table_name,
i.relname as index_name,
array_agg(a.attname ORDER BY array_position(ix.indkey, a.attnum)) as columns,
ix.indisunique as is_unique,
ix.indisprimary as is_primary,
pg_relation_size(i.oid) as size_bytes,
idx_scan as scan_count
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
LEFT JOIN pg_stat_user_indexes si ON si.indexrelid = i.oid
WHERE n.nspname = 'public'
GROUP BY n.nspname, t.relname, i.relname, ix.indisunique, ix.indisprimary, i.oid, idx_scan
)
SELECT
ii1.table_name,
ii1.index_name as keep_index,
ii2.index_name as drop_index,
ii1.columns as index_columns,
pg_size_pretty(ii2.size_bytes) as size_to_free,
ii2.scan_count as drop_index_scans,
'DROP INDEX ' || ii2.index_name || '; -- Duplicate of ' || ii1.index_name as drop_command
FROM index_info ii1
JOIN index_info ii2 ON
ii1.schema_name = ii2.schema_name
AND ii1.table_name = ii2.table_name
AND ii1.columns = ii2.columns
AND ii1.index_name < ii2.index_name
WHERE NOT ii2.is_primary -- プライマリキーは削除対象外
ORDER BY ii2.size_bytes DESC;
非効率なインデックスの特定
選択性が低いインデックス
カーディナリティが低いカラムのインデックス：
-- 選択性の低いインデックスを発見
WITH column_stats AS (
SELECT
schemaname,
tablename,
attname,
n_distinct,
null_frac,
avg_width,
correlation
FROM pg_stats
WHERE schemaname = 'public'
),
index_columns AS (
SELECT
n.nspname as schema_name,
t.relname as table_name,
i.relname as index_name,
a.attname as column_name,
pg_relation_size(i.oid) as index_size,
t.reltuples as table_rows
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
WHERE n.nspname = 'public'
AND array_length(ix.indkey, 1) = 1 -- 単一カラムインデックスのみ
)
SELECT
ic.table_name,
ic.index_name,
ic.column_name,
cs.n_distinct,
CASE
WHEN cs.n_distinct > 0 THEN cs.n_distinct
WHEN cs.n_distinct = -1 THEN ic.table_rows
ELSE ic.table_rows * (-cs.n_distinct)
END::BIGINT as estimated_distinct_values,
pg_size_pretty(ic.index_size) as index_size,
ROUND((
CASE
WHEN cs.n_distinct > 0 THEN cs.n_distinct
WHEN cs.n_distinct = -1 THEN ic.table_rows
ELSE ic.table_rows * (-cs.n_distinct)
END / GREATEST(ic.table_rows, 1)
)::numeric * 100, 2) as selectivity_percent,
CASE
WHEN cs.n_distinct BETWEEN 1 AND 10 THEN 'DROP - Very Low Selectivity'
WHEN (cs.n_distinct > 0 AND cs.n_distinct < 100) THEN 'Consider Dropping'
ELSE 'Keep'
END as recommendation
FROM index_columns ic
LEFT JOIN column_stats cs ON
ic.schema_name = cs.schemaname
AND ic.table_name = cs.tablename
AND ic.column_name = cs.attname
WHERE cs.n_distinct IS NOT NULL
ORDER BY selectivity_percent ASC;
⚠️ 削除前の影響分析
依存関係の確認
制約との関連を確認
削除できないインデックスを特定：
-- 制約に使用されているインデックスの確認
SELECT
n.nspname as schema_name,
t.relname as table_name,
i.relname as index_name,
con.conname as constraint_name,
con.contype as constraint_type,
CASE con.contype
WHEN 'p' THEN 'PRIMARY KEY'
WHEN 'u' THEN 'UNIQUE'
WHEN 'f' THEN 'FOREIGN KEY'
WHEN 'c' THEN 'CHECK'
WHEN 'x' THEN 'EXCLUSION'
END as constraint_type_desc,
'Cannot drop - Used by constraint: ' || con.conname as warning
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_constraint con ON con.conindid = i.oid
WHERE n.nspname = 'public'
ORDER BY n.nspname, t.relname, i.relname;
クエリへの影響予測
実行計画の変化を確認
インデックス削除の影響をシミュレート：
-- インデックスを無効化してテスト
BEGIN;
-- インデックスを一時的に無効化
UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = 'idx_users_email'::regclass;
-- 影響を受けるクエリの実行計画を確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'test@example.com';
-- ロールバック（インデックスは元に戻る）
ROLLBACK;
重要なクエリへの影響を事前確認：
-- 特定のインデックスを使用しているクエリを記録
CREATE TABLE index_usage_log (
index_name TEXT,
query_text TEXT,
calls BIGINT,
mean_time DOUBLE PRECISION,
total_time DOUBLE PRECISION,
recorded_at TIMESTAMP DEFAULT NOW()
);
-- pg_stat_statementsから使用状況を記録
INSERT INTO index_usage_log (index_name, query_text, calls, mean_time, total_time)
SELECT
'idx_users_email',
query,
calls,
mean_exec_time,
total_exec_time
FROM pg_stat_statements
WHERE query LIKE '%users%email%'
AND calls > 0;
🔧 安全な削除手順
基本的な削除コマンド
DROP INDEXの基本構文
シンプルな削除：
-- 基本的な削除
DROP INDEX index_name;
-- スキーマを指定して削除
DROP INDEX schema_name.index_name;
-- 存在確認付き削除（エラーを防ぐ）
DROP INDEX IF EXISTS index_name;
-- カスケード削除（依存オブジェクトも削除）
DROP INDEX index_name CASCADE;
-- 削除を制限（依存オブジェクトがある場合はエラー）
DROP INDEX index_name RESTRICT;
CONCURRENTLYオプションでの削除
ロックを最小化した削除
本番環境での推奨方法：
-- 通常の削除（テーブルをロック）
DROP INDEX idx_large_table; -- 危険：長時間のロック
-- CONCURRENTLYで削除（ロックを最小化）
DROP INDEX CONCURRENTLY idx_large_table; -- 推奨：ロック最小化
-- 注意点の確認
SELECT
pid,
usename,
application_name,
state,
query,
wait_event_type,
wait_event
FROM pg_stat_activity
WHERE query LIKE '%DROP INDEX CONCURRENTLY%';
CONCURRENTLYの制限事項：
-- トランザクション内では使用不可
BEGIN;
DROP INDEX CONCURRENTLY idx_test; -- ERROR!
COMMIT;
-- 正しい使い方（トランザクション外で実行）
DROP INDEX CONCURRENTLY idx_test;
段階的な削除戦略
本番環境での慎重な削除手順
3段階削除プロセス：
-- ステップ1: インデックスを無効化（即座に元に戻せる）
UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = 'idx_to_remove'::regclass;
-- 性能を1週間モニタリング
SELECT
schemaname,
tablename,
attname,
avg_width,
n_distinct
FROM pg_stats
WHERE tablename = 'target_table';
-- ステップ2: 問題なければインデックスの更新を停止
UPDATE pg_index
SET indisready = false
WHERE indexrelid = 'idx_to_remove'::regclass;
-- さらに1週間モニタリング
-- ステップ3: 最終的に削除
DROP INDEX IF EXISTS idx_to_remove;
🚀 大量インデックスの一括削除
バッチ削除スクリプト
条件に基づく一括削除
自動削除スクリプト：
-- 削除対象を確認してから実行する関数
CREATE OR REPLACE FUNCTION batch_drop_unused_indexes(
p_threshold_days INTEGER DEFAULT 90,
p_min_size_mb INTEGER DEFAULT 10,
p_dry_run BOOLEAN DEFAULT TRUE
)
RETURNS TABLE(
action TEXT,
index_name TEXT,
table_name TEXT,
size_freed TEXT,
command TEXT
) AS $$
DECLARE
v_index RECORD;
v_dropped_count INTEGER := 0;
v_total_size BIGINT := 0;
BEGIN
FOR v_index IN
SELECT
si.schemaname,
si.tablename,
si.indexrelname,
pg_relation_size(si.indexrelid) as size_bytes,
si.idx_scan,
EXTRACT(EPOCH FROM (NOW() - pg_stat_get_last_scan_time(si.indexrelid)))/86400 as days_unused
FROM pg_stat_user_indexes si
JOIN pg_index ix ON si.indexrelid = ix.indexrelid
WHERE NOT ix.indisprimary -- プライマリキー除外
AND NOT ix.indisunique -- ユニークインデックス除外
AND si.idx_scan = 0 -- 未使用
AND pg_relation_size(si.indexrelid) > p_min_size_mb * 1024 * 1024
ORDER BY pg_relation_size(si.indexrelid) DESC
LOOP
IF p_dry_run THEN
-- ドライラン：実行せずに結果を返す
RETURN QUERY SELECT
'DRY_RUN'::TEXT,
v_index.indexrelname::TEXT,
v_index.tablename::TEXT,
pg_size_pretty(v_index.size_bytes)::TEXT,
format('DROP INDEX %I.%I;', v_index.schemaname, v_index.indexrelname)::TEXT;
ELSE
-- 実際に削除
BEGIN
EXECUTE format('DROP INDEX %I.%I', v_index.schemaname, v_index.indexrelname);
v_dropped_count := v_dropped_count + 1;
v_total_size := v_total_size + v_index.size_bytes;
RETURN QUERY SELECT
'DROPPED'::TEXT,
v_index.indexrelname::TEXT,
v_index.tablename::TEXT,
pg_size_pretty(v_index.size_bytes)::TEXT,
format('DROP INDEX %I.%I;', v_index.schemaname, v_index.indexrelname)::TEXT;
EXCEPTION WHEN OTHERS THEN
RETURN QUERY SELECT
'ERROR'::TEXT,
v_index.indexrelname::TEXT,
v_index.tablename::TEXT,
pg_size_pretty(v_index.size_bytes)::TEXT,
SQLERRM::TEXT;
END;
END IF;
END LOOP;
IF NOT p_dry_run AND v_dropped_count > 0 THEN
RETURN QUERY SELECT
'SUMMARY'::TEXT,
format('%s indexes dropped', v_dropped_count)::TEXT,
''::TEXT,
pg_size_pretty(v_total_size)::TEXT,
format('Total space freed: %s', pg_size_pretty(v_total_size))::TEXT;
END IF;
END;
$$ LANGUAGE plpgsql;
-- ドライランで確認
SELECT * FROM batch_drop_unused_indexes(90, 10, TRUE);
-- 実際に削除（注意！）
SELECT * FROM batch_drop_unused_indexes(90, 10, FALSE);
スケジュール削除
メンテナンスウィンドウでの計画的削除
時間指定での削除実行：
-- メンテナンス時間に実行する削除ジョブ
CREATE OR REPLACE FUNCTION scheduled_index_cleanup()
RETURNS VOID AS $$
DECLARE
v_start_time TIMESTAMP;
v_end_time TIMESTAMP;
v_index RECORD;
v_dropped_count INTEGER := 0;
BEGIN
v_start_time := NOW();
-- 現在時刻がメンテナンスウィンドウか確認
IF EXTRACT(HOUR FROM NOW()) NOT BETWEEN 2 AND 5 THEN
RAISE NOTICE 'Not in maintenance window (2-5 AM). Exiting.';
RETURN;
END IF;
-- ログテーブルに記録
INSERT INTO maintenance_log (operation, started_at)
VALUES ('Index Cleanup', v_start_time);
-- 削除処理
FOR v_index IN
SELECT indexrelname, schemaname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND pg_relation_size(indexrelid) > 50 * 1024 * 1024 -- 50MB以上
LIMIT 5 -- 一度に5個まで
LOOP
BEGIN
EXECUTE format('DROP INDEX CONCURRENTLY %I.%I',
v_index.schemaname, v_index.indexrelname);
v_dropped_count := v_dropped_count + 1;
-- 各削除後に少し待機
PERFORM pg_sleep(5);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Failed to drop %: %', v_index.indexrelname, SQLERRM;
END;
END LOOP;
v_end_time := NOW();
-- 完了をログに記録
UPDATE maintenance_log
SET
completed_at = v_end_time,
details = format('Dropped %s indexes', v_dropped_count)
WHERE operation = 'Index Cleanup'
AND started_at = v_start_time;
END;
$$ LANGUAGE plpgsql;
📊 削除後の確認と最適化
削除成功の確認
システムカタログで確認
インデックスが削除されたことを確認：
-- 削除確認クエリ
SELECT
CASE
WHEN EXISTS (
SELECT 1 FROM pg_indexes
WHERE indexname = 'idx_deleted_index'
) THEN 'ERROR: Index still exists!'
ELSE 'SUCCESS: Index deleted successfully'
END as deletion_status;
-- 削除後のテーブル状態確認
SELECT
t.tablename,
COUNT(i.indexname) as remaining_indexes,
pg_size_pretty(pg_relation_size(t.tablename::regclass)) as table_size,
pg_size_pretty(pg_total_relation_size(t.tablename::regclass) -
pg_relation_size(t.tablename::regclass)) as total_index_size
FROM pg_tables t
LEFT JOIN pg_indexes i ON t.tablename = i.tablename
WHERE t.schemaname = 'public'
AND t.tablename = 'target_table'
GROUP BY t.tablename;
パフォーマンス改善の測定
削除前後の比較
性能改善の確認：
-- 削除前後のINSERT性能測定
-- 削除前
\timing on
INSERT INTO test_table (column1, column2, column3)
SELECT
generate_series(1, 10000),
md5(random()::text),
NOW();
-- Time: 2543.234 ms
-- インデックス削除後
DROP INDEX idx_test_column2;
-- 削除後
INSERT INTO test_table (column1, column2, column3)
SELECT
generate_series(10001, 20000),
md5(random()::text),
NOW();
-- Time: 1832.456 ms (28%改善！)
空き領域の回収
VACUUMで領域を整理
削除後のメンテナンス：
-- インデックス削除後の領域回収
-- 通常のVACUUM（領域を再利用可能にする）
VACUUM ANALYZE target_table;
-- VACUUM FULL（領域を完全に回収）
VACUUM FULL target_table;
-- 削除による空き領域の確認
SELECT
schemaname,
tablename,
pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
n_live_tup as live_tuples,
n_dead_tup as dead_tuples,
ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) as dead_tuple_ratio
FROM pg_stat_user_tables
WHERE tablename = 'target_table';
⚠️ トラブルシューティング
よくあるエラーと対処法
エラー1：依存関係によるエラー
問題と解決：
-- エラー: cannot drop index because other objects depend on it
DROP INDEX idx_referenced;
-- ERROR: cannot drop index idx_referenced because constraint fk_constraint on table other_table depends on it
-- 解決法1: 依存関係を確認
SELECT
dep.classid::regclass AS dependent_class,
dep.objid::regclass AS dependent_object,
dep.deptype,
pg_describe_object(ref.classid, ref.objid, ref.objsubid) AS referenced_object
FROM pg_depend dep
JOIN pg_depend ref ON dep.objid = ref.objid
WHERE ref.objid = 'idx_referenced'::regclass;
-- 解決法2: CASCADEで強制削除（注意！）
DROP INDEX idx_referenced CASCADE;
エラー2：アクティブな接続によるブロック
ロック解除の方法：
-- ロックされている状況を確認
SELECT
pid,
usename,
application_name,
query,
state,
wait_event_type,
wait_event,
pg_blocking_pids(pid) as blocked_by
FROM pg_stat_activity
WHERE query LIKE '%idx_%'
AND state != 'idle';
-- 強制的にセッションを終了（最終手段）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid IN (
SELECT unnest(pg_blocking_pids(pid))
FROM pg_stat_activity
WHERE query LIKE '%DROP INDEX%'
);
エラー3：CONCURRENTLYの失敗
CONCURRENTLY特有の問題：
-- エラー: DROP INDEX CONCURRENTLY cannot be executed from a function
-- 関数やトランザクション内では使用不可
-- 解決：直接実行する
\set AUTOCOMMIT on
DROP INDEX CONCURRENTLY idx_large;
-- 失敗した場合のゴーストインデックス確認
SELECT
n.nspname,
c.relname,
c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'i'
AND NOT EXISTS (
SELECT 1 FROM pg_index i WHERE i.indexrelid = c.oid
);
💡 ベストプラクティス
削除前のチェックリスト
必須確認項目：
-- 包括的な削除前チェック関数
CREATE OR REPLACE FUNCTION pre_drop_index_check(p_index_name TEXT)
RETURNS TABLE(
check_item TEXT,
status TEXT,
details TEXT
) AS $$
BEGIN
-- 1. インデックスの存在確認
RETURN QUERY
SELECT
'Index Exists'::TEXT,
CASE WHEN EXISTS(SELECT 1 FROM pg_indexes WHERE indexname = p_index_name)
THEN 'OK' ELSE 'ERROR' END,
CASE WHEN EXISTS(SELECT 1 FROM pg_indexes WHERE indexname = p_index_name)
THEN 'Index found' ELSE 'Index not found' END;
-- 2. 使用頻度確認
RETURN QUERY
SELECT
'Usage Statistics'::TEXT,
CASE WHEN idx_scan > 100 THEN 'WARNING' ELSE 'OK' END,
format('Used %s times', idx_scan)::TEXT
FROM pg_stat_user_indexes
WHERE indexrelname = p_index_name;
-- 3. 制約確認
RETURN QUERY
SELECT
'Constraint Dependencies'::TEXT,
CASE WHEN COUNT(*) > 0 THEN 'ERROR' ELSE 'OK' END,
CASE WHEN COUNT(*) > 0
THEN format('%s constraints depend on this index', COUNT(*))
ELSE 'No constraints' END
FROM pg_constraint
WHERE conindid = p_index_name::regclass;
-- 4. サイズ確認
RETURN QUERY
SELECT
'Index Size'::TEXT,
'INFO'::TEXT,
pg_size_pretty(pg_relation_size(p_index_name::regclass))::TEXT;
-- 5. 最終使用日時
RETURN QUERY
SELECT
'Last Used'::TEXT,
CASE
WHEN pg_stat_get_last_scan_time(indexrelid) IS NULL THEN 'WARNING'
WHEN pg_stat_get_last_scan_time(indexrelid) < NOW() - INTERVAL '30 days' THEN 'INFO'
ELSE 'WARNING'
END,
COALESCE(pg_stat_get_last_scan_time(indexrelid)::TEXT, 'Never')::TEXT
FROM pg_stat_user_indexes
WHERE indexrelname = p_index_name;
END;
$$ LANGUAGE plpgsql;
-- 使用例
SELECT * FROM pre_drop_index_check('idx_users_email');
削除ポリシーの策定
組織的なインデックス管理：
-- インデックス管理ポリシーテーブル
CREATE TABLE index_management_policy (
policy_id SERIAL PRIMARY KEY,
policy_name TEXT NOT NULL,
condition_type TEXT CHECK (condition_type IN ('UNUSED', 'LOW_USE', 'DUPLICATE', 'INEFFICIENT')),
threshold_days INTEGER,
threshold_scans INTEGER,
min_size_mb INTEGER,
action TEXT CHECK (action IN ('DROP', 'MONITOR', 'REVIEW')),
auto_execute BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT NOW()
);
-- ポリシー例の登録
INSERT INTO index_management_policy (policy_name, condition_type, threshold_days, threshold_scans, min_size_mb, action, auto_execute)
VALUES
('Drop Never Used Large Indexes', 'UNUSED', 90, 0, 100, 'DROP', FALSE),
('Review Rarely Used Indexes', 'LOW_USE', 30, 10, 50, 'REVIEW', FALSE),
('Drop Duplicate Indexes', 'DUPLICATE', NULL, NULL, NULL, 'DROP', FALSE),
('Monitor Inefficient Indexes', 'INEFFICIENT', NULL, 100, 10, 'MONITOR', FALSE);
ロールバック計画
削除を元に戻す準備：
-- インデックス定義のバックアップ
CREATE TABLE dropped_index_backup (
backup_id SERIAL PRIMARY KEY,
index_name TEXT,
table_name TEXT,
index_definition TEXT,
dropped_at TIMESTAMP DEFAULT NOW(),
dropped_by TEXT DEFAULT CURRENT_USER,
index_size BIGINT,
last_scan_count BIGINT
);
-- 削除前に定義を保存
INSERT INTO dropped_index_backup (index_name, table_name, index_definition, index_size, last_scan_count)
SELECT
i.indexname,
i.tablename,
i.indexdef,
pg_relation_size(indexrelid),
idx_scan
FROM pg_indexes i
LEFT JOIN pg_stat_user_indexes s ON i.indexname = s.indexrelname
WHERE i.indexname = 'idx_to_drop';
-- 必要に応じて復元
DO $$
DECLARE
v_indexdef TEXT;
BEGIN
SELECT index_definition INTO v_indexdef
FROM dropped_index_backup
WHERE index_name = 'idx_to_restore'
ORDER BY dropped_at DESC
LIMIT 1;
IF v_indexdef IS NOT NULL THEN
EXECUTE v_indexdef;
RAISE NOTICE 'Index restored: %', v_indexdef;
END IF;
END $$;
📈 自動化とモニタリング
定期的な削除ジョブ
cronで実行する削除スクリプト：
#!/bin/bash
# index_cleanup.sh
DB_NAME="production"
LOG_FILE="/var/log/postgresql/index_cleanup.log"
echo "Starting index cleanup: $(date)" >> $LOG_FILE
psql -d $DB_NAME -c "
SELECT
indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) as size,
'Dropping' as action
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND pg_relation_size(indexrelid) > 104857600 -- 100MB
AND schemaname = 'public'
" >> $LOG_FILE
psql -d $DB_NAME -c "
SELECT * FROM batch_drop_unused_indexes(90, 100, FALSE);
" >> $LOG_FILE
echo "Cleanup completed: $(date)" >> $LOG_FILE
アラート設定
問題のあるインデックスを通知：
-- 削除候補の監視ビュー
CREATE VIEW index_deletion_candidates AS
SELECT
schemaname,
tablename,
indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
idx_scan as total_scans,
EXTRACT(DAY FROM NOW() - pg_stat_get_last_scan_time(indexrelid)) as days_unused,
CASE
WHEN idx_scan = 0 AND pg_relation_size(indexrelid) > 536870912 THEN 'CRITICAL'
WHEN idx_scan = 0 AND pg_relation_size(indexrelid) > 104857600 THEN 'HIGH'
WHEN idx_scan < 10 AND pg_relation_size(indexrelid) > 52428800 THEN 'MEDIUM'
ELSE 'LOW'
END as priority
FROM pg_stat_user_indexes
WHERE idx_scan < 100
OR EXTRACT(DAY FROM NOW() - pg_stat_get_last_scan_time(indexrelid)) > 30
ORDER BY
CASE
WHEN idx_scan = 0 AND pg_relation_size(indexrelid) > 536870912 THEN 1
WHEN idx_scan = 0 AND pg_relation_size(indexrelid) > 104857600 THEN 2
WHEN idx_scan < 10 AND pg_relation_size(indexrelid) > 52428800 THEN 3
ELSE 4
END,
pg_relation_size(indexrelid) DESC;
📚 まとめ：インデックス削除で性能を向上させる
PostgreSQLインデックス削除の重要ポイント：
✅ 未使用・重複・非効率なインデックスを定期的に特定
✅ 削除前に必ず依存関係と影響を確認
✅ 本番環境ではCONCURRENTLYで安全に削除
✅ 段階的削除で リスクを最小化
✅ 削除後は必ずVACUUMで領域を回収
不要なインデックスの削除は、データベースの健康維持に不可欠です。
今すぐ実践すべき3つのアクション：
- pg_stat_user_indexesで未使用インデックスを確認
- 100MB以上の未使用インデックスから削除を検討
- 削除前に必ずインデックス定義をバックアップ
適切なインデックス管理で、データベースのパフォーマンスを最大化しましょう！
この記事が役立ったら、データベース管理で悩んでいる仲間にもシェアしてください。一緒に最適なPostgreSQL環境を作りましょう！
コメント