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

今日は、他のオープンソースシステムに対するPostgresの利点について話しましょう。 わずか2か月先のPG Day'16 Russiaでこのトピックを詳細に明らかにします。



「なぜPostgreSQLですか？」 PostgreSQLのスローガンは、「世界で最も先進的なオープンソースデータベース」であると主張しています。 Postgresがこのようなステートメントを作成する理由をいくつか説明します。



このシリーズの最初の部分では、データストレージ、つまりモデル、構造、タイプ、サイズ制限について説明します。 そして、 第2部では、サンプリングとデータ操作に重点を置きます。

データモデル

PostgreSQLは単なるリレーショナルではなく、オブジェクトリレーショナルDBMSです。 これにより、MySQL、MariaDB、Firebirdなどの他のオープンソースSQLデータベースに比べていくつかの利点が得られます。



オブジェクトリレーショナルデータベースの基本的な特徴は、データ型、機能、操作、ドメイン、インデックスなどのユーザーオブジェクトとその動作のサポートです。 これにより、Postgresは非常に柔軟で信頼性が高くなります。 とりわけ、複雑なデータ構造を作成、保存、取得できます。 以下のいくつかの例では、標準のRDBMSでサポートされていないネストされた複合構造が表示されます。

構造とデータ型

Postgresがサポートするデータ型の広範なリストがあります。 数値、浮動小数点、テキスト、ブール値、その他の予想されるデータ型（およびそれらの多くのバリエーション）に加えて、PostgreSQLはuuid、money、enumerated、geometric、binary型、ネットワークアドレス、ビット文字列、テキスト検索、xml、jsonのサポートを誇っています、配列、複合タイプと範囲、およびオブジェクトとログの場所を識別するためのいくつかの内部タイプ。 MySQL、MariaDB、Firebirdにもこれらのデータ型の一部があると言っても過言ではありませんが、それらすべてをサポートしているのはPostgresだけです。



それらのいくつかを詳しく見てみましょう：

ネットワークアドレス

PostgreSQLは、さまざまなタイプのネットワークアドレス用のストレージを提供します。 CIDRデータ型（クラスレスインターネットドメインルーティング）は、IPv4およびIPv6ネットワークアドレスの規則に従います。 以下に例を示します。

• 192.168.100.128/25
• 10.1.2.3/32
• 2001：4f8：3：ba：2e0：81ff：fe22：d1f1 / 128
• :: ffff：1.2.3.0/128

また、サブネットがオプションであるIPv4およびIPv6ホストに使用されるINETデータタイプも、ネットワークアドレスの格納に使用できます。 MACADDRデータタイプは、08-00-2b-01-02-03などの機器識別用のMACアドレスを格納するために使用できます。



MySQLおよびMariaDBには、ネットワークアドレスを変換するためのINET関数もありますが、ネットワークアドレスの内部ストレージ用のデータ型は提供しません。 Firebirdには、ネットワークアドレスを保存するためのタイプもありません。

多次元配列

Postgresはオブジェクトリレーショナルデータベースであるため、ほとんどの既存のデータ型に対して値の配列を格納できます。 これは、列のデータ型の指定に角かっこを追加するか、ARRAY式を使用して実行できます。 配列のサイズを指定できますが、これはオプションです。 休日のピクニックメニューを見て、配列の使用方法を示しましょう。

--  ,      CREATE TABLE holiday_picnic ( holiday varchar(50) --   sandwich text[], --  side text[] [], --   dessert text ARRAY, --  beverage text ARRAY[4] --   4-  ); --      INSERT INTO holiday_picnic VALUES ('Labor Day', '{"roast beef","veggie","turkey"}', '{ {"potato salad","green salad","macaroni salad"}, {"chips","crackers"} }', '{"fruit cocktail","berry pie","ice cream"}', '{"soda","juice","beer","water"}' );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MySQL、MariaDB、およびFirebirdはそれを行いません。 このような値の配列を従来のリレーショナルデータベースに格納するには、回避策を使用して、配列の各値に対応する行を持つ個別のテーブルを作成する必要があります。

幾何学的データ

