PostgreSQLが他のオープンソースSQLデータベースよりも優れている理由。 パート2

友人たち、翻訳の第2部「PostgreSQLの方が良いですか？」に注目してください。 私たちは、それがコメントの最初の部分と同じ熱烈な議論を引き起こすことを願っています。 また、 PG Day'16 Russiaで個人的に議論を続けていきます。



PostgreSQLのスローガンは、「世界で最も先進的なオープンソースデータベース」であると主張しています。 このシリーズの最初の部分では、データストレージ（モデル、構造、型、サイズ制限）を見て、Postgresが彼の言葉を行動で確認するいくつかの理由を説明しました。 第2部では、インデックス作成、仮想テーブル、クエリ機能など、データの操作と検索について説明します。 このシリーズでは、PostgreSQLが他のオープンソースデータベース、つまりMySQL、MariaDB、Firebirdと区別されるものを見つけます。

索引付け

Postgresは、他のオープンソースデータベースにはないインデックス機能を提供します。 標準インデックスに加えて、部分インデックス、機能インデックス、GiSTおよびGINインデックスをサポートしています。 それらのいくつかをより詳細に見てみましょう。

部分インデックス

テーブルの特定のサブセットのみにインデックスを作成する場合、部分インデックスを作成できます。 たとえば、列の値が特定の条件に対応する行のみ。 この優れた機能により、適切なインデックスサイズを維持できるため、パフォーマンスが向上し、ディスク領域の使用量が削減されます。 部分インデックスの重要な側面は、インデックス列が条件を決定する列と異なる場合があることです。 たとえば、内部テスト用に作成されたアカウントではなく、有料ユーザーのアカウントのみにインデックスを作成します。

--       CREATE INDEX paying_accounts_idx ON accounts (account_id) WHERE account_type <> 'test';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MySQLでは、条件に基づいてインデックス付きの行を制限するのではなく、「部分インデックス」という用語を使用して、インデックス付きの値を特定のバイト数に切り捨てることがあります。 私たちが説明した形式の部分インデックスは、MySQLではサポートされていません。

機能インデックス

関数インデックス（または式ベースのインデックス）は、インデックス作成のために列を事前計算する関数を使用して作成できます。 新しい値には、クエリが実行されるたびに計算されるのではなく、クエリ実行の定数としてインデックスが付けられ、扱われます。 たとえば、URLのクリックをあらゆる形式で収集するWebクリックログがある場合、データを正規化するための小文字のリンクにつながるインデックスを作成できます（PostgreSQLは大文字と小文字を区別します： compose.ioおよびCompose。 ioは異なる結果と見なされます）：

 --    URL    CREATE INDEX webhits_lower_urls_idx ON webhits (lower(url));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

GISTとGIN（およびBRIN！）

GiST（汎用検索ツリー）を使用すると、Bツリー、Rツリー、およびユーザー定義のインデックスタイプを組み合わせて、高度なクエリ機能を備えたカスタムインデックスを作成できます。 GiSTは、PostGIS（1月以降すべてのPostgreSQLインストールで標準化された）およびOpenFTS（オープンソースコードを使用した全文検索エンジン）で使用されます。 PostgresはSP-GiSTもサポートしています。これにより、非常に高速な検索のためにパーティション検索インデックスを作成できます。



GIN（Generalized Inverted Index）を使用すると、複合データ型にインデックスを付けることができます。これにより、他のデータ型をさまざまな方法で組み合わせて、完全に個別の何かを作成できます。 複合データ型の詳細な説明は、このシリーズの最初の部分にあります。



GISTおよびGINインデックスを作成するための構文は次のとおりです： CREATE INDEX ... ON ... USING GIST | GIN ....非常に簡単です！



PostgreSQL 9.5では、BRIN（ブロック範囲インデックス）が導入されました。これにより、大きなテーブルをインデックス作成用の列に基づいた範囲に分割できます。 これは、クエリプランナーがリクエストで指定された範囲のみをスキャンできることを意味します。 また、範囲をインデックス付けする場合、インデックス付けに必要なディスク容量は、標準のBツリーインデックスの場合よりも大幅に少なくなります。

