🐷

新卒3年目DBエンジニアが教えるDB基礎講座（Postgres編第3回：PostgreSQLのテーブル設計と物理設計）

2023/05/12に公開

PostgreSQL

入門

アーキテクチャー

tech

今回の内容

今回はPostgreSQLを動かすうえでは欠かせない以下5点を説明します。
1.テーブル設計
2.物理設計

今回から参考にさせていただいたサイトや分かりやすくまとまっているサイトを下に書くようにします。私の内容で分かりにくいorもう少し知りたい場合は参考サイトをご確認ください。

過去記事
第1回：https://zenn.dev/articles/a70f06f4652fe9/edit
第2回：https://zenn.dev/articles/add301d9c57683/edit

1.PostgreSQLのテーブル設計

PotsgreSQLというかすべてのDBではもちろんテーブルが存在し、この中にレコードとしてデータを保持しています。今回はデータ型や制約に関することを見ていきましょう

a.型

文字列
文字列のにはおおよそvarchar/char/textが存在します。
違いは(n)を使用するかどうかで変わります。
(n)<varchar(10)など>をつけると上限に満たさなかった場合でも空白を与えて格納しますので空白を含めた結果が出力されます。
正直charを使う場合はあまり見たことがなく実務では格納時のサイズ上限チェックが行われるvarcharを使う場合が多いでしょう。

数値型
数値にはおおよそsmallint/integer/bigint/double型が存在します。
それぞれ取れる値が異なりますが、おおよそintegerでいいと思います。
doubleは小数点が含まれる場合に使用します。
あとOracleで言うとこのSequenceがserialとして実装されています。

日時とタイムゾーン
time/timestampはUTCで表示するためJSTなどに直す場合はサーバ側で設定する必要があります。
タイムゾーンを含める場合はtimestamp with time zoneを選ぶとサマータイムの考慮ができていることになります。

b.制約

制約はおおよそいかに分類されます。
・NotNull
NotNull制約は、テーブルのカラムにNULL値を許可しないことを示します。つまり、そのカラムには必ず値が入力される必要があります
・Primary
Primary制約は、主キーとして指定されたカラムに適用されます。主キーは、テーブルの各行を一意に識別するためのカラムです。また、作成すると勝手に索引が作られます。
・外部キー
外部キー制約は、あるテーブルのカラムが、別のテーブルの主キーを参照することを示します。これにより、関連するテーブル間でデータの整合性が保たれます。外部キー制約が適用されたカラムは、参照先テーブルの主キーに存在する値のみを持つことができます。
イメージできない方は下記記事参照

・CHECK
CHECK制約は、テーブルのカラムに対して特定の条件を満たす必要があることを示します。これにより、データの品質を維持し、意味のない値や誤った値が入力されることを防ぎます。

実際にCREATE TABLEするときは下記のようになる

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
age INTEGER,
country_id INTEGER,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
CONSTRAINT check_age CHECK (age >= 0),
FOREIGN KEY (country_id) REFERENCES countries (id) ON DELETE CASCADE
);

外部キーですが、この場合だとcountry_id: 外部キー (Foreign Key) 制約が適用され、countriesテーブルのidカラムを参照しています。これにより、usersテーブルのcountry_idカラムには、countriesテーブルのidカラムに存在する値しか入力できません。
削除する際には先にusersのほうを削除しなければいけません。countryのほうを消すことはできません。ただし、ON DELETE CASCADEを使うと親が消されて参照先も消されます。

c.TOAST

PostgreSQLは、大容量のデータを効率的に扱うために、TOAST (The Oversized-Attribute Storage Technique) という機能を提供しています。
TOASTは、データの圧縮や外部ストレージへの格納を行うことで、大容量のデータを効率的に扱います。
大容量のデータを扱う場合、適切なデータ型を選択することが重要です。たとえば、テキストやバイナリデータは、textやbyteaデータ型を利用することで、TOASTが適用されます。

デフォルトの8KBの閾値が適切でない場合は、ALTER TABLEコマンドでTOASTテーブルの閾値を変更することができます。ただし、閾値を下げすぎると、逆にパフォーマンスが低下する場合がありますので注意が必要です。

TOASTでは、データの圧縮も行われますが、圧縮率や圧縮速度は、設定によって変わります。データの性質に応じて、圧縮設定を最適化することで、パフォーマンスを向上させることができます。

ALTER TABLE テーブル名　ALTER 列名 SET STORAGE 格納方式
で変更が可能です。
各型によって使用尾する指定方式が異なります。
しかし、実際にはTOASTの処理は効率的なので別にないですが、1行の格納サイズでTOASTになる場合は垂直分割を進めます。

d.ビュー

ビューには実態がなく、仮想的なテーブルにすぎません。
そのためサイズの取得をしても0で返されます。
1テーブルから構成されている場合のみINSERT/UPDATEが可能です。

マテリアライズドビューとは複雑なクエリや集計処理を高速に実行することができます。マテリアライズドビューは、通常のビューとは異なり、実際にデータが格納されているため、クエリの実行時間を短縮することができます。

作成するには下記コマンドを使います。
CREATE MATERIALIZED VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

