データ型

文字型

公式

textの内部表現(1)

'AAAA' (4bytes)

| 0x0b | 0x41 | 0x41 | 0x41 | 0x41 |

ヘッダバイト0x0bの由来
- ヘッダ込で5バイト(0b00000101)
- これを1ビット左シフトして0b00000001をORする
- 0b00001011 = 0x0b

char(8)の内部表現

'AAAA' (char(8) 8bytes)

| 0x13 | 0x41 | 0x41 | 0x41 | 0x41 | 0x20 | 0x20 | 0x20 | 0x20 |

ヘッダバイト0x13の由来
- ヘッダ込で9バイト0b00001001
- これを1ビット左シフトして0b00000001をORする
- 0b00010011 = 0x13

textの内部表現(2)

'LL....LL' (text 130bytes)

| 0x18 | 0x02 | 0x00 | 0x00 | 0x4c | 0x4c |...| 0x4c |

ヘッダ| 0x18 | 0x02 | 0x00 | 0x00 |
リトルエンディアンなので0x0218
由来
- 130 + 4バイト = 0b10000110
- これを左2ビットシフト
- 0b1000011000 = 0x0218

規則

文字列のバイト長が126byte以下: ヘッダは1バイト
- dec 126 = 0b01111110
- 左1ビットシフトして1をOR
- 0b11111101 = 0xfd
文字列のバイト長が127byte以上: ヘッダは4バイト
- dec 127 = 0b01111111
- 左2ビットシフト
- 0b0111111100 = 0x01fc
- リトルエンディアンだから 0xfc 0x01 0x00 0x00
【予想】ヘッダバイトの1バイトめが0b00で終わってたらヘッダを4バイトとして解釈するのかな？
テーブル内に格納される値が2kBを超えるとTOAST領域に分割

SQL標準との対応

character(n), character varying(n)
- SQL標準仕様
- char(n), varchar(n)では、文字数が超過するとエラー
  - ただし、超過部分が全部スペースならば切り詰め
    - SQL標準仕様
  - 明示的にキャストしたらエラー出ない
    - SQL標準仕様
- char -> character(1)
- varchar -> 長さ制限なし(1GBまで)
  - PostgreSQL拡張仕様

postgres=# CREATE TABLE tbl (col char);
CREATE TABLE

postgres=# \d tbl
                   Table "public.tbl"
 Column |     Type     | Collation | Nullable | Default 
--------+--------------+-----------+----------+---------
 col    | character(1) |           |          |

text
- SQL標準ではない
明示的にCHARにキャストすると、末尾スペースが無視される話
Cロケールにおいて、は\nよりもgreater

postgres=# SELECT ' ' collate "C" > E'\n';
 ?column? 
----------
 t
(1 row)

キャストすると

postgres=# SELECT 'a ' collate "C" > E'a\n';
 ?column? 
----------
 t
(1 row)

postgres=# SELECT 'a '::CHAR(2) collate "C" > E'a\n'::CHAR(2);
 ?column? 
----------
 f
(1 row)

これと同じ意味になっている

postgres=# SELECT 'a' collate "C" > E'a\n';
 ?column? 
----------
 f
(1 row)

文字数とバイト(オクテット)数は違うぞという話
- varchar(n)とかのnは文字数

postgres=# SELECT char_length('あ'),octet_length('あ');
 char_length | octet_length 
-------------+--------------
           1 |            3
(1 row)

パフォーマンスの話
- varchar(n) vs text
  - 前者は長さチェックぶんCPUクロックがかさむ
- char(n) vs varchar(n),text
  - 他RDBMSのように特別有利だということはない
  - n文字未満の文字列を格納すると、varcharやtextとは異なりスペースパディングぶんストレージがかさむ

Column: 内部的に使用される文字型

システムカタログ用の特殊な文字型がある
- "char"型
  - 1バイト
- name型
  - データベースオブジェクトの識別子の格納用

Column: char型に対する文字列操作の注意点

postgres=# CREATE TABLE example (c char(8), v varchar(8));
CREATE TABLE