比較のために

私たちが検討している他のSQLデータベースは、機能インデックスに関してギャップを減らします。 MySQL 5.7.6では、機能インデックスとして使用できる生成された列が導入されました。 MariaDBでは、仮想（「生成」または「計算」とも呼ばれる）列がバージョン5.2に登場しましたが、組み込み関数を使用して列を作成することのみをサポートしています（ユーザー定義関数はありません）。 バージョン2.0では、Firebirdは計算列を使用したインデックス式を導入しました。 ただし、これらのデータベースはいずれも部分インデックス、GiSTインデックス、またはGINインデックスをサポートしていません。 さらに、最初の部分で、ネイティブJSONデータ型はこれらのデータベースでインデックス付けできないことを説明しました。



すべてのインデックスを設定し、パフォーマンスを分析する場合は、mySidewalkのMatt Barrの記事「Simple PostgreSQL Index Verification」を必ず読んでください。

仮想テーブル関数

多くのクエリには仮想テーブルが必要です。 比較しているすべてのSQLデータベースは、何らかの仮想テーブル機能を提供します。 PostgreSQLはさらに多くを提供します。

CTEと再帰

Postgresは、 WITH句を使用した共通テーブル式（CTE）をサポートしています。 この機能は、 PostgreSQLの記事-Series、RandomおよびWithで実証されています 。 CTEを使用すると、クエリ内で仮想テーブルを作成し、論理的な操作のシーケンスを表現できます。 したがって、クエリの別の部分にネストされたクエリを使用して作成された仮想テーブルよりも、読み取りとテストがはるかに簡単です。 PostgreSQL CTEも再帰的に使用できます。 この便利な機能を使用すると、返されるデータのレベルがなくなるまで、クエリを繰り返し参照しながら階層をたどることができます。 以下は、トピック分類法でレベル、テーマ、および親関係を識別する再帰的CTEの例です。

 --    CTE WITH RECURSIVE topic_taxonomy_recursive (level, parent_topic_name, topic_name) AS ( SELECT 1, tt.parent_topic_name, tt.topic_name FROM topic_taxonomy tt WHERE tt.parent_topic_name = 'All Topics' UNION ALL SELECT ttr.level + 1, tt.parent_topic_name, tt.topic_name FROM topic_taxonomy_recursive ttr, topic_taxonomy tt WHERE ttr.topic_name = tt.parent_topic_name ) SELECT level, parent_topic_name, topic_name FROM topic_taxonomy_recursive;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MySQLおよびMariaDBはWITH句を使用しないため、正式にはCTEをサポートしません。 ネストされたクエリを使用して、これらのデータベースに派生テーブルを作成できますが、再帰は許可されません。 さらに、MySQLのクエリオプティマイザーがバージョン5.6のリリース以降に改善されたという事実にもかかわらず、このデータベースのネストされたクエリは問題で知られており、パフォーマンスに大きな影響を与える可能性があります。 この点でのFirebirdはMySQLおよびMariaDBよりも優れており、機能的にはPostgresと一致しています。CTEwith WITHをサポートし、再帰の可能性を提供します。

マテリアライズドビュー

マテリアライズドビューは、PostgreSQLがサポートするもう1つの便利な仮想テーブル機能です。 通常のビューと同様に、頻繁に使用するクエリの結果を表しますが、違いは結果が通常のテーブルのようにディスクに保存されることです。 マテリアライズドビューにインデックスを付けることができます。 さらに、呼び出されるたびに再作成される通常のビューとは異なり、保存された結果を持つビューは時間の経過とともにコミットされます。 意図的に更新しない限り、更新されません。 これにより、マテリアライズドビューを使用したクエリの実行速度が大幅に向上します。 通常のビューを使用したり、複雑なテーブル結合を行ったり、クエリでグループ化機能を実行したりする代わりに、必要なデータがすべて準備されてディスク上で待機しているマテリアライズドビューを使用します。 格納された結果を使用してマテリアライズドビューのデータを更新する必要がある場合、REFRESHコマンドを使用してオンデマンドでこれを実行できます。 以下に、収入の要約データを提供するマテリアライズドビューの例を示します。

 --   ,      CREATE MATERIALIZED VIEW aggregatedMonthlyRevenue (year, month, total_revenue) AS ( SELECT date_part('year', date) AS year, date_part('month', date) AS month, SUM(revenue) AS total_revenue FROM revenue WHERE date >= '2014-01-01' GROUP BY date_part('year', date), date_part('month', date) ORDER BY date_part('year', date), date_part('month', date) ); --  ,   REFRESH MATERIALIZED VIEW aggregatedMonthlyRevenue;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Firebird、MySQL、MariaDBはマテリアライズドビューをサポートしていませんが、通常のテーブルを作成し、ストアドプロシージャまたはトリガーを使用して必要に応じて更新することにより、これらのデータベースで回避策を使用できます。

