PostgreSQLとMySQLのデータベースとしての機能の違い

2つのデータベースは、Linuxオペレーティングシステム上で動作するように構築されており、Linux初心者の場合、これらのエンジンの管理は困難な場合があります。しかし、GoogleのCloud SQLやAlloyDB、AWSのAurora DB、Azure SQL for PostgreSQL and MySQLなどのクラウドサービスが、PostgreSQLやMySQLの管理者として時間を消費していた管理作業のほとんどを自動化してくれます。

データベースのプロを目指す人、あるいはスキルセットの拡大を目指す熟練したプロにとって、どちらか、あるいは両方のエンジンを習得することが最善です。。ここでは、PostgreSQLとMySQLのデータベースシステムについてハイレベルで説明します。新しいアプリケーションのためにどこに注力すべきかを理解するために、両方を見直すことは有用でしょう。

PostgreSQLとMySQLの簡単な歴史

PostgreSQLはもともと、1986年にPostgresプロジェクトとして知られる米国政府のプロジェクトとして始まりました。1994年、カリフォルニア大学バークレー校の数人の学生が、PostgresエンジンにSQL言語インタプリタを追加し、今日のPostgreSQLを開始しました。

MySQLは、スウェーデンのオープンソースリレーショナルデータベースプロジェクトとしてスタートしました。MySQLのオリジナル開発者の1人には「My」という娘がおり、MySQLのMyはここから来ているそうです。このエンジンはもともと1995年に登場し、元々の会社であるMySQL ABはSun Microsystemsに買収され、その後2010年にOracleに買収されました。OracleがMySQLを買収した後、MySQLのオリジナル作成者の1人がエンジンコードをフォークして、リレーショナルデータベースシステムのMariaDBを作成しました。

PostgreSQLとMySQLの主な違いとは？

PostgreSQLはオブジェクトリレーショナルデータベース管理システム(ORDBMS)として知られています。この概念は、機能や論理のために設計するのではなく、クラスやオブジェクトのために設計を構築するオブジェクト指向のプログラミングパラダイムから来ています。

PostgreSQLには、オブジェクト指向設計に関連する2つの主要な機能があります。

1.テーブル継承
このアイデアは、よく使われるフィールドを含む基本テーブルを定義し、そのテーブルからフィールドを継承する他のテーブルを作成することです。PostgreSQLでこれがよく使われる例として、テーブルのパーティショニングがあります。テーブル内の特定の列で範囲パーティショニングされた基本テーブルを定義し、その基本テーブルを継承してパーティション値の範囲を指定した追加のパーティションテーブルを宣言します。一般的な使用例としては、大規模なテーブルや、ETL操作のための大規模なデータ移動の実行が挙げられる。

2.関数のオーバーロード
これは、同じ名前で異なるパラメータリストを持つ複数の関数を持つことができる機能です。これは、ほとんどのプログラミング言語においては非常に一般的な機能ですが、ほとんどの RDBMS には含まれていない機能です。

MySQL は伝統的な RDBMS です。PostgreSQLと比較して興味深い利点は、さまざまなストレージエンジンをサポートしていることです。InnoDBエンジンはデフォルトのストレージエンジンで、ACID準拠（原子性、一貫性、分離、耐久性）、クラッシュリカバリ、行レベルロック、クラスタ化インデックス、レプリケーション（MyISAMなど別のストレージエンジンへのレプリケーションも可能）など、ほとんどのRDBMSが提供している機能を備えています。しかし、データのニーズに応じて活用できるストレージエンジンは他にもたくさんあります。その一例がMemoryストレージエンジンで、データを完全にメモリに保存して高速に検索することができます。このブログの続きでは、MySQLに言及する場合、InnoDBストレージ・エンジンについて説明すること計画中です。

バックアップとリカバリ機能

MySQLとPostgreSQLは、データベースのバックアップと、そのバックアップをある時点に復元することができます。バックアップの実行とその後のリストアプロセスは、かなり集中的に行われることがあります。

行のバージョン管理の類似性

PostgreSQLは、更新が発生した時に同じテーブル内に新しいバージョンの行を作成することで、多版型同時実行制御（MVCC）を実装しています。これにより、書き込み者が読み手をブロックすることなく、また読み手が書き込み者をブロックすることがないようにします。しかし、この機能の代償として、行のバージョンがガベージコレクションされない場合、テーブルが肥大化する可能性があります。vacuumと呼ばれるプロセスが、行のクリーンアップを担当します。このプロセスは通常バックグラウンドで実行されますが、必要に応じて手動で実行する必要がある場合もあります。。

MySQLにもMVCCの概念があり、書き込み操作が読み取り操作をブロックしないように、またその逆も然りです。MySQLでは、古いバージョンの行はUndo Tablespaceに保管されます。Undo Tablespaceは、ある変更をロールバックする方法に関する情報を持つレコードのコレクションです。

PostgreSQLとMySQLの開発環境の違い

MySQLにはMySQL Workbenchが、PostgreSQLにはpgAdminがあり、それぞれ独自のフリーの統合開発環境(IDE)があります。

DBeaver Community Editionはコードを整理しやすく、他のデータプラットフォームでも使えるので評判が高いです。

PostgreSQLは、MySQLに比べてデータ型が豊富です。例えば、PostgreSQLには配列データ型があり、単純または多次元の配列値のセットを格納することができます。列の配列の値には、通常の配列記法（配列内の値の数値オフセット）でアクセスすることができます。PostgreSQLには複合データ型もあり、これは基本的に行/レコードを表す型です。例えば、CustomerID、Price、SaleDateというフィールドを含むSales複合データ型を定義することができます。そして、Sales 型のテーブルにカラムを定義することができます。このカラムには、CustomerID、Price、および SaleDate フィールドが含まれます。これは、複雑なデータをテーブルの1つのカラムに格納する方法です。

