データ配置のポイント
base領域
/docker:2d5eccf8738e:/var/lib/postgresql/data $ ls -lA base
total 12
drwx------ 2 postgres postgres 4096 2020-02-08 01:17 1
drwx------ 2 postgres postgres 4096 2020-02-08 01:17 13407
drwx------ 2 postgres postgres 4096 2020-02-08 01:17 13408
-
テーブルファイルやインデックスファイルが格納される
- FSMやVMも
- 行挿入により領域サイズ増加する
- そのことを想定してサイズを検討する
WAL領域
/docker:2d5eccf8738e:/var/lib/postgresql/data $ ls -lA pg_wal
total 16388
-rw------- 1 postgres postgres 16777216 2020-02-08 01:22 000000010000000000000001
drwx------ 2 postgres postgres 4096 2020-02-08 01:17 archive_status
- ローテートするので領域全体のサイズは増加しない
アーカイブ領域
- archive_commandで指定されたコピー先ディレクトリ
postgres=# SELECT name,setting,context,boot_val FROM pg_settings WHERE name IN ('archive_command', 'archive_mode');
name | setting | context | boot_val
-----------------+------------+------------+----------
archive_command | (disabled) | sighup |
archive_mode | off | postmaster | off
(2 rows)
-
アーカイブはデフォルト無効、コマンドも未定義
- なのでアーカイブ領域もない
-
アーカイブファイルは循環しない
-
どれくらいとっておくかは運用次第
- 最新のものだけ取っておいたり
- 最新のベースバックアップ以前の時点へロールフォワードするためには古いのも必要
-
-
ディスクフルになると
- アーカイブへのコピーに失敗する
- WALファイルがWAL領域に残り続ける
- WAL領域がディスクフルになる
- PANIC
テーブル空間とテーブルパーティショニング
- CREATE TABLESPACE`でつくるやつ
- 公式
- symlink必須
- 速い/遅いけど安いディスクに置くと有用
- RAID0とか使う場合はPostgreSQLのテーブル空間ではなくRAID0側でI/O分散しよう
テーブルパーティショニングとの組み合わせ
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
CREATE TABLE measurement_y2007m12 PARTITION OF measurement
FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
TABLESPACE fasttablespace;
CREATE TABLE measurement_y2008m01 PARTITION OF measurement
FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
WITH (parallel_workers = 4)
TABLESPACE fasttablespace;
-
アクセス頻度の高いパーティションのみ高速なディスク(上のテーブル空間)に置いたりできる
- PostgreSQLバージョン11からHash Partitioningが利用可能に
Column: 別のテーブル空間へのデータベースオブジェクトの一括移動
CREATE TABLE
時にテーブルスペース指定するほか、既存のテーブルをALTER TABLE
文で別テーブルスペースへ移動できる- 公式/ALTER TABLE
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ]
- デフォルトのテーブルスペース
pg_default
にtbl
テーブル作る
postgres=# \d tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
col | integer | | |
ALTER TABLE
文でテーブルスペース変更my_spc
作っとく
postgres=# SELECT * FROM pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16384 | my_spc | 10 | |
(3 rows)
-
pg_default
テーブルスペース中の全テーブルをmy_spc
へ移動- 名前にクォート不要
postgres=# ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE my_spc;
ALTER TABLE
- 変更確認
postgres=# \d tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
col | integer | | |
Tablespace: "my_spc"
- 同じものが
ALTER INDEX
,ALTER MATERIALIZED VIEW
にもある - 公式/ALTER INDEX
- 公式/ALTER MATERIALIZED VIEW
性能を踏まえたインデックス定義
- インデックス使わないと検索時間は
O(n)
な傾向がある
インデックスの概念
- さくいん
更新に対するインデックスの影響
-
いらないインデックスは更新処理の性能劣化のもと
- 行更新・追加・削除時にインデックスは自動更新される
- それだけのコストを支払うことになる
複数列インデックス使用時の注意
-
順番
- 電話帳で姓単独で引けても名前単独で引けないようなもの
- 個々の列に対して別々のインデックスを作成すれば十分なこと多し
関数インデックスの利用
- 公式
- ‘Indexes on Expressions’ (式インデックス)
-
列値そのままでは演算子がなくインデックスを作成できない場合も、関数インデックスは作れたりする
- XMLとか
部分インデックスの利用
- 公式
-
テーブル全体ではなく、サブセットに対してインデックスを作成するやつ
-
「サブセット」は述語関数で定義する
CREATE INDEX
文のWHERE
句
-
postgres=# CREATE INDEX ON tbl USING btree (col) WHERE col > 0 AND col < 100;
CREATE INDEX
postgres=# \d tbl_col_idx
Index "public.tbl_col_idx"
Column | Type | Key? | Definition
--------+---------+------+------------
col | integer | yes | col
btree, for table "public.tbl", predicate (col > 0 AND col < 100)
-
頻出値をインデックス作成対象から外すことができる
NULL
とか- テーブル全体のうち数%も占めればもう「頻出」
- インデックススキャンされない
-
ので、無用なインデックスを作成しないに越したことはない
- 更新のオーバヘッドが無くなる
- インデックスが小さくなり、インデックスを利用する検索が速くなる
- インデックス作成対象ドメインのカーディナリティを高められる
Column: インデックスの種類
B-Tree
- いつもの
GiST
- 公式
-
汎用的なインデックス実装の基盤
- B-Treeとかの
Hash
SP-GiST
- 公式/SP-GiST
- Space-Partitioned GiST
-
いろいろなインデックス実装の基盤
- 四分木とか
GIN
- 公式/GIN
-
Generalized Inverted Index
- 文中の単語をGINインデックスとして構築したりする
BRIN
- 公式/BRIN
- Block Range Index
- 巨大なテーブルに
- 論理的な値の並びと物理的な並びに強い相関がある場合に
bloom
文字エンコーディングとロケール
文字エンコーディング
- 公式
- データベース単位
ロケール
- 列単位
postgres=# CREATE TABLE t (str text COLLATE "ja-x-icu");
CREATE TABLE
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
str | text | ja-x-icu | |
postgres=# \! locale -a
C
C.UTF-8
en_US.utf8
POSIX
-
ja_JP
はlibcのロケール- あまりうれしい並び方じゃない
- 入れておかないと使えない
postgres=# CREATE TABLE tbl (str1 TEXT COLLATE "ja_JP.utf8", str2 TEXT COLLATE "ja-x-icu");
ERROR: collation "ja_JP.utf8" for encoding "UTF8" does not exist
LINE 1: CREATE TABLE tbl (str1 TEXT COLLATE "ja_JP.utf8", str2 TEXT ...
-
ja-x-icu
は嬉しい感じに並ぶ- 公式
- libcとの比較ブログ
-
PostgreSQLのビルド時、configureオプションで
--with-icu
を入れておくと使える- 入れないとエラー
- UTF-8エンコーディングであること
postgres=# CREATE TABLE tbl (str2 TEXT COLLATE "ja-x-icu");
2020-02-07 19:18:59.932 UTC [20] ERROR: collation "ja-x-icu" for encoding "UTF8" does not exist at character 29
2020-02-07 19:18:59.932 UTC [20] STATEMENT: CREATE TABLE tbl (str2 TEXT COLLATE "ja-x-icu");
ERROR: collation "ja-x-icu" for encoding "UTF8" does not exist
LINE 1: CREATE TABLE tbl (str2 TEXT COLLATE "ja-x-icu");
^
- localeとencodingは基本揃える
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
- localeが
--no-locale
または--locale=C
なら例外的にだとEncodingはSQL_ASCII
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+-----------+---------+-------+-----------------------
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
- createdbでlocaleを変更するときは必ず
--template=template0
を指定する
postgres@2d5eccf8738e:~$ createdb --locale=en_US.UTF-8 --template=template0 lc_sample
- しないとエラー
postgres@2d5eccf8738e:~$ createdb --locale=en_US.UTF-8 lc_sample2
createdb: error: database creation failed: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf8)
HINT: Use the same collation as in the template database, or use template0 as template.
ロケールを適用するメリット
- 公式
- ORDER BYの順序がロケールの辞書順に
-
文字列関数で全角/半角英数字を等価に扱える
- 等価に扱いたくないなら適さない
- 通貨型の通貨記号
ロケールを適用するデメリット
- LIKEの前方一致検索のインデックスが効かなくなる
- パフォーマンス上のオーバヘッド
-
OSのロケール依存
- OSのバージョンやライブラリバージョンへの依存が生じる
-
エンコーディングが縛られる
- UTF-8以外のエンコーディングを利用するならロケール非推奨