gihyo.jpサイトのロゴ

詳解 PostgreSQL［10/11対応］―現場で役立つ新機能と実践知識

第1章PostgreSQLの今昔を知る―20年を超える歴史、リリースサイクル、環境構築

2019-04-23

特集のはじめに

みなさんは普段、どのようなRDBMS（Relational Database Management System）をご利用でしょうか。昨今、OSS DB（Open Source Software DataBase）の需要は高まっており、DB-Enginesのデータベース人気ランキングでもOSS DBは商用DBに負けないほどの需要を持っています。その中でも特にRDBMSは需要が高く、OSS DBであるMySQLとPostgreSQLは幅広く利用されており、PostgreSQLは2017年に大きくランキングスコアを伸ばしています。

本特集では、2018年10月18日にリリースされたばかりのバージョン11と、現場の主力として使える10にフォーカスし、進化したPostgreSQLの魅力を余すことなくお伝えします。ぜひ、これを機にPostgreSQLの新しいバージョンにチャレンジしてください。

第1章では、PostgreSQLの歴史とインストール方法を解説します。続く第2章では内部構造を中心に基礎を説明し、第3章では開発に役立つ機能、第4章では運用で役立つ機能を紹介をします。本特集を通じてPostgreSQLをより深く知っていただき、明日から使える技術と知識を身に付け、ステップアップしたデータベースライフを目指しましょう。

PostgreSQLの特徴

巷では「Postgres」や「ポスグレ」の名称で呼ばれているPostgreSQLは、UNIX系（FreeBSD、OpenBSD、Linux、macOS、Solaris）およびMicrosoft Windowsで動作し、次のようなことを大切しています。

  • OSSとしてソースコードをきれいに保つこと
  • 利用しやすいライセンス形態であること
  • 標準SQL準拠を重視すること
  • トランザクションを厳格にすること
  • 制約や型が豊富でデータを適切に守ること
  • 関数やデータ型など、ユーザー独自の拡張機能が開発しやすいこと

BSDライセンスベースの使いやすいライセンス形態ときれいなソースコード、そしてAPIの充実は豊富な拡張を生み出す理由になっていて、EDB PostgresやGreenplumなどforkされた多くのソフトウェアを生み出しています。また、CHECK制約や豊富な型をはじめとしたデータを守るしくみと、厳密なトランザクションがしっかり備わっていることから、業務系、Web系を問わず多種多様なシステムで利用されています。

PostgreSQLの歴史と最新事情

PostgreSQLの誕生から21年が経ち、前身のPostgresからはすでに29年が経っています。PostgreSQLはもともとIngresの後継として開発され、名前の由来は「Post-Ingres」から来ています。

PostgreSQLは安定した稼働実績で信用を勝ち取っていますが、さらにメジャーリリースごとにすばらしい進化をしています。たとえば複数のメジャーバージョンをまとめて系統ごとで簡単に振り返っただけでも、次のような進化を遂げています。

6系（1997年）
標準SQLに準拠し、MVCC（MultiVersion Concurrency Control）などのRDBMSとしての基本的な機能を実装
7系（2000年）
制約やWAL（Write Ahead Logging）を手に入れてACID（Atomicity：不可分性、Consistency：一貫性、Isolation：独立性、Durability：永続性）を満たす
8系（2005年）
VACUUMやHOTなどの運用に直結する性能改善
9系（2010年）
レプリケーションや、OLTP（Online Transaction Processing）とDWH（Data Warehouse）両方の面で性能向上

今回取り上げるPostgreSQL 10、11よりも1つ前のバージョンである上記9系は、メジャーバージョンごとに表1の進化を遂げました。レプリケーションを皮切りに、マテリアライズドビューやパラレルクエリと、商用データベースで主力として使われている機能をOSSで実装してきました。PostgreSQL 10、11も、後述するようにさらなる進化を遂げています。

表1　PostgreSQL 9系の振り返り
バージョン内容
9.0
（2010年）		レプリケーション、一括権限変更、64ビットWindows対応、移動平均、列／条件トリガ、一意制約の遅延、排他制約
9.1
（2011年）		同期レプリケーション、外部テーブル、UNLPGGEDテーブル、近傍検索へのインデックス
9.2
（2012年）		INDEX ONLY SCAN、カスケードレプリケーション、JSON型、範囲型、pg_basebackup
9.3
（2013年）		マテリアライズドビュー、外部テーブルへの書き出し、イベントトリガ、LATERAL句、更新可能なView
9.4
（2014年）		JSONB型、ALTER SYSTEM、レプリケーションスロット
9.5
（2016年）		UPSERT機能、行単位センキュリテ制御、BRIN INDEX
9.6
（2016年）		パラレルクエリ、複数同時スタンバイ、全文検索のフレーズ検索、COPY文のreturning句対応

