内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch6 物理設計 (2/2)

gihyo.jp

---

データ配置のポイント

• 公式

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)

• アーカイブはデフォルト無効、コマンドも未定義
  • なのでアーカイブ領域もない
• アーカイブファイルは循環しない
  • どれくらいとっておくかは運用次第
    • 最新のものだけ取っておいたり
    • 最新のベースバックアップ以前の時点へロールフォワードするためには古いのも必要
• ディスクフルになると
  1. アーカイブへのコピーに失敗する
  2. WALファイルがWAL領域に残り続ける
  3. WAL領域がディスクフルになる
  4. 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

• bloom
• bloom filter

文字エンコーディングとロケール

文字エンコーディング

• 公式
• データベース単位

ロケール

• 列単位

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以外のエンコーディングを利用するならロケール非推奨