postgres=# INSERT INTO example VALUES('abcd', 'abcd');
INSERT 0 1

postgres=# TABLE example;
    c     |  v   
----------+------
 abcd     | abcd
(1 row)

postgres=# SELECT concat(c,v), c||v FROM example;
    concat    | ?column? 
--------------+----------
 abcd    abcd | abcdabcd
(1 row)

concat関数と||演算子とでスペースパディングの扱いが違ったりする

数値データ型

整数型の内部表現

LEで突っ込むだけ

realの内部表現

例: 100
IEEE754仕様(単精度浮動小数点数)
- 符号部: 1bit
- 指数部: 8bit (127の下駄履き)
- 仮数部: 23bit (ケチ表現)
100の正規形: 0b01100100 -> 0xb(1).100100 << 6
IEEE754仕様による表現
- 符号部: 0 (+)
- 指数部: dec 6 + 127 = dec 133 = 0b10000101
- 仮数部: 0b1001000 0b00000000 0b00000000
- 全部くっつけると
  - 0b01000010 0b11001000 0b00000000 0b00000000
  - 0x42 0xc8 0x00 0x00
  - リトルエンディアンなので
  - 0x00 0x00 0xc8 0x42

numericの内部表現

公式

100の表現

0x0b 0x00 0x80 0x64 0x00

ヘッダのロジックはvarchar(n)とかとおなじ
- ヘッダ込のバイト数dec 5 = 0b101を1ビット左シフト OR 1

どれつかう

小数含まない: 整数 or クソデカならnumeric(decimal)
小数含む
- 誤差を含んでもよい: real/double precision or クソデカならnumeric(decimal)
- 誤差を含んではいけない: numeric(decimal)

【補】money型

Martin FowlerがPofEAAで欲しがってたやつ
通貨込の数値型
公式
IEEE754の浮動小数点数からmoneyにキャストするときはnumericを介する

postgres=# SELECT '12.34'::float8::money;
ERROR:  cannot cast type double precision to money
LINE 1: SELECT '12.34'::float8::money;
                              ^
postgres=# SELECT '12.34'::float8::numeric::money;
 money  
--------
 $12.34
(1 row)

しかし丸め誤差があるためこれは良くない
こうすべき

postgres=# SELECT '52093.89'::money;
   money    
------------
 $52,093.89
(1 row)

IEEE754浮動小数点数にキャストするときはnumericを介する

postgres=# SELECT '52093.89'::money::float8;
ERROR:  cannot cast type money to double precision
LINE 1: SELECT '52093.89'::money::float8;
                                ^
postgres=# SELECT '52093.89'::money::numeric::float8;
  float8  
----------
 52093.89
(1 row)

money -> numericまでは情報の欠落はおこらない

日付/時刻データ型

公式

dateの内部表現

2000-01-01からの経過日数(4バイト)をLEで格納
下限: 4713 BC
- 由来不明
上限: 5874897 AD
- 2^31 = 2147483648日
- 365で割ってだいたい5883516年

timestamp型の内部表現

2000-01-01 00:00:00からの経過マイクロ秒数(8バイト)をLE
下限: 4713 BC
- 由来不明
上限: 294276 AD
- 2^63 = 9.223372036854776e+18くらい
- 365 * 24 * 60 * 60 * 1000000 で割る
- だいたい292471年

interval型の内部表現

8バイト: 1日内のマイクロ秒単位の時間間隔
- まる一日はdec 24*60*60*1000000 = 0x141dd76000(5バイト)
- 5バイトは中途半端だから8バイトなんでしょうねきっと
4バイト: 日単位の時間間隔
- 1バイトで十分なのに4も使うのはアライメントのためか
4バイト: 月単位の時間間隔
-178000000~178000000年
- 2^31ヶ月 / 12 = 178956970年くらい

タイムゾーン

型	バイト数
timestamp without time zone	8
timestamp with time zone	8
date	4
time without time zone	8
time with time zone	12
interval	16

