--- title: "データスキーマとは？データベースのスキーマと三層スキーマ（外部・概念・内部）の違い・設計と管理の実務" url: "https://www.issoh.co.jp/tech/details/4124/" published: 2024-11-12 updated: 2026-10-28 categories: ["データベース"] publisher: "株式会社一創" --- # データスキーマとは？データベースのスキーマと三層スキーマ（外部・概念・内部）の違い・設計と管理の実務 データスキーマとは、データが「どんな項目を・どの型で・どんな制約と関連のもとで」持たれるかを、データそのものより先に決めておく構造定義のことです。データベースの文脈でスキーマと言うときには、さらに二つの別物が同じ名前で呼ばれています。データベース全体の構造を記述した設計図としてのスキーマと、`CREATE SCHEMA` で作るオブジェクトの入れ物としてのスキーマです。基本情報技術者試験に出る「外部スキーマ・概念スキーマ・内部スキーマ」は前者、MySQL のスキーマ一覧に並ぶ名前は後者の話です。この記事では両者を切り分け、三層スキーマの違いと試験での問われ方、RDBMS ごとのスキーマの実体、設計と稼働中の変更管理までを、PostgreSQL 18.6 と MySQL 8.4.8 で実行した SQL と出力つきで扱います（心理学のスキーマ＝認知の枠組みとは別の概念です）。 ## まとめ：データスキーマとデータベースのスキーマの要点 - データスキーマは、項目名・型・必須かどうか・制約・関連を前もって決めた構造定義。データベースの表定義のほか、JSON Schema や Avro で書くメッセージの形も含む。 - データベースのスキーマには「構造定義（設計図）」と「オブジェクトの名前空間（入れ物）」の二つの意味がある。 - 三層スキーマは ANSI/X3/SPARC が1975年の中間報告で示した構造で、外部スキーマ＝利用者ごとの見え方（ビュー）、概念スキーマ＝業務データの論理モデル（表定義）、内部スキーマ＝物理的な格納方法（ファイル編成・インデックス）に当たる。 - 三層に分ける目的は、上の層を壊さずに下の層を変えられること（論理的データ独立性・物理的データ独立性）。試験ではビューとサブスキーマを外部スキーマと見抜けるかが繰り返し問われる。 - MySQL では `CREATE SCHEMA` は `CREATE DATABASE` の同義語、PostgreSQL ではスキーマはデータベースの下の名前空間になる。 - スキーマ設計は概念設計（ER 図）→論理設計（正規化・表定義）→物理設計（インデックス）の順に降りる。金額を浮動小数点型にしない、といった型の判断もこの段階で決める。 - 稼働中のスキーマ変更では、ALTER TABLE のロック待ちが後続の SELECT まで待たせる事故に注意が必要。`lock_timeout` を付けて流し、列の改名や NOT NULL 化は段階に割る。 ## データスキーマとは：データの構造と型と制約を先に決めておく定義のこと スキーマ（schema）はギリシャ語由来の「形・枠組み」を語源とする語で、IT では「どんなデータをどう持つかを記述したもの」を広く指します。データスキーマという言い方はこの広い意味で使われることが多く、リレーショナルデータベースの表定義だけでなく、API がやり取りする JSON の形やイベントのメッセージ形式も含みます。データベースの全体像（DBMS の種類や RDB と NoSQL の違い）から整理したい場合は、[データベースとは？種類・DBMS・RDBとNoSQLの選び方](/tech/details/13012/)を先に読むと、スキーマの話が置かれている位置をつかみやすくなります。 ### データスキーマが決めるもの：項目名・データ型・必須・制約・関連の5要素 形式が SQL の DDL でも JSON Schema でも、データスキーマが決めている内容は次の5つに分けられます。どれも省略はできますが、書いた範囲の型や制約に照らしてデータを機械的に検証できるのがスキーマの役割です。 | 要素 | RDB での書き方 | JSON Schema での書き方 | | ---------- | ---------------- | ------------------------------ | | 項目名 | 列名 | properties のキー | | データ型 | integer、text など | type | | 必須・NULLの扱い | NOT NULLでNULLを禁止 | requiredでキーを必須化、typeでnull許可を指定 | | 値の制約 | CHECK、UNIQUE | minimum、pattern など | | 他データとの関連 | 主キー、外部キー | 参照整合性の検証は対象外 | RDB は関連を外部キーとして DBMS 自身が強制できますが、JSON Schema の `$ref` はスキーマ定義の再利用であり、参照先のデータが実在するかまでは確かめません。同じ「スキーマ」でも守ってくれる範囲が違うので、検証をどこに置くかはこの差から決めます。外部キーの書き方と削除時の動作の選び方は[外部キーとは：制約の書き方とON DELETEの選び方・後付け手順を実装目線で解説](/tech/details/18651/)で扱っています。 ### 構造定義としてのスキーマ：テーブルと列・型・制約をDDLで記す設計図 テーブル、列とその型、主キー・外部キー、制約、ビュー、インデックスといった定義の集合が、構造定義としてのスキーマです。DDL（`CREATE TABLE` など）で記述し、データそのものではなく「データの入れ物の形」を決めます。試験や設計論で「スキーマ」と言えばほぼこの意味で、三層スキーマもこちらの話です。 ### 名前空間としてのスキーマ：CREATE SCHEMAで作るオブジェクトの入れ物 SQL 標準では、スキーマはテーブルやビュー、関数などのオブジェクトをまとめる名前空間です。PostgreSQL で `sales.orders` と `hr.orders` が衝突せずに共存できるのは、`sales` と `hr` が別スキーマだからです。部門やテナント、本番と検証といった単位でオブジェクトを区切るための道具で、設計図という意味のスキーマとは役割が違います。 ### スキーマ・データベース・テーブルの違い：製品ごとの階層関係 PostgreSQL や SQL Server ではデータベース＞スキーマ＞テーブルの入れ子になります。テーブルは行と列でデータを実際に保持する実体、スキーマはテーブル群をまとめる区画、データベースはスキーマ群を含む最上位の器です。ただし MySQL ではスキーマとデータベースが同じものなので階層が一段減り、Oracle ではスキーマがユーザーと一体になります。製品ごとの違いは後半の章で実行結果とともに示します。 ### DB以外のデータスキーマ：JSON SchemaやAvroで記述するメッセージの型 データベースの外にもデータスキーマはあります。代表は JSON の構造を記述する JSON Schema で、[JSON Schema の仕様一覧](https://json-schema.org/specification)は2026年10月時点で現行版を 2020-12（前版は 2019-09）としています。API の入出力や設定ファイルの検証に使い、次のように書きます。 ``` { "$schema": "https://json-schema.org/draft/2020-12/schema", "type": "object", "properties": { "order_id": { "type": "integer" }, "amount": { "type": "integer", "minimum": 0 } }, "required": ["order_id", "amount"] } ``` 書き方と検証器ごとの差は[JSON Schemaとは？JSONの構造を検証する書き方とDraft 2020-12の実装](/tech/details/16289/)で詳しく扱っています。Kafka などのイベント基盤では Apache Avro や Protocol Buffers のスキーマでメッセージの形を決め、版を上げるときの互換性は[スキーマレジストリとは？互換性モードとスキーマ進化の実装](/tech/details/16115/)で管理するのが一般的です。 ### スキーマオンライトとスキーマオンリード：型を決めるタイミングの違い データスキーマには「いつ適用するか」という軸もあります。RDB は書き込みの時点でスキーマに合わないデータを拒否するため、スキーマオンライト（schema-on-write）と呼ばれます。データレイクのようにファイルをそのまま置き、クエリの時点で列と型を当てはめる方式はスキーマオンリード（schema-on-read）です。オンリードでは取り込み時の型検証を後回しにできる反面、読み出し時に初めて型の食い違いが見つかり、集計結果がある日から急に合わなくなる形で表面化します。生データはオンリードで受け、分析用の層ではオンライト相当の型を課す構成が[データレイクハウス](/tech/details/13628/)の基本形です。 ## 三層スキーマ（ANSI/SPARC）とは：外部・概念・内部スキーマの違い 三層スキーマは、米国規格協会（ANSI）の下部組織 ANSI/X3/SPARC のデータベース研究グループが、1975年の中間報告（[Interim Report: ANSI/X3/SPARC Study Group on Data Base Management Systems 75-02-08](https://sigmod.org/publications/dblp/db/journals/sigmod/SPARC75.html)、FDT – Bulletin of ACM SIGMOD 7(2)）で示した構造モデルです。同じデータベースを、利用者から見た姿・業務としての姿・機械が格納する姿の三つのレベルで記述し分けます。基本情報技術者試験やデータベーススペシャリスト試験で繰り返し出る論点です。 | 層 | 記述する内容 | RDBでの実体 | 数 | | ------ | ----------- | ------------- | -------- | | 外部スキーマ | 利用者ごとの見え方 | ビュー | 利用者ごとに複数 | | 概念スキーマ | 業務データの論理モデル | 表定義（正規化済み） | DB全体で1つ | | 内部スキーマ | 物理的な格納方法 | ファイル編成、インデックス | DB全体で1つ | 三層は上から下へ写像でつながっており、層と層の対応づけは DBMS が担います。複数あるのは外部スキーマだけで、概念スキーマと内部スキーマはデータベース全体で1つです。 ### 外部スキーマとは：利用者ごとの見え方をビューで切り出して見せる層 外部スキーマは、概念スキーマで定義されたデータのうち、その利用者に必要な部分だけを切り出した見え方です。リレーショナルデータベースではビューが該当します。給与テーブルに支給額と口座番号があっても、経理以外の部署には氏名と所属だけを見せるビューを渡せば、アプリケーションは不要な列の存在を知らずに済みます。集計が重いビューを実体として保持する[マテリアライズドビュー](/tech/details/3545/)も外部スキーマの道具ですが、格納とリフレッシュという内部スキーマの都合を持ち込むため、リフレッシュ間隔が業務の許容できる古さに収まるかを先に決めます。 ### 概念スキーマとは：業務データ全体を論理モデルと表定義で記述する層 概念スキーマは、対象業務にどんなデータが存在し、それらがどう関係するかを、特定の利用者にも格納方式にも依存しない形で記述したものです。エンティティと関連を洗い出す ER 図、そこから導いた表定義、正規化による冗長の排除がこの層に当たります。日本語の設計工程で言う概念設計は ER 図までを指すことが多く、正規化して表定義に落とす論理設計まで含めた全体が三層スキーマの概念スキーマに相当します。 ### 内部スキーマとは：ファイル編成やインデックスなど物理的な格納の層 内部スキーマは、概念スキーマのデータを記憶装置上にどう置くかの記述です。ファイル編成、インデックスの種類と対象列、パーティションの切り方、圧縮の有無などが含まれます。PostgreSQL で JSONB や全文検索を速くするために [GIN インデックス](/tech/details/8889/)を張る判断は、典型的な内部スキーマの設計です。 ### 三層に分ける目的：論理的データ独立性と物理的データ独立性の確保 三層スキーマの価値は分類そのものではなく、層を切り離して得られる二つの独立性にあります。物理的データ独立性は、内部スキーマを変えても概念スキーマとアプリケーションが影響を受けないこと。インデックスを張り替えても、表を別のディスクへ移しても、SELECT 文は書き換えずに済みます。論理的データ独立性は、概念スキーマを変えても外部スキーマ経由のアプリケーションが影響を受けないことです。 物理的独立性はDBMSの格納方式と論理定義の分離によって支えられます。論理的独立性を保つ代表的な方法は、ビューなどで利用者向けの構造を維持することです。アプリケーションが基底テーブルを直接参照する設計は外部スキーマの層を省いた設計であり、参照する列の削除や改名など、既存SQLと互換性のない変更では、その列を使うプログラムの修正が必要になります。 ### PostgreSQLで三層スキーマを再現するSQL：ビュー・表・索引の実行結果 三層の対応は次のSQLで確かめられます。ロール作成とSET ROLEを含むため、検証専用DBに管理者ロールで接続して実行します。意図的に権限エラーを発生させる箇所があるので、各文を自動コミットで順に実行します。以下は PostgreSQL 18.6 の空のデータベースで実行した結果で、表定義が概念スキーマ、ビューが外部スキーマ、インデックスが内部スキーマに当たります。 ``` CREATE SCHEMA hr; -- 概念スキーマ：業務データの表定義（型・必須・制約） CREATE TABLE hr.employees ( id bigint PRIMARY KEY, name text NOT NULL, dept text NOT NULL, salary integer NOT NULL CHECK (salary >= 0), bank_account text ); -- 外部スキーマ：給与と口座を見せないビュー CREATE VIEW hr.employees_directory AS SELECT id, name, dept FROM hr.employees; -- 内部スキーマ：表定義を変えずに探索の方法だけを足す CREATE INDEX employees_dept_idx ON hr.employees (dept); -- 利用者にはビューだけを渡す CREATE ROLE directory_reader; GRANT USAGE ON SCHEMA hr TO directory_reader; GRANT SELECT ON hr.employees_directory TO directory_reader; INSERT INTO hr.employees VALUES (1, '佐藤', '営業', 300000, '123-4567'); SET ROLE directory_reader; SELECT * FROM hr.employees_directory; -- id | name | dept -- ----+------+------ -- 1 | 佐藤 | 営業 SELECT * FROM hr.employees; -- ERROR: permission denied for table employees RESET ROLE; ``` 基底テーブルへの権限を持たないロールでもビュー経由なら読めるのは、[PostgreSQL の CREATE VIEW の仕様](https://www.postgresql.org/docs/current/sql-createview.html)のとおり、既定ではビュー内の基底テーブルへのアクセスがビュー所有者の権限で判定されるためです。PostgreSQL 15 以降で `security_invoker` を付けると呼び出し側の権限で判定されるので、外部スキーマとして使うビューに付けるかは意図して決めます。ロールと既定権限の設計は[PostgreSQLのロールと権限設計｜CREATE ROLEの属性とGRANT・既定権限の決め方](/tech/details/16984/)で整理しています。 続けて、論理的データ独立性も同じ環境で確かめました。給与と口座を別表 `hr.payroll` へ移して概念スキーマを変えても、ビューが移した列を参照していなければ、ビューにも利用者の権限にも手を入れずに結果は変わりません。 ``` BEGIN; CREATE TABLE hr.payroll ( employee_id bigint PRIMARY KEY REFERENCES hr.employees(id), salary integer NOT NULL CHECK (salary >= 0), bank_account text ); INSERT INTO hr.payroll SELECT id, salary, bank_account FROM hr.employees; ALTER TABLE hr.employees DROP COLUMN salary, DROP COLUMN bank_account; COMMIT; SET ROLE directory_reader; SELECT * FROM hr.employees_directory; -- id | name | dept -- ----+------+------ -- 1 | 佐藤 | 営業 RESET ROLE; -- ビューが使っている列は、ビューを残したまま消せない ALTER TABLE hr.employees DROP COLUMN dept; -- ERROR: cannot drop column dept of table hr.employees because other objects depend on it -- DETAIL: view hr.employees_directory depends on column dept of table hr.employees -- CREATE OR REPLACE VIEW でもビューの既存列は減らせない CREATE OR REPLACE VIEW hr.employees_directory AS SELECT id, name FROM hr.employees; -- ERROR: cannot drop columns from view ``` PostgreSQL は依存関係を追跡しており、ビューが使っている列の削除は止めます。[CREATE OR REPLACE VIEW](https://www.postgresql.org/docs/18/sql-createview.html) は末尾への列追加なら既存の権限を保ったまま行えますが、既存の列を減らすには DROP VIEW と CREATE VIEW が必要で、そのとき付けていた GRANT も消えます。利用者に見せる列を減らす変更では、作り直しと再付与を同じトランザクションに入れておく点が実務での落とし穴です。 ### 試験での三層スキーマの問われ方：ビューとサブスキーマは外部スキーマ 情報処理技術者試験の午前問題では、三層スキーマは定義の暗記より「どれがどの層に当たるか」の対応で問われます。@IT の[過去問解説（ANSI/SPARC 3層スキーマ）](https://atmarkit.itmedia.co.jp/fdb/rensai/tsubo01/tb01%5F3.html)が取り上げている設問では、「関係データベースのビューやネットワークデータベースのサブスキーマは、概念スキーマに相当する」が適切でない記述として正解になります。ビューもサブスキーマも、プログラムが必要とする部分だけを記述したものなので外部スキーマです。 | 選択肢に出る語 | 当たる層 | | --------------------------- | --------- | | ビュー、サブスキーマ、プログラムから見たデータ | 外部スキーマ | | 表定義、ER 図、特定アプリに依存しない全体 | 概念スキーマ | | ファイル編成、インデックス、VSAM、記憶装置上の配置 | 内部スキーマ | | インデックス追加でアプリが影響を受けない | 物理的データ独立性 | | 表の分割・統合でアプリが影響を受けない | 論理的データ独立性 | 引っかけになりやすいのは「概念スキーマは利用者ごとに複数ある」という記述（複数あるのは外部スキーマ）と、「3層クライアントサーバシステム」との混同です。後者はプレゼンテーション層・アプリケーション層・データ層というシステム構成の話で、三層スキーマとは別物です。 ## RDBMSごとのスキーマの実体：MySQLとPostgreSQLの意味の違い 三層スキーマの説明を読んだあとで `SHOW SCHEMAS` を実行すると、多くの人が混乱します。実装上のスキーマ（名前空間）は製品ごとに位置づけが異なり、同じ SQL が別の意味になるためです。 ### MySQLのスキーマとは：CREATE DATABASEと同義のデータベースそのもの MySQL 8.4 のリファレンスマニュアル（CREATE DATABASE Statement）は `CREATE SCHEMA` を `CREATE DATABASE` の同義語と定義しています。MySQL 8.4.8 で実行すると、そのことが出力からはっきり分かります。 ``` CREATE SCHEMA sales; CREATE TABLE sales.customers (id BIGINT PRIMARY KEY, mail VARCHAR(255)); CREATE TABLE sales.orders (id BIGINT PRIMARY KEY, amount INT NOT NULL); SHOW DATABASES; -- information_schema / mysql / performance_schema / sales / sys CREATE SCHEMA a; CREATE SCHEMA a; -- ERROR 1007 (HY000): Can't create database 'a'; database exists SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sales'; -- def | sales | customers -- def | sales | orders ``` スキーマを作ったのにエラーメッセージは「database exists」と返り、データディレクトリにも `sales` というフォルダが1つできるだけです。MySQL に「データベースの中に複数のスキーマを作る」階層はなく、テーブルは `データベース名.テーブル名` の2段で指します。`information_schema` の `TABLE_CATALOG` は常に `def` で、`TABLE_SCHEMA` 列にはデータベース名が入ります。MySQL Workbench が左のツリーで「Schemas」と表示しているのも、データベースそのものです。 ### PostgreSQLのスキーマとは：DBの下位にある名前空間とsearch\_path PostgreSQL では、一つのデータベースが複数のスキーマを持ち、スキーマがテーブルを持ちます。実際に書く修飾は `スキーマ名.テーブル名` の2段です。`データベース名.スキーマ名.テーブル名` も構文としては書けますが、[PostgreSQL 公式ドキュメントの 5.10 Schemas](https://www.postgresql.org/docs/current/ddl-schemas.html) が SQL 標準への形式的な準拠のためと明記しているとおり、指定できるのは接続中のデータベース名だけです。スキーマ名を省いたときの探索順は `search_path` が決め、PostgreSQL 18.6 の初期状態で確認した既定値は `"$user", public` でした。 ``` SHOW search_path; -- "$user", public -- PostgreSQLの検証用DBで、この例の表を準備する CREATE SCHEMA sales; CREATE TABLE sales.orders (id bigint PRIMARY KEY, amount integer NOT NULL); SET search_path TO sales, public; SELECT * FROM orders; -- sales.orders が解決される ``` 権限の既定値は版で変わっています。PostgreSQL 14 以前は public スキーマに全ユーザーが CREATE 権限を持っていましたが、[PostgreSQL 15 のリリースノート](https://www.postgresql.org/docs/release/15.0/)で「Remove PUBLIC creation permission on the public schema」とされ、既定から外れました。14 以前から pg\_upgrade で上げたデータベースには旧来の権限が残るため、次の剥奪を明示的に流します。 ``` REVOKE CREATE ON SCHEMA public FROM PUBLIC; ``` search\_path の解決順やテナントごとにスキーマを分ける判断は[PostgreSQLのスキーマ運用｜search\_pathの解決順とpublic権限・テナント分割の判断基準](/tech/details/16980/)、スキーマ以外の差（データ型や全文検索）は[PostgreSQLとMySQLの違いの比較](/tech/details/6262/)で整理しています。 ### スキーマ一覧の確認方法：information\_schemaで構造をSQLから調べる 動いているデータベースの構造を確かめるときは、SQL 標準の情報スキーマ `information_schema` を引くのが移植性の高い方法です。[PostgreSQL の The Information Schema の章](https://www.postgresql.org/docs/current/information-schema.html)にあるとおり、スキーマ・テーブル・列・制約の定義をビューとして参照できます。 ``` -- スキーマごとのテーブル一覧（システム用スキーマは除く） SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name; -- 1テーブルの列定義（型と必須かどうか） SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'hr' AND table_name = 'employees' ORDER BY ordinal_position; ``` 同じ SQL を MySQL で流すと `table_schema` にはデータベース名が入るので、返ってくる「スキーマ」の粒度が製品によって変わります。PostgreSQL の psql なら `\dn` でスキーマ一覧、`\d hr.employees` で列と制約を確認できます。 ### OracleとSQL Serverのスキーマ：ユーザーと一体の方式とdbo既定の方式 Oracle ではスキーマはユーザーと一対一で結びつき、ユーザーを作ると同名のスキーマができます。オブジェクトの所属先は作成先のスキーマで決まり、CREATE ANY TABLEなどの権限があれば、別ユーザーのスキーマにも作成できます。スキーマを分けることはアカウントを分けることになります。SQL Server はスキーマとユーザーが分離しており、既定のスキーマは `dbo` です（[データベース スキーマの作成](https://learn.microsoft.com/ja-jp/sql/relational-databases/security/authentication-access/create-a-database-schema)）。マルチテナントでスキーマ分離を選ぶと、この差がそのまま移植性の壁になります。PostgreSQL でテナントごとにスキーマを切る設計を MySQL に持ち込むと、テナントごとにデータベースを作る構成になります。ただし、MySQLでは必要な権限があれば同じ接続から複数のデータベースを参照できるため、接続をテナントごとに分ける必要はありません。バックアップ対象の指定も移行先に合わせて見直します。 ## データベースのスキーマ設計の進め方：概念設計・論理設計・物理設計 スキーマ設計は、業務の言葉から始めて機械の都合へ降りていく工程です。概念設計と論理設計は主に概念スキーマ、物理設計は主に内部スキーマを具体化します。上流で見落とした業務要件は、後から表定義やデータ移行のやり直しにつながります。 ### 概念設計の手順：業務エンティティ・関連の抽出とER図への整理 業務で扱う対象（顧客、注文、商品）をエンティティとして抽出し、関連と多重度（1対多、多対多）を ER 図で表します。記法は IE 記法（鳥の足）や IDEF1X などに統一し、多重度と任意・必須の記号を凡例で共有します。この段階では RDBMS も性能も考えません。多対多の関連を見つけたら、注文明細のような関連エンティティを立てて1対多の組に分解します。書き方は[ER図とは？書き方・エンティティやリレーション・カーディナリティを実装目線で解説](/tech/details/13484/)で扱っています。 ### 論理設計の手順：正規化による更新異常の排除と表・キーの定義 ER 図をテーブル・列・主キー・外部キーに変換し、正規化で更新異常（挿入時・更新時・削除時の矛盾）を除きます。実務では第三正規形までを基準に置き、参照が集中する箇所だけ意図して非正規化を残します。非正規化は「性能のため」と曖昧に決めず、どのクエリの何ミリ秒を縮めるためかを設計書に残しておくと、後から戻す判断ができます。手順は[データベース正規化とは？第1〜第3正規形の手順と非正規化の判断](/tech/details/16281/)にまとめています。 ### 列の型を決める基準：金額は numeric、日時は timestamptz を選ぶ理由 論理設計で最も後から直しにくいのが列の型です。PostgreSQL 18.6 で実行した次の2つの結果が、判断の根拠になります。 ``` SELECT 0.1::float8 + 0.2::float8 = 0.3::float8 AS float_eq, 0.1::numeric + 0.2::numeric = 0.3::numeric AS numeric_eq, 0.1::float8 + 0.2::float8 AS float_sum; -- float_eq | numeric_eq | float_sum -- ----------+------------+--------------------- -- f | t | 0.30000000000000004 CREATE TABLE ts (a timestamp, b timestamptz); SET timezone = 'Asia/Tokyo'; INSERT INTO ts VALUES ('2026-10-22 09:00', '2026-10-22 09:00'); SET timezone = 'UTC'; SELECT a, b FROM ts; -- a | b -- ---------------------+------------------------ -- 2026-10-22 09:00:00 | 2026-10-22 00:00:00+00 ``` 浮動小数点型は 0.1 と 0.2 の和が 0.3 と一致しないため、金額や数量の合計を比較する列には `numeric`（または最小通貨単位の整数）を使います。`timestamp`（タイムゾーンなし）は入力した壁時計の値をそのまま持つので、接続のタイムゾーンが変わると同じ瞬間を指さなくなります。受注日時など同じ瞬間を共有する列には `timestamptz` を使い、入力のUTCオフセットまたは接続のTimeZoneを明確にします。保存した値は接続ごとのタイムゾーンで表示されますが、指す瞬間は同じです。地域の壁時計時刻そのものを表す値は、timestampの採用を検討します。 ### 物理設計の手順：EXPLAINによる実行計画の確認と索引・格納方式の選定 実際に流れるクエリを前提に、インデックス、パーティション、格納のパラメータを決めます。インデックスは検索を速くする代わりに更新を遅くし容量も食うので、「主キーと外部キー以外は、遅いクエリの実行計画を見てから張る」順序を守ります。PostgreSQL なら `EXPLAIN (ANALYZE, BUFFERS)` で 実行時間・推定行数と実行行数・読み込んだページ数を確認して、索引候補を決めます。Seq Scanは少量の表や大量行を読む処理では妥当なため、索引追加後の計画と時間も比較します。PostgreSQL は外部キーの参照元の列にインデックスを自動では作らないため、親の行を削除するときの子表の全件走査を避けたいなら自分で張ります。 ### 分析用のスキーマ設計：スタースキーマとスノーフレークスキーマ ここまでの正規化中心の設計は、注文や在庫を1件ずつ書き換える業務系（OLTP）のためのものです。集計や分析を主に行うデータウェアハウスでは、売上などの数値を持つファクト表の周りに、日付・商品・店舗といったディメンション表を並べるスタースキーマを使います。ディメンション表をさらに正規化して分けたものがスノーフレークスキーマで、容量は減る代わりに結合が増えます。業務系と分析系では正しい設計が逆向きになるため、同じ表で両方を満たそうとしないことが出発点です。違いは[OLTPとは？OLAPとの違いとAWSのOLAP基盤Redshift・Athenaの選び方](/tech/details/9237/)、ディメンショナルモデリングの進め方は[BEAM✲とは？7Wで進めるアジャイルなディメンショナルモデリングの手順とJEDUF](/tech/details/10099/)で扱っています。 ## スキーマ管理の実務：マイグレーションとダウンタイムなしの変更手順 設計より難しいのは、稼働中のデータベースのスキーマを変え続けることです。ALTER TABLE を流す前には、ロック待ち、表の書き換え、既存行の検証、新旧アプリケーションとの互換性を確認します。 ### マイグレーションでスキーマの変更履歴をリポジトリで管理する方法 スキーマ変更は SQL を手で流さず、マイグレーションファイルとしてコードと同じリポジトリで管理します。Python なら [SQLAlchemy 向けの Alembic](/tech/details/3417/)、Ruby on Rails なら Active Record、Java なら Flyway や Liquibase が定番です。ファイルが順に積み上がるので、どの環境がどの版のスキーマかを記録できます。あるべき最終形を1ファイルに書いて差分を自動で当てる宣言型の方式もあり、Rails 界隈では [Ridgepoleとは？Schemafileでスキーマを管理する使い方と落とし穴](/tech/details/5805/)が使われます。 ロールバック用の down スクリプトは、削除されるデータと新旧アプリケーションへの影響を確認してから使います。データが入った後に列を消す down を流せばデータが消えるからです。壊れた変更は前の版へ戻すのではなく、打ち消す新しい変更を前へ積む（前進のみのマイグレーション）ほうが安全です。 ### ALTER TABLEのロック待ちが後続のSELECTまで止める問題とlock\_timeout [PostgreSQL 18 の ALTER TABLE](https://www.postgresql.org/docs/18/sql-altertable.html) は「An ACCESS EXCLUSIVE lock is acquired unless explicitly noted」と定めており、列の追加のような一瞬で終わる変更でも、表への最も強いロックを取ろうとします。危ないのは、そのロックを待っている間です。PostgreSQL 18.6 で、表を読むだけの長いトランザクションを開いたまま列を追加し、別の接続から通常の SELECT を投げた結果が次です。 ``` -- 事前準備：検証専用DBで一度だけ実行してコミットする CREATE TABLE public.customers AS SELECT g AS id, 'u' || g || '@example.com' AS mail FROM generate_series(1, 200000) AS g; -- 各接続は同じDBに接続し、search_pathをpublicにする -- 接続1：表を読んだまま8秒コミットしない BEGIN; SELECT count(*) FROM customers; SELECT pg_sleep(8); COMMIT; -- 接続2：列の追加（ロック待ちで止まる） ALTER TABLE customers ADD COLUMN note2 text; -- 接続3：ただの SELECT が、ALTER の後ろに並んで止まる SET statement_timeout = '2s'; SELECT count(*) FROM customers; -- ERROR: canceling statement due to statement timeout -- pg_locks で見た待ち行列 -- mode | granted | query -- ---------------------+---------+---------------------------------- -- AccessShareLock | t | BEGIN; SELECT count(*) FROM ... -- AccessExclusiveLock | f | ALTER TABLE customers ADD COLUMN ... ``` ALTER が待ち行列の先頭にいる間、その後ろに来た読み取りもすべて待たされます。一瞬で終わるはずの列追加が、長いバッチ1本と重なるだけで、その表を使う処理をすべて止めてしまう仕組みです。対策は、ALTER の前に `lock_timeout` を付け、取れなければ諦めて再試行することです。 ``` -- 接続1の読み取りトランザクションを再度開始し、未コミットの間に別接続で実行する SET lock_timeout = '2s'; ALTER TABLE customers ADD COLUMN note text; -- ERROR: canceling statement due to lock timeout ``` [lock\_timeout](https://www.postgresql.org/docs/18/runtime-config-client.html) は1回のロック取得ごとの待ち時間の上限です。超過すると実行中の文がエラーになり、明示的なトランザクション内では、そのトランザクション全体のロールバックか、事前に設けたセーブポイントへのロールバックが必要になります。タイムアウトで ALTER が待ち行列から外れれば、その後ろで待っていた SELECT も動き出します。ALTER が原因で読み取りが待たされる時間は、lock\_timeout の値までに抑えられます。lock\_timeout が制限するのはロックの取得待ちだけで、取得後の処理時間やトランザクションを閉じるまでのロック保持は制限しない点に注意します。マイグレーションツールで流すときも、各ファイルの先頭でこの値を設定しておくのが安全です。 ### 列追加とNOT NULL化を書き換えなしで行う：既定値とNOT VALID ロック取得後の処理時間は、表全体の書き換えに加えて、制約検証のための全行走査が必要かどうかにも左右されます。ALTER TABLE の仕様では、揮発性でない既定値（定数など）を付けた列の追加は値をメタデータに持つだけで書き換えが要らず、`clock_timestamp()` のような揮発性の既定値では表とインデックスが書き換えられます。20万行の表で、ファイル番号（relfilenode）が変わるかを確かめました。 ``` SELECT pg_relation_filenode('customers'); -- 16426 ALTER TABLE customers ADD COLUMN status text NOT NULL DEFAULT 'active'; SELECT pg_relation_filenode('customers'); -- 16426（書き換えなし） ALTER TABLE customers ADD COLUMN created_at timestamptz DEFAULT clock_timestamp(); SELECT pg_relation_filenode('customers'); -- 16434（書き換えあり） ``` 既存の列を後から NOT NULL にする場合、`SET NOT NULL` は全行の検査が終わるまで ACCESS EXCLUSIVE ロックを持ち続けます。[PostgreSQL 18 のリリースノート](https://www.postgresql.org/docs/release/18.0/)で「Allow ALTER TABLE to set the NOT VALID attribute of NOT NULL constraints」が入り、制約の追加と既存行の検査を分けられるようになりました。以下は各文を自動コミットで実行する検証例です。制約追加・既存行の更新・検証を一つのトランザクションにまとめると、制約追加時のACCESS EXCLUSIVEロックが終了まで保持されるため、処理を分ける効果が失われます。 ``` ALTER TABLE customers ADD COLUMN email_address text; ALTER TABLE customers ADD CONSTRAINT email_address_nn NOT NULL email_address NOT VALID; -- 追加後のINSERT・UPDATEでemail_addressがNULLになる行は拒否される -- 既存のNULL行も、別の列だけを更新すると制約違反になる INSERT INTO customers (id, mail) VALUES (999999, 'x@example.com'); -- ERROR: null value in column "email_address" of relation "customers" -- violates not-null constraint UPDATE customers SET email_address = mail; -- 既存行を埋める ALTER TABLE customers VALIDATE CONSTRAINT email_address_nn; -- 既存行を検査 ``` インデックスの追加も同じ考え方で、`CREATE INDEX CONCURRENTLY` を使うと書き込みを止めずに作れます。ただしトランザクションの中では実行できず、`ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block` が返ります。マイグレーションを1ファイルずつトランザクションで包んで流す設定のツールでは、このファイルだけ包まずに流す指定が要ります。 ### expand/contract：列を追加してから削る二段階のスキーマ変更 アプリケーションを止めずに列名を変えるという要求は、単独の ALTER TABLE では満たせません。新旧どちらのアプリケーションも同時に動く瞬間があるためです。そこで変更を段階に割ります。新しい列を追加して両方に書き込み（expand）、読み取りを新しい列へ切り替え、旧い列への読み書きがすべて止まった版を出し切ってから旧い列を削ります（contract）。 ``` -- この例は前節とは独立。id・mail列を持ち、email_address列がない検証用customers表を使う -- リリース1（expand）：新しい列を足す。既定値なしの列追加は表の書き換えを伴わない ALTER TABLE customers ADD COLUMN email_address text; -- この版のアプリは mail と email_address の両方へ書き込む -- リリース2：既存行を新しい列へ写す（大きな表は id の範囲で分割して流す） UPDATE customers SET email_address = mail WHERE email_address IS NULL AND id BETWEEN 1 AND 10000; -- 全id範囲の移行と旧版アプリの停止を確認してから読み取りを切り替える -- 移行中はmailへの更新もemail_addressへ同期する -- この版のアプリは email_address だけを読む -- リリース3（contract）：mailへの読み書きをすべて停止した版の展開完了後に削る ALTER TABLE customers DROP COLUMN mail; ``` この手順は、スキーマ変更をアプリケーションのデプロイと同じリリースに束ねないという原則から導かれます。束ねると、どちらかのロールバックがもう一方を壊します。常に「旧いコードでも新しいスキーマで動く」状態を保つのが無停止運用の条件です。 MySQL の InnoDB には、表を書き換えずにメタデータだけを変える `ALGORITHM=INSTANT` があります。[MySQL 8.4 のオンライン DDL の資料](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html)では任意の位置への列追加が INSTANT で行え、列名の変更は 8.0.28 から INSTANT に対応しています。MySQL 8.4.8 で試すと、列の追加と改名は通り、インデックスの追加と型の縮小は拒否されました。 ``` ALTER TABLE sales.customers ADD COLUMN email_address VARCHAR(255), ALGORITHM=INSTANT; -- 成功 ALTER TABLE sales.customers RENAME COLUMN email_address TO email, ALGORITHM=INSTANT; -- 成功 ALTER TABLE sales.customers ADD INDEX mail_idx (mail), ALGORITHM=INSTANT; -- ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. ALTER TABLE sales.customers MODIFY mail VARCHAR(100), ALGORITHM=INPLACE; -- ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. ``` ALGORITHM を明示しておくと、想定より重い方式（COPY）へ黙って落ちる代わりにエラーで止まります。本番向けのマイグレーションでは、期待する方式を必ず書いておくと安全です。ただし INSTANT で改名できても、旧い列名を読むアプリケーションは壊れるので、expand/contract の段階分けは不要になりません。 ### 本番と定義のずれを検知する：pg\_dump –schema-onlyの差分とrestrict-key マイグレーションを通さない手作業の変更（障害対応で張ったインデックスなど）が入ると、リポジトリのスキーマと本番がずれていきます。PostgreSQL なら `pg_dump --schema-only` の出力を前回と比べるのが手軽な検知方法です。ただし PostgreSQL 18.6 の pg\_dump で2回ダンプを取ると、何も変えていなくても先頭と末尾の行が毎回変わります。 ``` pg_dump --schema-only --no-owner -n hr mydb > a.sql psql -c "CREATE INDEX hotfix_idx ON hr.employees (name);" mydb pg_dump --schema-only --no-owner -n hr mydb > b.sql diff a.sql b.sql -- 5c5 -- < \restrict oZYCQETfpZau... -- > \restrict pedglSEtj0ov... -- 90a91,97 -- > CREATE INDEX hotfix_idx ON hr.employees USING btree (name); ``` この `\restrict` 行は、ダンプ元サーバーの悪意あるスーパーユーザーが、復元する側の psql で任意のコードを実行させられる脆弱性（CVE-2025-8714）への対策として、[PostgreSQL 17.6 などのマイナーリリース](https://www.postgresql.org/docs/release/17.6/)で追加されたものです。キーは既定で毎回ランダムに作られるため、差分を取る用途では [pg\_dump の –restrict-key](https://www.postgresql.org/docs/18/app-pgdump.html) で比較専用のキーを指定するか、比較用コピーからこの2行を除外します。固定キーは一般利用には推奨されないため、復元用ダンプは加工せず保管し、同じpg\_dump版・オプションで比較します。 ## スキーマの厳密さをどう決めるか：固定スキーマを採る条件と見送る場面 データスキーマは厳しくするほど不正なデータを入口で止められますが、変更のたびにマイグレーションが要るぶん、変化の速い領域では足かせになります。どこを固定スキーマで守り、どこを柔らかくしてよいかの基準を示します。 ### 固定スキーマ（RDBの表定義）を採るべき条件：整合性が売上に直結する業務 受注・請求・在庫・会員の契約状態のように、1件の不整合が金額や出荷の誤りに直結するデータは、型・NOT NULL・外部キー・CHECK 制約まで表定義で固めます。アプリケーション側だけで検証すると、バッチや管理画面など別経路の実装で同じ検証が抜けるおそれがあるためです。複数のシステムが同じデータベースに書き込む構成では、この判断はさらに強くなります。 ### JSONBや柔軟スキーマに逃がしてよい場面と、後で困る典型パターン 商品ごとに項目が違う属性情報、外部 API の応答の保管、画面設定のように項目の増減が頻繁で集計の対象にならないデータは、PostgreSQL の JSONB 列などに逃がしても困りません。避けたいのは、検索条件や集計に使う項目まで JSON の中に入れる設計です。JSONB内の項目には通常列と同じ型宣言や外部キーを直接設定できませんが、CHECK制約などによる検証は可能です。検証しなければ、項目名の綴りの揺れが集計漏れにつながります。「検索・集計・結合に使うなら列にする、表示するだけなら JSON でよい」と線を引けば、多くの場合で迷いません。 長年の継ぎ足しでスキーマが崩れている、あるいは別の RDBMS への移行を機に設計から見直したい段階では、外部の手を借りる判断もありえます。一創の[データベース設計・移行支援](https://www.issoh.co.jp/service/system/database/)では、現行スキーマの棚卸しから正規化の見直し、無停止でのマイグレーション計画までを支援しています。 ## よくある質問 ### データスキーマとデータベースのスキーマは同じ意味ですか？ 重なりますが、範囲が違います。データスキーマはデータの構造定義全般を指し、データベースの表定義のほか、JSON Schema や Avro で書いた API やイベントの形も含みます。データベースのスキーマはそのうちデータベースの中の構造定義、または `CREATE SCHEMA` で作る名前空間を指す言葉です。 ### スキーマとテーブルの違いは何ですか？ テーブルは行と列でデータを実際に保持する実体で、スキーマはテーブルなどのオブジェクトをまとめる名前空間、または構造全体の定義です。スキーマ自体はデータを持ちません。PostgreSQL では `スキーマ名.テーブル名` の関係になり、MySQL ではスキーマがデータベースと同義なので `データベース名.テーブル名` になります。 ### 外部スキーマはビューのことですか？ リレーショナルデータベースでは、外部スキーマの実体はビューです。ANSI/SPARC の三層スキーマはリレーショナルモデルに限らない一般モデルのため抽象的な名前になっており、ネットワーク型データベースではサブスキーマが外部スキーマに当たります。試験では、ビューやサブスキーマを「概念スキーマに相当する」とする選択肢が誤りとして出ます。 ### 概念スキーマと概念設計は同じものですか？ 厳密には違います。三層スキーマの概念スキーマは、ER 図に加えて正規化された表定義まで含む論理モデル全体を指します。設計工程で言う概念設計は ER 図の作成までで、正規化と表定義は論理設計の担当とされるのが一般的です。概念スキーマは概念設計と論理設計を合わせた成果物、と対応づけると混乱しません。 ### MySQL のスキーマとデータベースは違うものですか？ MySQL では同じものです。リファレンスマニュアルが `CREATE SCHEMA` を `CREATE DATABASE` の同義語と定義しており、MySQL 8.4.8 で同じ名前のスキーマを2回作ると「Can’t create database ‘a’; database exists」と返ります。PostgreSQL や Oracle の感覚でデータベースの下にスキーマを作ろうとすると、実際にはデータベースが増えます。 ## 関連記事 - [データベースとは？種類・DBMS・RDBとNoSQLの選び方を実装目線で解説](/tech/details/13012/) - [ER図とは？書き方・エンティティやリレーション・カーディナリティを実装目線で解説](/tech/details/13484/) - [データベース正規化とは？第1〜第3正規形の手順と非正規化の判断を解説](/tech/details/16281/) - [PostgreSQLのスキーマ運用｜search\_pathの解決順とpublic権限・テナント分割の判断基準](/tech/details/16980/) - [Ridgepoleとは？Schemafileでスキーマを管理する使い方と落とし穴](/tech/details/5805/) --- 出典: [データスキーマとは？データベースのスキーマと三層スキーマ（外部・概念・内部）の違い・設計と管理の実務]()（株式会社一創）