8.3 REINDEX와 인덱스 BLOAT

REINDEX INDEX idx_orders_user_id;            -- 단일 인덱스
REINDEX TABLE orders;                         -- 한 테이블의 모든 인덱스
REINDEX SCHEMA public;                        -- 스키마 전체
REINDEX DATABASE app_main;                    -- DB 전체 (위험)
REINDEX SYSTEM postgres;                      -- 시스템 카탈로그

-- 단일 인덱스 (가장 권장)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- 한 테이블의 모든 인덱스 (PG 14+에서 동작)
REINDEX TABLE CONCURRENTLY orders;

SELECT indexrelid::regclass, indisvalid
  FROM pg_index
 WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY orders_user_id_ccnew;   -- _ccnew 접미사
-- 또는
REINDEX INDEX CONCURRENTLY idx_orders_user_id;  -- 다시 시도

SELECT pg_size_pretty(pg_relation_size('idx_orders_user_id')) AS index_size;

# 한 테이블
pg_repack -d app_main -t orders

# 인덱스만
pg_repack -d app_main -t orders --only-indexes

# 스키마 전체
pg_repack -d app_main -s public

# dry-run (실제 실행 안 함)
pg_repack -d app_main -t orders --dry-run

# RHEL/Rocky
sudo dnf install -y pg_repack_17

# Debian/Ubuntu
sudo apt-get install -y postgresql-17-repack

CREATE EXTENSION pg_repack;

-- BLOAT 큰 인덱스
SELECT i.relname AS index,
       pg_size_pretty(pg_relation_size(i.oid)) AS size,
       round(s.avg_leaf_density, 1) AS density
  FROM pg_index x
  JOIN pg_class i ON x.indexrelid = i.oid
  JOIN pgstatindex(i.oid) s ON true
 WHERE i.relkind = 'i' AND pg_relation_size(i.oid) > 100*1024*1024
 ORDER BY s.avg_leaf_density;

#!/bin/bash
# 큰 인덱스 자동 REINDEX
psql -d app_main -t -c "
SELECT format('REINDEX INDEX CONCURRENTLY %I.%I;',
       schemaname, indexname)
  FROM (
    SELECT n.nspname AS schemaname, i.relname AS indexname,
           pgstatindex(i.oid).avg_leaf_density AS density
      FROM pg_class i
      JOIN pg_namespace n ON i.relnamespace = n.oid
     WHERE i.relkind = 'i' AND pg_relation_size(i.oid) > 100*1024*1024
  ) t
 WHERE density < 70;
" | psql -d app_main

pg_repack -d app_main -t orders --no-superuser-check

-- 새 인덱스 빌드
CREATE UNIQUE INDEX CONCURRENTLY users_email_new ON users(email);

-- 기존 인덱스 drop
ALTER TABLE users DROP CONSTRAINT users_email_key;
DROP INDEX users_email_key;

-- 새 인덱스를 unique constraint로 부착
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_new;