ロケーションデータは、多くのアプリケーションにとって急速に主要な要件になりつつあります。 PostgreSQLは、点、線、円、多角形など、多くの幾何データ型を長い間サポートしてきました。 これらのタイプの1つはPATHであり、多くの連続したポイントで構成され、開いている（開始ポイントと終了ポイントが接続されていない）か、閉じている（開始ポイントと終了ポイントが接続されている）ことができます。 ハイキングコースの例を見てみましょう。 この場合、ハイキングトレイルはループであるため、始点と終点が接続されているため、私のパスは閉じられます。 一連の座標を囲む括弧は閉じたパスを示し、角括弧は開いたパスを示します。

  --      CREATE TABLE trails ( trail_name varchar(250), trail_path path ); --    , --        - INSERT INTO trails VALUES ('Dool Trail - Creeping Forest Trail Loop', ((37.172,-122.22261666667), (37.171616666667,-122.22385), (37.1735,-122.2236), (37.175416666667,-122.223), (37.1758,-122.22378333333), (37.179466666667,-122.22866666667), (37.18395,-122.22675), (37.180783333333,-122.22466666667), (37.176116666667,-122.2222), (37.1753,-122.22293333333), (37.173116666667,-122.22281666667)));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

PostgreSQL用のPostGIS拡張機能は、補助的な空間タイプ、関数、演算子、およびインデックスを使用して、ジオメトリデータの既存のプロパティを拡張します。 ロケーションのサポートを提供し、ラスターとベクターの両方のデータをサポートします。 また、データを表示、レンダリング、および操作するための多くのサードパーティの地理空間ツール（著作権およびオープンソース）との互換性も提供します。



MySQL 5.7.8およびMariaDBでは、バージョン5.3.3以降、OpenGIS地理情報標準をサポートするためにデータタイプ拡張が追加されています。 このバージョンのMySQLおよび以降のバージョンのMariaDBは、Postgresの通常のジオデータと同様のデータタイプストレージを提供します。 ただし、MySQLおよびMariaDBでは、データ値をテーブルに挿入する前に、単純なコマンドを使用して最初に幾何学的形式に変換する必要があります。 Firebirdは現在、幾何データ型をサポートしていません。

JSONサポート

PostgreSQLのJSONサポートにより、スキーマなしのデータをSQLデータベースに保存することができます。 これは、データ構造にある程度の柔軟性が必要な場合に役立ちます。たとえば、開発プロセス中に構造が変更されている場合や、データオブジェクトに含まれるフィールドが不明な場合などです。



JSONデータ型はJSON検証を提供します。これにより、Postgresに組み込まれた特殊なJSON演算子と関数を使用して、クエリを実行し、データを操作できます。 JSONBタイプも利用可能です-スペースが削除され、オブジェクトの並べ替えが保持されず、代わりに最適な方法で保存され、重複キーの最後の値のみが保存されるJSON形式のバイナリバリアントです。 JSONBは、再解析を必要としないため、オブジェクトに必要なスペースが少なく、インデックス付けと処理を高速化できるため、通常は推奨される形式です。



MySQL 5.7.8およびMariaDB 10.0.1では、組み込みJSONオブジェクトのサポートが追加されました。 ただし、これらのデータベースで使用できるJSONの関数と演算子は多数ありますが、PostgreSQLのJSONBのようにインデックス付けされていません。 Firebirdはまだトレンドに参加しておらず、JSONオブジェクトのみをテキストとしてサポートしています。

新しいタイプを作成する

Postgresデータ型の広範なリストが見つからないことが突然発生した場合、CREATE TYPEコマンドを使用して、複合、列挙、範囲、ベースなどの新しいデータ型を作成できます。 新しい複合タイプのリクエストを作成および送信する例を考えてみましょう。

  --     "wine" CREATE TYPE wine AS ( wine_vineyard varchar(50), wine_type varchar(50), wine_year int ); --  ,     "wine" CREATE TABLE pairings ( menu_entree varchar(50), wine_pairing wine ); --        ROW INSERT INTO pairings VALUES ('Lobster Tail',ROW('Stag''s Leap','Chardonnay', 2012)), ('Elk Medallions',ROW('Rombauer','Cabernet Sauvignon',2012)); /*        ( ,        ) */ SELECT (wine_pairing).wine_vineyard, (wine_pairing).wine_type FROM pairings WHERE menu_entree = 'Elk Medallions';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これらはオブジェクトリレーショナルではないため、MySQL、MariaDB、Firebirdはそのような強力な機能を提供しません。

