PostgreSQL 18 アップグレードとチューニングガイド 2026

-- 現在の認証方式を確認
SELECT usename, passwd IS NOT NULL AS has_password,
       CASE
           WHEN passwd LIKE 'md5%' THEN 'MD5'
           WHEN passwd LIKE 'SCRAM-SHA-256$%' THEN 'SCRAM-SHA-256'
           ELSE 'unknown'
       END AS auth_method
FROM pg_shadow
WHERE passwd IS NOT NULL;

-- SCRAM-SHA-256へ移行
SET password_encryption = 'scram-sha-256';
ALTER USER myapp_user PASSWORD 'new_secure_password';

-- pg_hba.confでmd5 -> scram-sha-256に変更
-- host all all 10.0.0.0/8 scram-sha-256

# 現在インストールされている拡張機能一覧とPG 18互換性確認
psql -U postgres -d mydb -c "
SELECT e.extname, e.extversion,
       a.default_version AS available_version
FROM pg_extension e
LEFT JOIN pg_available_extensions a ON a.name = e.extname
ORDER BY e.extname;
"

# 主要拡張機能の互換性（2026年3月時点）
# pg_stat_statements: 1.11 -> 互換
# PostGIS: 3.5+ -> 互換
# pgvector: 0.8+ -> 互換
# pg_cron: 1.6+ -> 互換
# hypopg: 1.4+ -> 互換
# timescaledb: 2.17+ -> PG 18サポートの確認が必要

# PG 18バイナリのインストール（Ubuntu/Debian）
sudo apt-get install postgresql-18

# ディレクトリ構造の確認
# /usr/lib/postgresql/17/bin/  <- 既存のPG 17
# /usr/lib/postgresql/18/bin/  <- 新規のPG 18
# /var/lib/postgresql/17/main/ <- 既存データ
# /var/lib/postgresql/18/main/ <- 新規データ（pg_upgradeが生成）

# PG 17の正常停止
sudo systemctl stop postgresql@17-main

# 新規PG 18クラスタの初期化
sudo -u postgres /usr/lib/postgresql/18/bin/initdb \
  -D /var/lib/postgresql/18/main \
  --encoding=UTF8 \
  --locale=ko_KR.UTF-8 \
  --data-checksums

# 必ず--checkモードで先に実行
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/17/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --old-bindir=/usr/lib/postgresql/17/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --check

# 正常出力例：
# Performing Consistency Checks
# -----------------------------
# Checking cluster versions                         ok
# Checking database user is the install user         ok
# Checking database connection settings              ok
# Checking for prepared transactions                 ok
# Checking for system-defined composite types        ok
# Checking for reg* data types in user tables        ok
# Checking for contrib/isn with bigint-passing mismatch ok
# Checking for user-defined encoding conversions     ok
# Checking for user-defined postfix operators         ok
# Checking for incompatible polymorphic functions     ok
# *Clusters are compatible*

# --linkオプションでハードリンクを使用（データコピーなしで数秒で完了）
# 注意：--link使用時は以前のクラスタへのロールバック不可
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/17/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --old-bindir=/usr/lib/postgresql/17/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --link \
  --jobs=4

# 出力例：
# Performing Upgrade
# ------------------
# ...
# Setting next OID for new cluster                   ok
# Sync data directory to disk                        ok
# Creating script to delete old cluster              ok
# Checking for extension updates                     ok
#
# Upgrade Complete
# ----------------
# Optimizer statistics are carried over.              <- PG 18の新機能！

# PG 18の起動
sudo systemctl start postgresql@18-main

# バージョン確認
psql -U postgres -c "SELECT version();"
# PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc ...

# 拡張機能の更新
psql -U postgres -d mydb -c "ALTER EXTENSION pg_stat_statements UPDATE;"
psql -U postgres -d mydb -c "ALTER EXTENSION postgis UPDATE;"

# 統計情報の状態確認（PG 18：既に存在しているはず）
psql -U postgres -d mydb -c "
SELECT schemaname, relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;
"

-- PG 18：VIRTUALがデフォルト（STOREDは明示的に指定が必要）
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    base_price numeric(10,2) NOT NULL,
    tax_rate numeric(4,3) NOT NULL DEFAULT 0.1,
    -- VIRTUAL generated column：ディスク容量0、クエリ時に計算
    total_price numeric(10,2) GENERATED ALWAYS AS (
        base_price * (1 + tax_rate)
    ) VIRTUAL,
    -- STORED generated column：ディスクに保存、INSERT/UPDATE時に計算
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('korean', name)
    ) STORED
);

-- VIRTUAL vs STORED 比較
-- VIRTUAL：ディスク0、読み取り時にCPU使用、インデックス作成不可
-- STORED：ディスク使用、読み取り時に即座に返却、インデックス作成可能

-- VIRTUALカラムには直接インデックスを作成できないため、
-- インデックスが必要な場合はexpression indexを使用
CREATE INDEX idx_products_total ON products ((base_price * (1 + tax_rate)));