PostgreSQLの高度なデータ型の最後に、cidr、inet、macaddrなどのNetwork Address型を挙げます。これらのデータ型を使用することは、ネットワークアドレスをテキストとして格納するよりも望ましいことです。なぜなら、アドレスの正確さを保証する検証ルーチンや、ネットワークに特化した組み込み関数や演算子が存在するからです。

PostgreSQLとMySQLの性能はどちらが良いですか？

データベースの性能は、常にアプリケーションの個々の必要性に偏っています。しかし、PostgreSQLとMySQLのどちらかを選択する際に考慮すべき点がいくつかあります。

PostgreSQLはMySQLよりはるかに優れたクエリ並列処理をサポートしています。クエリ並列処理はMySQL 8.0で導入されましたが、現在はWHERE句のないクラスタ化インデックススキャンにしか対応していません。一方、PostgreSQLはシーケンシャルテーブルスキャン、ビットマップヒープスキャン、インデックスオンリースキャンに並列処理を使用することができます。また、結合、集約クエリ、追加操作の並列化もサポートしています。

プランの再利用はシステム全体のパフォーマンスを向上させるため、プランキャッシュはリレーショナルデータベースにとって非常に重要な概念ですが、これはいくつかの変数に依存します。MySQL にはクエリプランをキャッシュする機能がありますが、それは非常に限定的です。MySQL のキャッシュプランは異なるセッション間で再利用できないため、セッションが終了するとすぐにプランキャッシュ内のプランは破棄されます。また、キャッシュされたプランは、プリペアド・ステートメントやストアドプロシージャ、ファンクション、トリガ内のステートメントにのみ利用することができます。同様に、PostgreSQLも共有のクエリー・プラン・キャッシュを持っていません。PostgreSQLのプリペアドステートメントはキャッシュすることができますが、プリペアド・ステートメントが閉じられると、そのステートメントはキャッシュから追い出されます。

MySQLには’クエリキャッシュ’と呼ばれる非常に面白い機能があります。このキャッシュはSELECT文のテキストとその文の関連する結果データセットをキャッシュに保存します。同じステートメントが別のセッションから入ってきた場合、ステートメントを再度パースして実行する必要なく、結果を送信することができます。これは、データが頻繁に変更されず、同じクエリが頻繁に実行されるような環境では素晴らしいことです。しかし、この機能はパーティション分割されたテーブルでは機能せず、キャッシュ内の関連するエントリにデータが変更されると、その結果は無効となります。

PostgreSQLの大きな欠点は、データベースエンジンへの新しい接続を開始するのに非常にコストがかかることです。接続を大量に行うアプリケーションでは、PostgreSQLが接続を生成する方法がボトルネックになる可能性があります。そこで、pgBouncerのような接続プールアプリケーションを用意し、作業負荷を軽減することが重要になります。MySQLの接続確立はPostgreSQLのようなオーバーヘッドを伴わないので、MySQLアプリケーションの問題ははるかに少なくなります。

性能に関して覚えておくべきもう1つの特徴は、PostgreSQLがマテリアライズド・ビューをサポートしているという事実です。マテリアライズド・ビューとは、テーブルのような形で永続化されたビューの定義のことです。これは頻繁に呼び出される複雑なクエリに対して、大きな性能向上となり得ます。MySQLにはマテリアライズド・ビューの機能はありません。

PostgreSQLとMySQLのどちらがサポートが良いですか？

PostgreSQLは完全にオープンソースのソフトウェアプロジェクトであるため、問題が発生したときにサポートを受けるのは時に困難な場合があります。しかし、プロフェッショナルサポートサービスを提供する会社がいくつかあり、PostgreSQLのWebサイトにリストアップされています。残念ながら、問題が発生したときに電話できる集中的なサポート組織はありませんので、PostgreSQL.orgが提供するリストの中から1社選ぶか、地元のコンサルティング会社を見つける必要があります。

一方、MySQLはOracleが所有しており、MySQLのEnterprise Editionを購入した場合は、Oracleのテクニカルサポートを利用することができます。

どのデータベースエンジンを選べばいいのか？

ここでは、MySQLとPostgreSQLの違いについて、概要を説明しました。これらは大規模なソフトウェアパッケージであるため、1つのブログで両者の違いをすべて説明することは不可能です。しかしPostgreSQLは、より堅牢なエンジンと（少し）優れた機能を備えていると感じています。主な懸念は、MySQLではクエリ並列処理のサポートが非常に限られているのに対し、PostgreSQLはその分野ではるかに進んでいるようです。

データベースを利用し、維持するために重要なのは、そのパフォーマンスを最適化する能力です。クライムの提供するのデータベースソリューションは、クラウドからオンプレミスまで、あらゆる場所にあるデータベースプラットフォームで、データベースパフォーマンスをより簡単に管理できるように構築されています。

関連したトピックス

• SQL ServerとMySQLリレーショナルデータベースの比較
• ユーザ・データベースの最適化とOracle支出の削減
• DPA(Database Performance Analyzer) for Open-Source Database: MySQL, MariaDB, Percona, PostgreSQL
• PostgreSQLパフォーマンス・チューニング・ツール（クエリアナライザ付き) [DPA]
• 時代はマルチ・データベース管理へ
• MySQLを最適化するための10の秘訣
• PostgreSQLがエンタープライズレベルのデータベースの選択しであるかどうか？
• Heroku Postgres と 異種DB間でのリアルタイム・レプリケーションを検証・確認[Syniti DR]
• SAP HANA で、他RDBからのレプリケーション（AWS準備編）
• 進化するクエリと PostgreSQLの台頭：そのデータベー スパフォーマンス重要性