新しい機能が詰まったPostgreSQL 10

2017年にリリースされたPostgreSQL 10の特徴は、垂涎（すいえん）の新機能です。もちろん性能や運用についての改善もたくさん含まれていますが、PostgreSQL 10の魅力はなんと言っても新機能による表現力の向上です。

機能強化

PostgreSQL 10によって圧倒的進化を遂げた機能の代表は、9.6で追加されたパラレルクエリの強化と、postgres_fdwで接続した外部サーバが実行する集約を外部サーバが処理して、本体側に処理結果のみを返す処理（プッシュダウン）ができるようになったことです。これらの機能は、9.6では本番運用するには足りない部分がありました。PostgreSQL 10は、それを本番に導入できるすばらしい機能にまで引き上げました。postgres_fdwはPostgreSQLの拡張の話なので今回は割愛しますが、パラレルクエリについては後日公開予定の第3章で詳しく紹介します。

待望の新機能

PostgreSQL 10の機能追加の注目株は、ロジカルレプリケーションとネイティブのパーティションです。これらは同じOSS DBであるMySQLではすでに使える機能でしたが、PostgreSQLでは使えませんでした。そのため、表現力の違いからPostgreSQLを採用できないケースもたくさんありました。第4章ではそれぞれの有効なケースを紹介し、機能の詳細を紹介します。

10から強化／進化したPostgreSQL 11

リリースされたばかりのPostgreSQL 11は、新機能が多く追加されたと言うよりも、既存機能の改善や性能向上が多いことが特徴です。そのため、PostgreSQLを利用しているユーザーの多くは恩恵を受けます。機能改善や性能向上は、バージョンアップするには十分な理由です。

機能強化

PostgreSQL 11で強化された目玉機能は、Window関数とパーティションです。第3章でWindow関数を、第4章でパーティションの詳しい紹介は行いますが、SQLの表現力が増え、運用のかゆいところに手が届く強化は、PostgreSQLの魅力をより輝かせます。ほかにもCREATE INDEX文のINCLUDE句の導入など、運用改善や信頼性向上の機能もたくさんリリースされています。

性能の向上

PostgreSQL 11の性能改善は著しく、特に注目すべきはパラレルクエリの強化とJIT（Just-In-Time）コンパイラです。これらの詳しい紹介は、第3章で行います。

本特集で紹介する機能以外にも、LIMIT句のプッシュダウンのように意識しなくても同じクエリが最適化される機能などもあります。

PostgreSQLのリリースフロー

リリースされたばかりのPostgreSQL 11ですが、PostgreSQLのメジャーバージョンは、9まではx.y.z（例：9.6.11）のx.yの部分を指していました。ですが10からはx.y表記（例：10.6）となり、メジャーバージョンはxを指すことになりました。

PostgreSQLのメジャーバージョンは毎年リリースされています。PostgreSQLのリリースの流れを知っておけば、次期バージョンPostgreSQL 12のキャッチアップの際にも役立つでしょう。

リリースまでの流れ

PostgreSQLは特定の企業に依存せず、コミュニティベースで開発されているにもかかわらず、統率されたリリースフローとなっています。図1のように1年間で、新しいメジャーバージョンのα版の開発から、機能確定後のβ版のテスト、リリースに向けた安定化までを行っています。

図1　PostgreSQLの開発の流れ
図1　PostgreSQLの開発の流れ

この流れの中で、PostgreSQLの開発者どうしのコミュニケーションはメーリングリストのpgsql-hackers@postgresql.orgで行われています。ここでのやりとりはすべてWebにアーカイブされており、誰でも自由に読むことができ、単独の企業や独裁者による開発ではなく、中立公正なコミュニケーションが約束されています。

PostgreSQLのソースコードはgit.postgresql.org/git/postgresql.gitで管理されており、GitHubではありません[1]⁠。あなたが新機能やバグ対応のパッチを送りたい場合、パッチファイルを作成して先ほどのメーリングリストへ英文メールで連絡する必要があります。送られたパッチをコミュニティでチェックするしくみがCommit Festです。Commit Festの詳細は次項で説明しますが、Commit Festを乗り越えたパッチは無事に本体に取り込まれます。この流れを数度繰り返し、1年の周期でメジャーバージョンアップします。

Commit Festによるレビュー

