N.Kikuchiをフォローする

普段なんとなく使用している「PostgreSQL」について深堀って調べてみた

バックエンド

今回は皆さんご存じ「PostgreSQL」についてまとめてみました。
普段から「PostgreSQLだからどう」とか私はあまり意識せず触っていたので、
今回のブログ作成を機に基本的な情報からユニーク機能など、勉強もかねて調査しています。

PostgreSQLについて

PostgreSQL（ポストグレス キューエル[※ 1]）は、拡張性とSQL準拠を強調するフリーでオープンソースの関係データベース管理システム（RDBMS）である。Postgresとしても知られている。もともとは、カリフォルニア大学バークレー校で開発されたIngresデータベースの後継としてその起源を根拠としたPOSTGRESという名前であった。1996年に、プロジェクトはSQLのサポートを反映してPostgreSQLに改名された。2007年の検討の結果、開発チームはPostgreSQLという名前とPostgresという別名を維持することを決定した。

（引用元：https://ja.wikipedia.org/wiki/PostgreSQL）

他RDBMとの比較

項目PostgreSQLMySQLOracleSQL Server
トランザクション制御ACID準拠、MVCCで高い整合性基本的なACIDサポート、MVCCなし強力なトランザクション制御ACID準拠、トランザクション管理機能が豊富
拡張性新しいデータ型や関数を追加可能、拡張性が高い拡張性は限定的高い拡張性（ただしライセンス費用が必要）一定の拡張性あり、CLR統合でカスタム開発可
JSONデータ型の違いJSON/JSONBを標準サポート、効率的な検索機能ありJSONサポートはあるが検索はやや弱いJSON対応だが、柔軟性は低いJSONサポートはあるが処理速度に限界あり
価格無料（オープンソース）無料（オープンソース）商用ライセンスが必要有償ライセンス（無償版あり）
パフォーマンス大量データや複雑なクエリに強く、高度な処理が可能シンプルなクエリには高速だが、複雑な処理は苦手高速だが、ハードウェア要求が高い高速だが、Windows環境に依存
プラグインの柔軟性拡張モジュール（PostGIS、pgvectorなど）が豊富限定的なプラグイン提供プラグインはあるが商用中心いくつかの拡張機能はあるが制限あり

PostgreSQLのユニーク機能

PostGIS

PostgreSQLの拡張機能で地理空間データを扱うためのOSS。
そもそもGISは「Geographic Information System」の略で、「地図」を「作る」ソフトウェアの総称だそうです。
特徴としては、地理空間情報の演算を行うことができ、3×3行列での変換や、面積、距離、周囲の長さなども計算できます。

ウィンドウ関数

現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。
これは集約関数(GROUP BY とか)により行われる計算の形式と似たようなものですが
ウィンドウ関数では対象となった行はそのまま残ります。

代表的なウィンドウ関数として「OVER」句があります。
使い方としては集約関数(SUMとかAVG) + OVER句とすると特定の範囲内で集約ができます。

例として、学生毎の科目の平均点を求めてみます。

student_idstudent_namesubjectscore
1山田太郎数学80
2佐藤花子数学70
3鈴木一郎英語85
4高橋次郎英語79
5田中三郎理科82
6渡辺四郎理科76

上記、テーブルに対して以下クエリを実行してみます。
> AVG(score) OVER (PARTITION BY subject) AS avg_score
とすることで、subject毎のscoreの平均点を求めることができます。

SELECT
  student_name,
  subject,
  score,
  AVG(score) OVER (PARTITION BY subject) AS avg_score
FROM
  ExamResults;

以下、結果となります。

student_namesubjectscoreavg_score
山田太郎数学8075
佐藤花子数学7075
鈴木一郎英語8582
高橋次郎英語7982
田中三郎理科8279
渡辺四郎理科7679

このように、各行には元の得点（score）が保持されながら、同じ科目の平均得点（avg_score）を計算することができます。
この関数を使用すれば自身の得点とその教科の平均点と比較することができます。

パフォーマンスチューニング

自動バキューム

そもそもバキューム(VACUUM)とは、タプルが使用する領域を回収します。 
PostgreSQLの通常動作では、削除されたタプルや更新によって不要となったタプルは、テーブルから物理的には削除されません。 そのためバキュームを実行することで不要な領域を回収することができます。

その中での、自動バキューム（autovacuum）は、PostgreSQLの独自機能で、他RDMSでは手動で実行することが多いのですが、PostgreSQLでは自動で実行できますよというものです。

BRAINインデックス

BRIN (Block Range Index) は、PostgreSQLに固有のインデックスであり、巨大なテーブルに対して高速な検索を提供するために設計されています。 特に、時系列データや連続する値を持つデータに適したインデックス方式です。

例えば、大量のログデータを含むテーブルがある場合：

CREATE INDEX idx_log_time ON logs USING BRIN (log_timestamp);

この場合、PostgreSQLは各ブロックの log_timestamp の最小値・最大値だけを記録するため、 検索時には「対象ブロックにデータが含まれるか？」を確認するだけで済みます。

SELECT * FROM logs WHERE log_timestamp BETWEEN '2025-04-01' AND '2025-04-10';

→ BRIN により、該当範囲のブロックのみをスキャンするため、大規模なデータでも高速に検索可能。

まとめ

今回はPostgreSQLになった経緯から、独自で使用できるユニーク機能まで調べてみました。
個人的にはユニーク機能である「ウィンドウ関数」は実用的に使用できるかなと思っております。
（データ分析の業務に携わっているため）