更新は下記のとおりです。
REFRESH MATERIALIZED VIEW view_name;

マテリアライズドビューは、レポート作成やダッシュボードの構築など、データ分析や集計に頻繁に使用されるクエリのパフォーマンスを向上させるために役立ちます。ただし、データの更新やメンテナンスが必要な点に注意してください。

5.パーティショニング

そもそもパーティションとは
→大きなテーブルを小さなサブテーブル（パーティション）に分割するプロセスです。これにより、データの管理が容易になり、クエリパフォーマンスが向上することがあります。PostgreSQLでは、主に2つのパーティショニング手法が提供されています

Range Partitioning
Rangeパーティショニングでは、指定された範囲の値に基づいてテーブルを分割します。たとえば、日付範囲に基づいて売上データを分割することができます。Rangeパーティショニングの手順は以下の通りです。

a.親テーブルの作成
CREATE TABLE sales (
id SERIAL NOT NULL,
sale_date DATE NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL
) PARTITION BY RANGE (sale_date);

b.子テーブルの作成
CREATE TABLE sales_2023_01 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE sales_2023_02 PARTITION OF sales
FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

List Partitioning
Listパーティショニングでは、個別の値のリストに基づいてテーブルを分割します。たとえば、地域ごとにデータを分割することができます。Listパーティショニングの手順は以下の通りです。

親テーブルの作成
CREATE TABLE customers (
id SERIAL NOT NULL,
name VARCHAR(255) NOT NULL,
country_code CHAR(2) NOT NULL
) PARTITION BY LIST (country_code);

子テーブルの作成
CREATE TABLE customers_us PARTITION OF customers
FOR VALUES IN ('US');

CREATE TABLE customers_jp PARTITION OF customers
FOR VALUES IN ('JP');

パーティションの基礎がわかったところで設計する際の注意点を記載します。
i.適切なパーティショニングキーを選択
パーティションのパフォーマンスや管理の容易さは、選択したパーティショニングキーに大きく依存します。パーティショニングキーは、クエリの検索条件やアプリケーションの要件に基づいて選択する必要があります。

ii.パーティションの数とサイズを適切に設計
パーティションの数やサイズを適切に設計することで、クエリのパフォーマンスやデータ管理が向上します。ただし、あまりに多くのパーティションを作成すると、管理が複雑になる場合があります。

iii.パーティションのメンテナンス
データの増加や変化に対応するために、新しいパーティションの作成や不要になったパーティションの削除など、定期的なメンテナンスが必要です。メンテナンスプロセスを効率化するために、スクリプトや自動化ツールを使用することが推奨されます。

iv.制約の適用
制約を適用して、データの整合性を維持することが重要です。パーティションテーブルでは、CHECK制約を使用して、データが適切なパーティションに格納されるように制御できます。

2.物理設計

続いてはPostgreSQLの物理設計の話です。
物理設計というと難しく聞こえるかもですが、ファイルの意味やその実態に関してお勉強していきましょう

テーブルファイルと索引ファイル
基本的には8192Byteでページという単位で構成されていて固定長領域で配置されています。
最大1GBまで拡張され超える場合にはセグメントとして管理されます。
ページ内では主に以下の要素で構成されています
・ヘッダ(管理情報などを持っている)
・アイテムデータ(アイテムのポインタ)
・アイテム(実体。末尾から埋められる)
・空き領域(FILFACTORの設定で決めることができる。)

索引ファイルも基本的にテーブルファイルと同じでツリー構造になっています。

WALとアーカイブファイル
WAL=OracleではRedoLog/SQL ServerではTransactionLOGに相当し、ここに書き込まれたデータはファイルが壊れない限り不変でありPITRに利用されます。
WALは増え続けることはなくアーカイブ化された後はチェックポイントで再利用もしくは削除されます。
アーカイブファイルは過去のWALの履歴であり要件にもよりますが、最新のバックアップを取ったら意味のないファイルとなるため手動削除が必要です。
ディスクの上限まで行くとWALのアーカイブ化に失敗しPostgreSQLは停止してしまいます。
富士通さんの記事がバックアップと絡めてていいと思います。

HOT
HOTですが、これはHeap Only Tupleの略
データベースのパフォーマンスを向上させるための仕組みです。
具体的には、同じページ内での行の更新を効率的に行うことができます。
行が更新されると、新しい行が作成され、旧い行は不活動状態になります。
これにより頻繁に更新されるデータベースでは時間の経過とともに不活動な行が増え、スペースの無駄につながる可能性があります。
HOTはこれを解消します。行が同じページ内で更新されると新しい行が旧い行のスペースを再利用します。ディスクスペースの無駄が抑えられ、インデックスの大幅な変更も避けられます。

FILLFACTOR
テーブルやインデックスを作成する際に指定できるパラメータで、空きスペースをどれだけ残しておくかを制御します。
デフォルト値は100（％）これはページを可能な限り詰め込むことを意味します。
しかし、行が頻繁に更新されるテーブルでは、FILLFACTORを少し低く設定することで、HOTが有効に機能する余地を作ることができます。
例えば、FILLFACTORを80に設定すると、ページの20％が更新のための空きスペースとして確保されます。