SQLite vs MySQL vs PostgreSQL：リレーショナルデータベース管理システムの比較

紹介

関係データモデルは、データを行と列のテーブルに整理するもので、データベース管理ツールで主導的な存在です。今日では、NoSQLやNewSQLなど、他のデータモデルも存在しますが、関係データベース管理システム（RDBMS）は世界中でデータの保存と管理において主導的な存在です。依然として主要です。

本稿では、最も広く実装されている3つのオープンソースRDBMS、SQLite、MySQL、およびPostgreSQLを比較・対比します。具体的には、各RDBMSが使用するデータ型、それぞれの利点と欠点、および最適化される状況について探ります。

A Bit About Database Management Systems

データベースは、情報やデータの論理的にモデル化されたクラスターです。一方、データベース管理システム（DBMS）は、データベースとやり取りするコンピュータープログラムです。DBMSを使用すると、データベースへのアクセスを制御したり、データを書き込んだり、クエリを実行したり、データベース管理に関連するその他のタスクを実行したりできます。

データベース管理システムはしばしば単に「データベース」と呼ばれますが、これら2つの用語は同義ではありません。データベースは、コンピュータに保存されているものに限らず、任意のデータの集まりです。一方、DBMSは具体的にはデータベースとやり取りするためのソフトウェアを指します。

すべてのデータベース管理システムには、データがどのように格納およびアクセスされるかを構造化する基本モデルがあります。リレーショナルデータベース管理システムは、リレーショナルデータモデルを採用したDBMSです。このリレーショナルモデルでは、データがテーブルに整理されます。RDBMSのコンテキストでは、テーブルはより正式には関係と呼ばれます。関係はタプルの集まりであり、タプルはテーブル内の行を表し、それぞれのタプルは属性のセットを共有します。属性はテーブル内の列です。

ほとんどのリレーショナルデータベースは、データの管理やクエリ処理に構造化クエリ言語（SQL）を使用します。ただし、多くのRDBMSは、独自のSQL方言を使用しており、特定の制限や拡張機能を持つ場合があります。これらの拡張機能には、標準のSQLでは実行できないより複雑な操作を行うための追加機能が通常含まれます。

データ型と制約

各列にはデータ型が割り当てられ、その列に入力できる種類のエントリを指示します。さまざまなRDBMSは異なるデータ型を実装しており、これらは常に直接交換可能ではありません。一般的なデータ型には、日付、文字列、整数、およびブール値が含まれます。

データベースに整数を保存することは、単にテーブルに数値を入れるよりも微妙です。数値データ型は、正と負の両方の数値を表すことができるsigned、または正の数値のみを表すことができるunsignedになります。たとえば、MySQLのtinyintデータ型は8ビットのデータを保持でき、これは256の可能な値に相当します。このデータ型のsigned範囲は-128から127までであり、unsigned範囲は0から255までです。

データベースに許可されるデータを制御できることは重要です。場合によっては、データベース管理者が表に制約を課して、入力できる値を制限することがあります。制約は通常、特定の列に適用されますが、一部の制約はテーブル全体に適用される場合もあります。以下はSQLで一般的に使用されるいくつかの制約です。

• UNIQUE：この制約を列に適用すると、その列内の2つのエントリが同一でないことが保証されます。
• NOT NULL：この制約は、列にNULLエントリがないことを保証します。
• PRIMARY KEY： UNIQUEとNOT NULLの組み合わせであり、PRIMARY KEY制約は、列内のエントリがNULLでないことと、すべてのエントリが一意であることを保証します。
• FOREIGN KEY： FOREIGN KEYは、別のテーブルのPRIMARY KEYを参照する1つのテーブル内の列です。この制約は、2つのテーブルをリンクするために使用されます。書き込みプロセスが成功するには、FOREIGN KEY列へのエントリが親PRIMARY KEY列にすでに存在している必要があります。
• CHECK：この制約は、列に入力できる値の範囲を制限します。たとえば、アプリケーションがアラスカ州の居住者のみを対象としている場合、ZIPコード列に99501から99950の間のエントリのみを許可するCHECK制約を追加できます。

データベース管理システムについて詳しく知りたい場合は、当社の記事「NoSQLデータベース管理システムとモデルの比較」をご覧ください。

これで、一般的なリレーショナルデータベース管理システムについて説明しましたので、この記事で取り上げる3つのオープンソースのリレーショナルデータベースの最初に進みましょう：SQLite。

SQLite

