DBAからのSQLトリック。データベース開発者向けのすぐに使えるアドバイス

開発者としてのキャリアを始めたとき、最初の仕事はDBA（データベース管理者、DBA）でした。当時、AWS RDS、Azure、Google Cloud、その他のクラウドサービスが登場する前から、DBAには次の2種類がありました。

• , . « », , .
• : , , SQL. ETL- . , .

アプリケーションDBAは通常、開発チームの一部です。彼らは特定のトピックについて深い知識を持っていたので、通常は1つか2つのプロジェクトにしか取り組みませんでした。インフラストラクチャDBAは通常ITチームの一部であり、同時に複数のプロジェクトに取り組むことができました。

私はアプリケーションデータベースの管理者です

バックアップやストレージの調整をいじりたいという衝動は一度もありませんでした（きっと楽しいです！）。今日まで、私はアプリケーションの開発方法を知っているDB管理者であり、データベースを理解している開発者ではないと言いたいです。



この記事では、私のキャリアの中で学んだデータベース開発の秘訣をいくつか紹介します。



コンテンツ：

• 更新が必要なものだけを更新する
• 重い負荷の制約とインデックスを無効にする
• 中間データにはUNLOGGEDテーブルを使用します
• WITHおよびRETURNINGを使用してプロセス全体を実装する
• 選択性の低い列のインデックスは避けてください
• 部分インデックスを使用する
• ソートされたデータを常にロードする
• BRINとの相関性の高い列インデックス
• インデックスを「非表示」にする
• 長いプロセスを1時間の開始時に開始するようにスケジュールしないでください
• 結論

更新が必要なものだけを更新する

この操作UPDATEは非常に多くのリソースを消費します。それをスピードアップする最良の方法は、更新する必要があるものだけを更新することです。



メール列を正規化するリクエストの例を次に示します。

db=# UPDATE users SET email = lower(email);
UPDATE 1010000
Time: 1583.935 ms (00:01.584)

無実に見えますよね？このリクエストにより、1,010,000ユーザーのメールアドレスが更新されます。しかし、すべての行を更新する必要がありますか？

db=# UPDATE users SET email = lower(email)
db-# WHERE email != lower(email);
UPDATE 10000
Time: 299.470 ms

更新する必要があるのは10,000行だけです。処理するデータ量を減らすことで、実行時間を1.5秒から300ms未満に短縮しました。これにより、データベースの保守にかかる労力も節約できます。





更新が必要なものだけを更新します。



このタイプの大規模な更新は、データ移行スクリプトで非常に一般的です。次回このようなスクリプトを作成するときは、必要なものだけを更新してください。

重い負荷の制約とインデックスを無効にする

制約はリレーショナルデータベースの重要な部分です。制約はデータの一貫性と信頼性を維持します。ただし、すべてに独自の価格があり、多くの場合、多数の行をダウンロードまたは更新するときに料金を支払う必要があります。



小さなストレージスキーマを定義しましょう：

DROP TABLE IF EXISTS product CASCADE;
CREATE TABLE product (
    id serial PRIMARY KEY,
    name TEXT NOT NULL,
    price INT NOT NULL
);
INSERT INTO product (name, price)
    SELECT random()::text, (random() * 1000)::int
    FROM generate_series(0, 10000);

