むかしむかしに一つのプロジェクトがありました。 そして彼にはデータベースがありました。 そして、たとえば猫を保管するためのデータベース内のテーブルがありました。 以下がその1つです。
CREATE TABLE cats ( id serial, cname varchar(20), ctype varchar(20), primary key(id) );
すべてが非常に簡単です。各猫にはID、名前、特定のタイプがあります。
もちろん、猫とその種類に関するビジネス要件がありました。 たとえば、 大きな毛皮 、 ネコ 、 突然の危険というタイプがあることは確かです。 タイプの長い尾と寝台食いが現れるかもしれないことが示唆されました。 しかし、要件が変わると予想していました。 そして、最終的にどのタイプが必要になるかはまだわかっていません。 そのため、 varcharデータ型が使用されました（20） 。
長い時間といくつかのリリースの後、最終的に猫の種類の正確なリストをコンパイルしました。 この時点で、テーブルにはすでに数千万のさまざまなタイプの猫がいて、その多くは時代遅れです。 新しい要件に従って、テーブル内のすべての値をクリーンアップし、取り込む必要がありました。
そこで、エンジニアリングアプローチを適用します。
- 理論を構築する
- 実験でテストします
- 理論に基づいた実用的なソリューションを開発し、
- ソリューションを適用し、結果を評価します。
理論の構築
ENUMデータ型を作成し、その中の有効な値をリストします。 次に、移行を実行します。
CREATE TYPE cat_type AS ENUM ('big furry', 'small red', 'long tail', 'crafty hunter', 'sudden danger', 'sleeper-eater'); ALTER TABLE cats ALTER COLUMN ctype TYPE cat_type USING ctype::cat_type;
この形式では移行が機能しないことはまだわかりません。 テーブルにすでに存在する無効な値を忘れていました。 これについては、後ほど移行を適用するときに学習します=）
したがって、無効な型の値を持つ猫の作成を禁止します。 また、テーブルのサイズとctypeフィールドのインデックスのサイズを減らします。 テーブルのサイズはそれほど重要ではありませんが、インデックスを減らすことは適切です。 RAMに収まらないインデックスはすでに扱っています。 そして、これらは控えめに言っても、あまり有用なインデックスではありません。
予想されるメモリゲインを推定しましょう。
varchar型の値を格納するには、文字ごとに1〜4バイト（エンコードに応じて）および文字列の長さを格納するための別の1または4バイトが割り当てられます（詳細については、 www.postgresql.org / docs / current / static / datatype-character.htmlを参照）。 この例では、文字（utf8、ラテン文字）ごとに1バイト、行の長さごとに1バイトです。 文字列の長さは9〜14文字です。 平均して、値ごとに12バイトあると想定しています。
> select pg_column_size('big furry'); 10 > select pg_column_size('sleeper-eater'); 14
enum値は、長さに関係なく4バイトを占めることが知られています。
> select pg_column_size('big furry'::cat_type); 4 > select pg_column_size('sleeper-eater'::cat_type); 4
表の1行は次のとおりです。
- 行ヘッダーあたり27バイト、
- 8バイトのID
- 21バイトのcname（すべての猫はそれぞれ20文字であると仮定します）、
- 12バイトのctype
合計：68バイト。
移行後、27 + 8 + 21 + 4 = 60バイトになります。 差はわずかですが、5000万行の場合、合計ゲインはかなり大きくなります。
idとctypeの2つのインデックスがあります。 idのインデックスは変更されません。 ctypeインデックスは減少するはずです。 インデックスメモリがどのように配置されているかはわかりませんが、1つの値が3倍減少すると、インデックスは2〜3倍減少すると予想されます。
実験番号1
実験のために、2つのテーブルを作成します。
CREATE TABLE cats1 ( id serial, name varchar(20), type varchar(20), primary key(id) );
CREATE TYPE cat_type AS ENUM ('big furry', 'small red', 'long tail', 'crafty hunter', 'sudden danger', 'sleeper eater'); CREATE TABLE cats2 ( id serial, name varchar(20), type cat_type, primary key(id) );
テストデータを入力します。
CREATE SEQUENCE ss; INSERT INTO cats1 (name, type) SELECT substring(md5(random()::text), 0, 20), (ARRAY['big furry', 'small red', 'long tail', 'crafty hunter', 'sudden danger', 'sleeper eater']) [nextval('ss') % 5 + 1] FROM generate_series(1, 500000); INSERT INTO cats2 (name, type) SELECT substring(md5(random()::text), 0, 20), ((ARRAY['big furry', 'small red', 'long tail', 'crafty hunter', 'sudden danger', 'sleeper eater']) [nextval('ss') % 5 + 1])::cat_type FROM generate_series(1, 500000);
はい、私たちの猫の名前はかなり奇妙です。 しかし、実験には適しています。
インデックスを作成します。
CREATE INDEX cats1_index ON cats1(type); CREATE INDEX cats2_index ON cats2(type);
そして、どれだけのメモリを使用したかを見てみましょう。
SELECT pg_relation_size('cats1') AS table_size, pg_indexes_size('cats1') AS indexes_size; SELECT pg_relation_size('cats2') AS table_size, pg_indexes_size('cats2') AS indexes_size;
理論的には、最初のテーブルの行は68 * 500,000 = 34,000,000バイトを占有し、2番目のテーブルの行は60 * 500,000 = 30,000,000バイトを占有します。 実際には、34,136,064および30,121,984バイトが表示されます。 数字は近いです。
テーブルが次々に一様に500,000行よりも複雑であることは明らかです。 8 KBのメモリページが割り当てられています。 ページには独自の見出しとその他のメタ情報があります。 そして、行の値は何らかの形で整列しています（詳細については、 www.postgresql.org / docs / 9.5 / static / storage-page-layout.htmlを参照してください ）。
しかし、インデックスはどうでしょうか？
pg_indexes_size関数は、テーブルに関連付けられたすべてのインデックスのメモリ消費量を表示し、それぞれのインデックスのメモリ消費量を個別に表示しません。 しかし、これは重要ではありません。ctypeでインデックスを作成する前と後で呼び出すことができます。 そして、idによるインデックスは11,255,808バイトであり、最初のテーブルのctypeインデックスは15,794,176バイトであり、2番目のテーブルでは11,255,808バイトであることがわかります。
予想したほど顕著ではありませんが、2〜3倍ではありません。 なぜそう
実験2
1列のみを含むいくつかの単純なテーブルを作成しましょう。
CREATE TABLE t_bool (f bool); CREATE TABLE t_sint (f smallint); CREATE TABLE t_int (f int); CREATE TABLE t_bint (f bigint); CREATE TABLE t_c7 (f char(7)); CREATE TABLE t_c8 (f char(8)); CREATE TABLE t_c9 (f char(9)); CREATE TABLE t_c15 (f char(15)); CREATE TABLE t_c16 (f char(16)); CREATE TABLE t_c20 (f char(20));
データを入力します。
INSERT INTO t_bool (f) SELECT true FROM generate_series(1, 500000); INSERT INTO t_sint (f) SELECT 1 FROM generate_series(1, 500000); ... INSERT INTO t_c7 (f) SELECT 'abcdefg' FROM generate_series(1, 500000); ... INSERT INTO t_c20 (f) SELECT 'abcd efgh abcd efgh ' FROM generate_series(1, 500000);
インデックスを作成します。
CREATE INDEX ON t_bool(f); CREATE INDEX ON t_sint(f); ... CREATE INDEX ON t_c20(f);
そして、テーブルとインデックスがどれだけのスペースを占有するか見てみましょう：
| データ型
| シングルバイト
| テーブルサイズ
| インデックスサイズ
|
| ブール
| 1
| 18,128,896
| 11,255,808
|
| 小さい
| 2
| 18,128,896
| 11,255,808
|
| int
| 4
| 18,128,896
| 11,255,808
|
| ビッグ
| 8
| 18,128,896
| 11,255,808
|
| チャー（7）
| 8
| 18,128,896
| 11,255,808
|
| char（8）
| 9
| 22,142,976
| 15,794,176
|
| char（9）
| 10
| 22,142,976
| 15,794,176
|
| char（15）
| 16
| 22,142,976
| 15,794,176
|
| char（16）
| 17
| 26,091,520
| 20,332,544
|
| char（20）
| 21
| 26,091,520
| 20,332,544
|
テーブルとインデックスのサイズは、1〜8バイト、9〜16バイト、および16バイト以上の範囲で同じであることがわかります。
intをsmallintに置き換えるなどのマイナーな最適化は、ほとんど役に立たないようです。 1つのテーブルに多くの列があり、この方法で最適化できる場合を除きます。
varchar値が平均で8バイト（7文字を超える）を超える場合、varcharをenumに置き換えるとゲインが得られます。
実用的なソリューションを開発します
これで、実際に何を期待するかがわかり、移行を実現する準備が整いました。
私たちは猫に戻ります：
CREATE TABLE cats ( id serial, cname varchar(20), ctype varchar(20), primary key(id) ); CREATE INDEX c1 ON cats(ctype);
テーブルにデータを入力して、無効な値とNULL値が含まれるようにします。
CREATE SEQUENCE ss; INSERT INTO cats (cname, ctype) SELECT substring(md5(random()::text), 0, 20), (ARRAY['big furry', 'small red', 'long tail', 'crafty hunter', 'sudden danger', 'sleeper-eater', 'black eye', 'sharp claw', 'neko', NULL]) [nextval('ss') % 10 + 1] FROM generate_series(1, 500000);
移行しようとしています：
CREATE TYPE cat_type AS ENUM ('big furry', 'small red', 'long tail', 'crafty hunter', 'sudden danger', 'sleeper-eater'); ALTER TABLE cats ALTER COLUMN ctype TYPE cat_type USING ctype::cat_type;
そして、単純なALTER TABLEが機能しないことがわかります。
ERROR: invalid input value for enum cat_type: "black eye"
そして、型を変換する関数を書く必要があります：
CREATE OR REPLACE FUNCTION cast_to_cat_type(ctype varchar) RETURNS cat_type AS $$ DECLARE res cat_type; BEGIN CASE ctype WHEN 'big furry' THEN res := 'big furry'; WHEN 'small red' THEN res := 'small red'; WHEN 'long tail' THEN res := 'long tail'; WHEN 'crafty hunter' THEN res := 'crafty hunter'; WHEN 'sudden danger' THEN res := 'sudden danger'; WHEN 'sleeper-eater' THEN res := 'sleeper-eater'; ELSE res := NULL; END CASE; RETURN res; END $$ LANGUAGE plpgsql;
そして再試行してください：
ALTER TABLE cats ALTER COLUMN ctype TYPE cat_type USING cast_to_cat_type(ctype);
今回はうまくいきました。 誰かにそのような機能を見せることだけが恥ずかしいです。 ああ、私はちょうどコピーペーストのために私の秘密の傾向を裏切ったと思う=）Shh、私はこれを書いていないふりをしましょうが、あなたはそれを見なかった、大丈夫？ そして、私は別の方法で書きます：
CREATE OR REPLACE FUNCTION cast_to_cat_type(ctype varchar) RETURNS cat_type AS $$ DECLARE res cat_type; BEGIN BEGIN res := ctype::cat_type; EXCEPTION WHEN others THEN res := NULL; END; RETURN res; END $$ LANGUAGE plpgsql;
これはコードレビューに安全に送信できます。
結果を評価する
その結果、何が得られましたか？ 移行前のテーブルとインデックスのサイズ：33,038,336および26,140,672バイト。 移行後：28,581,888および22,511,616バイト。 実際のテーブルでは50万件のレコードではなく、5,000万件のレコードがあるため、大幅な増加が見込まれます。
しかし、特定の条件下では、さらに勝つことができます。 間違ったタイプまたは不明なタイプの猫に興味がないと仮定すると、彼らはリクエストから除外されます。 その後、それらをインデックスから除外できます。
部分インデックスを使用します ：
CREATE index CONCURRENTLY c2 ON cats(ctype) WHERE ctype IS NOT NULL; DROP index c1;
そして現在、インデックスサイズは18,014,208バイトです。 ここでは、もちろん、すべてが間違っていることが判明した猫の数に依存します。
好奇心が強い質問は、次に間違った猫をどうするかです。 しかし、これは開発者のためではなく、ビジネスのための質問です。
正しい値がテーブルに挿入され、間違った値が挿入されないようにするために残ります。
> INSERT INTO cats (cname, ctype) VALUES ('Murzik', 'crafty hunter'), ('Vasjka', 'sleeper-eater'); INSERT 0 2 > INSERT INTO cats (cname, ctype) VALUES ('Sharik', 'big dog'); ERROR: invalid input value for enum cat_type: "big dog"
すべてが正常に機能します。
移行についての興味深い話がいくつかあり、大きなテーブルを使用するのがどれほど難しいかがわかります。 次回はそれについて話しましょう。
ユラ・ズロバ、
Web開発者。