Highload ++ 2017でのPostgres Proクイズタスク

Skolkovoで開催されたHigload ++ 2017で、当社のPostgres Professionalは、ニシュティヤクの従来の配布でクイズを開催しました。これは、2月のPgConf.Russia 2018のチケットでした。

この記事では、クイズの質問を調べます。

1. SELECT * FROMユーザーWHERE lower（name）= 'vasya'クエリを効果的に実行できるのはどのタイプのインデックスですか？

PostgreSQLのlower関数は、文字列を小文字にキャストします。

a）部分的
b）機能
c）カバー
d）二次

多くの人は、このクエリがクエリで使用されるlower(name)式を使用して機能インデックスを実行するのに役立つと推測しました。

 CREATE INDEX ON users ( lower(name) ); 

ただし、クイズの勝者の1人だけが、 部分インデックス

 CREATE INDEX ON users ( somefield) WHERE lower(name) = 'vasya' ; 

somefieldはusersテーブルの任意のフィールドでも有効です。

citextデータ型を使用して、コンテキストに依存しない文字列を整理することもできます。

2.次のリストのどのタイプのインデックスは存在しませんか？

a）部分的
b）二次
c）閉会
d）機能

正解は「閉じる」です。 カバーするインデックスはすでに存在し、クローズするインデックスはまだ作成者を待っています。 あなたは一つになれる！
リクエストのカバリングインデックスは、このリクエストを実行するのに十分なデータを含むインデックスです。
有用なカバリングインデックスを簡単に作成できるようにするために、 CREATE INDEX...INCLUDEコンストラクトがPostgreSQL用に開発されました。 これについては、PostgreSQLのこの機能の作成者であるAnastasia LubennikovaとIvan Frolkovのプレゼンテーションで読むことができます。 これまでのところ、コミュニティバージョンには含まれておらず、Postgres Proでのみ使用できます。

INCLUDEコンストラクトの本質は、インデックスのみのスキャンの適用性を拡張する追加の（キーではない）情報をインデックスに追加することです。 簡単な例で彼の仕事を説明します。

インデックスのみのスキャンは、結果を判断するのに十分な情報がインデックスにある場合にクエリ実行モードであり、テーブル自体を調べる必要はありません。

yにフィールドとインデックスが含まれるテーブルを用意します。

 CREATE TABLE users ( name text, score int ); REATE INDEX users_name_score ON users ( name, score ); 

クエリがテーブルで実行される場合

  SELECT name, score FROM users WHERE name = 'vasya'; 

このクエリは、インデックスのみのスキャンモードで実行できます。

タスクを複雑にしましょう。 nameフィールドの一意性も制御する必要があります。
このため、 users_name_scoreインデックスusers_name_score適切でusers_name_scoreません。 UNIQUEパラメーターを定義に追加すると、フィールドのペア（ name, score ）の一意性が制御されます。

名前フィールドに別個の一意のインデックスを作成できますが、これはリソースを大量に消費するため、ここではカバーインデックスが役立ちます。

 CREATE UNIQUE INDEX ON users ( name ) INCLUDE ( score ); 

これで、スコアフィールドの値はインデックスに追加されますが、キー部分には追加されません。これにより、1つのインデックスで2つの問題を解決できます。

3.次のリストのうち、インデックスではないものはどれですか？

a）ハッシュ
b）ジン
c）ウイスキー
d）ラム

WHISKEYインデックスはまだありません。 これが何を意味するのか考えてみてください。 特定のキーなし？ ところで、RUMがどの程度正確に復号化されるかはまだ決定されていません。

PostgreSQLには長い間Hashのようなインデックスがありましたが、実際には、WALログのサポートがついに登場した10番目のバージョンからしか使用できません。 Bツリーと比較して、HASHインデックスの有効性には大きな疑問がありました。

GIN（Generalized Inverse iNdex）は、Oleg BartunovとFedor Sigaevによって開発された一般化された逆インデックスです。 彼のデバイスに関する詳細はYegor Rogovの記事に書かれています 。 全文検索、インデックス配列、JSONおよびJSONBに適しています。

RUMは、全文検索を高速化するためにPostgres Professionalによって開発された新しいタイプのインデックスです。 RUMは Postgres Pro Enterpriseに含まれている拡張機能に実装されていますが、そのソースはオープンソースであり、 githubで入手できます。 RUMは、データを変更する場合、GINよりわずかに重いですが、インデックスに追加情報を保存することで検索を高速化できます。

4. ACIDで文字Iの実行を保証するものは何ですか？

a）トランザクションログ
b）MVCC
c）外部キー
d）共有ページキャッシュ