SQLiteは、自己完結型でファイルベースの完全なオープンソースのRDBMSであり、移植性、信頼性、および低メモリ環境でも強力なパフォーマンスで知られています。そのトランザクションは、システムがクラッシュしたり停電が発生した場合でも、ACID準拠です。

SQLiteプロジェクトのウェブサイトでは、それを「サーバーレス」データベースと説明しています。ほとんどのリレーショナルデータベースエンジンは、プログラムが要求を中継するプロセス間通信を介してホストサーバーと通信するサーバープロセスとして実装されています。対照的に、SQLiteはデータベースにアクセスする任意のプロセスが、データベースディスクファイルに直接読み書きできるようにします。これにより、SQLiteの設定プロセスが単純化されます。サーバープロセスを構成する必要がないためです。同様に、SQLiteデータベースを使用するプログラムには構成が必要ありません。ディスクへのアクセスが必要です。

SQLiteは無料でオープンソースのソフトウェアであり、使用するために特別なライセンスは必要ありません。ただし、プロジェクトでは圧縮や暗号化を支援するいくつかの拡張機能を、一度の料金で提供しています。さらに、プロジェクトでは年間料金の商用サポートパッケージも提供しています。

SQLiteのサポートされているデータ型

SQLiteはさまざまなデータ型をサポートしており、次のようにストレージクラスに整理されています。

SQLiteの文脈では、「ストレージクラス」と「データ型」の用語は同義と見なされます。SQLiteのデータ型とSQLiteの型親和性について詳しく知りたい場合は、SQLiteの公式ドキュメントを参照してください。

SQLiteの利点

• 小さなフットプリント: その名前が示すように、SQLiteライブラリは非常に軽量です。インストールされているシステムによって使用するスペースが異なりますが、600KiB未満のスペースを占有することがあります。さらに、完全に自己完結型であり、SQLiteが動作するためにシステムにインストールする必要がある外部依存関係はありません。
• ユーザーフレンドリー: SQLiteは時々、「ゼロ構成」と呼ばれ、そのまま使用できるデータベースとして説明されます。SQLiteはサーバープロセスとして実行されないため、停止、開始、再起動する必要がなく、管理する必要がある構成ファイルもありません。これらの機能により、SQLiteのインストールからアプリケーションとの統合までの経路が合理化されます。
• ポータブル: 他のデータベース管理システムとは異なり、単一のファイルに整形されたSQLiteデータベース全体が保存されます。このファイルはディレクトリ階層内のどこにでも配置でき、取り外し可能なメディアやファイル転送プロトコルを介して共有できます。

SQLiteの欠点

• 限られた同時実行性: 複数のプロセスが同時にSQLiteデータベースにアクセスしてクエリを実行できますが、与えられた時点では1つのプロセスしかデータベースへの変更を行うことができません。これは、SQLiteが多くの他の埋め込み型データベース管理システムよりも大きな同時実行性をサポートしている一方で、MySQLやPostgreSQLのようなクライアント/サーバーRDBMSほど多くの実行をサポートできないことを意味します。
• ユーザー管理: データベースシステムには、データベースやテーブルへの定義済みのアクセス権限を持つユーザーや管理された接続をサポートする機能がしばしば付属しています。しかし、SQLiteは通常のディスクファイルに直接読み書きするため、適用可能なアクセス許可は基本的にはその下にあるオペレーティングシステムの典型的なアクセス許可のみです。そのため、特別なアクセス許可を必要とする複数のユーザーを必要とするアプリケーションにとって、SQLiteは適していない選択肢となります。
• セキュリティ: サーバーを使用するデータベースエンジンは、場合によっては、SQLiteのようなサーバーレスのデータベースよりも、クライアントアプリケーションのバグからより良い保護を提供することができます。たとえば、クライアント内の迷子のポインターがサーバー上のメモリを破損することはありません。また、サーバーが単一の永続的なプロセスであるため、クライアントサーバーデータベースはデータアクセスをより細かく制御し、より細かい粒度のロックとより良い並行性を実現できます。

SQLiteの使用時期

• 組み込みアプリケーション: SQLiteは、移植性が必要で将来の拡張が必要ないアプリケーションに最適なデータベースです。例として、単一ユーザーのローカルアプリケーション、モバイルアプリケーション、またはゲームが挙げられます。
• ディスクアクセスの置換: アプリケーションが直接ディスクにファイルの読み書きを行う必要がある場合、SQLを使用することで得られる追加の機能性とシンプルさのために、SQLiteを使用することが有益である場合があります。
• テスト: 多くのアプリケーションでは、追加のサーバープロセスを使用するDBMSで機能をテストするのは過剰になることがあります。SQLiteには、実際のデータベース操作のオーバーヘッドを避けて迅速にテストを実行できるインメモリモードがあり、テストに最適な選択肢となります。

