2022年6月9日

PostgreSQL®とは？

このページでは、PostgreSQL®の内部と外部についてご紹介します。

この投稿は万能のPostgreSQL入門です。多くの質問を扱います：

PostgreSQLデータベースとは何ですか？
PostgreSQLは何をするのか、何が特別なのか？
PostgreSQL はどのように動作するのか？
PostgreSQL はどのようにデータアーキテクチャに適合するのですか？

この投稿では

1.[PostgreSQLとは何か、なぜそれを気にする必要があるのか
2.PostgreSQLは何をしているのか
3.PostgreSQLの使用例とユーザ
4.PostgreSQLの機能
5.PostgreSQL はどのようにしているのか
6.Aiven for PostgreSQL
7.AivenでPostgreSQLを始める
8.さらに読む

PostgreSQLとは何か、なぜ使うべきか

定義と用途

Postgresとしても知られるPostgreSQLは、柔軟なオープンソースのオブジェクトリレーショナルデータベース管理システムです。PostgreSQLは、シングルマシンからデータウェアハウス、多数の同時ユーザを持つウェブサービスまで、非常に多様なユースケースを扱うことができます。PostgreSQLはSQLを使用し拡張しており（これが名前の由来です）、単なるトランザクションデータだけでなく、様々なユースケースに幅広く拡張可能です。

PostgreSQLは、企業や個人の貢献者からなるグローバル開発グループによって強力にサポートされています。

PostgreSQLにも適用される最も一般的なSQLの用語と概念の定義については、[(Postgre)SQLの概念と用語を参照してください。

PostgreSQLは何をするのですか?

リレーショナルデータベースとして、PostgreSQLはエンティティ*(文書や人など)と関係(著者関係など)を表すタプルを含むテーブル**(関係と呼ばれます)にデータを格納します。リレーションは、エンティティのプロパティ（タイトルなど）を表す固定型の属性を主キーとともに保持します。属性タイプは、アトミック（整数、浮動小数点、ブーリアンなど）または構造化（配列、ネストされたJSON、プロシージャなど）のいずれかになります。

PostgreSQLはACID属性を持つトランザクションをサポートします。つまり、トランザクションは4つの属性をサポートしなければなりません：

**トランザクションは完全に成功するか、完全に失敗するかのどちらかです。

**データベースに書き込まれるすべてのデータは、既存の制約、トリガー、カスケード、および関連する組み合わせに従わなければなりません。

分離** - *同時実行制御**の機能で、不正なトランザクションや同時実行トランザクションによってデータが破壊されないようにします。

**通常、完了したトランザクションはwrite-ahead logなどに記録される。

PostgreSQL の使用例とユーザ

db-enginesのランキングによると、PostgreSQLは4番目に人気のあるデータベース管理システム（そして最も人気のあるオープンソースDBMS）です。

今日、企業ユーザーに加え、多くのサードパーティベンダー、コンサルタント会社、独立系グループがPostgreSQLから製品を派生させ、相談し、サポートし、補助的な機能拡張を提供しています。人工知能（AI）や機械学習（ML）との統合、多くのクラウド版、ハードウェア最適化版などもあります。私たちのブログのPostgreSQLの使用例にもっと多くのことが書かれています。

KafkaとPostgreSQLによるデータパイプラインの構築
PostgreSQLを使用している業界は、コンピュータ・ソフトウェア業界が圧倒的に多く、次いで情報技術・サービス業界となっています。その他の業界としては、病院や医療、高等教育、金融サービス、通信、教育、マーケティング、広告などがあります。FBIを含む政府機関もユーザです。

PostgreSQL の特徴

PostgreSQLがサポートしている機能の種類については、PostgreSQLを参照してください。

自動的に更新可能なビュー

SQLのサポートに加えて、PostgreSQLは自動的に更新可能なビューを提供します。これは、トランザクションがビューを提供するデータを更新した時に、問い合わせの結果のビューが自動的に更新されることを意味します。

マテリアライズドビュー

マテリアライズド・ビューは、指定されたクエリの結果を含むデータ・オブジェクトです。マテリアライズド・ビューは、データベースのテーブルを直接クエリするのと同じようにクエリできます。また、マテリアライズド・ビューは、基本的にある時点のデータのスナップショットであるため、スケジュールに従って更新することができます。

トリガー

トリガーは、テーブルやビューに対するトランザクションやその他の変更に応じてコードを実行します。インベントリでアイテムが更新された時、トラッキングの目的で、更新時間と操作を実行したユーザー名を専用の列に自動的に記録したい場合があります。

外部キー

(参照: プライマリキー外部キーは、子テーブルと親テーブル間の有効な参照を保証するために存在します。例えば、ordersテーブルのすべての行は、注文自体に関連付けられた有効なcustomer参照を持つ必要があります。

ストアドプロシージャ

ストアド・プロシージャは、データベースのデータ辞書に保持され、それぞれ複数のSQLプロシージャが組み合わされている場合があります。ストアド・プロシージャは、一般的に使用されるデータ検証、データ操作、アクセス制御、その他のメソッドへのアクセスをアプリケーションに提供します。ストアド・プロシージャはユーザーによって定義され、必要なときに実行されます。

パブ/サブ論理レプリケーション

PostgreSQLはパブリッシュ/サブスクライブ論理レプリケーションの手段を提供します。基本的に、PostgreSQLインスタンスはアプリケーションとイベントコンシューマの間でイベントキャッシュとして動作します。

PostgreSQL論理レプリケーションを理解する
(この将来のPostgreSQLブログ記事で、レプリケーションプロトコルの新たな改良点を取り上げていますので、そちらも参照してください)

データ取り込みの変更

また、変更データキャプチャーの手段もある。つまり、以前のデータベース変更を巻き戻して再生することができ、ディザスタリカバリに不可欠な機能である。

テキスト検索

PostgreSQLは、例えば、ワイルドカードと組み合わせたテキスト内容に基づいて列の値を検索するSQL問い合わせをサポートすることができます：

SELECT ˶* from books WHERE column_name LIKE 'Zen%';`Copy to clipboard

これはZen and the Art of Motorcycle MaintenanceやZen Flesh, Zen Bonesのような本のタイトルを含むレコードを返すことができる。これらのワイルドカードは検索文字列の先頭にも置くことができます。同じクエリで、検索文字列を %Zen% とすると、上記のような結果が返されます。さらに、The Three Pillars of Zen や The Essential Teachings of Zen Master Hakuin のように、「Zen」が文字列の最初の単語ではないタイトルも返されます。

テキスト検索に加えて、PostgreSQLはtsvector（テキスト検索）データ型をサポートしています。

関数 to_tsvector を使うと、文字列をトークン化して tsvector を生成するリストを作成することができる。to_tsqueryは、特定の単語や語句がtsvector` に出現するか問い合わせるために使用する。

再帰クエリ

PostgreSQLには再帰問い合わせを生成するための14のメソッドがあります。また、新しいSEARCHとCYCLE機能があり、再帰問い合わせには非常に便利です。

json言語のサポート

PostgreSQLは優れた json 言語をサポートしています。テーブルの列を json または jsonb データ型として定義し、json 関数を使用することで、その内容を解析したり操作したりすることができます。

同時実行

PostgreSQLは、並行ACIDトランザクションをサポートするオープンソースデータベースの最もよく知られた例の1つです。これを実現するために、PostgreSQLは複数のロックを使用してデータベースの変更を直列化することができます。同時に変更が行われた場合、最初のトランザクションは（ロックが呼び出されるため）続行することができます。

以下に例を示します。ターミナルウィンドウを2つ開いたとする。psql`セッションで、次のように実行します：

 CREATE TABLE records (
 header text NOT NULL primary key、
 info json NOT NULL
 );

 BEGIN；
 ALTER TABLE records ADD COLUMN recent_update timestamp;`クリップボードにコピーする

別のpsqlセッションで、以下を実行してみる：

 SELECT \* FROM records;`クリップボードにコピーする

最初のトランザクションが完了するまで何も起こりません。

信頼性と災害復旧

フルページイメージをディスクに書き込む前に、PostgreSQLはライトアヘッドログ(WAL)に定期的に書き込みます。これにより、PostgreSQLはログへの書き込みを再生することでクラッシュから回復することができます。

postgresql.confのfull_page_writes`パラメータは、システム内で部分的な書き込みを妨げているものがあれば、オフに切り替えることができます。

Aivenのブログでこのトピックについてさらに読むことができます：データと災害復旧.

WALの個々のレコードはCRC-32チェックによってチェックサムされ、（リストア操作時に）レコードの内容が正しいことを検証する。ただし、データページや内部データ構造など、チェックをバイパスする項目もいくつかある。

セキュリティ

PostgreSQLはあらゆる種類のセキュリティ問題を処理する方法をたくさん持っています。例えば、サーババックエンドを起動する際に -i スイッチを使用しない限り、クライアント接続はデフォルトでローカルの Unix ソケット経由でのみ許可されます（つまり、TCP/IP ソケット経由では許可されません）。また、PG_DATA内のpg_hba.confファイルでクライアント接続をブラックリストに登録することができる。

オブジェクトに対するスーパーユーザ権限は、PostgreSQLで様々な操作を行うためのオプションの1つです。例えば、PostgreSQLのスーパーユーザアカウントだけがDBファイル自体を読むことができます。これらの権限を付与することで、テーブルデータをコピーしたり、データを自動的に発行する発行物を作成したり、購読を作成したりすることができます。これらは論理レプリケーションにとっても重要な権限です。

実際、PostgreSQLの各ユーザにはユーザ名とパスワードが必要です。ユーザをグループに割り当てることができ、そのグループ権限に基づいてテーブル、あるいは行へのアクセスを割り当てたり制限したりすることができます。

PostgreSQLはまた、特定の列、データパーティション、ネットワーク上での暗号化など、様々な暗号化オプションを提供しています。

サーバなりすまし防止は、再配置された脆弱なソケットファイルへのシンボリックリンクを作成することで機能します。

拡張性

PostgreSQLはカタログ駆動型です。データ辞書、つまりシステムカタログは、データベース自体、セル、列、データ型、関数、アクセスメソッドなどに関する情報を保持します。これらのカタログはそれ自体がテーブルであり、ユーザが問い合わせ可能で変更可能です。

ユーザはデータベースを直接変更することができ、動的にロード可能なコード変更やユーザ定義関数（UDF）を追加することができるため、多くのユーザがアプリケーションやデータ構造のラピッドプロトタイピングにPostgreSQLを選択しています。

拡張性は、特定の業界や業務に特化したニーズを持つ多くの組織にとって非常に重要です。従来のデータベースシステムが不十分で、ビジネスユーザ自身がデータベースの専門家でない場合、データベースを変更しても可用性を妨げず、コアデータベースエンジンを変更する必要もありません。

実際、ユーザはシームレスなソリューションを求めています。PostgreSQLでは、独自のデータ型、関数、集約、演算子、演算子クラス（インデックスアクセス用）、データラッパーなどをシームレスに作成することができ、ドメイン固有のソリューションのためのツールやアドオンを提供します。

拡張機能は、それ自体が完全な製品になることもあります。具体的な例としてはPostGISは、地理座標を使ったロケーションクエリをサポートするように改良されている。さらに、[Timescale]は時系列データ用に最適化されたPostgreSQLです。

PostgreSQLを他のツールと一緒に使う方法をいくつか紹介します：

ツールサポート

最近の開発者は、psqlをはるかに超える、データベースと対話するための多くのツールを楽しむことができます。pgAdminはPostgreSQLを全面的にサポートしており、DBAや開発者の長年のお気に入りです。Aiven固有の設定手順もあります。また、psqlで接続というターミナルベースのフロントエンドツールも便利です。

JetBrains社のDataGripは、人気のある統合開発環境(IDE)で、クロスプラットフォームで、さまざまなデータベースで動作します。usql`は、多くのデータベースでネイティブに動作するユニバーサルコマンドラインインターフェイスである。最後に、OmniDBはPostgreSQLやその他のデータベース用の完全な共同ワークスペースです。

コアとなるPostgreSQLそのものに加えて、Aivenの開発者はpghoard(バックアップとリストア用)、pglookout(レプリケーション監視とフェイルオーバーデーモン用)、および様々なコネクタを含む多くのPostgreSQL拡張に貢献しています。Aivenバージョンで動作する拡張機能の全リストはこちら

データを可視化するには、例えばオープンソースの分析ツールGrafanaを選ぶことができます。Aivenプラットフォームの助けを借りてPostgreSQLとGrafanaを統合する方法を説明します。GrafanaはPostgreSQLのメトリクスを監視するにも最適なツールです。

あるいは、pgwatch2でメトリクスを監視する方法をチェックしてください。

オーケストレーションに関しては、TerraformでPostgreSQLデータベースを管理することができます。

PostgreSQLデータベースの管理を委譲する

上記では、PostreSQLがどのようにデータ操作の担当者を支援するかについて、多くのエキサイティングな機能を取り上げました。しかし、これがデータベース管理のすべてではありません。どのようなデータベースでも、高可用性、安定した性能、弾丸のようなデータベースセキュリティを達成するために、多くの日常的な操作が必要です。

データベースとアプリケーションが成長するにつれて、これらのルーチン・オペレーションは多大な時間を消費し、偶発的なエラーによる多大なコストを伴うようになります。そのため、ある時点でPostgreSQLデータベースを管理するために専門家のサポートを利用したり、データベース管理タスクを実行するために外部の支援を利用したりすることを検討するかもしれません。

エラーが発生しやすい定型的な操作には、以下のようなものがあります。

定期的なバックアップと、必要なときのデータのリストア；
メジャーバージョンアップやフェイルオーバー手順の制御を行う簡単な方法を含め、最新のセキュリティアップデートやパッチを常に把握しておくこと；
コネクション・プーリングの助けを借りて、データベースへの非常に多くの接続を維持する。

マネージドサービスに依存することは、拡張の使用と管理においても利点があります。

PostgreSQLデータベースの管理を委ねることは簡単な選択ではありません。しかし、それは双方向のドアの決定になり得ます。結局のところ、オープンソースのソリューションにこだわるのであれば、後でいつでもサーバーの管理に戻ることができます。

その他にもいろいろあります。

PostgreSQLのホームページには、サポートされているデータ型、より詳細なデータ整合性機能、通貨をサポートする機能、災害復旧機能、セキュリティ機能、拡張性、国際文字サポートを含むテキスト検索など、他にもたくさんの機能が掲載されています。

PostgreSQL はどのように機能するか

PostgreSQLの中核は、データが完全に構造化されており、特定のスキーマに準拠していることを要求します。

**PostgreSQLはその後進化し、JSON/JSONB、XML、その他の一般的なデータ入出力形式もサポートするようになりました。

オープンソースのデータベース管理システムであるため、PostgreSQLユーザは言語を切り替えながら、再コンパイルすることなく、データベースを継続的に進化させることができます。SQLへの適合は継続的に行われていますが、新機能は常に追加されています。PostgreSQLでオブジェクトがどのように作成され、操作されるかを見てみましょう。

SQLは非常に広範囲にわたりますので、ここでは基本的なことにしか触れません。

DDL - データ定義言語

データ定義言語（DDL）はデータ構造を定義するために使用され、以下のコマンドを含む：

データ型宣言
CREATE` - 新しいデータベース、テーブル、インデックス、プロシージャを作成する。
ALTER` - 既存のデータベースオブジェクトを変更する。
DROP` - 既存のデータベース、テーブル、インデックスまたはプロシージャを削除する。
TRUNCATE TABLE` - テーブルを再利用するために空にする。

例えば

 データベースライブラリを作成する；

 CREATE TABLE Borrowers (
 id INT PRIMARY KEY、
 first_name VARCHAR(20) not null、
 last_name VARCHAR(50) not null、
 birth_date DATE not null)；

 ALTER TABLE Borrowers ADD COLUMN years_member INT NOT NULL CHECK years_member > 0；

 CREATE TABLE books (
 id INT PRIMARY KEY、
 タイトル VARCHAR(50)、
 著者 VARCHAR(50)
 publication_date DATE not null)；

 /* books'テーブルへの入力後 */

 TRUNCATE TABLE books；

 /* books'テーブルから全てのデータを削除する */`クリップボードにコピーする

DML - データ操作言語

データ操作言語は、以下のようにテーブルとその内容を操作するために使用される：

INSERT` - テーブルに新しい行を挿入する。
UPDATE - テーブルの既存の行を更新する。
DELETE` - テーブルから行を削除する。
SELECT - データベースからデータを問い合わせる主なメソッドである。

例えば

 INSERT INTO Borrowers(id,first_name,last_name,birth_date,years_member) VALUES(1,'Riley','Jimbo',1964-05-02,12)；
 INSERT INTO Borrowers(id,first_name,last_name,birth_date,years_member) VALUES(2,'Smith','Bubba',1973-09-21,5)；
 INSERT INTO Borrowers(id,first_name,last_name,birth_date,years_member) VALUES(3,'Eberhardt','Jocelyn',1930-12-22,71)；

 UPDATE Borrowers SET years_member = 1 WHERE id = 1；
 DELETE FROM Borrowers WHERE years_member > 70；

 SELECT * FROM Borrowers WHERE years_member > 5;`クリップボードにコピーする

一括挿入のためのコンパクトな構文

1つのコマンドで複数行を一度に挿入する簡単な方法がある：

 INSERT INTO Borrowers (id,first_name,last_name,birth_date,years_member) VALUES
 (4, 'Evelyn', 'Snarfington', 1959-08-03, 8)、
 (5, 'Robert', 'Jones', 2000-01-23, 6)、
 (6, 'Joseph', 'Chu', 1987-11-07, 10);`Copy to clipboard

概要DCLとTCL

DCL - データ制御言語 - データベースシステムへのアクセスを管理する権限やその他の制御を扱います。以下のステートメントで構成されています：

GRANT` - アクセス権限を付与する。
REVOKE` - アクセス権限を取り消す。

TCL - トランザクション制御言語 - データベースのトランザクションに関するもので、以下のステートメントで構成される：

COMMIT` - トランザクションをコミットする。
ROLLBACK` - エラーが発生した場合、トランザクションをロールバックする。
SAVEPOINT` - トランザクションのセーブポイントを設定する。
SET_TRANSACTION` - トランザクションの特性を定義する。

データの照会

PostgreSQLでの問い合わせのほとんどは（SQLそのものと同様に）、以下の例のようにSELECT文を介して行われます：

クリップボードにコピーします。

クエリは、結果を条件に限定するなど、さまざまな方法で絞り込むことができます：

SELECT \* FROM Borrowers WHERE years_member > 3;`クリップボードにコピーする

結果を特定の結果数に制限する

SELECT \* FROM Borrowers LIMIT 5;`クリップボードにコピーする

特定のフィールドのみを照会する

SELECT id, last_name, years_member FROM Borrowers;`Copy to clipboard

クエリ結果を特定のフィールドでソートする

SELECT \* FROM Borrowers ORDER BY years_member DESC;`Copy to clipboard

などとする。

PostgreSQL 用 Aiven

Aiven for PostgreSQLは、自動アップデートとバックアップによる高可用性を提供する、あらゆるプロバイダー向けのマネージド・ホスティングソリューションです。AivenはPostGISやTimescaleDBなどの様々な拡張をサポートし、publish/subscribe論理レプリケーションを提供します。

Aiven for PostgreSQLは、当社が提供するデータプラットフォームの一部に過ぎません。Aiven for Apache Kafka、OpenSearch、Redisなどを含むシステムの一部として統合することができます。

どのクラウドを選ぶか迷ったら、[GCP、AWS、Azureの最新のベンチマークを見てみよう！

PostgreSQLのクラウドパフォーマンス

Aiven for PostgreSQLを始めるにあたって

まず始めに、開発者ポータルの説明を読むのが一番です。

しかし、Aiven for PostgreSQLのセットアップは簡単です。ここではAiven Consoleで始める方法を説明します：

また、コマンドラインやREST APIを使用して直接行うこともできます。

詳しくはdeveloper.aiven.ioのPostgreSQLセクションをご覧ください！

続きを読む

AivenのPostgreSQLに関するブログでは、PostgreSQLのクールな使い方を紹介しています。