Commit Festとは、集中的にパッチのレビューを行い、コードツリーにパッチを適用してコミットしていく期間およびプロセスのことを言います。端的に言えば「みんなでパッチをレビューしましょう」というしくみです。

Commit Festは、α版が出る前まで2ヵ月ごとのサイクルで行われます。Commit Festでは、1ヵ月かけてパッチを登録し、もう1ヵ月で登録されたパッチをレビューします。

Commit Festには次のメリットがあります。

  • 作成したパッチが放置されない
  • 一定の品質を保ちつつ、コードを取り込める

これにより、PostgreSQLのコードを高い品質に保ちながら、毎年のメジャーバージョンアップを可能にしているのです。

Commit Festは誰でも気軽に参加できます。興味がある人は、@sawada_masahikoさんのQiitaの記事「PostgreSQLコミュニティへのパッチの投げ方」をご覧ください。正しく動作するか境界テストをしたり、大きなテストデータで確認したり、typoがないかコードレビューしたりするだけでもすばらしい貢献です。コードを書く以外にもコミュニティに貢献できる場所の一つですし、誰よりも先に新機能をキャッチアップできる場所です。みなさんぜひ、Commit Festを覗いてみましょう。

コマンドによるインストール

そろそろ実際にPostgreSQLを試したくなってきたことでしょう。本節からは、最新版のPostgreSQL 11をインストールする方法を紹介します。

PostgreSQLのインストール方法はいろいろあります。コンテナを立ち上げてCLI（Command Line Interface）からインストールすることもできますし、macOS、Windowsのインストーラを使ってGUI（Graphical User Interface）でインストールすることもできます。また、クラウドサービスがセットアップ不要のDatabase as a Serviceとして提供しています。今回紹介する方法やクラウドサービスなど、ご自身の環境に合わせてお選びください。

本特集の以降では、PostgreSQL自体を指すときは「データベース」と表記し、CREATE DATABASEで作成されるデータベースは「DB」と表記します。

本節では、CLIを利用したインストール方法を解説します。本番で使うOSはLinuxであることが多いでしょう。ここでは本番でよく使われるLinuxディストリビューションであるCentOS 7での方法を紹介します。

PostgreSQLのインストール
# yum localinstall https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
# yum install postgresql11-server \
postgresql11-contrib \
ostgresql11-devel \
postgresql11-libs
postgresユーザーで実行する
# su postgres
PostgreSQLのセットアップ
$ /usr/pgsql-11/bin/initdb -E UTF-8 \
--no-locale -D /var/lib/pgsql/11/data
$ exit
# systemctl start postgresql-11.service
バージョンの確認
# psql -V
psql (PostgreSQL) 11

以上で完了です。CLIでのインストールはWeb上に多くのHow Toが公開されていますので、特に迷うことなく行えるのではないでしょうか。

ただし、PostgreSQLをインストールする際には次の注意点があります。

  • ロケールの設定
  • 文字エンコーディングの指定
  • アクセス制限の指定

以降で順に説明します。

ロケールの設定

PostgreSQLはinitdbの際にロケールを指定しない場合、OS側に設定されているロケールを使用します。この場合の多くは、ja_JP.UTF-8が選ばれるでしょう。これに伴いDBが壊れることはありませんし、エラーなどもありません。ただし、ソート関連に影響し、OSに依存してしまうことから、意図しない挙動をすることがあります。

それを防ぐためにPostgreSQLでは一般的に、ロケールとして明示的にCを指定します。これにより、OSに依存することなく、バイナリ値を基準にした一定のソートが行われ、ORDER BYの性能も向上します。

ロケールにCを指定するには、前述の手順のとおり--no-localeを指定するか、--locale=Cを指定します。--no-localeと--locale=Cは同義ですのでどちらでもかまいません。

もし指定し忘れた場合、基本的にはDBを作りなおすことになるため、無停止では変更できないと思ってください。運用が走り出すと変更がしにくい箇所ですので、最初の構築が重要です。

文字エンコーディングの設定

続いてエンコーディングの指定です。

initdbで明示的に指定しなかった場合のデフォルトではsql_asciiになります。sql_asciiではターミナルでマルチバイト文字を入力できず、たいへん苦労しますので、前述の手順のとおり明示的に-E UTF-8を指定するようにしましょう。

DB作成時のロケールとエンコーディングの指定

万が一、initdbの際にロケールとエンコーディングを指定し忘れた場合は、次の方法で対応できます。

DBの作成時に指定する例
postgres=# CREATE DATABASE DB名 \
LC_COLLATE 'C' LC_CTYPE 'C' \
ENCODING 'UTF8' TEMPLATE template0;