DROP TABLE IF EXISTS customer CASCADE;
CREATE TABLE customer (
    id serial PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO customer (name)
    SELECT random()::text
    FROM generate_series(0, 100000);

DROP TABLE IF EXISTS sale;
CREATE TABLE sale (
    id serial PRIMARY KEY,
    created timestamptz NOT NULL,
    product_id int NOT NULL,
    customer_id int NOT NULL
);

「nullではない」などのさまざまなタイプの制約と一意の制約を定義します...



開始点を設定するために、テーブルへのsale外部キーの追加を開始しましょう。

db=# ALTER TABLE sale ADD CONSTRAINT sale_product_fk
db-# FOREIGN KEY (product_id) REFERENCES product(id);
ALTER TABLE
Time: 18.413 ms

db=# ALTER TABLE sale ADD CONSTRAINT sale_customer_fk
db-# FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE
Time: 5.464 ms

db=# CREATE INDEX sale_created_ix ON sale(created);
CREATE INDEX
Time: 12.605 ms

db=# INSERT INTO SALE (created, product_id, customer_id)
db-# SELECT
db-#    now() - interval '1 hour' * random() * 1000,
db-#    (random() * 10000)::int + 1,
db-#    (random() * 100000)::int + 1
db-# FROM generate_series(1, 1000000);
INSERT 0 1000000
Time: 15410.234 ms (00:15.410)

制約とインデックスを定義した後、100万行をテーブルにロードするのに約15.4秒かかりました。



それでは、最初にデータをテーブルにロードしてから、制約とインデックスを追加しましょう。

db=# INSERT INTO SALE (created, product_id, customer_id)
db-# SELECT
db-#    now() - interval '1 hour' * random() * 1000,
db-#    (random() * 10000)::int + 1,
db-#    (random() * 100000)::int + 1
db-# FROM generate_series(1, 1000000);
INSERT 0 1000000
Time: 2277.824 ms (00:02.278)

db=# ALTER TABLE sale ADD CONSTRAINT sale_product_fk
db-# FOREIGN KEY (product_id) REFERENCES product(id);
ALTER TABLE
Time: 169.193 ms

db=# ALTER TABLE sale ADD CONSTRAINT sale_customer_fk
db-# FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE
Time: 185.633 ms

db=# CREATE INDEX sale_created_ix ON sale(created);
CREATE INDEX
Time: 484.244 ms

読み込みははるかに速く、2.27秒でした。15.4の代わりに。インデックスと制限は、データのロード後にはるかに長く作成されましたが、プロセス全体ははるかに高速でした：3.1秒。15.4の代わりに。



残念ながら、PostgreSQLでは、インデックスを使用して同じことを行うことはできません。インデックスを破棄して再作成することしかできません。Oracleなどの他のデータベースでは、再構築せずにインデックスを無効または有効にできます。

UNLOGGED-

PostgreSQLでデータを変更すると、変更は先読みログ（WAL）に書き込まれます。これは、一貫性を維持し、リカバリ中に迅速にインデックスを再作成し、レプリケーションを維持するために使用されます。



WALへの書き込みが必要になることがよくありますが、WALをオプトアウトして処理を高速化できる状況もあります。たとえば、ステージングテーブルの場合です。



中間テーブルはワンタイムテーブルと呼ばれ、一部のプロセスの実装に使用される一時データを格納します。たとえば、ETLプロセスでは、CSVファイルからステージングテーブルにデータをロードし、情報をクリアしてから、ターゲットテーブルにロードするのが非常に一般的です。このシナリオでは、ステージングテーブルは1回限りの使用であり、バックアップまたはレプリカでは使用されません。





UNLOGGEDテーブル。



障害が発生した場合に回復する必要がなく、レプリカで必要とされないステージングテーブルは、UNLOGGEDとして設定できます。

CREATE UNLOGGED TABLE staging_table ( /* table definition */ );

注意：を使用UNLOGGEDする前に、すべての影響を完全に理解していることを確認してください。

WITHおよびRETURNINGを使用してプロセス全体を実装する

ユーザーテーブルがあり、データが重複していることがわかったとします。

Table setup
db=# SELECT u.id, u.email, o.id as order_id
FROM orders o JOIN users u ON o.user_id = u.id;

 id |       email       | order_id
----+-------------------+----------
  1 | foo@bar.baz       |        1
  1 | foo@bar.baz       |        2
  2 | me@hakibenita.com |        3
  3 | ME@hakibenita.com |        4
  3 | ME@hakibenita.com |        5

ユーザーhakibenitaはメールME@hakibenita.comとで2回登録しましたme@hakibenita.com。テーブルに入力するときに電子メールアドレスを正規化していないため、重複を処理する必要があります。



必要なもの：

1. 小文字で重複するアドレスを特定し、重複するユーザーを相互にリンクします。
2. 重複の1つのみを参照するように注文を更新します。
3. テーブルから重複を削除します。

ステージングテーブルを使用して、重複するユーザーをリンクできます。

db=# CREATE UNLOGGED TABLE duplicate_users AS
db-#     SELECT
db-#         lower(email) AS normalized_email,
db-#         min(id) AS convert_to_user,
db-#         array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
db-#     FROM
db-#         users
db-#     GROUP BY
db-#         normalized_email
db-#     HAVING
db-#         count(*) > 1;
CREATE TABLE

db=# SELECT * FROM duplicate_users;
 normalized_email  | convert_to_user | convert_from_users
-------------------+-----------------+--------------------
 me@hakibenita.com |               2 | {3}

中間テーブルには、テイク間のリンクが含まれています。正規化された電子メールアドレスを持つユーザーが複数回表示される場合、最小のユーザーIDを割り当て、すべての重複を折りたたみます。残りのユーザーは配列列に格納され、それらへのすべての参照が更新されます。



中間テーブルを使用して、テーブル内の重複へのリンクを更新しますorders。

db=# UPDATE
db-#    orders o
db-# SET
db-#    user_id = du.convert_to_user
db-# FROM
db-#    duplicate_users du
db-# WHERE
db-#    o.user_id = ANY(du.convert_from_users);
UPDATE 2

これで、以下から重複を安全に削除できますusers。

db=# DELETE FROM
db-#    users
db-# WHERE
db-#    id IN (
db(#        SELECT unnest(convert_from_users)
db(#        FROM duplicate_users
db(#    );
DELETE 1

unnest 関数を使用して配列を「変換」し、各要素を文字列に変換することに注意してください。



結果：

db=# SELECT u.id, u.email, o.id as order_id
db-# FROM orders o JOIN users u ON o.user_id = u.id;
 id |       email       | order_id
----+-------------------+----------
  1 | foo@bar.baz       |        1
  1 | foo@bar.baz       |        2
  2 | me@hakibenita.com |        3
  2 | me@hakibenita.com |        4
  2 | me@hakibenita.com |        5

すべてのuser 3（ME@hakibenita.com）インスタンスがuser 2（me@hakibenita.com）に変換されます。



重複がテーブルから削除されていることを確認することもできますusers。

db=# SELECT * FROM users;
 id |       email
----+-------------------
  1 | foo@bar.baz
  2 | me@hakibenita.com

これで、ステージングテーブルを取り除くことができます。

db=# DROP TABLE duplicate_users;
DROP TABLE

大丈夫ですが、時間がかかりすぎて掃除が必要です！より良い方法はありますか？

一般化されたテーブル式（CTE）

では、一般的なテーブル式も式として知られている、WITH我々は、単一のSQL式で全体の手順を実行することができます：

WITH duplicate_users AS (
    SELECT
        min(id) AS convert_to_user,
        array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
    FROM
        users
    GROUP BY
        lower(email)
    HAVING
        count(*) > 1
),

update_orders_of_duplicate_users AS (
    UPDATE
        orders o
    SET
        user_id = du.convert_to_user
    FROM
        duplicate_users du
    WHERE
        o.user_id = ANY(du.convert_from_users)
)

DELETE FROM
    users
WHERE
    id IN (
        SELECT
            unnest(convert_from_users)
        FROM
            duplicate_users
    );

ステージングテーブルの代わりに、汎用テーブル式を作成して再利用しました。

CTEから結果を返す

式内でDMLを実行する利点の1つは、RETURNINGWITHキーワードを使用してDMLからデータを返すことができることです。更新および削除された行の数に関するレポートが必要だとします。

WITH duplicate_users AS (
    SELECT
        min(id) AS convert_to_user,
        array_remove(ARRAY_AGG(id), min(id)) as convert_from_users
    FROM
        users
    GROUP BY
        lower(email)
    HAVING
        count(*) > 1
),

update_orders_of_duplicate_users AS (
    UPDATE
        orders o
    SET
        user_id = du.convert_to_user
    FROM
        duplicate_users du
    WHERE
        o.user_id = ANY(du.convert_from_users)
    RETURNING o.id
),

delete_duplicate_user AS (
    DELETE FROM
        users
    WHERE
        id IN (
            SELECT unnest(convert_from_users)
            FROM duplicate_users
        )
        RETURNING id
)

SELECT
    (SELECT count(*) FROM update_orders_of_duplicate_users) AS orders_updated,
    (SELECT count(*) FROM delete_duplicate_user) AS users_deleted
;

結果：

orders_updated | users_deleted
----------------+---------------
              2 |             1

このアプローチの利点は、プロセス全体が1つのコマンドで実行されるため、トランザクションを管理したり、プロセスに障害が発生した場合にステージングテーブルをフラッシュすることを心配したりする必要がないことです。



警告：Redditの読者が、汎用テーブル式でのDML実行の予測できない動作の可能性を指摘しました。

の部分式はWITH、相互に、およびメインクエリと同時に実行されます。したがって、WITHデータ変更式で使用する場合、実際の更新順序は予測できません。

これは、独立した部分式が実行される順序に依存できないことを意味します。上記の例のように、それらの間に依存関係がある場合、それらを使用する前に、依存する部分式の実行に依存できることがわかります。

選択性の低い列のインデックスは避けてください

ユーザーが電子メールアドレスでログインするサインアッププロセスがあるとします。アカウントをアクティブ化するには、メールを確認する必要があります。テーブルは次のようになります。

db=# CREATE TABLE users (
db-#    id serial,
db-#    username text,
db-#    activated boolean
db-#);
CREATE TABLE

ほとんどのユーザーは市民を意識しており、正しい郵送先住所で登録し、すぐにアカウントをアクティブにします。テーブルにユーザーデータを入力し、ユーザーの90％がアクティブ化されていると仮定しましょう。

db=# INSERT INTO users (username, activated)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     random() < 0.9 AS activated
db-# FROM
db-#     generate_series(1, 1000000);
INSERT 0 1000000

db=# SELECT activated, count(*) FROM users GROUP BY activated;
 activated | count
-----------+--------
 f         | 102567
 t         | 897433

db=# VACUUM ANALYZE users;
VACUUM

アクティブ化されたユーザーとアクティブ化されていないユーザーの数を照会するには、列ごとにインデックスを作成しますactivated。

db=# CREATE INDEX users_activated_ix ON users(activated);
CREATE INDEX

また、アクティブ化されていないユーザーの数を尋ねると、データベースはインデックスを使用します。

db=# EXPLAIN SELECT * FROM users WHERE NOT activated;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=1923.32..11282.99 rows=102567 width=38)
   Filter: (NOT activated)
   ->  Bitmap Index Scan on users_activated_ix  (cost=0.00..1897.68 rows=102567 width=0)
         Index Cond: (activated = false)

ベースは、フィルターが102,567アイテム、つまりテーブルの約10％を返すことを決定しました。これはロードしたデータと一致しているので、テーブルはうまく機能しました。



ただし、アクティブ化されたユーザーの数を照会すると、データベースがインデックスを使用しないことを決定したことがわかります。

db=# EXPLAIN SELECT * FROM users WHERE activated;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on users  (cost=0.00..18334.00 rows=897433 width=38)
   Filter: activated

データベースがインデックスを使用していない場合、多くの開発者は混乱します。これを行う理由を説明すると、次のようになります。テーブル全体を読み取る必要がある場合、インデックスを使用しますか？



おそらくそうではないでしょう、なぜこれが必要なのですか？ディスクからの読み取りはコストがかかるため、読み取りはできるだけ少なくする必要があります。たとえば、テーブルのサイズが10 MBで、インデックスが1 MBの場合、テーブル全体を読み取るには、ディスクから10MBを読み取る必要があります。また、インデックスを追加すると、11MBになります。それは無駄です。



次に、PostgreSQLがテーブルに収集した統計を見てみましょう。

db=# SELECT attname, n_distinct, most_common_vals, most_common_freqs
db-# FROM pg_stats
db-# WHERE tablename = 'users' AND attname='activated';
------------------+------------------------
attname           | activated
n_distinct        | 2
most_common_vals  | {t,f}
most_common_freqs | {0.89743334,0.10256667}

PostgreSQLがテーブルを解析したところ、列にactivated2つの異なる値があることがわかりました。t列の値は列most_common_valsの周波数0.89743334に対応し、most_common_freqs値fは周波数に対応します0.10256667。テーブルを分析した後、データベースは、レコードの89.74％がアクティブ化されたユーザーであり、残りの10.26％が非アクティブ化されていると判断しました。



これらの統計に基づいて、PostgreSQLは、行の90％が条件を満たすと想定するよりも、テーブル全体をスキャンする方がよいと判断しました。データベースがインデックスを使用するかどうかを決定できるしきい値は、多くの要因に依存し、大まかなルールはありません。





選択性が低い列と高い列のインデックス。

部分インデックスを使用する

前の章では、レコードの約90％true（アクティブ化されたユーザー）を持つブール列のインデックスを作成しました。



アクティブユーザーの数を尋ねたところ、データベースはインデックスを使用していませんでした。また、非アクティブ化された数を尋ねられたとき、データベースはインデックスを使用しました。



データベースがアクティブユーザーを除外するためにインデックスを使用しない場合、そもそもなぜそれらにインデックスを付けるのでしょうか。



この質問に答える前に、列ごとの完全なインデックスの重みを見てみましょうactivated。

db=# \di+ users_activated_ix

 Schema |      Name          | Type  | Owner | Table | Size
--------+--------------------+-------+-------+-------+------
 public | users_activated_ix | index | haki  | users | 21 MB

インデックスの重量は21MBです。参考までに、ユーザーのいるテーブルは65MBです。つまり、インデックスの重みはベースの重みの約32％です。そうは言っても、インデックスコンテンツの約90％が使用される可能性は低いことがわかっています。



PostgreSQLでは、テーブルの一部にのみインデックスを作成できます。いわゆる部分インデックスです。

db=# CREATE INDEX users_unactivated_partial_ix ON users(id)
db-# WHERE not activated;
CREATE INDEX

式を使用WHEREして、インデックスでカバーされる文字列を制約します。それが機能するかどうかを確認しましょう：

db=# EXPLAIN SELECT * FROM users WHERE not activated;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Index Scan using users_unactivated_partial_ix on users  (cost=0.29..3493.60 rows=102567 width=38)

すばらしいことに、データベースは、クエリで使用したブール式が部分的なインデックスに対して機能する可能性があることを理解するのに十分スマートであることがわかりました。



このアプローチには別の利点があります。

db=# \di+ users_unactivated_partial_ix
                                 List of relations
 Schema |           Name               | Type  | Owner | Table |  Size
--------+------------------------------+-------+-------+-------+---------
 public | users_unactivated_partial_ix | index | haki  | users | 2216 kB

完全な列のインデックスの重みは21MBで、部分的なインデックスはわずか2.2MBです。これは10％であり、テーブル内の非アクティブ化されたユーザーの割合に対応します。

ソートされたデータを常にロードする

これは、コードを解析するときに最も頻繁に使用するコメントの1つです。アドバイスは他のアドバイスほど直感的ではなく、生産性に大きな影響を与える可能性があります。



あなたが特定の売上高を持つ巨大なテーブルを持っているとしましょう：

db=# CREATE TABLE sale_fact (id serial, username text, sold_at date);
CREATE TABLE

ETLプロセス中は毎晩、データをテーブルにロードします。

db=# INSERT INTO sale_fact (username, sold_at)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at
db-# FROM
db-#     generate_series(1, 100000);
INSERT 0 100000

db=# VACUUM ANALYZE sale_fact;
VACUUM

ダウンロードをシミュレートするために、ランダムデータを使用します。ランダムな名前の10万行を挿入し、2020年1月1日から2年前までの販売日を記載しました。



ほとんどの場合、この表は要約販売レポートに使用されます。ほとんどの場合、特定の期間の売上を確認するために日付でフィルタリングします。範囲スキャンを高速化するために、次の方法でインデックスを作成しましょうsold_at。

db=# CREATE INDEX sale_fact_sold_at_ix ON sale_fact(sold_at);
CREATE INDEX

2020年6月にすべての売上を取得するリクエストの実行計画を見てみましょう。

db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';

                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sale_fact  (cost=108.30..1107.69 rows=4293 width=41)
   Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
   Heap Blocks: exact=927
   ->  Bitmap Index Scan on sale_fact_sold_at_ix  (cost=0.00..107.22 rows=4293 width=0)
         Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Planning Time: 0.191 ms
 Execution Time: 5.906 ms

キャッシュをウォームアップするためにリクエストを数回実行した後、実行時間は6ミリ秒のレベルで安定しました。

ビットマップスキャン

実行に関しては、ベースがビットマップスキャンを使用していることがわかります。それは2つの段階で行われます：

• (Bitmap Index Scan)：ベースはインデックス全体sale_fact_sold_at_ixを調べて、関連する行を含むテーブル内のすべてのページを検索します。
• (Bitmap Heap Scan)：ベースは、関連する文字列を含むページを読み取り、条件を満たすページを見つけます。

ページには多くの行を含めることができます。最初のステップでは、インデックスを使用してページを検索します。第2段階ではページ内の行を検索するためRecheck Cond、実行計画の操作は次のようになります。



この時点で、多くのDBAと開発者は締めくくり、次のクエリに進みます。しかし、このクエリを改善する方法があります。

インデックススキャン

データの読み込みに小さな変更を加えましょう。

db=# TRUNCATE sale_fact;
TRUNCATE TABLE

db=# INSERT INTO sale_fact (username, sold_at)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at
db-# FROM
db-#     generate_series(1, 100000)
db-# ORDER BY sold_at;
INSERT 0 100000

db=# VACUUM ANALYZE sale_fact;
VACUUM

今回は、でソートされたデータをロードしましたsold_at。



これで、同じクエリの実行計画は次のようになります。

db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';

                           QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Scan using sale_fact_sold_at_ix on sale_fact (cost=0.29..184.73 rows=4272 width=41)
   Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Planning Time: 0.145 ms
 Execution Time: 2.294 ms

数回実行した後、実行時間は2.3msで安定しました。約60％の持続可能な節約を達成しました。



また、今回はデータベースがビットマップスキャンを使用せず、「通常の」インデックススキャンを適用したこともわかります。どうして？

相関

データベースがテーブルを分析すると、取得できるすべての統計が収集されます。パラメータの1つは相関です：

行の物理的な順序と列の値の論理的な順序の間の統計的相関。値が約-1または+1の場合、ランダムディスクアクセスの数が減少するため、相関値が約0の場合よりも、列全体のインデックススキャンの方が有利であると見なされます。

公式ドキュメントで説明されているように、相関は、ディスク上の特定の列の値がどのように「ソート」されているかの尺度です。





相関= 1。



相関が1程度の場合、ページがテーブルの行とほぼ同じ順序でディスクに保存されていることを意味します。これは非常に一般的です。たとえば、自動インクリメントIDの相関は1に近い傾向があります。行が作成された日時を示す日付列とタイムスタンプ列の相関も1に近くなります。



相関が-1の場合、ページは列の逆の順序で並べ替えられます。





相関〜0。



相関が0に近い場合は、列の値がテーブルのページ順序と相関していないか、ほとんど相関していないことを意味します。



に戻りましょうsale_fact。事前に並べ替えずにデータをテーブルにロードすると、相関関係は次のようになります。

db=# SELECT tablename, attname, correlation
db-# FROM pg_stats
db=# WHERE tablename = 'sale_fact';

 tablename | attname  | correlation
-----------+----------+--------------
 sale      | id       |            1
 sale      | username | -0.005344716
 sale      | sold_at  | -0.011389783

自動生成された列IDの相関は1です。列の相関はsold_at非常に低く、連続する値がテーブル全体に散らばっています。



ソートされたデータをテーブルにロードすると、彼女は相関関係を計算しました。

tablename | attname  |  correlation
-----------+----------+----------------
 sale_fact | id       |              1
 sale_fact | username | -0.00041992788
 sale_fact | sold_at  |              1

これで、相関sold_atは等しくなり1ます。



では、なぜデータベースは相関が低いときにビットマップスキャンを使用し、相関が高いときにインデックススキャンを使用したのでしょうか。

• 相関が1の場合、ベースは、要求された範囲の行が連続したページにある可能性が高いと判断しました。次に、インデックススキャンを使用して複数のページを読み取ることをお勧めします。
• 相関が0に近い場合、ベースは、要求された範囲の行がテーブル全体に散在している可能性が高いと判断しました。次に、必要な行を含むページのビットマップスキャンを使用し、条件を使用してそれらを抽出することをお勧めします。

次にデータをテーブルにロードするときは、要求される情報の量を検討し、インデックスが範囲をすばやくスキャンできるように並べ替えます。

CLUSTERコマンド

特定のインデックスで「ディスク上のテーブルをソート」する別の方法は、CLUSTERコマンドを使用することです。



例えば：

db=# TRUNCATE sale_fact;
TRUNCATE TABLE

-- Insert rows without sorting
db=# INSERT INTO sale_fact (username, sold_at)
db-# SELECT
db-#     md5(random()::text) AS username,
db-#     '2020-01-01'::date + (interval '1 day') * round(random() * 365 * 2) AS sold_at
db-# FROM
db-#     generate_series(1, 100000)
INSERT 0 100000

db=# ANALYZE sale_fact;
ANALYZE

db=# SELECT tablename, attname, correlation
db-# FROM pg_stats
db-# WHERE tablename = 'sale_fact';

  tablename | attname  |  correlation
-----------+-----------+----------------
 sale_fact | sold_at   | -5.9702674e-05
 sale_fact | id        |              1
 sale_fact | username  |    0.010033822

データをランダムな順序でテーブルにロードしたため、相関sold_atはゼロに近くなります。



によってテーブルを「再構成」するsold_atには、次のコマンドを使用して、CLUSTERディスク上のテーブルをインデックスに従ってソートしますsale_fact_sold_at_ix。

db=# CLUSTER sale_fact USING sale_fact_sold_at_ix;
CLUSTER

db=# ANALYZE sale_fact;
ANALYZE

db=# SELECT tablename, attname, correlation
db-# FROM pg_stats
db-# WHERE tablename = 'sale_fact';

 tablename | attname  | correlation
-----------+----------+--------------
 sale_fact | sold_at  |            1
 sale_fact | id       | -0.002239401
 sale_fact | username |  0.013389298

テーブルをクラスター化した後、相関sold_atは1になりました。





CLUSTERコマンド。



注意点：

• 特定の列でテーブルをクラスター化すると、別の列の相関に影響を与える可能性があります。たとえば、でクラスタリングしsold_atた後のIDの相関関係を見てみましょう。
• CLUSTER 重くてブロッキング操作なので、ライブテーブルには適用しないでください。

これらの理由から、すでにソートされており、に依存しないデータを挿入することをお勧めしCLUSTERます。

BRINとの相関性の高い列インデックス

インデックスに関しては、多くの開発者がBツリーについて考えています。ただし、PostgreSQLは、BRINなどの他のタイプのインデックスを提供します。

BRINは、一部の列がテーブル内の物理的な位置と自然に相関する非常に大きなテーブルで機能するように設計されています

BRINはBlockRangeIndexの略です。ドキュメントによると、BRINは相関性の高い列で最適に機能します。前の章で見たように、自動インクリメントIDとタイムスタンプはテーブルの物理的構造と自然に相関するため、BRINはそれらにとってより有益です。



特定の条件下では、BRINは、同等のBツリーインデックスと比較して、サイズとパフォーマンスの点でより優れた「コストパフォーマンス」を提供できます。





ブリン。



BRINは、テーブル内のいくつかの隣接するページ内の値の範囲です。列に次の値があり、それぞれが別々のページにあるとしましょう：

1, 2, 3, 4, 5, 6, 7, 8, 9

BRINは、隣接するページの範囲で機能します。隣接する3つのページを指定すると、インデックスはテーブルを次の範囲に分割します。

[1,2,3], [4,5,6], [7,8,9]

範囲ごとに、BRINは最小値と最大値を格納します。

[1–3], [4–6], [7–9]

このインデックスを使用して、値5を探しましょう。

• [1–3]-彼は確かにここにいません。
• [4–6]-ここにあるかもしれません。
• [7–9]-彼は確かにここにいません。

BRINを使用して、検索領域をブロック4〜6に制限しました。



別の例を見てみましょう。列の値の相関がゼロに近い、つまり並べ替えられていないようにします：

[2,9,5], [1,4,7], [3,8,6]

隣接する3つのブロックにインデックスを付けると、次の範囲が得られます。

[2–9], [1–7], [3–8]

値5を探しましょう：

• [2-9]-ここにあるかもしれません。
• [1-7]-ここにあるかもしれません。
• [3–8]-ここにあるかもしれません。

この場合、インデックスは検索をまったく絞り込まないため、役に立ちません。

pages_per_rangeを理解する

隣接するページの数は、パラメータによって決定されますpages_per_range。範囲内のページ数は、BRINのサイズと精度に影響します。

• pages_per_rangeインデックスが小さくて精度が低いと、大きな値になります。
• 値pages_per_rangeを小さくすると、インデックスが大きくなり、より正確になります。

デフォルトpages_per_rangeは128です。





低いpages_per_rangeのBRIN。



説明のために、2ページの範囲でBRINを作成し、5の値を探しましょう。

• [1–2]-彼は確かにここにいません。
• [3–4]-彼は確かにここにいません。
• [5-6]-ここにあるかもしれません。
• [7–8]-彼は確かにここにいません。
• [9]-ここでは絶対にそうではありません。

2ページの範囲では、検索をブロック5と6に制限できます。範囲が3ページの場合、インデックスは検索をブロック4、5、6に制限します



。2つのインデックスのもう1つの違いは、範囲が3ページの場合、3つの範囲を格納する必要があることです。 、および範囲内に2ページある場合、すでに5つの範囲が取得され、インデックスが増加します。

BRINを作成する

テーブルsales_factを取り、列ごとにBRINを作成しましょうsold_at：

db=# CREATE INDEX sale_fact_sold_at_bix ON sale_fact
db-# USING BRIN(sold_at) WITH (pages_per_range = 128);
CREATE INDEX

デフォルトはpages_per_range = 128です。



次に、販売期間を照会してみましょう。

db=# EXPLAIN (ANALYZE)
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                    QUERY PLAN
--------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sale_fact  (cost=13.11..1135.61 rows=4319 width=41)
   Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
   Rows Removed by Index Recheck: 23130
   Heap Blocks: lossy=256
   ->  Bitmap Index Scan on sale_fact_sold_at_bix  (cost=0.00..12.03 rows=12500 width=0)
         Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Execution Time: 8.877 ms

ベースはBRINを使用して日付期間を取得しましたが、これは興味深いことではありません...

pages_per_rangeの最適化

実行計画によると、データベースは、インデックスを使用して見つけたページから23130行を削除しました。これは、インデックスに指定した範囲がこのクエリに対して大きすぎることを示している可能性があります。範囲内のページ数が半分のインデックスを作成しましょう。

db=# CREATE INDEX sale_fact_sold_at_bix64 ON sale_fact
db-# USING BRIN(sold_at) WITH (pages_per_range = 64);
CREATE INDEX

db=# EXPLAIN (ANALYZE)
db- SELECT *
db- FROM sale_fact
db- WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sale_fact  (cost=13.10..1048.10 rows=4319 width=41)
   Recheck Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
   Rows Removed by Index Recheck: 9434
   Heap Blocks: lossy=128
   ->  Bitmap Index Scan on sale_fact_sold_at_bix64  (cost=0.00..12.02 rows=6667 width=0)
         Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
 Execution Time: 5.491 ms

範囲内の64ページで、データベースはインデックス-9 434を使用して見つかった行をより少なく削除しました。これは、実行するI / O操作が少なくて済み、クエリが少し速く実行され、約8.9ではなく約5.5ミリ秒で実行されたことを意味します。



さまざまな値でインデックスをテストしてみましょうpages_per_range：





減少pages_per_range指数は、より正確になり、それが見つかったページから少ない行を削除します。



非常に具体的なクエリを最適化したことに注意してください。これは説明には問題ありませんが、実際には、ほとんどのクエリのニーズを満たす値を使用することをお勧めします。

インデックスのサイズの見積もり

BRINのもう1つの大きな利点は、そのサイズです。前の章では、フィールドのsold_atBツリーインデックスを作成しました。サイズは2,224KBでした。また、パラメータを使用したBRINサイズはpages_per_range=128わずか48 KBで、46分の1になります。

Schema |         Name          | Type  | Owner |   Table   | Size
--------+-----------------------+-------+-------+-----------+-------
 public | sale_fact_sold_at_bix | index | haki  | sale_fact | 48 kB
 public | sale_fact_sold_at_ix  | index | haki  | sale_fact | 2224 kB

BRINサイズも影響を受けpages_per_rangeます。たとえば、BRINのpages_per_range=2重量は56 Kbで、48Kbをわずかに上回ります。

インデックスを「非表示」にする

PostgreSQLにはクールなトランザクションDDL機能があります。Oracleと長年にわたり、私はDDLのようなコマンドを使用することに慣れてきたCREATE、DROPとの取引の終わりにALTER。ただし、PostgreSQLでは、トランザクション内でDDLコマンドを実行でき、変更はトランザクションがコミットされた後にのみ適用されます。



最近、トランザクションDDLを使用すると、インデックスが非表示になる可能性があることを発見しました。これは、インデックスのない実行計画を確認する場合に役立ちます。



たとえば、テーブルsale_factでは、列にインデックスを作成しましたsold_at。7月の販売フェッチリクエストの実行計画は次のようになります。

db=# EXPLAIN
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Index Scan using sale_fact_sold_at_ix on sale_fact  (cost=0.42..182.80 rows=4319 width=41)
   Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))P

インデックスがない場合のプランがどのようになるかを確認するにはsale_fact_sold_at_ix、トランザクション内にインデックスを配置して、すぐにロールバックします。

db=# BEGIN;
BEGIN

db=# DROP INDEX sale_fact_sold_at_ix;
DROP INDEX

db=# EXPLAIN
db-# SELECT *
db-# FROM sale_fact
db-# WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
                                   QUERY PLAN
---------------------------------------------------------------------------------

 Seq Scan on sale_fact  (cost=0.00..2435.00 rows=4319 width=41)
   Filter: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))

