MySQL移行者向けのPostgreSQL機能

先週の急なvaranioは 、MySQLを使用してPosgresに移行したが、まだ新しいデータベースを完全には使用していないすべての人のためのDevConfのボトムホールレポートを読みました。 スピーチに基づいて、この出版物が誕生しました。

PG Day'17 Russiaの準備が本格化したことをお知らせいたします。 今後のイベントの完全なスケジュールを公開しました。 アントンと一緒に個人的にリラックスしてください

DevConfのレポートは一般に好意的なレビューを引き起こしたため、何らかの理由で会議に参加できなかった人向けの記事として発行することにしました。

なぜこのようなレポートのアイデアを思いついたのですか？ 事実、PostgreSQLは明らかに誇大広告テクノロジーであり、多くがこのDBMSに切り替えています。 時には-客観的な理由で、時には-単にそれが流行しているからです。

しかし、条件付きプログラマーの一部のVasyaが昨日MySQLで書いた今日、彼はPosgresで突然書き始めました。 彼はどう書く？ はい、一般に、以前と同様に、新しいベースの最小限の機能セットのみを使用します。 実践では、DBMSがほぼ完全に使用され始めるまでに年が経過することが示されています。

ホリバーではない

すぐに免責事項：これは筋肉対進行についての記事ではありません。 オンまたはオフに行くことはあなたのビジネスです。 たとえば、Uberは何らかの理由でMySQLに切り替えました。

Oracleは称賛されなければなりません。彼らは明らかにMySQLを正しい方向に動かしています。 5.7では、デフォルトで厳格モードが作成されました。 8番目のバージョンでは、CTEとウィンドウ関数を約束し、システムテーブルのMyISAMエンジンを削除します。 つまり データベースにリソースが投資されていることは明らかであり、ウィッシュリストのユーザーは非常に真剣に研究されています。

ただし、PostgreSQLにはまだ独自の機能がたくさんあります。 その結果、私は開発者のためにベースの機能の簡単な概要を作成しようとしました。

組み込みデータ型

通常の数値と文字列に加えて、多くのタイプのデータがデータベースに組み込まれています。 同様に彼らの相互作用のための演算子。

たとえば、ipアドレスを操作するためのcidr、inet、macaddrタイプがあります。

-- ,   ip  '128.0.0.1'  cidr '127.0.0.0/24' --    && select '127.0.0.0/24'::cidr && '128.0.0.1'; --  false 

または、たとえば、タイムゾーン（タイムスタンプ）、時間間隔などの時間

 --     -? SELECT NOW() AT TIME ZONE 'America/New_York'; --        -? SELECT NOW() AT TIME ZONE 'America/New_York' - NOW() AT TIME ZONE 'Europe/Moscow'; -- : -07:00:00 

このスライドを準備していたとき、私は好奇心から100年前の1917年にUTCと比較した時間のオフセットを確認しようと決めました。

 select '1917-06-17 00:00:00 UTC' at time zone 'Europe/Moscow'; -- : 1917-06-17 02:31:19 

つまり 白雲母はUTC + 02：31：19に住んでいた

上記に加えて、UUID、JSONB、XML、ビット文字列など、他の組み込みデータ型があります。

型配列

それとは別に、「配列」のタイプを考慮してください。 配列は長く、PostgreSQLにうまく統合されています。 多次元配列、スライス、交差演算子、結合など 配列を操作するための多くの関数があります。

 ---     SELECT ARRAY [1, 2, 8, 10] && ARRAY [1, 2, 3, 4, 5]; ---      ? SELECT ARRAY [1, 2] <@ ARRAY [1, 2, 3, 4, 5] 

配列と呼ばれる非常に便利な関数があります。 引数として、出力でSELECTリクエストが提供されます-リクエストはリクエストの結果を配列の形で提供します。

逆関数があります：ネスト解除。 彼女は配列を受け取り、リクエストの結果としてそれを返します。 これは、たとえば、異なるIDを持つ複数の同一のレコードを手動で挿入する必要があるが、コピーと貼り付けを行いたくない場合に便利です。

 INSERT INTO users (id, status, added_at) SELECT user_id, 5, '2010-03-03 10:56:40' FROM unnest(array[123, 1232, 534, 233, 100500]) as u(user_id) 