この方法は、initdbできない場合、たとえばAmazon RDS（Relational Database Service）などで利用する際に必要です。

アクセス制限の設定

PostgreSQLは、pg_hba.confによってアクセス制限を行います。デフォルトではlocalhost以外のアクセスは無効になっていますので、自分の環境に合わせたネットワークを指定します。

その際、注意点が2つあります。

  • trustは指定しない
  • 0.0.0.0/0は指定しない

trustはパスワード認証をしない設定です。スーパーユーザーであるpostgresなどにノンパスワードでアクセスできるためたいへん危険です。0.0.0.0/0はすべてのIPアドレスからアクセスできます。

ときどきWebの記事などで上記の設定を指定しているHow Toがありますので注意してください。もし外部からアクセスできる場所のサーバで0.0.0.0/0 trustで設定すると、悪意のあるユーザーは不正アクセスし放題となります。RDBMSは個人情報など重要なデータを保存することが多いので、正しくアクセス制限して不正アクセスから守りましょう。

設定方法は、たとえば192.169.1.0/24のネットワークからのみパスワード認証でアクセスさせたい場合、次のとおりになります。

# TYPE DATABASE USER ADDRESS        METHOD
host   all      all  192.169.1.0/24 md5

アクセス制限の解除

PostgreSQLのアクセス制限には、設定箇所が2つあるというハマりどころがあります。もう一つのファイルはpostgresql.confです。postgresql.confは、アクセス制限に限らず、PostgreSQLの全体に関わる設定をするところです。たとえばwork_memやshared_buffersの値を指定できます。

postgresql.confも、デフォルトではlocalhost以外のアクセスは無効になっています。pg_hba.confだけ変更しても、postgresql.confを変更していない場合は外部からアクセスできませんので注意してください。localhost以外からアクセスさせたい場合、次のように修正する必要があります。

変更前
listen_addresses = 'localhost'
変更後
listen_addresses = '*'

postgresql.confとpg_hba.confは、PostgreSQLサーバの設定リロードまたは再起動するまで反映されません。両ファイルを変更しても、適切に読み込んでいないため不適切な設定のまま運用される可能性がありますので、必ず再読み込みしましょう。

ここまで設定すればPostgreSQLにアクセスできるはずです。接続方法については後述します。

GUIによるインストール

CLIは苦手な方もいると思います。PostgreSQLはmacOS、Windowsともにインストーラが用意されています。各自の環境に合わせてダウンロードし、試してください。

次のとおり、インストール手順は簡単です。

インストール先のフォルダ
デフォルトのままで問題ない
Select Componentsの指定
SQLエディタであるpgadmin3のインストールの有無。pgadmin3を利用しない場合はcheckを入れずにそのままNextを選ぶ
postgres（スーパーユーザー）のパスワード
任意のパスワードを指定する
アクセスポート
デフォルトの5432で問題ない

以上でPostgreSQLのインストールが完了です。簡単に始めることができるので、SQLの勉強としてPostgreSQLを使いたい初学者の人にはお勧めです。

なお、インストーラでインストールした場合、自動起動が有効になります。WindowsやmacOSを起動したときにPostgreSQLが自動的に起動されます。

また、注意点としてインストール時にinitdbによるロケールと文字エンコーディングが指定できません。前述のロケールと文字エンコーディングを指定したDBの作成方法を利用しましょう。

Dockerによるインストール

昨今、ミドルウェアのインストールにコンテナを使うことが一般的になってきました。PostgreSQLの環境構築も例外ではなく、Dockerを利用したコンテナで簡単に作成できます。

それではさっそく、Dockerを利用してPostgreSQLの環境を構築しましょう。Dockerのインストール自体は割愛します。

PostgreSQLは、コミュニティの公式イメージをDocker Hubで公開しています。Dockerの環境があれば、次のコマンド一発でインストールできます。

PostgreSQLのインストールコマンド
# docker run -d --name postgres \
-e POSTGRES_PASSWORD={スーパーユーザーのパスワード} \
-p {フォワードするホストのポート:フォワード先のポート} \
postgres{:バージョン（指定しなければ最新）}
実際のコマンド例
# docker run -d --name postgres \
-e POSTGRES_PASSWORD=test \
-p 5432:5432 postgres:11

インストール後、コンテナは起動しています。ポートフォワードも指定していますから、SQLエディタなどで5432を指定してログインできます。次のとおり、CLIでDocker内のPostgreSQLにもログインできます。

