PostgreSQL

PostgreSQL（ポストグレス キューエル）は、拡張性とSQL準拠を強調するフリーでオープンソースの関係データベース管理システム（RDBMS）である。Postgresとしても知られている。もともとは、カリフォルニア大学バークレー校で開発されたIngresデータベースの後継としてその起源を根拠としたPOSTGRESという名前であった。1996年に、プロジェクトはSQLのサポートを反映してPostgreSQLに改名された。2007年の検討の結果、開発チームはPostgreSQLという名前とPostgresという別名を維持することを決定した。 
PostgreSQLは、原子性、整合性、独立性、耐久性 (ACID)プロパティを持つトランザクション、自動更新可能なビュー、マテリアライズドビュー、トリガ、外部キー、ストアドプロシージャを特徴としている。単一マシンからデータウェアハウスや多数の同時使用ユーザを持つWebサービスまで、さまざまなワークロードを扱えるように設計されている。macOS Serverのデフォルトデータベースであり、Linux、FreeBSD、OpenBSD、Windowsでも利用可能である。
概要.
PostgreSQLはIllustraや、Illustraを買収しその技術を採りいれたInformixとともにオブジェクト関係データベース管理システムを実装してきた。
問い合わせ言語には SQL を用いており、SQL92, の大部分と、, の一部をサポートしている。
市場シェア.
DB-Engines.comによるマーケットシェア調査では、2018年2月現在、Oracle Database、MySQL、Microsoft SQL Server に続いて4位であり、MySQL とのシェアの差は年々縮まる傾向にある。
2012年7月当時は、クラウドサービスプロバイダの によると、オープンソースDBの中でのPostgreSQLの世界シェアは Jelastic のユーザー内では14%程度であった（MySQL系 70%（MySQL 56%、MariaDB 14%）、MongoDB 15%）。日本の Jelastic のユーザー内では8%であり（MySQL系 66%（MySQL 50%、MariaDB 16%））、世界的なシェアとは状況が異なる。
特徴.
関数.
関数（ストアドファンクション）によりサーバで実行される処理のまとまりを定義できる。PostgreSQL は行を返却する関数を定義することができる。関数の出力は複数の行であり、クエリの中でテーブルと同様に扱うことができる。実行するユーザまたは定義したユーザのどちらの権限で実行されるかを指定して関数を定義できる。
関数の定義には SQL の他、分岐やループをサポートする下記の言語で実装することが可能である。言語によっては関数をデータベーストリガとして実行することもできる。
インデックス.
PostgreSQL は組み込みで以下のインデックスをサポートしている。デフォルトはB+木。また、ユーザ定義インデックスを追加することもできる。
PostgreSQL のインデックスには以下の特徴がある。
トリガ.
データベーストリガは SQL データ操作言語 (SQL DML) の文 (INSERT, UPDATE / UPDATE OF, DELETE, TRUNCATE) を実行した際に呼び出される。
利用例として、INSERT 文で挿入される値が妥当かの検証がある。
トリガが実行される条件は WHEN 句で与えることができる。
トリガはテーブルに対してのみ定義できる。
ビューに対するトリガが必要な場合には、代わりにルールを使用する。
複数のトリガが定義されている場合、アルファベット順に実行される。
トリガで実行される処理は関数として定義する。
トリガ用の関数の定義には SQL 関数は使用できないが、PL/pgSQL やその他の多くの関数用言語を使うことができる。
ルール.
ルールにより SQL の内部表現である「クエリ木」を書き換えることができる。
一般的なルールの用途は更新可能ビューを実現することであり、標準 SQL で規定される "INSTEAD OF" トリガ の代わりに用いられる。
データ型.
多くのデータ型が利用できる。
可変長文字列と可変長バイト列には最大で 1GB を格納できる。一定のサイズを上回るデータ値は TOAST と呼ばれる機能により自動的に圧縮され別領域に配置される。そのため、ページサイズ (通常8KB) を上回るサイズの行であっても保存できる。
さらに、ユーザがデータ型を追加することもでき、それに対してインデックスを作成することもできる。
利用例として、GIS 用の型を GiST インデックスで検索可能な PostGIS プロジェクトがある。
ユーザ定義オブジェクト.
ユーザはほとんどのデータベース・オブジェクトを追加できる。
PostgreSQLが規定する上限.
データベースの大きさの上限はない。テーブルのバイト数の最大は32Tbyteである。
テーブルの列は1600まで可能だが、運用上の上限はデータ型に依存する。
バキューム.
バキューム (VACUUM) とは、追記型アーキテクチャにおける不要領域を回収し、再利用またはOSに返却する処理である。
なお、バージョン8.3からはHeap-Only Tuples (HOT) が採用され、インデックスの変更を伴わない更新については、削除された行を直ちに再利用することが可能となり、バキュームの必要な頻度は下がった。
PostgreSQLは、MVCCの実現のため、追記型のアーキテクチャを採用している。
データを削除する際は実際のレコードは削除せず、該当行に削除マークを付けるのみである。
更新の際も内部的には削除と挿入を同時に行っている。
そのため、更新・削除が繰り返されるテーブルにおいては、たとえ理論的な行数が変わらなくとも、更新・運用を重ねるごとに物理的なファイルサイズが増加する。肥大化によるパフォーマンスの劣化を回避するため、次節に述べるバキューム作業を定期的に行う必要がある。
各バージョンによって以下の差異がある。
パーティショニング.
PostgreSQL 8.1 より、パーティショニングを組み込みでサポートしている。バージョンが上がる度に機能が追加されている。
テーブル・パーティショニングは継承を用いて実現する。
これは、Oracle Database 7 のパーティション・ビューに近い実装である。
テーブルを作成する際、他テーブルを「親」テーブルとして指定し、継承関係を定義できる。
「子」テーブルに挿入された行は、親テーブルを参照した際にも取得される。
親テーブルに対する列の追加やCHECK制約の定義は自動的に子テーブルにも反映されるが、外部キーや一意性制約は継承をサポートしていない。
パーティショニングされたテーブルへは親テーブルを通してアクセスする。
SELECT, UPDATE, DELETE 文は子テーブルを含むよう展開されるが、クエリの条件が CHECK 制約に適合しない子テーブルは設定により自動的に除外することもできるため効率よく処理できる。
INSERT については、バージョン10以降は宣言的テーブルパーティショニングにより子テーブルに振り分けることが出来る。バージョン9.6までは、子テーブルを直接指定するか、親テーブルにトリガを作成することで挿入先を指示して振り分けることが出来る。
レプリケーション.
PostgreSQL 9.0 より、ストリーミングレプリケーションを組み込みでサポートしている。トランザクションログを転送し、全てのデータベース・ファイルの変更をコミット後に他のサーバへ非同期に転送する。単一マスタと複数スレーブを構成でき、スレーブは参照の問い合わせを受け付ける。参照処理を複数のノードで負荷分散するスケールアウトが可能である。
PostgreSQL 10 より、ロジカルレプリケーションを組み込みでサポートしている。データベース全体ではなく、指定した部分だけをレプリケーションできる。
全文検索.
LIKE 述語と正規表現による文字列検索のほか、全文検索の機能を持つ。バージョン 8.3 以降は組み込みで、それ以前のバージョンでは contrib/tsearch2 として提供されている。この全文検索では文字列から単語を抽出し、転置テーブル (GIN) または単語空間を多次元木 (GiST) とするインデックスを作成できる。SQL/MM の全文検索とは異なり、「@@」演算子を使用する独自の文法で検索を行う。
SELECT * FROM テーブル WHERE to_tsvector(文字列カラム) @@ to_tsquery('検索クエリ')
標準では日本語の文字列から単語を抽出するパーサを持たないが、外部拡張である textsearch-ja を使用することで形態素解析による検索が可能となる。
また、標準の全文検索以外にも、PGroonga (Groonga を使用), Ludia, textsearch_senna (Senna を使用), pgestraier (Hyper Estraier), pgRast (Rast) などが外部拡張として存在する。
UPSERT機能.
PostgreSQL 9.5 より、データの新規挿入または更新を行う「UPSERT」機能が実装された。「UPSERT」機能とは、データの新規挿入（INSERT）ができれば挿入を行い、新規挿入ができなければ更新（UPDATE）を行うもの。「ON CONFLICT」句を指定すると、データ変更の衝突を適切に処理できるという。
基本的機能.
その他の特徴.
オンラインオフラインバックアップ.
バックアップには主に３つの方法があり、SQLダンプ、ファイルシステムレベルバックアップ、連続アーカイブである。それぞれに長所・短所がある。
SQLダンプではpg_dumpのようなクライアントアプリケーションでバックアップをとり、リモートホストからのバックアップが可能であるがデータベース
全体のバックアップをとる場合にはほぼ常にスーパーユーザー権限が必要である。ファイルシステムレベルバックアップでは、bashコマンドで
データファイルのバックアップをとる。この場合はオンラインバックアップやテーブル個別のバックアップは出来ない。連続アーカイブはWALを利用するものであり
アドミニストレーターにとって複雑であるが、バックアップでの内部不整合がlog replayで解決されることやWALファイルをアーカイブするだけで連続バックアップできる
利点もある。ただこの方法ではデータベースクラスター全体のバックアップとなるため要求されるストレージは大である。
性能.
CPU スケーラビリティ.
バージョン 8.1 以降 CPU スケーラビリティは大幅に改善された。
以降、改善を積み重ね、中規模のハードウェアであればスケーラビリティを十分に確保できるRDBMSとなっている。
更新処理.
過去のバージョンの PostgreSQL は他の関係データベース管理システム (RDBMS) と比較して更新処理が遅いと言われていた。追記型アーキテクチャが採用されており、更新処理は削除と挿入の組み合わせとして実現されていた。特に挿入の際にインデックスのキーを追加する必要がある点で性能差が生じていた。
しかし、バージョン 8.3 にて Heap-Only Tuples (HOT) と呼ばれる機能が採用され、インデックスのキーとなっている列の値に変更が無い場合にはインデックスの更新を回避できるようになった。HOT により約2倍のスループット向上が確認されている。
ベンチマーク.
業界標準の規格に則ったベンチマーク結果として 2007年8月の サン・マイクロシステムズ (Sun) による報告がある。以下のハードウェアを使用し、813.73 SPECjAppServer2004 JOPS@Standard であった。
周辺ツール.
管理ツール.
PostgreSQL専用もしくは各種データベース汎用のデータベース接続クライアントを利用して管理できる。
psql.
psql は PostgreSQL 付属のコマンドライン・プログラムである。
SQL を直接入力またはファイルから読み込んで実行するほか、スキーマ情報の表示などのメタコマンドを持つ。
また、SQL 構文やテーブル名などをタブキーにより入力補完できる。
pgAdmin.
pgAdmin は GUI の管理インタフェースである。
PostgreSQL License で配布される オープンソースソフトウェア (OSS) である。
多くのプラットフォームで動作し、日本語を含む多くの言語が利用できる。
また、専用の SQL エディタは psql と同様の入力補完機能を持つ。
Microsoft SQL Server Management Studio と似たインタフェースでデータベースを操作できる。
phpPgAdmin.
phpPgAdminはウェブベースの管理ツールである。PHPで作られており GPL で配布されている。名称はphpMyAdminと似ているが、製品同士の関連性は無く、操作性はかなり異なる。
レプリケーション・アドオン.
PostgreSQL はバージョン 9.0 よりレプリケーションを標準でサポートするが、サードパーティー製のオプション・ソフトウェアも利用できる。
接続インタフェース.
PostgreSQL はクライアントサーバモデルであり、データベースへの接続は主に TCP/IP ポート番号 5432 を用いて通信を行う。通信プロトコルは「フロントエンド/バックエンドプロトコル」として公開されている。
歴史.
マイケル・ストーンブレーカーは、自分が開発を主導した関係データベース管理システム (RDBMS) であるIngres の商業化事業を一段落させると、カリフォルニア大学バークリー校 (UCB) に戻り、同校で新たなプロジェクトを開始した。
プロジェクトの名称は Postgres と名づけられた。
このプロジェクト名称は、Ingres の後継を意味する Post-Ingres に由来している。
Postgresプロジェクトは、関係モデルを使ったこれまでの既存のデータベース管理システムの限界に対処することを目的として、開始された。
最も重要な課題は、これまでのDBMSではユーザが自分で新たな定義域 (ドメイン、型) を既存の単純な定義域をもとにして定義できない点であった。
Postgresでは型 (定義域) を完全にサポートするために必要な最小限の機能だけを導入した。
Postgres ではデータベースが関係を「理解」すると言われ、「規則」に従って自然な方法で関連する関係 (リレーション、表、テーブル) から情報を得ることができた。
ユーザ自身が型を定義する機能に加えて、関連を完全に記述できる機能も備えていた。
プロジェクトは他にも、追記型メディア (光ディスクなど) への対応、大容量記憶装置への対応、推論、オブジェクト指向型データモデルなどを、取り入れた。
実装においては、データベースとアプリケーションソフトウェアの間の新たなインタフェースを実験的に導入した。
プロジェクトチームは、1986年からPostgresシステムの基盤を説明した多数の論文を公表した。
1988年、Postgres のプロトタイプバージョンを発表した。
1989年6月、数名のユーザに対してPostgresバージョン1を公開した。
1990年6月、ルールシステム (RULE) を実装し直したバージョン2を公開した。
1991年、バージョン3を公開した。
バージョン3では、ルールシステムが再度実装し直され、複数の記憶装置を管理する機構が追加され、クエリエンジンが改良された。
1993年には、非常に多くのユーザが、プロジェクトに対して、サポートと追加機能を要望して、圧倒させるほどの状態となっていた。
1993年、主として雑然とした部分をきれいにしたことを内容とするバージョン4.2が公開された。
バージョン4.2が公開された後、Postgres プロジェクトは終了した。
Postgres は広く使われたが、保守はユーザに任されていた。
マイケル・ストーンブレーカーと Paula Hawthorn は、Postgresを商業化するために、Illustra Information Technologies 社を創業して、Illustraの製品名で開発・販売した。その技術は IBM Informix Dynamic Server (IDS) に導入されている。
一方、オープンソースの世界のソフトウェア開発者たちは、Postgres のコピーを入手してシステムのさらなる開発を進めることができた。
なぜならカリフォルニア大学バークリー校 (UCB) は、Postgres をオープンソースライセンスであるBSDライセンスのもとで公開していたからである。
1994年に、カリフォルニア大学バークリー校 (UCB) の大学院生であった Andrew Yu と Jolly Chen は、システムの問い合わせ言語のインタプリタを、Ingres を基にした QUEL のインタプリタから、SQL のインタプリタに置き換える作業を行った。
SQLインタプリタを備えたこのシステムは、Postgres95 と呼ばれた。
Postgres95 のソースコードは、ワールドワイドウェブに公開された。
1996年7月に Hub.Org Networking Services の Marc Fournier は、大学外の組織としては最初に、開発用サーバをオープンソースソフトウェア開発のために活動する人々に提供した。
Postgres95プロジェクトは、Bruce Momjian と Vadim B. Mikheev とともに、カリフォルニア大学バークリー校 (UCB) に由来するソースコードを堅牢にする作業を始めた。
1996年8月1日に、Postgres95の最初のオープンソースのバージョンが公開された。
1996年に Postgres95 プロジェクトは、プロジェクトの名称を、SQL のサポートをしているという意味をこめて PostgreSQLに変更した。
1997年1月に PostgreSQL プロジェクトとしての最初のバージョンである、PostgreSQL バージョン 6.0 が公開された。
このときから、インターネットを通じて世界中のデータベース開発者のグループがPostgreSQLの開発に参加し、共同作業によるプロジェクトをうまく調整する体制ができあがった。
1999年7月23日、日本PostgreSQLユーザ会が設立し、任意団体として活動を開始した。
Postgres は Illustra により商業化されていたが、Illustra は Informix に買収され、Informix は 2001年に IBM に買収された。2001年以降には PostgreSQL を商用サポートする会社が現れた。
2006年2月1日、日本PostgreSQLユーザ会は NPO として再編成された。
2011年7月、オープンソースデータベース技術者認定試験(OSS-DB Exam)において基準のRDBMSとして採用された。
バージョン履歴.
PostgreSQLのバージョンは以下のように表現される。
注目すべきユーザー.
PostgreSQLをプライマリデータベースとして使用している注目すべき組織や製品には、以下のようなものがある。
受賞.
2008年の時点で、PostgreSQL は以下の受賞をしている。