クエリ機能

Postgresには広範なクエリ機能があります。



前のセクションで既にWITHについて少し説明しました。 SELECTクエリで使用できる追加の関数をいくつか見てみましょう。

クエリ集約

PostgreSQLは、SELECTステートメント間の相互作用のためにUNION、INTERSECT、およびEXCEPT句を提供します。 UNIONは、2番目のSELECTクエリの結果を最初のSELECTクエリの結果に追加します。 INTERSECTは、両方のSELECTステートメントに一致する行のみを返します。 EXCEPTは、2番目のSELECTクエリの行と一致しない最初のSELECTクエリの行のみを返します。 EXCEPTを使用した例を見てみましょう。ここでは、過去1週間にユーザーがメールを受信して​​返信した場合を除き、ユーザーの連絡先情報を返します。

 /*      ,        */ SELECT c.lastName, c.firstName, c.email FROM customers c EXCEPT SELECT e.lastName, e.firstName, e.email FROM email_log e WHERE e.email_date > current_date - interval '7 days' AND e.email_action_date > current_date - interval '7 days' AND email_action_type = 'response';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MySQL、MariaDB、およびFirebirdはUNIONをサポートしていますが、INTERSECTまたはEXCEPTのいずれもサポートしていません。 ただし、クエリでjoinおよびEXISTS句を使用すると、PostgreSQLと同じ結果を得ることができます。 しかし、要求はより複雑になります。

ウィンドウ関数

ウィンドウ関数は、結果のいくつかの行の上にある集合関数です（サブセットに「ウィンドウ」を提供する）は、非常に便利です。 本質的には、関数を実行するために、現在の行に属するセクション内の行を反復処理できます。 標準関数には、ROW_NUMBER（）、RANK（）、DENSE_RANK（）、およびPERCENT_RANK（）が含まれます。 オプションでPARTITION BYおよびORDER BYとともに使用されるOVERキーワードは、ウィンドウ関数が使用されていることを示します。 例として、以下の「関数とその他」の段落では、ROW_NUMBER（）OVER ...のウィンドウ関数を使用して、一連の数値の中央値を決定しました。 ウィンドウ関数を使用したクエリのWINDOW句はオプションですが、順序を維持するためにウィンドウを作成して名前を付けることができます。



Firebird、MySQL、MariaDBは現在、ウィンドウ関数をサポートしていませんが、Firebird 3の計画時に数年前に発表されました。

横サブクエリ

LATERALキーワードをFROM句のサブクエリに適用して、サブクエリとその前に作成された他のテーブルまたは仮想テーブルとの相互参照を追加できます。 これにより、より簡単なクエリを作成できます。 これは、各行が相互参照されるテーブルに対して評価されるように機能します。これは、クエリの実行中の速度インジケータの改善を意味する場合があります。 以下は、学生のリストと彼らが最近テクノロジーに関する何かを読んだかどうかについての情報を取得したい例です。

 --    LATERAL    SELECT s.firstName, s.LastName, x.topic_name FROM students s JOIN content_log c ON c.student_id = s.id LEFT OUTER JOIN LATERAL ( SELECT t.topic_name FROM content_topics t WHERE t.parent_topic_name = 'Technology' AND t.id = c.topic_id AND c.date > current_date - interval '30 days' ) x ON true;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MySQL、Firebird、およびMariaDBは現在、横方向のサブクエリをサポートしていません。 繰り返しますが、回避策を見つけることができますが、これによりクエリがより複雑になります。