db=# ROLLBACK;
ROLLBACK

まず、でトランザクションを開始しましょうBEGIN。次に、インデックスを削除して実行計画を生成します。プランでは、インデックスが存在しないかのようにテーブル全体のスキャンが使用されることに注意してください。この時点では、トランザクションはまだ進行中であるため、インデックスはまだ削除されていません。インデックスを削除せずにトランザクションを完了するには、コマンドを使用してトランザクションをロールバックしROLLBACKます。



インデックスがまだ存在することを確認しましょう。

db=# \di+ sale_fact_sold_at_ix
                                 List of relations
 Schema |         Name         | Type  | Owner |   Table   |  Size
--------+----------------------+-------+-------+-----------+---------
 public | sale_fact_sold_at_ix | index | haki  | sale_fact | 2224 kB

トランザクションDDLをサポートしない他のデータベースは、異なる方法で目標を達成する可能性があります。たとえば、Oracleはあなたにインデックスをマークすることができます目に見えないし、オプティマイザはそれを無視します。



警告：あなたはトランザクション内でインデックスを削除する場合、それは競争力のある事業の閉塞につながるSELECT、INSERT、UPDATEとDELETEテーブルにトランザクションがアクティブになるまで。テスト環境では注意して使用し、生産施設での使用は避けてください。