独自のタイプを作成する

カスタムタイプは3つの方法で作成できます。 まず、C言語を知っている場合は、intまたはvarcharとともに基本型を作成できます。 マニュアルの例：

 CREATE TYPE box ( INTERNALLENGTH = 16, INPUT = my_box_in_function, OUTPUT = my_box_out_function ); 

つまり cstringから型を作成できる関数と、その逆の関数を作成します。 次に、この型を、たとえばテーブル宣言で使用できます。

 CREATE TABLE myboxes ( id integer, description box ); 

2番目の方法は複合型です。 たとえば、複素数を保存するには：

 CREATE TYPE complex AS ( r double precision, i double precision ); 

そして、これを使用します：

 CREATE TABLE math ( result complex ); INSERT INTO math (result) VALUES ((0.5, -0.6)::complex); SELECT (result).i FROM math; -- : -0.6 

作成できる3番目の種類のタイプは、ドメインタイプです。 ドメインタイプは、異なる名前を持つ既存のタイプの単なるエイリアスです。 ビジネスロジックに一致する名前。

 CREATE DOMAIN us_postal_code AS TEXT; 

us_postal_codeは、いくつかの抽象的なテキストやvarcharよりもセマンティックです。

独自のオペレーターを作成する

独自の演算子を実行できます。 たとえば、複素数の追加（上記の複素数型を定義しました）：

 --   , ,   SQL CREATE OR REPLACE FUNCTION sum_complex(x COMPLEX, y COMPLEX) RETURNS COMPLEX AS $$ SELECT xr + yr, xi + yi; $$ language sql; --   ""    CREATE OPERATOR + ( PROCEDURE = sum_complex, LEFTARG = COMPLEX, RIGHTARG = COMPLEX ); 

型変換のカスタムルールを作成する

真空の例で球状にしましょう。 タイプRURとUSD、および1つのタイプを別のタイプに変換するルールを作成します。 私はsiをよく知らないので、たとえば単純な複合型を作成します。

 CREATE TYPE USD AS ( sum FLOAT ); CREATE TYPE RUR AS ( sum FLOAT ); --      (  60,    ) CREATE FUNCTION usd2rur(value USD) RETURNS RUR AS $$ SELECT value.sum * 60.0; $$ LANGUAGE SQL; --    ,    "". CREATE CAST ( USD AS RUR ) WITH FUNCTION usd2rur(USD) AS ASSIGNMENT; 

実際、これですべてです。使用できるようになりました。 ルーブルには100ドルがいくらありますか？

 select '(100.0)'::usd::rur; 

結果は次のようになります。

 rur -------- (6000) (1 row) 

PostgreSQL拡張機能のタイプ

データ型とそれらに必要なすべてが記述されている拡張機能があります。 たとえば、IPアドレスのタイプとその範囲を記述するip4r拡張。

ソースhttps://github.com/RhodiumToad/ip4r/blob/master/ip4r--2.2.sqlを見ると、実際には、拡張機能はCREATE TYPE 、 CREATE OPERATOR 、 CREATE CAST一連の命令であることがわかります。など

インデックス付けルールについて説明します。 たとえば、 ip4rタイプ（IPアドレス範囲）は、 &&演算子（およびその他）のGISTインデックスでインデックス付けできます。 したがって、IPで都市を検索するためのテーブルを作成できます。

または、たとえば、 uri拡張機能があります。これにより、リンクを保存できるタイプになり、回線またはホストを簡単に引き出すことができます（まだ運用環境で試していないので、予定しています）。

指数

標準のbtree加えて、 GIN （jsonb、全文検索などの配列の一部の操作に使用可能）、 GIST 、 brinなどがあります。

部分インデックス

テーブルに1,000万行ある状況があり、そのうち100個だけがステータス「支払処理中」などである場合があります。 そして、あなたは常にこのような「処理中」ステータスを引き出します： select ... where status = 2 。

ここにインデックスが必要であることは明らかです。 しかし、このようなインデックスは多くのスペースを占有しますが、実際には非常に小さな部分が必要です。