time zoneつきのtimestamp, timeはUTC
タイムゾーンを含める場合はtime with time zoneではなくtimestamp with time zone推奨
- timeは時刻しか格納せず、日付情報をもたないのでサマータイム対応できない
  - 夏場だけ標準時を1時間とか進めるやつ
- ストレージ効率

Column: アンチパターン: 文字型で日時を管理する

ストレージがかさむ
日付・時刻データ型の演算ができない

バイナリ列データ型

bytea型
- ~1GB
- 列自体にデータが格納される

postgres=# SELECT '\xDEADBEEF';
  ?column?  
------------
 \xDEADBEEF
(1 row)

large object
- ~4TB
- oidで外部参照する
- pg_largeobjectシステムカタログで実体を管理している
  - 2kBで分割
- OSのファイルアクセスに似たAPIでハンドリングする
どっちつかう
- ~100kB: bytea
  - あまり大きいと内部でのデータコピー量が多くなる
- それ以上: large object

Column: JSON型とJSONB型

公式
JSONは文字列として格納
JSONBはバイナリ列として格納
- json関数・演算子を高速に処理するため
numberのE記法(指数)が展開されるかされないか、とかが違ってくる
- JSON: 文字列をそのまま格納するのでそのまま
- JSONB: バイナリ列として格納するので指数が展開される

postgres=# SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

Column: 型名のエイリアス

int2, int4, int8 (smallint, integer, bigint)
decimal(numeric)
float (double precision)
- 単精度はreal

制約

主キー

暗黙のB-treeインデックスが生える

一意性制約とNOT NULL制約

unique: 暗黙のB-treeインデックスが生える

外部キー制約

PostgreSQLにおける注意点

実行計画
- FK側には暗黙のB-treeインデックスは生えない
- FK列を条件とするクエリを発行すると、そのテーブルをフルスキャンしてしまう
- 必要に応じてFK側にもインデックスを設定しよう
型を一致させること

検査制約

列制約,テーブル制約

CREATE TABLE book (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER CONSTRAINT positive CHECK (price >= 0),
  discount INTEGER,
  CONSTRAINT price_discount CHECK (price > discount)
);

postgres=# \d book
                 Table "public.book"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 id       | integer |           | not null | 
 name     | text    |           |          | 
 price    | integer |           |          | 
 discount | integer |           |          | 
Indexes:
    "book_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "positive" CHECK (price >= 0)
    "price_discount" CHECK (price > discount)

列制約
- 1列で完結するやつ
- 上記の例ではpositive
テーブル制約
- 複数列またぐやつ
- 上記の例ではprice_discount
- 特定の列にぶら下がるわけではないので、CREATE TABLE文の列定義のリストとしてCHECK(条件式)を記述する

Column: 検査制約の適用順序

9.4以前: 不定
9.5以降: 列制約およびテーブル制約の名称順
上記の例の2制約を同時に破ってみる

postgres=# INSERT INTO book (id, price, discount) VALUES (1, -1, 0);
ERROR:  new row for relation "book" violates check constraint "positive"
DETAIL:  Failing row contains (1, null, -1, 0).

positiveのほうが辞書順において若いのでこちらで落ちる
どれか1つでも違反すると失敗
- 以降は評価されないっぽい？
テーブル制約だけ破ってみる

postgres=# INSERT INTO book (id, price, discount) VALUES (1, 0, 0);
ERROR:  new row for relation "book" violates check constraint "price_discount"
DETAIL:  Failing row contains (1, null, 0, 0).

PostgreSQL固有のテーブル設計

TOASTを意識したテーブル設計

公式
TOAST: The Oversized-Attribute Storage Technique
- 過大属性格納技法
格納方法(4種類)

	圧縮する	圧縮しない
行外格納する	EXTENDED	EXTERNAL
	(CHAR,VARCHAR,TEXT,BYTEAとか)	(TEXT, BYTEAの高速化に)
行外格納しない	MAIN	PLAIN
	(NUMERIC)	(数値、日時等)

\d+で確認

postgres=# CREATE TABLE tbl (id int, name text);
CREATE TABLE