SQLiteを使用しない場合

• 大量のデータを扱う: SQLiteは理論的にはサイズが140TBまでのデータベースをサポートできますが、ディスクドライブとファイルシステムもデータベースのサイズ要件をサポートしている必要があります。ただし、SQLiteのウェブサイトでは、1TBに近づくデータベースは、そのサイズ以上のSQLiteデータベースを管理するのが難しいため、集中型クライアントサーバーデータベースに収容することを推奨しています。
• 高い書き込み量: SQLiteは、任意の時点で1つの書き込み操作しか許可せず、それによってスループットが大幅に制限されます。アプリケーションが多くの書き込み操作や複数の同時ライターを必要とする場合、SQLiteは必要な要件を満たさない可能性があります。
• ネットワークアクセスが必要です：SQLiteはサーバーレスのデータベースであるため、データへの直接的なネットワークアクセスは提供しません。このアクセスはアプリケーションに組み込まれています。SQLiteのデータがアプリケーションとは別のマシンにある場合、ネットワークを介した高帯域幅のエンジンからディスクへのリンクが必要です。これは高コストで効率が悪い解決策であり、そのような場合にはクライアントサーバーDBMSがより良い選択肢になる場合があります。

MySQL

DB-Enginesランキングによると、MySQLは2012年以来、このサイトがデータベースの人気を追跡し始めた以来、最も人気のあるオープンソースのRDBMSです。これは多くの世界最大のウェブサイトやアプリケーション、例えばTwitter、Facebook、Netflix、Spotifyなどを支える機能豊富な製品です。MySQLを始めるのは比較的簡単です。それには、詳細なドキュメントや大規模な開発者コミュニティ、オンライン上の豊富なMySQL関連リソースが大きく貢献しています。

MySQLは速度と信頼性を重視して設計されていますが、標準SQLへの完全な準拠は犠牲にされています。MySQLの開発者は常に標準SQLへのより密接な準拠に向けて取り組んでいますが、他のSQL実装に比べてまだ遅れがあります。ただし、MySQLには、様々なSQLモードや拡張機能が付属しており、これにより標準準拠に近づけることができます。

SQLiteを使用するアプリケーションとは異なり、MySQLデータベースを使用するアプリケーションは、独立したデーモンプロセスを介してそれにアクセスします。サーバープロセスがデータベースと他のアプリケーションの間に立ち、データベースへのアクセス権をより細かく制御できるようにします。

MySQLは、その機能を拡張し、作業を容易にするためのサードパーティ製のアプリケーション、ツール、統合ライブラリを数多く生み出しました。これらのサードパーティツールの中でよく使用されているものには、phpMyAdmin、DBeaver、HeidiSQLなどがあります。

MySQLのサポートされているデータ型

MySQLのデータ型は、数値型、日付および時刻型、文字列型の3つの大きなカテゴリに分類されます。

数値型:

日付および時刻型:

文字列型:

MySQLの利点

• 人気と使いやすさ: MySQLは世界でもっとも人気のあるデータベースシステムの一つであり、MySQLでの作業経験があるデータベース管理者は多いです。同様に、MySQLデータベースのインストールや管理方法に関する印刷物やオンラインの文献も豊富にあります。これには、データベースを始めるプロセスを簡略化するためのphpMyAdminなどのサードパーティ製ツールも含まれます。
• セキュリティ: MySQLには、インストールのパスワードセキュリティレベルの設定、rootユーザーのパスワードの定義、匿名アカウントの削除、およびデフォルトですべてのユーザーにアクセス可能なテストデータベースの削除を支援するスクリプトが同梱されています。また、SQLiteとは異なり、MySQLはユーザー管理をサポートしており、ユーザーごとにアクセス権限を付与することができます。
• 速度: 特定のSQL機能を実装しないことを選択することで、MySQLの開発者は速度を重視することができました。より最近のベンチマークテストでは、PostgreSQLなどの他のRDBMSがMySQLと同等またはそれに近い速度を実現できることが示されていますが、MySQLは依然として非常に高速なデータベースソリューションとしての評判を持っています。
• レプリケーション：MySQLはさまざまな種類のレプリケーションをサポートしており、これは2台以上のホスト間で情報を共有して信頼性、可用性、および障害耐性を向上させる実践です。これは、データベースのバックアップソリューションを設定するか、データベースを水平方向にスケーリングするのに役立ちます。