データサイズ

PostgreSQLは多くのデータを処理できます。 現在公開されている制限は以下のとおりです。

最大データベースサイズ	無制限
最大テーブルサイズ	32 TB
最大行サイズ	1.6 TB
最大フィールドサイズ	1 GB
テーブル内の最大行数	無制限
テーブル内の列の最大数	列のタイプに応じて250〜1600
テーブル内のインデックスの最大数	無制限

作成中[約 transl。：元の記事の作者が働いている組織]データ量の増加を心配する必要がないように、インストールを自動的にスケーリングします。 ただし、データベース管理者なら誰でも知っているように、無制限の可能性に注意する必要があります。 テーブルを作成してインデックスを追加するときは、常識を使用することをお勧めします。



比較すると、MySQLとMariaDBは行サイズを65,535バイトに制限することで有名です。 Firebirdは、最大行サイズとして64 KBも提供します。 通常、データの量は、オペレーティングシステムの最大ファイルサイズによって制限されます。 PostgreSQLは多くの小さなファイルに表形式のデータを保存できるため、この制限を回避できます。 ただし、ファイルが多すぎるとパフォーマンスに悪影響を与える可能性があることに注意してください。 MySQLおよびMariaDBは、テーブル内の多数の列（データ型に応じて4.096まで）およびPostgreSQLよりも大きい個々のテーブルサイズをサポートしますが、既存のPostgresの制限を超える必要が生じるのは非常にまれな場合のみです。

データの完全性

PostgresはANSI-SQL：2008標準への準拠に努め、ACID要件（原子性、一貫性、分離、および信頼性）を満たし、参照およびトランザクションの整合性で知られています。 主キー、外部キーの制限とカスケード、一意制約、NOT NULL制約、検証制約、およびその他のデータ整合性機能により、有効なデータのみが保存されます。



MySQLおよびMariaDBは、InnoDB / XtraDBテーブルエンジンを使用してSQL標準を満たすことにコミットしています。 現在では、SQLモードを使用したSTRICTオプションが提供され、使用するデータの検証が設定されます。 それにもかかわらず、使用するモードに応じて、更新中に誤ったデータや切り捨てられたデータが挿入または作成される場合があります。 これらのデータベースは現在、CHECK制限をサポートしていません。 さらに、外部キーの参照整合性制限に関して多くの機能があります。 上記に加えて、選択したストレージエンジンによっては、データの整合性が大きく影響を受ける場合があります。 MySQL（およびMariaDBのフォーク）は、速度と効率のために、整合性と標準への準拠を交換することを秘密にしません。

まとめると

Postgresには多くの可能性があります。 オブジェクトリレーショナルモデルを使用して作成され、複雑な構造と幅広い組み込みおよびユーザー定義のデータ型をサポートします。 データ容量を強化し、データの整合性に対する敬意を払って信頼を獲得しています。 この記事で説明した高度なストレージ機能のすべてが必要なわけではありませんが、ニーズが急速に拡大する可能性があるため、すべての機能を手元に置くことには明確な利点があります。



PostgreSQLがあなたのニーズに合わないようである場合、または腰から撃つことを好む場合、Composeで提供するNoSQLデータベースに注意を払うか、言及した他のSQLデータベースについて考える必要があります。 それぞれに独自の利点があります。 Composeは、特定のタスクに適切なデータベースを選択することが非常に重要であると確信しています...時には、複数のデータベースを選択する必要があることを意味します！



もっとPostgresが必要ですか？ このシリーズの第2部では 、仮想テーブル関数、クエリ機能、インデックス作成、言語拡張など、PostgreSQLのデータ操作と検索について説明します。