コンテナへログイン
# docker exec -ti postgres bash
バージョンの確認
# psql --version
psql (PostgreSQL) 11.0 (Debian 11.0-1.pgdg90+2)
DBにログイン
# psql -U postgres
psql (11.0 (Debian 11.0-1.pgdg90+2))
Type "help" for help.

このようにコンテナのメリットは、お手軽に作れることです。また、簡単に捨てて作りなおすことができます。

PostgreSQLへの接続

お好みの方法で環境が構築できました。もちろんその次は接続です。PostgreSQLはほかのRDBMSと同様に、CLI、GUIの両方の接続を公式にサポートしています。

CLIとしてpsqlを使う

まずCLIでの接続を紹介します。CLIではpsqlという高機能なCLIツールを用います。前述のLinuxへのインストールの手順を行っていた場合やDockerコンテナを利用した場合、一緒にインストールされています。

今回は、Dockerコンテナへ$docker exec -tipostgres bashでログインしたあとの手順を紹介します。

接続
$ psql -U ユーザー名 -d DB名 -h ホスト名
demo=> SELECT datname FROM pg_database;
  datname
-----------
postgres
（省略）
ログアウト
demo=> \q

メタコマンド

ログアウトの際に入力したような\で始まるコマンドを、メタコマンドと呼びます。ヘルプを\?で見ることができ、メタコマンドの一覧を確認できます（表2⁠）⁠。

表2　よく使うメタコマンドの一覧
コマンド内容
\h［名前］SQLコマンドの文法ヘルプ。*で全コマンドを表示する
\xMySQLの\Gと同様に横列を縦に表示する。カラム数が多いときなどでもコンソールでの表示を崩すことなく表示できる
\lDBの一覧を表示する
\i［ファイルパス］ファイルからコマンドを読み込んで実行する
\dtテーブルの一覧を表示
\c［DB名］新しいDBに接続する
\password［ユーザー名］ユーザーのパスワードを安全に変更する
\timing実行時間の表示の有無を切り替える

メタコマンドを覚えると効率が断然上がります。psqlは強力なTAB補完をサポートしており、「⁠sel」と入力後に［tab」を入力すると「select」を補完してくれます。基本的な構文だけでなく、テーブル名やカラム名も強力に補完してくれますので、慣れるとコンソールでの作業が捗ること間違いなしです。

第3章の「Window関数の実行」ではpsqlを使っていますので、便利なメタコマンドを合わせて紹介します。

GUIでの接続

GUIでの接続では、pgadmin4からローカルに構築したPostgreSQLに対する接続を紹介します。pgadmin4はPostgreSQLに特化したブラウザで動作するDB管理ツールで、SQLを実行したり、UIからテーブルを追加したりできます。

無料で利用でき、macOSでもWindowsでもインストーラで簡単にインストールできます。UIはわかりやすいうえにちゃんと日本語化されています。

インストールして起動すると、ブラウザ上のたった2ステップだけで接続できます。

  1. Serverを右クリックして、サーバを作成する
  2. 名称、接続先を入力し保存する

接続できたら該当のDBを選択してみましょう。図2のとおり、ダッシュボードが見えていれば接続成功です。

このようにGUI上でテーブルも作成できますし、SQLを書くこともできます。手軽に始めることができ、実務でも十分使えるツールですので、この機会にお試しください。

図2　pgadmin4のダッシュボード
図2　pgadmin4のダッシュボード

まとめ

本章では、PostgreSQLの歴史から、リリースフローを通したPostgreSQLの最新情報のキャッチアップ、そしてインストールと接続までを紹介しました。このようにコミュニティベースで運用された歴史あるプロダクトは、ソースコード以外にリリースまでのしくみや機能実装のやりとりの中でも多くの学びがあります。これを機にみなさんもぜひインストールしてCommit Festにも興味を持っていただき、PostgreSQLのコミュニティに参加していただければと思います。

次の第2章では、PostgreSQLの内部構造に触れていきます。

WEB+DB PRESS

本誌最新号をチェック！
WEB+DB PRESS Vol.130

2022年8月24日発売
B5判／168ページ
定価1,628円
（本体1,480円＋税10%）
ISBN978-4-297-13000-8

  • 特集1
    イミュータブルデータモデルで始める
    実践データモデリング
    業務の複雑さをシンプルに表現！
  • 特集2
    いまはじめるFlutter
    iOS/Android両対応アプリを開発してみよう
  • 特集3
    作って学ぶWeb3
    ブロックチェーン、スマートコントラクト、NFT

おすすめ記事

記事・ニュース一覧