MySQLのデメリット

• 既知の制限事項：MySQLは速度と使いやすさを重視して設計されているため、完全なSQL準拠よりも機能的な制限があります。たとえば、FULL JOIN句のサポートがありません。
• ライセンスとプロプライエタリな機能：MySQLはデュアルライセンスのソフトウェアで、GPLv2の下でライセンスされた無料のオープンソースコミュニティエディションと、いくつかの有料の商用エディションがプロプライエタリなライセンスでリリースされています。そのため、一部の機能やプラグインはプロプライエタリなエディションでのみ利用可能です。
• 開発の遅れ: MySQLプロジェクトが2008年にSun Microsystemsによって、その後2009年にOracle Corporationによって買収されて以来、DBMSの開発プロセスが大幅に遅れているというユーザーからの苦情があります。コミュニティが問題に迅速に対応して変更を実装する権限を持っていないため、

MySQLの使用時期

• 分散オペレーション: MySQLのレプリケーションサポートは、プライマリセカンダリアーキテクチャやプライマリプライマリアーキテクチャなどの分散データベースセットアップに最適な選択肢となります。
• ウェブサイトおよびWebアプリケーション: MySQLはインターネット上の多くのウェブサイトやアプリケーションを支えています。これは、MySQLデータベースのインストールとセットアップが非常に簡単であり、長期的にはその全体的な速度と拡張性に感謝すべきです。
• 将来の成長の見込み: MySQLのレプリケーションサポートは水平スケーリングを促進するのに役立ちます。さらに、MySQL Clusterなどの商用MySQL製品にアップグレードするプロセスは比較的簡単で、これにより別の水平スケーリングプロセスである自動シャーディングをサポートします。

MySQLを使用しない場合

• SQLの準拠が必要な場合: MySQLは完全なSQL標準を実装しようとしないため、このツールは完全にSQLに準拠していません。完全またはほぼ完全なSQL準拠がユースケースに必須である場合は、より完全に準拠したDBMSを使用した方が良いでしょう。
• 同時実行と大容量データ: MySQLは一般的に読み取り重い操作でうまく機能しますが、同時の読み書きは問題が発生することがあります。アプリケーションに多くのユーザーが同時にデータを書き込む場合は、他のRDBMS（例: PostgreSQL）がデータベースとしてより良い選択肢になるかもしれません。

PostgreSQL

PostgreSQLは、自らを「世界で最も高度なオープンソースのリレーショナルデータベース」と位置付けています。これは高度に拡張可能であり、標準に準拠して作成されました。PostgreSQLはオブジェクトリレーショナルデータベースであり、主にリレーショナルデータベースですが、テーブル継承や関数のオーバーロードなどの機能も含まれています。

Postgresは同時に複数のタスクを効率的に処理する能力を持っており、これは同時実行として知られています。これは、Readロックを使用せずにこれを実現しており、マルチバージョン同時制御（MVCC）の実装によるもので、これによりトランザクションの原子性、一貫性、分離性、および耐久性（ACID準拠とも呼ばれる）を確保しています。

PostgreSQLはMySQLほど広く使用されていませんが、pgAdminやPostbirdなど、PostgreSQLを扱いやすくするためのサードパーティ製のツールやライブラリがいくつか存在します。

PostgreSQLのサポートされるデータ型

PostgreSQLは、MySQLと同様に数値、文字列、日付時刻のデータ型をサポートしています。さらに、幾何学的形状、ネットワークアドレス、ビット文字列、テキスト検索、およびJSONエントリなどのデータ型をサポートしており、いくつかの独自のデータ型もあります。

数値型:

文字型:

日付時刻型:

幾何学的型:

ネットワークアドレス型:

ビット文字列型:

テキスト検索型:

JSON型:

その他のデータ型:

PostgreSQLの利点

