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, 99の大部分と、2003, 2008の一部をサポートしている。
DB-Engines.comによるマーケットシェア調査では、2018年2月現在、Oracle Database、MySQL、Microsoft SQL Server に続いて4位であり、MySQL とのシェアの差は年々縮まる傾向にある。
2012年7月当時は、クラウドサービスプロバイダの Jelastic によると、オープンソースDBの中でのPostgreSQLの世界シェアは Jelastic のユーザー内では14%程度であった(MySQL系 70%(MySQL 56%、MariaDB 14%)、MongoDB 15%)。日本の Jelastic のユーザー内では8%であり(MySQL系 66%(MySQL 50%、MariaDB 16%))、世界的なシェアとは状況が異なる。
Unix系(FreeBSD、OpenBSD、Linux、macOS、Solaris)および Microsoft Windows で動作する。Windowsにおいては、バージョン7.4以前はCygwinを必要としたが、バージョン8.0以降はネイティブで動作する。
32ビット / 64ビット の両アーキテクチャ上で動作する。32ビット版では共有バッファサイズが最大2GBに制限されるが、64ビット版では上限は無い。
配布形態は、ソースコードや RPM・APT の他、EnterpriseDB 社よりGUIインストーラが提供されている。このパッケージにはGUIの管理ツールであるpgAdminやドライバ等の追加インストーラが同梱されている。
関数(ストアドファンクション)によりサーバで実行される処理のまとまりを定義できる。PostgreSQL は行を返却する関数を定義することができる。関数の出力は複数の行であり、クエリの中でテーブルと同様に扱うことができる。実行するユーザまたは定義したユーザのどちらの権限で実行されるかを指定して関数を定義できる。
関数の定義には SQL の他、分岐やループをサポートする下記の言語で実装することが可能である。言語によっては関数をデータベーストリガとして実行することもできる。
PL/pgSQL: Oracle Databaseで用いられるPL/SQLを参考にして実装されたビルトイン言語。
PL/tcl
PL/Perl
PL/Python
PostgreSQL は組み込みで以下のインデックスをサポートしている。デフォルトはB+木。また、ユーザ定義インデックスを追加することもできる。
GiST (汎用検索ツリー, 多次元木)
SP-GiST (Space-Partitioned GiST)
GIN (汎用転置インデックス)
BRIN (Block Range INdex)
PostgreSQL のインデックスには以下の特徴がある。
必要に応じて逆順でスキャンできる。逆順スキャン用のインデックスを別に定義する必要は無い。
式インデックス (関数インデックス) を定義できる。複数の列の値を引数に取る関数の結果をインデックス化する。
部分インデックス (条件付きインデックス) を定義できる。条件を指定し、条件に適合する行のみをインデックス化することで、インデックスのサイズを縮小できる。
クエリオプティマイザ (planner) は複数のインデックスを同時に使用するクエリ実行計画を作成できる。複数のインデックスの結果をメモリ上のビットマップとして併せ、そのビットマップに対応する行をテーブルから取得する。
データベーストリガは SQL データ操作言語 (SQL DML) の文 (INSERT, UPDATE / UPDATE OF, DELETE, TRUNCATE) を実行した際に呼び出される。
利用例として、INSERT 文で挿入される値が妥当かの検証がある。
トリガが実行される条件は WHEN 句で与えることができる。
トリガはテーブルに対してのみ定義できる。
ビューに対するトリガが必要な場合には、代わりにルールを使用する。
複数のトリガが定義されている場合、アルファベット順に実行される。
トリガで実行される処理は関数として定義する。
トリガ用の関数の定義には SQL 関数は使用できないが、PL/pgSQL やその他の多くの関数用言語を使うことができる。
ルールにより SQL の内部表現である「クエリ木」を書き換えることができる。
一般的なルールの用途は更新可能ビューを実現することであり、標準 SQL で規定される "INSTEAD OF" トリガ の代わりに用いられる。
多くのデータ型が利用できる。
数値型:整数、浮動小数点数、任意の精度を持つ数値、連番
文字列:固定長、可変長
日時、日付、時刻、時間差分 (タイムゾーンの有無を指定可能)
列挙型(8.3以降)
幾何型:点、直線、線分、矩形、閉経路、開経路、多角形、円
ネットワークアドレス:IPv4 / IPv6 アドレス, MAC アドレス
UUID 型
XML 型
JSON 型:テキスト形式、バイナリ形式
可変長文字列と可変長バイト列には最大で 1GB を格納できる。一定のサイズを上回るデータ値は TOAST と呼ばれる機能により自動的に圧縮され別領域に配置される。そのため、ページサイズ (通常8KB) を上回るサイズの行であっても保存できる。
さらに、ユーザがデータ型を追加することもでき、それに対してインデックスを作成することもできる。
利用例として、GIS 用の型を GiST インデックスで検索可能な PostGIS プロジェクトがある。
ユーザはほとんどのデータベース・オブジェクトを追加できる。
データ型 (TYPE) と データの定義域 (DOMAIN)
関数 (FUNCTION) と集約 (AGGREGATE)
演算子 (OPERATOR)
型変換 (CAST)
文字コード変換 (CONVERSION)
手続き言語 (LANGUAGE)
全文検索の設定 (TEXT SEARCH CONFIGURATION)
インデックス・アクセス・メソッド
データベースの大きさの上限はない。テーブルのバイト数の最大は32Tbyteである。
テーブルの列は1600まで可能だが、運用上の上限はデータ型に依存する。
バキューム (VACUUM) とは、追記型アーキテクチャにおける不要領域を回収し、再利用またはOSに返却する処理である。
なお、バージョン8.3からはHeap-Only Tuples (HOT) が採用され、インデックスの変更を伴わない更新については、削除された行を直ちに再利用することが可能となり、バキュームの必要な頻度は下がった。
PostgreSQLは、MVCCの実現のため、追記型のアーキテクチャを採用している。
データを削除する際は実際のレコードは削除せず、該当行に削除マークを付けるのみである。
更新の際も内部的には削除と挿入を同時に行っている。
そのため、更新・削除が繰り返されるテーブルにおいては、たとえ理論的な行数が変わらなくとも、更新・運用を重ねるごとに物理的なファイルサイズが増加する。肥大化によるパフォーマンスの劣化を回避するため、次節に述べるバキューム作業を定期的に行う必要がある。
各バージョンによって以下の差異がある。
7.1 以前
データベースファイル内の未使用領域を解放しOSに返却する処理のみをサポートする。このVACUUMでは、処理中のテーブルに対して排他ロックが獲得されるため、VACUUMの間は対象テーブルへのアクセスがブロックされる。システムの規模やテーブルの行数にもよるが、本バージョンにおいてシステムの停止を伴わない運用は困難であった。
7.2
以前の動作を FULL 方式 (VACUUM FULL) とし、新たにコンカレント方式 (VACUUM) が実装された。現在、単にバキュームと言った場合、後者のコンカレントバキュームを指す。コンカレントバキュームでは、テーブルの排他ロックを伴わずに不要領域の回収を行う。不要領域に対して再利用可能フラグを付けるのみの処理となるため、コンカレントバキュームを行っても基本的にデータベースの物理的なサイズは縮小しない。しかし、以降の更新・挿入において、このとき回収した領域が優先的に使用され、更新・削除によるファイルサイズの肥大を防止できる。
7.3
インデックスもコンカレントバキュームの対象になり、肥大化から回復させるための定期的にインデックスを再編成 (REINDEX) する必要が無くなった。これによりデータベース・オブジェクトの排他ロックを要するメンテナンスが不要になり、無停止での運用が可能になった。
7.4
自動的にバキュームを行う contrib/pg_autovacuum モジュールが提供された。autovacuum はシステムを監視し、INSERT/UPDATE/DELETE の回数などの統計情報を利用して、適切なタイミングで適切なテーブルのみに対してバキュームを行う。このため、高度な知識を要すことなく、不要領域の増加を十分に抑えることが可能となった。なお、自動バキューム処理の際に参照される統計情報の記録はデフォルトでオフとなっているため、本機能を利用する際は統計情報の記録オプションもオンにする必要がある。
8.0
バキュームは多くのI/Oが必要なため、負荷の高い処理である。バキューム実行中のシステムの全体の性能悪化を防ぐため、バキュームを行う速度を制限する機能が追加された。ただし、バキューム自体の処理時間はその分多く要する。
8.1
contribより提供されていた自動バキューム (autovacuum) 機能が本体に統合された。不要領域の監視が効率化され、コマンドで発行した VACUUM との連携が可能になった。
8.2
トランザクションIDの周回がテーブル単位で管理されるようになり、定期的にデータベース単位でバキュームを行う必要が無くなった。テーブル単位のバキュームのみが必要である。また複数のバキュームを並列して実行した際の回収効率が向上した。
8.3
自動バキューム機能が標準で有効とされ、複数のテーブルに並列してバキュームを行うようになった。加えて Heap-Only Tuplesの採用により、バキューム自体の必要性が低減した。
8.4
Visibility Map で処理が必要なページを追跡するようになり、バキュームが高速化された。また空き領域のあるページを管理する Free Space Map のメモリ管理が自動化された。
9.0
VACUUM FULL が CLUSTER と類似の処理に変更され、高速化された。
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 の全文検索とは異なり、「@@」演算子を使用する独自の文法で検索を行う。
標準では日本語の文字列から単語を抽出するパーサを持たないが、外部拡張である  を使用することで形態素解析による検索が可能となる。
また、標準の全文検索以外にも、 (Groonga を使用), Ludia,  (Senna を使用),  (Hyper Estraier),  () などが外部拡張として存在する。
PostgreSQL 9.5 より、データの新規挿入または更新を行う「UPSERT」機能が実装された。「UPSERT」機能とは、データの新規挿入(INSERT)ができれば挿入を行い、新規挿入ができなければ更新(UPDATE)を行うもの。「ON CONFLICT」句を指定すると、データ変更の衝突を適切に処理できるという。
副問合せ (サブクエリ)
結合 (自然結合, 左外結合, 右外結合, 完全外結合)
制約 (外部キー, 一意性制約, CHECK制約)
バックアップ直後からデータ障害直前までの任意の時点への復旧 (Point-in-time recovery)
バックアップには主に3つの方法があり、SQLダンプ、ファイルシステムレベルバックアップ、連続アーカイブである。それぞれに長所・短所がある。
SQLダンプではpg_dumpのようなクライアントアプリケーションでバックアップをとり、リモートホストからのバックアップが可能であるがデータベース
全体のバックアップをとる場合にはほぼ常にスーパーユーザー権限が必要である。ファイルシステムレベルバックアップでは、bashコマンドで
データファイルのバックアップをとる。この場合はオンラインバックアップやテーブル個別のバックアップは出来ない。連続アーカイブはWALを利用するものであり
アドミニストレーターにとって複雑であるが、バックアップでの内部不整合がlog replayで解決されることやWALファイルをアーカイブするだけで連続バックアップできる
利点もある。ただこの方法ではデータベースクラスター全体のバックアップとなるため要求されるストレージは大である。
バージョン 8.1 以降 CPU スケーラビリティは大幅に改善された。
以降、改善を積み重ね、中規模のハードウェアであればスケーラビリティを十分に確保できるRDBMSとなっている。
7.4 以前
スケーラビリティはページ置換アルゴリズムとして採用されていた LRU により抑制されていた。ページを参照するたびにバッファ・プール全体を排他ロックしていたため、スケーラビリティは低かった。SMP 構成で 4CPU 程度が限界だった。
8.0
LRU に代わり ARC が採用された(ただし、特許侵害の回避のため途中で 2Q に変更された)。ARC によりキャッシュヒット率は向上したものの、排他制御にオーバーヘッドが生じた。また、サブトランザクションをサポートするため追加された排他制御も新たなロック競合を生んだ。スケーラビリティは以前のバージョンと比較してむしろ低下しており、2CPU 程度で頭打ちになった。
8.1
ページ置換アルゴリズムはクロックに変更され、スケーラビリティが大幅に向上した。ページの参照には共有ロックのみが必要であるため並行してアクセスが可能になった。8コア程度が上限となった。
8.2
ページを管理するハッシュテーブルのロックが16個に分割され、共有ロックの実装に使用されるスピンロックへのアクセスが分散された。他にスピンロックの実装やサブトランザクションの排他制御が改良され、16コアまでのスケーラビリティが確認されている。
9.2
EnterpriseDB の Robert Haas が Linux カーネル 3.2 および PostgreSQL 9.2 の改善により、64コア(8コア×8CPU)のマシン上でCPUスケールすることを確認した。
9.5
LWLock (Lightweight lock) において、一部、スピンロックからアトミック命令に切り替え、また、共有バッファのマッピングのハッシュテーブルのパーティション数を16から128に増やすなどの改善により、並列度が32〜64あたりでのパフォーマンスを改善した。
過去のバージョンの PostgreSQL は他の関係データベース管理システム (RDBMS) と比較して更新処理が遅いと言われていた。追記型アーキテクチャが採用されており、更新処理は削除と挿入の組み合わせとして実現されていた。特に挿入の際にインデックスのキーを追加する必要がある点で性能差が生じていた。
しかし、バージョン 8.3 にて Heap-Only Tuples (HOT) と呼ばれる機能が採用され、インデックスのキーとなっている列の値に変更が無い場合にはインデックスの更新を回避できるようになった。HOT により約2倍のスループット向上が確認されている。
業界標準の規格に則ったベンチマーク結果として 2007年8月の サン・マイクロシステムズ (Sun) による報告がある。以下のハードウェアを使用し、813.73 SPECjAppServer2004 JOPS@Standard であった。
PostgreSQL 8.2.4
Sun Java System Application Server 9.1 (商用版 GlassFish)
Solaris 10
Sun Fire T2000 (UltraSPARC T1 1.2GHz : 4スレッド × 8コア × 1個)
PostgreSQL専用もしくは各種データベース汎用のデータベース接続クライアントを利用して管理できる。
psql は PostgreSQL 付属のコマンドライン・プログラムである。
SQL を直接入力またはファイルから読み込んで実行するほか、スキーマ情報の表示などのメタコマンドを持つ。
また、SQL 構文やテーブル名などをタブキーにより入力補完できる。
pgAdmin は GUI の管理インタフェースである。
PostgreSQL License で配布される オープンソースソフトウェア (OSS) である。
多くのプラットフォームで動作し、日本語を含む多くの言語が利用できる。
また、専用の SQL エディタは psql と同様の入力補完機能を持つ。
Microsoft SQL Server Management Studio と似たインタフェースでデータベースを操作できる。
phpPgAdminはウェブベースの管理ツールである。PHPで作られており GPL で配布されている。名称はphpMyAdminと似ているが、製品同士の関連性は無く、操作性はかなり異なる。
Webmin (OSS)
Navicat for PostgreSQL (商用)
SQLMaestro (商用)
Database Master (商用)
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 を商用サポートする会社が現れた。
2001年 Command Prompt, Inc. (Mammoth Replicator; レプリケーション)
2003年 SRAOSS (PowerGres, PowerGRES Plus)
PowerGRES Plusは富士通とSRAOSSが開発したRDBMSであり、SRAOSSがPostgreSQLより開発した商用RDB『PowerGres』をベースとして、ストレージ管理部分に富士通『Symfoware Server』のものを移植している。
2005年 EnterpriseDB 社 (Postgres Plus; Oracle との互換性向上)
2005年 Greenplum 社 (BizGres; データウェアハウスやBI向け)
2006年2月1日、日本PostgreSQLユーザ会は NPO として再編成された。
2011年7月、オープンソースデータベース技術者認定試験(OSS-DB Exam)において基準のRDBMSとして採用された。
1986年 - カリフォルニア大学バークレー校 (UCB) でマイケル・ストーンブレーカーがPOSTGRESプロジェクトを発足。
1987年 - プロトタイプが完成、翌年のACM-SIGMODコンファレンスで紹介される。