Oracle から PostgreSQL 移行でハマるポイント｜症状別の切り分けと対処
目次
OracleからPostgreSQLへの移行は、ツールでスキーマとデータを流し込んだ時点では成功したように見えます。問題が出るのはその後で、「合計値が1円ずれる」「特定のバッチだけ落ちる」「開発環境では速かったのに本番データ量で止まる」といった形で表面化します。移行検証中に遭遇しがちな症状を起点に、原因の切り分け手順と具体的な書き換え方、そして両者のアーキテクチャ差から来る性能問題の見方をまとめます。
🎯 結論：移行直後のトラブルは「空文字とNULL」「DATE型」「識別子の大文字小文字」で大半が説明できる
非互換は無数にありますが、移行直後に「動かない・合わない」として現れるものは偏っています。
- Oracleは
''を NULL として扱い、PostgreSQLは長さ0の文字列という別の値として扱います。この差がNVL、文字列連結、NOT NULL制約、IS NULL判定のすべてに波及します。 - Oracleの
DATEは日付＋時刻（秒精度）で、PostgreSQLのdateは日付のみです。安易にdateへマッピングすると時刻が消えます。 - 未クォートの識別子をOracleは大文字に、PostgreSQLは小文字に畳み込みます。移行ツールが
"EMP"とクォート付きで作ると、アプリのSELECT * FROM empがrelation "emp" does not existで落ちます😇
この3つを先に潰すと、残るのはSQL構文（ROWNUM、CONNECT BY、MERGE）とトランザクション制御、そして性能です。性能は書き換え漏れではなくアーキテクチャの違いから来るので、切り分けの筋道が別になります。
🔍 症状から原因を切り分ける手順
手順1：データが合わない — NULL・暗黙の型変換・ソート順を疑う
まず「件数が違う」のか「値が違う」のか「順番が違う」のかを分けます。
件数が違う場合は、WHERE col = '' や WHERE col IS NULL の判定が変わっている可能性が高いです。移行後のテーブルで、空文字とNULLが混在していないかを確認します。
-- 空文字とNULLの混在を検出する
SELECT
count(*) FILTER (WHERE col IS NULL) AS nulls,
count(*) FILTER (WHERE col = '') AS empties,
count(*) AS total
FROM t;Oracle側には構造上 '' が存在しないため、移行直後に empties > 0 になっていればデータ移送の過程（CSV経由のロードなど）で空文字が生まれています。逆に、移行後にアプリが空文字を書き込むと、Oracle時代なら NOT NULL 制約で弾かれていた行が通ってしまいます。
値が違うときは、集計での型の扱いを見ます。Oracleの NUMBER を numeric にマッピングすると桁は保たれますが、double precision にすると丸め誤差が出ます。逆に NUMBER(10) のような整数カラムを numeric のままにすると、正しいが遅い状態になります。
順番が違う場合、ORDER BY のNULL位置はデフォルトでは一致します（どちらも昇順でNULLが最後、降順で最初）。差が出やすいのは文字列の照合順序です。OracleのデフォルトがバイナリソートであるのにPostgreSQL側を ja_JP.UTF-8 などのロケール付きで作ると、記号や大小文字の並びが変わります。既存の並び順を維持したいなら、列やクエリ単位で COLLATE "C" を指定するのが確実です。
SELECT name FROM t ORDER BY name COLLATE "C";手順2：実行時エラーで止まる — 識別子、予約語、アボートしたトランザクション
エラーメッセージで原因はほぼ特定できます。代表的なものを対応表にしておくと調査が速くなります。
| メッセージ | 原因 | 対処 |
|---|---|---|
relation "emp" does not exist |
クォート付き大文字でDDLが作られた | DDLのクォートを外して小文字に統一 |
column "user" does not exist / syntax error at or near "limit" |
PostgreSQLの予約語をカラム名に使用 | クォートするか改名 |
operator does not exist: character varying = integer |
Oracleでは通っていた暗黙変換 | 明示キャストか、バインド型の修正 |
current transaction is aborted, commands ignored until end of transaction block |
直前のSQLエラーでトランザクション全体が無効化 | SAVEPOINT か例外ブロックで囲む |
could not serialize access due to concurrent update |
分離レベルが REPEATABLE READ 以上 |
既定の READ COMMITTED を使うか再試行実装 |
予約語は推測せず、カタログで機械的にチェックします。
-- 予約語とぶつかっているカラムを洗い出す
SELECT c.table_name, c.column_name, k.catcode
FROM information_schema.columns c
JOIN pg_get_keywords() k ON lower(c.column_name) = k.word
WHERE c.table_schema = 'public' AND k.catcode IN ('R','T');current transaction is aborted は移行時の定番です。Oracleでは単一SQLのエラーでもトランザクションは生き続け、そのままCOMMITできますが、PostgreSQLはエラー時点でトランザクションが失敗状態になり、以降のコマンドをすべて拒否します。「まずINSERTしてみて、一意制約違反ならUPDATE」という実装はそのままでは動きません😇
-- 対処1: SAVEPOINT で部分ロールバック
SAVEPOINT sp1;
INSERT INTO t VALUES (1, 'a');
-- エラーなら ROLLBACK TO SAVEPOINT sp1; して UPDATE へ
-- 対処2: UPSERT に置き換える（推奨）
INSERT INTO t (id, val) VALUES (1, 'a')
ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;⚠️ なお、SAVEPOINTはサブトランザクションを作ります。1トランザクション内で多数（目安として64を超えると）発行するとサブトランザクションIDのキャッシュが溢れ、LWLock:SubtransSLRU などの待機で急激に遅くなります。ループ内でSAVEPOINTを打つ設計は避け、UPSERTや事前チェックに寄せます。
手順3：遅い — 統計情報・実行計画・プリペアド文のキャッシュを順に見る
順番を守ると無駄が減ります。
(1) 統計情報があるか。データを一括ロードした直後は統計が空で、プランナが件数を見誤ります。まず ANALYZE を明示実行し、autovacuumが動いた痕跡を確認します。
SELECT relname, n_live_tup, n_dead_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;(2) 実行計画を実測付きで見る。推定値と実測値の乖離が原因の所在を示します。
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...;💡 見るべきは rows=推定 と actual rows=実測 の比です。1桁以上ずれているノードの直下が問題箇所です。Seq Scan 自体は悪ではなく、「数十行しか返らないのに全件読んでいる」ときだけ問題になります。Buffers: shared read= が大きければキャッシュに乗っていない、Rows Removed by Filter が大きければインデックスの選択性不足です。
(3) プリペアド文のプランを疑う。PostgreSQLはプリペアド文を5回実行した後、パラメータ値に依存しない汎用プラン（generic plan）に切り替えることがあります。値の偏りが大きい列を条件にすると、6回目から急に遅くなる挙動になります。JDBCの prepareThreshold、あるいはサーバ側の設定で制御します。
-- セッション単位で毎回プランを作り直す
SET plan_cache_mode = force_custom_plan;PL/pgSQL内のSQLも内部的にプリペアドされるため、同じ現象が起きます。
常時の観測には pg_stat_statements と auto_explain を入れておきます。Oracleの V$SQL / DBMS_XPLAN に相当する情報源で、これがないと「遅かった時刻のSQL」を後から追えません。
🔄 SQL とデータ型の非互換：よく出る症状と書き換え方
空文字とNULLの扱いの違い（NVL・連結・CHECK制約への波及）
最も影響範囲が広い差異です。挙動を並べると違いが明確になります。
| 式 | Oracle | PostgreSQL |
|---|---|---|
'' IS NULL |
真 | 偽 |
'a' || NULL |
'a' |
NULL |
NVL(col,'x') / COALESCE(col,'x')（colが ''） |
'x' |
'' |
length('') |
NULL |
0 |
連結が特に厄介です。Oracleの col1 || col2 はどちらかがNULLでも残った値を返しますが、PostgreSQLはNULLを返します。住所やコードの組み立てが黙って空になるため、エラーにならず気づきにくい典型です。concat() はNULLを空文字として無視するので、移行時の等価な置き換えになります😇
-- Oracle: pref || city （NULL は無視される）
-- PostgreSQL: そのままだと NULL になる
SELECT concat(pref, city) FROM addr;
-- あるいは
SELECT coalesce(pref,'') || coalesce(city,'') FROM addr;制約への波及も確認します。Oracleで NOT NULL だった列は、空文字の投入を実質的に禁止していました。PostgreSQLで同じ意味を保つなら、チェック制約を足す必要があります。
ALTER TABLE t ADD CONSTRAINT t_col_not_empty CHECK (col <> '');DATE / TIMESTAMP と SYSDATE、ORDER BY の NULL の位置
OracleのDATEは時刻を持ちます。時刻が使われている可能性がある列は timestamp(0)（またはタイムゾーンを扱うなら timestamptz）へマッピングし、純粋な日付列だけ date にします。移行前に、Oracle側で時刻部分の有無を実データで確認しておくのが安全です。
-- Oracle 側で時刻を持つ行があるか確認
SELECT count(*) FROM t WHERE ord_date <> TRUNC(ord_date);日付関連の置き換えは次の通りです。
| Oracle | PostgreSQL | 注意点 |
|---|---|---|
SYSDATE |
localtimestamp(0) / statement_timestamp() |
SYSDATE は文の中では固定で小数秒を持たない。now() はトランザクション開始時刻で固定、clock_timestamp() は呼ぶたびに進む |
SYSTIMESTAMP |
clock_timestamp() |
|
SYSDATE + 1 |
now() + interval '1 day' |
timestamp + 整数 はエラー |
ADD_MONTHS(d, 3) |
d + interval '3 month' |
月末の丸め方が一致しない場合がある |
MONTHS_BETWEEN(a,b) |
自作関数または date_part 演算 |
端数の扱いが異なる |
TRUNC(d, 'MM') |
date_trunc('month', d) |
|
TO_CHAR(d,'YYYYMMDD') |
同じ | 書式要素の一部は非互換 |
DUAL |
FROM句を省略 | 互換ビューを作る手もある |
ORDER BY のNULL位置は前述のとおり既定では一致します。ただしOracle側で '' だった値がNULL扱いだった点は影響します。PostgreSQLへ空文字として持ち込むと、NULLは末尾、空文字は先頭に並び、同じクエリで結果の並びが変わります。NULLに正規化するか、ORDER BY nullif(col,'') NULLS LAST のように明示するかを決めておきます。
ROWNUM・CONNECT BY・MERGE・ROWID の置き換え
ROWNUM は単純なページングなら LIMIT / OFFSET、グループ内順位付けならウィンドウ関数です。注意すべきは、Oracleの ROWNUM はソート前に採番されるため、WHERE ROWNUM <= 10 ORDER BY x は「任意の10件を並べた結果」である点です。移行時に ORDER BY x LIMIT 10 と書き換えると結果が変わる（そして多くの場合、本来の意図に合う）ので、仕様として確認が必要です。
-- Oracle: ORDER BY 付きサブクエリ + ROWNUM
-- PostgreSQL:
SELECT * FROM t ORDER BY x LIMIT 10 OFFSET 20;
-- グループごとに最新1件（Oracleの ROW_NUMBER 相当はそのまま使える）
SELECT DISTINCT ON (grp) grp, x, val
FROM t ORDER BY grp, x DESC;CONNECT BY は再帰CTEに書き換えます。LEVEL は深さカウンタ、SYS_CONNECT_BY_PATH は配列や文字列の蓄積で表現します。循環がある可能性があるデータでは、PostgreSQL 14以降の CYCLE 句か、経路配列での自己チェックが必要です。
WITH RECURSIVE tree AS (
SELECT id, parent_id, 1 AS lvl, ARRAY[id] AS path
FROM org WHERE parent_id IS NULL
UNION ALL
SELECT o.id, o.parent_id, t.lvl + 1, t.path || o.id
FROM org o JOIN tree t ON o.parent_id = t.id
WHERE NOT o.id = ANY(t.path) -- 循環防止
)
SELECT repeat(' ', lvl - 1) || id::text FROM tree ORDER BY path;MERGE はPostgreSQL 15以降で利用できますが、RETURNING の対応はさらに後のバージョンです。より広いバージョンで動かすなら INSERT ... ON CONFLICT DO UPDATE を使います。ただしこちらは一意制約（または一意インデックス）が必要で、DELETEを含むMERGEや、任意条件でのマッチングは表現できません。使用するバージョンで何が使えるかは公式ドキュメントで確認してください。
ROWID に相当するのは ctid ですが、これは物理位置なのでUPDATEやVACUUM FULLで変わります。「読み取ったROWIDで後からUPDATE」という実装は主キーに置き換えるのが原則です。
🧩 PL/SQL から PL/pgSQL へ：例外処理とトランザクション制御の落とし穴
構文は似ていますが、トランザクションの扱いが根本的に違います。
⚠️ 関数の中でCOMMITできません。PostgreSQLの関数は呼び出し元トランザクションの内側で実行されるため、COMMIT / ROLLBACK は使えません。中間コミットが必要な長時間バッチは、PostgreSQL 11以降の PROCEDURE にして CALL で呼び出すか、制御をアプリ側へ出します。プロシージャであっても、CALL が既にトランザクションブロック内にある場合はコミットできません。
自律トランザクション（PRAGMA AUTONOMOUS_TRANSACTION）に直接の代替はありません。「本体はロールバックしてもエラーログだけは残す」という定番パターンは、dblink経由で別セッションに書くか、ログをアプリ層で出す設計に変えるのが素直です。
例外ブロックは暗黙のサブトランザクションを作ります。これが性能に効きます。
-- ループ1回ごとに例外ブロックを通す＝サブトランザクションを毎回作る
FOR r IN SELECT * FROM src LOOP
BEGIN
INSERT INTO dst VALUES (r.id, r.val);
EXCEPTION WHEN unique_violation THEN
NULL; -- エラーも握り潰している
END;
END LOOP;この形は件数が増えるほど不利になります。ON CONFLICT に置き換えれば例外ブロックは不要になり、サブトランザクションも発生しません。また WHEN OTHERS THEN NULL はOracle同様アンチパターンで、移行時の不具合を隠してしまいます。最低限 SQLSTATE と SQLERRM を残します。
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'failed id=% state=% msg=%', r.id, SQLSTATE, SQLERRM;その他の対応として、RAISE_APPLICATION_ERROR(-20001, msg) は RAISE EXCEPTION '%', msg USING ERRCODE = 'P0001'、DBMS_OUTPUT.PUT_LINE は RAISE NOTICE になります。⚠️ SELECT INTO の挙動は要注意です。PL/pgSQLは既定では0件でもエラーにならず（変数はNULLのまま）、複数件でも先頭の1行を黙って使います。Oracleと同じく NO_DATA_FOUND / TOO_MANY_ROWS を発生させたい場合は SELECT ... INTO STRICT と書きます。パッケージ変数のような状態保持の仕組みはないため、スキーマ＋関数群への再編と、状態はテーブルやカスタムGUCで持つ設計変更が必要です。
DDLの扱いも違います。PostgreSQLではDDLがトランザクション制御の対象で、暗黙コミットされません。移行スクリプトをまとめてロールバックできる利点がある一方、ALTER TABLE は ACCESS EXCLUSIVE ロックを取り、待たされている間に後続のSELECTまで詰まります。本番オペレーションでは lock_timeout を設定してから実行するのが定石です。
⚡ 性能が出ないときに見るべき内部の違い
オプティマイザとヒント：統計情報、拡張統計、pg_hint_plan の使いどころ
PostgreSQLのプランナはコストベースですが、Oracleと比べて統計の種類が少なく、特に列間の相関を既定では知りません。WHERE pref = '東京都' AND city = '新宿区' のような相関のある条件では、独立と仮定して選択度を掛け合わせるため推定行数が過小になり、ネステッドループを選んで遅くなります。Oracleの列グループ統計に相当するのが拡張統計です。
CREATE STATISTICS st_addr (dependencies, ndistinct) ON pref, city FROM addr;
ANALYZE addr;その他、効果が大きい調整点：
default_statistics_target（既定100）を、値の偏りが大きい列でALTER TABLE ... ALTER COLUMN col SET STATISTICS 500;のように個別に引き上げる- SSDやクラウドストレージでは
random_page_costを既定の4.0から下げる（1.1前後が一般的な出発点）。effective_cache_sizeは実メモリに見合う値にする - 結合数が多いクエリでは
join_collapse_limit/from_collapse_limit（既定8）を超えると探索が打ち切られ、書いた順に引きずられる
💡 それでもプランが安定しない場合の最後の手段が pg_hint_plan です。Oracleのヒントに近い記法でスキャン方法や結合順を固定できますが、移行の初期段階から多用すると、統計やインデックス設計の不備が見えなくなります。優先順位は「統計 → インデックスとSQLの書き方 → パラメータ → ヒント」です。
/*+ IndexScan(t idx_t_x) NestLoop(t u) */
SELECT ... ;なお索引構成表（IOT）やビットマップインデックスといったOracleの機能に直接対応するものはありません。前者は覆う範囲を広げたインデックス（INCLUDE 句）や CLUSTER、後者は複数インデックスのビットマップANDで代替を検討します。
追記型MVCCとVACUUM：Oracle の UNDO との違いが招く肥大化と性能劣化
追記型MVCCの影響は、移行後にいちばん想定外の形で出てきます😇
Oracleは更新を元の場所で行い、旧イメージをUNDO表領域へ退避します。PostgreSQLは追記型で、UPDATEは「旧行に削除マークを付け、新しい行バージョンを書く」動作です。この差から次の帰結が生まれます。
- 更新のコストが高い。更新対象がインデックス列を含まず、同一ページに空きがあればHOT更新となりインデックス更新を省けますが、条件を外すと全インデックスにエントリが追加されます。更新頻度の高いテーブルは
fillfactorを下げてHOTが効く余地を作ります - 不要行はVACUUMが回収するまで残る。テーブルとインデックスが膨れ、同じ行数でもスキャンするページ数が増えて遅くなります
- 長時間トランザクションがVACUUMを止める。参照するだけのセッションでも、開いたまま放置されればその時点のスナップショットが必要とみなされ、以降の不要行を回収できません。Oracleなら
snapshot too oldでエラーになる代わりに、PostgreSQLでは肥大化として現れます - 放置するとXID周回の危険がある。最悪ケースでは書き込みを受け付けなくなります
監視は次の3点です。
-- 1) 不要行とVACUUMの実施状況
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC;
-- 2) VACUUMを妨げる長時間トランザクション
SELECT pid, state, now() - xact_start AS xact_age, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND now() - xact_start > interval '5 min'
ORDER BY xact_age DESC;
-- 3) XID の消費状況
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;対処の方向性：
autovacuum_vacuum_scale_factorの既定0.2は大きなテーブルでは緩すぎます。行数の多いテーブルは個別にALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_delay = 0);のように積極化する- 追いつかない場合は
autovacuum_max_workersとautovacuum_vacuum_cost_limitを見直す - アイドル状態のトランザクション放置を防ぐため
idle_in_transaction_session_timeoutを設定する - 既に膨らんだテーブルは、ロックを長く取れないなら
pg_repack等を検討する（VACUUM FULLはACCESS EXCLUSIVEロックを取る） - インデックスの膨張には
REINDEX CONCURRENTLY
もう一点、プロセスモデルの違いも押さえておきます。PostgreSQLは接続ごとにプロセスを生成するため、Oracleの共有サーバのような多重化機構を持ちません。接続数の多いアプリや短命コネクションを多発させる構成では、PgBouncerなどのプーラーを前提に設計します。
📝 再発防止：移行前に潰しておくチェック項目と、次に学ぶこと
検証フェーズで機械的に確認できる項目を並べます。
- 非互換の棚卸しは
ora2pgのレポート機能などで一覧化する。手作業のgrepでは(+)外部結合やDECODEの取りこぼしが出る - 識別子はDDLからクォートを排し、すべて小文字に統一する。予約語衝突は
pg_get_keywords()との突き合わせで検出できる - 型マッピング表を作る。
DATEの時刻有無を実データで確認し、NUMBERは精度に応じてinteger/bigint/numericを選び分ける。VARCHAR2のバイト長／文字長の違いで桁を縮めていないかも見る - アプリが空文字を書き込む経路を洗い、NULLへ正規化するか
CHECK (col <> '')を付けるかを決める ||を使っている箇所はNULLの挙動が変わるので総点検し、concat()かcoalesce()へ寄せる- 既存の並び順に依存した画面や帳票があるなら、照合順序を
COLLATE "C"などで固定する - トランザクション設計として、エラー後に処理を続ける実装がないかを確認する。SAVEPOINTのループ発行、関数内COMMIT、自律トランザクションを洗い出す
- 本番相当のデータ量でプランを確認する。行数が少ないと問題が出ないため、統計を取った上で主要SQLの
EXPLAIN (ANALYZE, BUFFERS)を取得し、乖離の大きいものを事前に潰す - 安全弁として
statement_timeout、lock_timeout、idle_in_transaction_session_timeoutを初期から入れる - 観測の準備として
pg_stat_statements、auto_explain、pg_stat_activityの定期サンプリング（またはマネージドサービスの性能分析機能）を用意する
次に踏み込むべきは、待機イベントを起点とした性能分析です。Oracleでの V$SESSION / ASHによる待機分析の考え方はそのまま活かせますが、PostgreSQLは既定でアクティブセッション履歴を持たないため、サンプリングの仕組みを自前で用意するか、マネージドサービスの機能を使う前提で設計する必要があります。あわせてVACUUMとautovacuumの挙動、WALとチェックポイントの関係、shared_buffers とOSキャッシュの二段構成を押さえると、「なぜこの構成でこの待機が出るのか」が繋がって理解できるようになります✨
パラメータの既定値や対応バージョン、利用可能な構文はメジャーバージョンごとに変わります。設定前に、使用するバージョンの公式ドキュメント（Aurora/RDSを使う場合はそのエンジン固有の制約も）を確認します。