正解はMVCC（MultiVersion Concurrency Control）です。 ACID-DBMSデバイスの原則。アトミック性、一貫性、分離、耐久性を意味する略語。 この問題では、I-トランザクション分離に興味があり、それを実現するためにマルチバージョン性が使用されます。 各時点で、必要な数のデータベース内の各レコードのバージョンがあるため、各トランザクションはそれに対して正しいバージョンを見ることができます。 たとえば、MVCCのおかげで、レコードを変更するUPDATEのトランザクションは、他の全員が同じレコードを読み取ることを妨げません。 誰も必要としない古いバージョンは、特別なバキュームプロセス（ 真空 ）で削除されます。 ほとんどのリレーショナルデータベースシステムでは 、 競争力のあるデータアクセスによるトランザクション分離を保証するマルチバージョン性が 使用されています。

5.論理複製は同期できますか？

PostgreSQL 10は論理複製を公​​式に導入しました。 同期モードで機能するには、 CREATE SUBSCRIPTIONコマンドで定義されたサブスクリプション名をウィザードのsynchronous_standby_namesパラメーターで指定する必要があります。

6.ユーザーXは、ユーザーYが作成した一時テーブルを見つけることができますか？ もしそうなら、どのように、そうでなければ-なぜ？

たぶん 一時テーブルは通常のテーブルとほぼ同じ方法で配置され、特別なスキームのみが割り当てられます（ユーザーセッションごとに1つ）。

  SELECT nspname, ( SELECT json_agg(json_build_object('name',relname, 'owner', rolname)) FROM pg_class JOIN pg_roles ON pg_roles.oid = relowner WHERE relnamespace = pg_namespace.oid ) FROM pg_namespace WHERE nspname LIKE 'pg_temp%'; 

このクエリを使用すると、システム内のすべての一時テーブルとそれらの作成者を確認できます。

7.ストアドプロシージャを最も迅速に実行するために、どの言語で記述する必要がありますか？

トリックの質問。 もちろん、最速はCです。しかし、SQLの方が速い場合があります。 SQLの関数は、それを呼び出すクエリに含めて、最適化することができます。 Ivan Frolkovの記事で、SQLの関数のこのプロパティの詳細（PL / PGSQLと混同しないでください）を読むことができます。

一般的な手続き型言語の中で、PL / PgSQLは最速ではありません。 場合によっては、PL / PerlまたはPL / Python、さらにはPL / v8が著しく高速です。 プロシージャがデータベースの操作だけでなく、コンピューティングやデータ処理にも関与している場合、違いは特に顕著に見えます。

手続き型言語の比較に関する質問については、それらのドキュメント（ PL / Perl 、 PL / Python 、 PL / v8 ）およびIvan Panchenkoのレポートを参照できます。

8.要求で関数Fが呼び出される回数
SELECT F(a%a) FROM generate_series(-1,1) a ？

正解は1回です。 式a%aは、変数aをそれ自体で割った余りを取得a%aことを意味します。 ゼロで除算することは不可能であるため、2回目の反復で要求は失敗します。 したがって、変数a最初の値のみが関数Fマイナス1になります。 ドキュメントで generate_series関数について読むことができます 。

9. SELECT t.abc FROM tを要求します。 成功しますが、テーブルtには列abcがありません（tは実際にはテーブルです）。 これはどのように可能であり、サンプルのabc列に何が表示されますか？

これは、関数abc(t%rowtype)いる場合に可能です。 この場合の表記t.abcは、表記abc(t)と同等です。 これは文書化された機会であり、postgresにおける客観性の痕跡です。

10. PostgreSQLインスタンスはfsync = offパラメーターを使用して動作し（これを行うことはお勧めしません）、データベースにアクティブに書き込みを行うセッションを提供します。 OOMの最中に、キラーがバックエンドの1つを殺し、それに応じてポストマスターがインスタンス全体を殺しました。 その後、PostgreSQLインスタンスが再起動されました。 回復プロセスはどのように終了しますか？

ほとんどの参加者は、 fsync=offが非常に悪いことを知っていました。失敗すると、データを失う可能性があります。 しかし、どのような失敗で？ fsyncはOSバッファをディスクにフラッシュしています。 OSバッファーをディスクにフラッシュできないのはいつですか？ もちろん、OSのカーネルの機能が中断されたとき。 たとえば、ハードウェア障害中。 タスクの状態では、OSは正常に機能します。したがって、ディスク上のデータがフラッシュされ、次にpostgresが開始されると、データベースがWALから復元されることを期待するあらゆる理由があります。

謝辞

Ivan Frolov、Alexander Alekseev、Arseniy Sher、Ivan Panchenkoが質疑応答の準備に参加しました。 そして、もちろん、HighLoad ++とそのオーガナイザーに向けられた称賛を歌うことは避けられません。 いつものように、よくやったことは最高でした。 本当の休日。

クイズ結果

クイズの質問には50人のHighload参加者が回答しました。 その結果、2018年2月5〜7日にモスクワで予定されているPgConf.Russia 2018会議への7つの無料招待が行われました。