進行中、テーブル全体ではなく、特定の条件で定義された行に対してインデックスを作成できます。

 CREATE INDEX my_money_status_idx on my_money(status) WHERE status = 2; 

このインデックスはselect * from my_money where status = 2クエリであると同時に、スペースをほとんど占有しません。

式インデックス

進行中、インデックスは1つの列ではなく、任意の式で作成できます。 たとえば、姓と名のインデックスを作成できます。

 CREATE INDEX people_names ON people ((first_name || ' ' || last_name)); 

そして、そのようなクエリはすぐに動作します：

 SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith'; 

制約

標準のUNIQUEおよびNOT NULLに加えて、他の整合性チェックもデータベースで実行できます。 ドメインタイプでは、チェックを書くことができます：

 CREATE DOMAIN us_postal_code AS TEXT CHECK( VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$' ); 

5桁または5桁、ハイフン、4桁のみがus_postal_codeのような列に分類されることを確認します。 もちろん、ここではレギュラーだけでなく、他の条件も作成できます。

また、表にチェックを書くことができます。

 CREATE TABLE users ( id integer, name text, email text, CHECK (length(name) >= 1 AND length(name) <= 300) ); 

つまり 名前には少なくとも1文字、300文字以下が必要です。

一般的に、型自体も特定の制限であり、データベースが行う追加のチェックです。 たとえば、本質的に2つの数値で構成される複合型（上記を参照）がある場合、そこに誤って文字列を挿入することはありません。

 INSERT INTO math (result) VALUES ((0.5, '')::complex); ERROR: invalid input syntax for type double precision: "" 

そのため、jsonでは何でも詰め込むことができるため、jsonbよりも複合型の方が望ましい場合があります。

表現の部分的な一意性と一意性

UNIQUEキーまたはPRIMARY KEYの単純な一意性とは異なり、条件で指定された特定の文字列セット間で進行を一意にすることができます。 たとえば、電子メールは非リモートユーザー間で一意である必要があります。

 CREATE UNIQUE INDEX users_unique_idx ON users(email) WHERE deleted = false; 

もう1つの面白いことは、1つのフィールドではなく、任意の表現によって一意性を実現できることです。 たとえば、テーブル内で2つの列の合計が繰り返されないようにすることができます。

 CREATE TABLE test_summ ( a INT, b INT ); CREATE UNIQUE INDEX test_summ_unique_idx ON test_summ ((a + b)); INSERT INTO test_summ VALUES (1, 2); INSERT INTO test_summ VALUES (3, 0); --    

制約除外

EXCLUDEキーワードを使用すると、行を挿入または更新するときに、指定した演算子によってこの行が他の行と比較されるようにできます。 たとえば、互いに素なIP範囲を含むテーブル（ &&交差演算子によってチェックされます）：

 CREATE TABLE ip_ranges ( ip_range ip4r, EXCLUDE USING gist (ip_range WITH &&) ); 

一般に、通常のUNIQUEは、 =演算子を含むEXCLUDEです。

ストアドプロシージャ

ストアドプロシージャは、SQL、pl / pgsql、javascript、（pl / v8）、pythonなどで記述できます。 たとえば、R言語でいくつかの統計を計算し、その結果のグラフを返すことができます。
これは別の大きなトピックです。このテーマに関するIvan Panchenkoのレポートを探すことをお勧めします。

CTE（共通テーブル式）

これはMySQL 8に含まれますが、それでも簡単に説明します。

CTEは簡単です。 リクエストの一部を取り出して、名前を付けて個別に取り出します。

 WITH subquery1 AS ( SELECT ... --      . ), subquery2 AS ( SELECT ... --    ,  ) SELECT * --    FROM subquery1 JOIN subquery 2 ON ... 

クエリ最適化の観点から、このような各CTEサブクエリは個別に実行されることに留意する必要があります。 これはプラスとマイナスの両方にすることができます。

たとえば、サブクエリとグループ化による20の結合がある場合、クエリプランナーは意図を理解できない可能性があり、クエリプランは最適ではありません。 その後、リクエストの一部をcteサブクエリに配置し、残りをメインリクエストでフィルタリングできます。

逆に、読みやすくするためだけにCTEでリクエストの一部を作成することにした場合は、横向きになることがあります。

CTEでは、SELECTクエリだけでなく、UPDATEも使用できます。

例：年齢が20歳以上のユーザーを更新し、同じリクエストで、更新されたユーザーの名前とその国を返します。

 with users_updated AS ( UPDATE users SET status = 4 WHERE age > 20 RETURNING id ) SELECT name, country FROM users JOIN countries ON users.country_id = countries.id WHERE id IN ( SELECT id FROM users_updated ); 

しかし、ここでは、CTEの助けを借りて、時には自分の足でうまく撃つことができることを理解する必要があります。
そのようなクエリは構文的に正しいですが、意味は完全にナンセンスです：

 WITH update1 AS ( UPDATE test SET money = money + 1 ), update2 AS ( UPDATE test SET money = money - 1 ) SELECT money FROM test; 

ルーブルを追加してからルーブルを取り除いたようで、すべてがそのまま残っているはずです。

しかし、実際には、update1とupdate2を実行すると、テーブルの初期バージョン、つまり 実際、1つの更新が他の更新を上書きすることがわかりました。 そのため、CTE内での更新では、何をしていて、なぜなのかを正確に知る必要があります。

ウィンドウ関数

ウィンドウ関数については、 https ： //habrahabr.ru/post/268983/に詳細を書きました。 ウィンドウ関数はMySQL 8でも約束されています。

雑多

フィルター

関数（COUNTやSUMなど）を集約するには、FILTER条件を追加できます。 すべての行を集約するのではなく、特定の式のみに制限します。

 SELECT count(*) FILTER (WHERE age > 20) AS old, count(*) FILTER (WHERE age <= 20) AS young FROM users; 

つまり 20歳以上の人と20歳未満の人をカウントしました。

\見る

psqlには、さまざまなオブジェクトを表示するためのコマンド（ \d 、 \dt+など）があることは誰もが知っています。
\watchと呼ばれる特別なコマンドがあります。 つまり リクエストを実行してから、書き込みます
\watch 5で、キャンセルされるまでリクエストは5秒ごとに実行されます。
これはselectだけでなく、たとえばupdateのような他のすべての場合にも機能します（たとえば、大きなテーブルを少しだけ更新する必要がある場合）。

マテリアライズドビュー

これはビューのようなもので、キャッシュされた（実体化された）だけです。 キャッシュは、REFRESH MATERIALIZED VIEWコマンドを使用して更新できます。 更新時にPostgresがSELECTクエリを更新しないようにするCONCURRENTLYキーワードもあります。

聞く/通知する

私はまだ本番環境でこれを試していないので、これが実際に適用可能かどうかはわかりません（誰かが使用した場合、コメントで経験を共有してください）。 一番下の行は、ある種のイベントをサブスクライブできることです。また、extを使用して行を送信しながら、イベントが発生したことをサブスクライバーに通知することもできます。 情報。

Fdw

外部データラッパーメカニズムを使用すると、単純なテーブルなどの外部データを使用できます。 つまり たとえば、postgresテーブル、muscleテーブル、csvファイルを結合できます。

シーケンス

SEQUENCEは、MySQL AUTO_INCREMENTのポストゴーストアナログです。 MySQLとは異なり、シーケンスはテーブルとは別個に、またはその逆に、一度に複数のテーブルを「ティック」することができます。 増分サイズ、ループなど、さまざまなパラメーターを設定できます。

結論の代わりに

これは、実際には氷山の一角です。 すべての記事が不足しているため、まだ記事には記載されていないニュアンスがたくさんあります。 ストアドプロシージャだけで本を書くことができます。 または、たとえば、現在のバージョンのsqlコマンドの完全なリストをご覧ください ： https : //www.postgresql.org/docs/9.6/static/sql-commands.html

私がこの記事で見せたかった主なもの：誇大宣伝にもかかわらず、PostgreSQLは非常に古いDBMSであり、多くのものがあり、非常にうまく拡張されています。 したがって、MySQLからそれに切り替えるときは、マニュアルを読んだり、記事を読んだりすることをお勧めします。