-- PG 18：ネイティブUUIDv7生成
SELECT uuidv7();
-- 結果：019576a0-6c00-7def-8000-1a2b3c4d5e6f
-- 先頭48ビットがUnixタイムスタンプ（ミリ秒）

-- UUIDv7をPrimary Keyとして使用
CREATE TABLE distributed_events (
    id uuid DEFAULT uuidv7() PRIMARY KEY,
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- UUIDv7 vs UUIDv4 インデックスパフォーマンス比較実験
-- 100万件INSERT後のインデックスサイズとページ分割を比較
CREATE TABLE bench_uuid4 (id uuid DEFAULT gen_random_uuid() PRIMARY KEY, val int);
CREATE TABLE bench_uuid7 (id uuid DEFAULT uuidv7() PRIMARY KEY, val int);

INSERT INTO bench_uuid4 (val) SELECT i FROM generate_series(1, 1000000) i;
INSERT INTO bench_uuid7 (val) SELECT i FROM generate_series(1, 1000000) i;

SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid)) AS table_size,
    pg_size_pretty(pg_indexes_size(oid)) AS index_size
FROM pg_class
WHERE relname IN ('bench_uuid4', 'bench_uuid7');

-- 予想結果：
-- bench_uuid4 | table: 42MB | index: 34MB（ランダム分散によるページ分割が多い）
-- bench_uuid7 | table: 42MB | index: 21MB（順次挿入による効率的なパッキング）

-- UPDATEで変更前/後の値を同時に取得
UPDATE products
SET base_price = base_price * 1.05  -- 5%値上げ
WHERE id BETWEEN 1 AND 100
RETURNING
    old.id,
    old.base_price AS old_price,
    new.base_price AS new_price,
    new.base_price - old.base_price AS price_diff;

-- 監査ログテーブルの自動生成パターン
CREATE TABLE price_audit_log (
    id bigint GENERATED ALWAYS AS IDENTITY,
    product_id bigint,
    old_price numeric(10,2),
    new_price numeric(10,2),
    changed_at timestamptz DEFAULT now()
);

-- UPDATE + RETURNING + INSERTをCTEで結合
WITH price_changes AS (
    UPDATE products
    SET base_price = base_price * 0.9  -- 10%割引
    WHERE tax_rate > 0.15
    RETURNING old.id, old.base_price AS old_price, new.base_price AS new_price
)
INSERT INTO price_audit_log (product_id, old_price, new_price)
SELECT id, old_price, new_price FROM price_changes;

-- AIO関連の設定確認
SHOW io_method;              -- 'io_uring' または 'worker' または 'sync'
SHOW io_max_concurrency;     -- 同時非同期I/Oリクエスト数

-- NVMe SSD環境の推奨設定
ALTER SYSTEM SET io_method = 'io_uring';        -- Linux 5.1以上が必要
ALTER SYSTEM SET io_max_concurrency = 256;
ALTER SYSTEM SET effective_io_concurrency = 256; -- AIOと連動
SELECT pg_reload_conf();

Checking for reg* data types in user tables     FAILED

Your installation contains one of the reg* data types in user tables.
These data types reference system OIDs that are not preserved by
pg_upgrade.

-- 問題のあるカラムを見つける
SELECT n.nspname, c.relname, a.attname, t.typname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname IN ('regproc','regprocedure','regoper','regoperator',
                    'regclass','regtype','regconfig','regdictionary')
  AND c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog','information_schema');

-- 解決：該当カラムをtextに変換
ALTER TABLE my_table ALTER COLUMN proc_col TYPE text;

# pg_upgradeが生成する削除スクリプトを確認
cat ./delete_old_cluster.sh

# 内容例：
#!/bin/sh
rm -rf '/var/lib/postgresql/17/main'

# 注意：--link使用後は旧クラスタを絶対に起動してはならない
# ハードリンクでデータファイルを共有しているため、両方から同時アクセスするとデータ破損が発生する

# アップグレード前に拡張機能を削除、アップグレード後に再インストール
psql -U postgres -d mydb -c "DROP EXTENSION IF EXISTS old_extension CASCADE;"

# pg_upgradeを実行

# アップグレード後に最新バージョンの拡張機能をインストール
psql -U postgres -d mydb -c "CREATE EXTENSION old_extension VERSION '2.0';"

-- アップグレード前に重要なクエリプランのスナップショットを保存
\o /tmp/critical_query_plans_pg17.txt
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
-- 重要クエリ1
SELECT ...;
-- 重要クエリ2
SELECT ...;
\o

-- アップグレード後に同じクエリプランを比較
\o /tmp/critical_query_plans_pg18.txt
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
-- 同じクエリを実行
\o

-- プランが変わった場合、コストパラメータを調整
-- PG 18で変更されたデフォルト値を確認
SHOW random_page_cost;    -- AIO導入により変更されている可能性あり
SHOW cpu_tuple_cost;
SHOW parallel_tuple_cost;