長いプロセスを1時間の開始時に開始するようにスケジュールしないでください

投資家は、株価が10ドル、100ドル、1000ドルなどの美しいラウンド値に達すると、奇妙なことが起こる可能性があることを知っています。ここだ、彼らはそれについて書かれたものは：

[...]資産価格は予測できないほど変化する可能性があり、1株あたり50ドルや100ドルなどのラウンド値を超えます。経験の浅いトレーダーの多くは、公正な価格であると考えているため、価格が概数に達したときに資産を売買することを好みます。

この観点から、開発者は投資家とそれほど違いはありません。長いプロセスをスケジュールする必要がある場合、通常は1時間を選択します。





典型的な夜間のシステム負荷。



これにより、これらの時間帯に負荷が急上昇する可能性があります。したがって、長いプロセスをスケジュールする必要がある場合、システムが他の時間にアイドル状態になる可能性が高くなります。



また、毎回同時に開始しないように、スケジュールでランダムな遅延を使用することをお勧めします。そうすれば、この時間に別のタスクがスケジュールされていても、大きな問題にはなりません。systemdタイマーを使用している場合は、RandomizedDelaySecオプションを使用できます。

結論

この記事では、私の経験に基づいてさまざまな程度の証拠のヒントを提供します。実装が簡単なものもあれば、データベースの動作を深く理解する必要があるものもあります。データベースは最新のシステムのバックボーンであるため、作業方法の学習に費やす時間は、どの開発者にとっても良い投資です。