gihyo.jp
データ型
文字型
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領域に分割
character(n)
, character varying(n)
- SQL標準仕様
- char(n), varchar(n)では、文字数が超過するとエラー
- ただし、超過部分が全部スペースならば切り詰め
- 明示的にキャストしたらエラー出ない
- char -> character(1)
- varchar -> 長さ制限なし(1GBまで)
postgres=# CREATE TABLE tbl (col char);
CREATE TABLE
postgres=# \d tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default
--------+--------------+-----------+----------+---------
col | character(1) | | |
text
- 明示的に
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)
postgres=# SELECT char_length('あ'),octet_length('あ');
char_length | octet_length
-------------+--------------
1 | 3
(1 row)
- パフォーマンスの話
varchar(n)
vs text
char(n)
vs varchar(n)
,text
- 他RDBMSのように特別有利だということはない
- n文字未満の文字列を格納すると、
varchar
やtext
とは異なりスペースパディングぶんストレージがかさむ
Column: 内部的に使用される文字型
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
関数と||
演算子とでスペースパディングの扱いが違ったりする
数値データ型
整数型の内部表現
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
- 231 = 2147483648日
- 365で割ってだいたい5883516年
timestamp型の内部表現
2000-01-01 00:00:00
からの経過マイクロ秒数(8バイト)をLE
- 下限: 4713 BC
- 上限: 294276 AD
- 263 = 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年
- 231ヶ月 / 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は時刻しか格納せず、日付情報をもたないのでサマータイム対応できない
- ストレージ効率
Column: アンチパターン: 文字型で日時を管理する
- ストレージがかさむ
- 日付・時刻データ型の演算ができない
バイナリ列データ型
postgres=# SELECT '\xDEADBEEF';
?column?
------------
\xDEADBEEF
(1 row)
- large object
- ~4TB
- oidで外部参照する
pg_largeobject
システムカタログで実体を管理している
- OSのファイルアクセスに似たAPIでハンドリングする
- どっちつかう
- ~100kB: bytea
- それ以上: large object
Column: JSON型とJSONB型
- 公式
- JSONは文字列として格納
- JSONBはバイナリ列として格納
- 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)
制約
主キー
一意性制約とNOT NULL制約
- unique: 暗黙のB-treeインデックスが生える
外部キー制約
- 実行計画
- 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)
- 列制約
- テーブル制約
- 複数列またぐやつ
- 上記の例では
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).
TOASTを意識したテーブル設計
- 公式
- TOAST: The Oversized-Attribute Storage Technique
- 格納方法(4種類)
|
圧縮する |
圧縮しない |
行外格納する |
EXTENDED |
EXTERNAL |
|
(CHAR,VARCHAR,TEXT,BYTEAとか) |
(TEXT, BYTEAの高速化に) |
行外格納しない |
MAIN |
PLAIN |
|
(NUMERIC) |
(数値、日時等) |
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エントリ
- ビューの1行が実表の1行と対応している
- トップレベルにWITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET句を含めない
- トップレベルに集合演算を含めない
- トップレベルにSELECTリストに集約関数、ウィンドウ関数、集合を返す関数を含めない
有効なケース
- 即応性が必要ないとき
- 元となった実表が更新されてもマテリアライズドビューの結果は変わらない
- それが受け入れられるようなケースで