• SQLの準拠: SQLiteやMySQLよりも、PostgreSQLはSQL標準に密接に準拠することを目指しています。公式のPostgreSQLドキュメントによれば、PostgreSQLは完全なコアSQL:2011準拠に必要な179の機能のうち160をサポートしており、さらに多くのオプション機能を提供しています。公式のPostgreSQLドキュメントによれば
• オープンソースおよびコミュニティ主導: PostgreSQLは完全なオープンソースプロジェクトであり、そのソースコードは大規模で熱心なコミュニティによって開発されています。同様に、Postgresコミュニティは、公式ドキュメント、PostgreSQL wiki、さまざまなオンラインフォーラムなど、DBMSの使用方法を説明する多数のオンラインリソースを維持および貢献しています。公式ドキュメントPostgreSQL wiki
• 拡張可能: ユーザーは、PostgreSQLをカタログ駆動型の操作と動的ローディングの使用を通じて、プログラムで拡張することができます。オブジェクトコードファイル（共有ライブラリなど）を指定すると、PostgreSQLは必要に応じてそれをロードします。カタログ駆動型の操作動的ローディング

PostgreSQLの欠点

• メモリの性能: 各新しいクライアント接続ごとに、PostgreSQLは新しいプロセスをフォークします。それぞれの新しいプロセスには約10MBのメモリが割り当てられますが、多くの接続を持つデータベースの場合、これはすぐに加算されます。したがって、単純な読み取り重い操作の場合、PostgreSQLは通常、MySQLなどの他のRDBMSよりもパフォーマンスが低い傾向があります。
• 人気: 近年よりも広く使用されているものの、歴史的にはMySQLに比べてPostgreSQLの人気は低かったです。これの一つの結果として、PostgreSQLデータベースを管理するのに役立つサードパーティ製ツールがまだ少ないことが挙げられます。同様に、MySQLの経験を持つデータベース管理者と比較して、Postgresの経験を持つ管理者は少ないです。

PostgreSQLを使用するタイミング

• データの整合性が重要です: PostgreSQLは2001年以来、完全なACID準拠であり、データが一貫性を保つためにマルチバージョン通貨制御を実装しています。したがって、データの整合性が重要な場合、PostgreSQLは強力なRDBMSの選択肢となります。
• 他のツールとの統合：PostgreSQLは幅広いプログラミング言語やプラットフォームと互換性があります。これは、もしもデータベースを別のオペレーティングシステムに移行したり、特定のツールと統合したりする必要がある場合、PostgreSQLデータベースを使用すれば他のDBMSよりも簡単になる可能性があることを意味します。
• 複雑な操作：PostgreSQLは、複数のCPUを活用したクエリプランをサポートしており、より高速なクエリ応答が可能です。これに加えて、複数の同時ライターを強力にサポートしているため、データウェアハウジングやオンライントランザクション処理などの複雑な操作に適しています。

PostgreSQLを使用しない場合

• 速度が重要な場合：速度を犠牲にして、PostgreSQLは拡張性と互換性を重視して設計されています。プロジェクトが可能な限り最速の読み取り操作を必要とする場合、PostgreSQLはDBMSとして最適ではありません。
• シンプルなセットアップ：豊富な機能セットと標準SQLへの強い準拠のため、Postgresはシンプルなデータベースセットアップには過剰になる場合があります。速度が必要な読み取り重視の操作では、一般的にMySQLがより実用的な選択肢です。
• 複雑なレプリケーション：PostgreSQLはレプリケーションに強力なサポートを提供していますが、それでも比較的新しい機能です。一部の構成（プライマリ-プライマリアーキテクチャなど）は、拡張機能でのみ可能です。多くのユーザーは、MySQLのレプリケーションが実装しやすいと見なしており、特に必要なデータベースおよびシステム管理の経験が不足している場合に当てはまります。

結論

今日、SQLite、MySQL、およびPostgreSQLは世界で最も人気のある3つのオープンソースの関係データベース管理システムです。それぞれ独自の特徴と制限があり、特定のシナリオで優れています。RDBMSを選択する際には、様々な変数が関与し、最速のものや最も機能が豊富なものを選ぶということはめったにありません。次回、関係データベースソリューションが必要な場合は、これらや他のツールを十分に調査し、自分のニーズに最も適したものを見つけてください。

SQLとその使用方法について詳しく学びたい場合は、SQLデータベースの管理方法チートシートを参照することをお勧めします。一方、非リレーショナル（またはNoSQL）データベースについて学びたい場合は、NoSQLデータベース管理システムの比較をご覧ください。

参考文献

• DB-Enginesランキング
• SQLite公式ドキュメント
• SQLiteはサーバーレスです
• SQLiteの適切な使用方法
• MySQL公式ドキュメント
• MySQLとPostgres 9.0のレプリケーションの比較
• PostgreSQL公式ドキュメント
• PostgreSQLの時代はついに来たのでしょうか？