postgres=# \d+ tbl
                                    Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              | 
 name   | text    |           |          |         | extended |              | 
Access method: heap

ALTER TABLE文で変更可能
name textカラムを(extendedから)externalに変更してみる
- 圧縮しないので…
  - 部分文字列操作は高速化
  - ストレージは当然かさむ

postgres=# ALTER TABLE tbl ALTER COLUMN name SET STORAGE external;
ALTER TABLE


postgres=# \d+ tbl
                                    Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              | 
 name   | text    |           |          |         | external |              | 
Access method: heap

列数が増えすぎて1行の格納サイズがTOAST対象になる場合、リレーションを適切に垂直分割することでTOAST対象外にできる

結合を意識したテーブル設計

まじめに正規化するとJOIN増えちゃうね、という話
非正規化して結合数を減らすことも検討する

Column: 遺伝的問い合わせ最適化

公式
GEQO: GEnetic Query Optimization
実行計画を評価するにあたり、クエリ内で扱うテーブル数が閾値(デフォルト12)を超えると総当りからGEQOに切り替わる
GEQOの特徴
- 作成される実行計画の精度は総当りによるものと比べて劣ることがある
- 実行計画の作成時間を短縮して、総実行時間の短縮をはかる
  - 最適なクエリの探索は巡回セールスマン問題
  - なので総当りでは限度がある

postgres=# SELECT name,setting,context FROM pg_settings WHERE name SIMILAR TO 'geqo%';
ngs WHERE name SIMILAR TO 'geqo%';
        name         | setting | context 
---------------------+---------+---------
 geqo                | on      | user
 geqo_effort         | 5       | user
 geqo_generations    | 0       | user
 geqo_pool_size      | 0       | user
 geqo_seed           | 0       | user
 geqo_selection_bias | 2       | user
 geqo_threshold      | 12      | user
(7 rows)

ビューの活用

ビュー

公式
ビューを指定することによって実行計画が劣化することは基本的にない
PostgreSQL 9.3から、単純なビューならば更新(CUD)を行える
「単純なビュー」ってなに
- FROMリストがちょうど1エントリ
  - JOINとかしない
- ビューの1行が実表の1行と対応している
  - トップレベルにWITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET句を含めない
  - トップレベルに集合演算を含めない
  - トップレベルにSELECTリストに集約関数、ウィンドウ関数、集合を返す関数を含めない

マテリアライズドビュー

有効なケース

即応性が必要ないとき
- 元となった実表が更新されてもマテリアライズドビューの結果は変わらない
- それが受け入れられるようなケースで

マテリアライズドビューの更新

公式/CREATE MATERIALIZED VIEW
公式/REFRESH MATERIALIZED VIEW
更新 = 破棄+生成
- 差分更新とかはない
- 初期生成と同程度のコストがかかる
ロック
- 9.3以前、更新中は他セッションからのSELECTがロックアウトされる
- 9.4以降、CONCURRENTLYオプションをつけることでSELECT可能にできる
  - リソースかさむ
  - 更新遅くなる

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch5 テーブル設計

出典:

データ型

文字型

textの内部表現(1)

char(8)の内部表現

textの内部表現(2)

規則

SQL標準との対応

Column: 内部的に使用される文字型

Column: char型に対する文字列操作の注意点

数値データ型

整数型の内部表現

realの内部表現

numericの内部表現

どれつかう

【補】money型

日付/時刻データ型

dateの内部表現

timestamp型の内部表現

interval型の内部表現

タイムゾーン

Column: アンチパターン: 文字型で日時を管理する

バイナリ列データ型

Column: JSON型とJSONB型

Column: 型名のエイリアス

制約

主キー

一意性制約とNOT NULL制約

外部キー制約

PostgreSQLにおける注意点

検査制約

列制約,テーブル制約

Column: 検査制約の適用順序

PostgreSQL固有のテーブル設計

TOASTを意識したテーブル設計

結合を意識したテーブル設計

Column: 遺伝的問い合わせ最適化

ビューの活用

ビュー

マテリアライズドビュー

有効なケース

マテリアライズドビューの更新