もう1つの注意：MySQLとMariaDBはFULL OUTER JOINをサポートしていませんが、UNION ALLを使用して2つのテーブルのすべての行を結合する回避策を使用できます。

機能など

PostgreSQLは、このシリーズの最初の部分で詳細に説明した特殊なデータ型をサポートするものを含む、堅牢な組み込み演算子と関数を提供します。 さらに、独自の演算子と関数（集約を含む）、およびストアドプロシージャとトリガーを作成できます。 トピックが広すぎるため（！）、すべてを詳細に調べることはできませんが、関数の簡単な例をいくつか見てみましょう。



Postgresは、クエリ言語、手続き言語、C言語、内部の4種類のユーザー定義関数をサポートしています。 各ビューは、基本データ型と複合データ型の両方を取得して返すことができます。 PostgreSQLでは、CREATE FUNCTIONコマンドは関数の作成だけでなく、ストアドプロシージャの作成にも使用されることに注意してください。



複合データ型を返す関数を作成する例を見てみましょう。

 --       "datetext" CREATE TYPE datetext AS ( date date, date_as_text text ); /*  ,           datetext */ CREATE FUNCTION show_date_as_text(date) RETURNS datetext -- this is our composite type AS $$ SELECT $1, to_char($1, 'FMMonth FMDD, YYYY') $$ LANGUAGE SQL; --     SELECT show_date_as_text('2015-01-01'); -- : (2015-01-01,"January 1, 2015")
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

一連の数値データの中央値を見つけるための実際の関数の例を次に示します。

 --  ,        CREATE FUNCTION median(numeric[]) RETURNS numeric AS $$ SELECT AVG(x.result) FROM ( SELECT result, ROW_NUMBER() OVER (ORDER BY val) as ra, ROW_NUMBER() OVER (ORDER BY val DESC) as rd FROM unnest($1) result -- notice the use of array "unnest" ) AS x WHERE x.ra BETWEEN x.rd - 1 AND x.rd + 1; $$ LANGUAGE SQL; --     SELECT median(ARRAY[1,2,3,4,5,6,7]); -- : 4
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この比較に関係する他のオープンソースSQLデータベースでは、独自の関数、ストアドプロシージャ、トリガーを作成することもできますが、Postgresが持っているのと同じ種類のデータ型とパーソナライズオプションはありません。 さらに、PGでは、独自の演算子を作成できます。 他の比較されるデータベースは、ユーザー定義演算子をサポートしていません。



Postgresを個別化する可能性は、MySQL、MariaDB、Firebirdの中で他に類を見ません。

言語拡張

PostgreSQLには多くの言語拡張機能があります。 それらのいくつかはディストリビューションの一部であり、他の多くはサードパーティの開発者を通じて利用可能です。



Composeでは、インストールのセキュリティを確保するために、PostgreSQLの信頼できる言語拡張機能のみをサポートしています。 2月にPL / Perl 、8月にPL / v8 （JavaScriptベースの手続き言語）のサポートを追加しました。 SQLベースのPL / pgSQL組み込み言語（Composeインストールでも利用可能）よりも多くの組み込み関数を持つこれらの言語拡張機能により、サーバー上のデータを操作および処理するための洗練されたスクリプトを作成できます。

まとめると

PostgreSQLは非常に機能が豊富で、多くの組み込みの「機能」と、ニーズに合わせてパーソナライズおよび拡張する無数の方法があります。 これに普遍的に認識されている信頼性と成熟度を加えると、このデータベースソリューションが大企業の努力に値する理由が明らかになります。 ただし、小規模なプロジェクトでも手頃な価格で効果的です。



Postgresが他のオープンソースSQLソリューションと区別する少数の機能についてのみ説明したという事実にもかかわらず、実際にはさらに多くの機能があります（バージョン9.5でさらに登場します！）。 この2部構成のシリーズが、PostgreSQLを選択する理由の説得力のある概要を提供することを願っています。