この記事の要点

PostgreSQLは世界最高峰のオープンソース RDBMS。1986 年カリフォルニア大学バークレー校発 SQL 標準準拠 (SQL:2016 大部分対応)、ACID 完全、複雑なクエリに強い リッチな型: JSON / JSONB、配列型、範囲型、UUID、PostGIS (地理空間) 高度機能: CTE / ウィンドウ関数 / Full Text Search / パーティション マネージド: AWS RDS / Cloud SQL / Supabase / Neon

PostgreSQL とは

PostgreSQL (通称 Postgres) は、商用 RDBMS に匹敵する機能を持つオープンソースの ORDBMS (Object-Relational DBMS)。BSD/MIT 系の PostgreSQL ライセンスで、商用利用も完全フリー。MySQL と並ぶ二大 OSS RDBMS で、近年は MySQL を抜く勢いで採用が増えています。

特長

項目	内容
SQL 準拠度	SQL:2016 の大部分に対応 (MySQL より厳格)
ACID	完全準拠 (デフォルト動作で安全)
MVCC	マルチバージョン同時実行制御 (読み込みはロックフリー)
JSON / JSONB	ネイティブ型、インデックス可能 (GIN)
拡張型	UUID、配列、範囲型、HStore、ENUM 等が標準
CTE	WITH 句、再帰 CTE 対応
ウィンドウ関数	ROW_NUMBER / LAG / LEAD / NTILE 全対応
パーティション	宣言的パーティション (RANGE / LIST / HASH)
FTS	全文検索 (tsvector / tsquery)、多言語対応
地理空間	PostGIS 拡張で GIS の業界標準
レプリケーション	物理 / 論理 / 同期 / 非同期
拡張機能	CREATE EXTENSION で多数の拡張 (uuid-ossp, pg_stat_statements, citus, TimescaleDB)

インストール

Ubuntu / Debian

sudo apt update
sudo apt install -y postgresql postgresql-contrib

# サービス起動
sudo systemctl enable --now postgresql

# postgres ユーザに切替えて接続
sudo -u postgres psql

macOS

brew install postgresql@16
brew services start postgresql@16

createuser -s myuser
createdb mydb
psql mydb

Docker

docker run -d \
  --name pg \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:17

# 接続
docker exec -it pg psql -U postgres mydb

psql の基本コマンド

\l              -- DB 一覧
\c mydb         -- DB 切替
\dt             -- テーブル一覧
\d users        -- テーブル定義
\du             -- ユーザ一覧
\df             -- 関数一覧
\dn             -- スキーマ一覧
\x              -- 縦表示モード切替
\q              -- 終了

\timing on      -- クエリ実行時間表示
\e              -- 外部エディタで SQL 編集
\i file.sql     -- ファイル実行
\copy users TO 'users.csv' CSV HEADER  -- CSV エクスポート

主要なデータ型

型	用途
SERIAL / BIGSERIAL	自動採番 (PG10+ は GENERATED ALWAYS AS IDENTITY 推奨)
UUID	UUID 型 (uuid-ossp 拡張で生成関数)
TEXT / VARCHAR	文字列 (TEXT は無制限長で性能差なし)
NUMERIC(p,s)	正確な十進数 (金額系)
TIMESTAMP WITH TIME ZONE	タイムゾーン付き日時 (推奨)
JSONB	バイナリ JSON、検索/インデックス可
INTEGER[]	配列型
INT4RANGE / TSRANGE	範囲型 (期間など)
INET / CIDR	IP アドレス
POINT / POLYGON	幾何 (PostGIS なしでも)

JSONB の例

CREATE TABLE products (
  id   BIGSERIAL PRIMARY KEY,
  data JSONB NOT NULL
);

INSERT INTO products(data) VALUES
  ('{"name":"Laptop","price":1500,"tags":["pc","work"]}'),
  ('{"name":"Phone","price":800,"tags":["mobile"]}');

-- 抽出 (-> は jsonb / ->> はテキスト)
SELECT data->>'name' AS name, (data->>'price')::int AS price FROM products;

-- 含有検索
SELECT * FROM products WHERE data @> '{"tags":["mobile"]}';

-- GIN インデックス
CREATE INDEX idx_products_data ON products USING GIN (data jsonb_path_ops);

-- 更新
UPDATE products SET data = jsonb_set(data, '{price}', '1600') WHERE id = 1;

CTE とウィンドウ関数

-- 再帰 CTE: 組織階層
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth FROM org WHERE parent_id IS NULL
  UNION ALL
  SELECT o.id, o.name, o.parent_id, t.depth + 1
    FROM org o JOIN tree t ON o.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

-- ウィンドウ関数: 部署内売上ランキング
SELECT
  dept,
  name,
  sales,
  RANK() OVER (PARTITION BY dept ORDER BY sales DESC) AS rank
FROM employees;

全文検索 (FTS)

-- 検索カラム作成
ALTER TABLE articles ADD COLUMN tsv TSVECTOR;

UPDATE articles
SET tsv = to_tsvector('simple', title || ' ' || body);

CREATE INDEX idx_articles_tsv ON articles USING GIN(tsv);

-- 検索
SELECT id, title
FROM articles
WHERE tsv @@ to_tsquery('simple', 'PostgreSQL & JSONB')
ORDER BY ts_rank(tsv, to_tsquery('simple', 'PostgreSQL & JSONB')) DESC;

-- 日本語は pg_bigm / pg_trgm / textsearch_ja などの追加が必要

バックアップ / リストア

# 論理バックアップ (SQL)
pg_dump -U postgres mydb > mydb.sql
pg_dump -U postgres -F c mydb > mydb.dump  # カスタム形式 (推奨)

# リストア
psql -U postgres mydb < mydb.sql
pg_restore -U postgres -d mydb mydb.dump

# クラスタ全体
pg_dumpall -U postgres > all.sql

# 物理バックアップ (PITR)
pg_basebackup -D /backup -F t -X stream -P

PostGIS (地理空間)

-- PostGIS 拡張
CREATE EXTENSION postgis;

CREATE TABLE shops (
  id   SERIAL PRIMARY KEY,
  name TEXT,
  geom GEOMETRY(POINT, 4326)  -- WGS84
);

INSERT INTO shops(name, geom) VALUES
  ('Tokyo', ST_SetSRID(ST_MakePoint(139.7671, 35.6812), 4326));

-- 半径 1km 以内
SELECT name FROM shops
WHERE ST_DWithin(
  geom::geography,
  ST_SetSRID(ST_MakePoint(139.7, 35.68), 4326)::geography,
  1000  -- meters
);

マネージドサービス

サービス	特徴
AWS RDS for PostgreSQL	定番。自動バックアップ、レプリカ
AWS Aurora PostgreSQL	互換版、ストレージ自動スケール
GCP Cloud SQL	シンプル運用
Azure Database for PostgreSQL	Hyperscale (Citus) も提供
Supabase	BaaS。Auth / REST / Realtime も込みで OSS
Neon	サーバレス Postgres、ブランチング機能
Render / Railway	個人開発向け軽量
Heroku Postgres	古参 PaaS

MySQL との比較

項目	PostgreSQL	MySQL
SQL 標準準拠	◎	○
JSON	JSONB (高速・インデックス可)	JSON 型 (PostgreSQL より弱い)
CTE / ウィンドウ関数	古くから対応	MySQL 8 で対応
レプリケーション	論理 / 物理 / 同期可	非同期が主、GTID
ライセンス	PostgreSQL License (BSD-like)	GPL / 商用
採用	金融 / 地理空間 / SaaS	Web 一般 / WordPress

最新バージョン (2026 時点)

• PostgreSQL 17 (2024 年リリース) — VACUUM 効率化、論理レプリ強化
• PostgreSQL 16 (2023 年) — 並列ハッシュ結合、SQL/JSON 強化
• サポート期間はリリースから 5 年 (例: 16 は 2028 年まで)

FAQ

Q: PostgreSQL と MySQL、新規に選ぶならどっち？
A: 2020 年代以降は特に理由がなければ PostgreSQLを推奨。SQL の表現力、JSON、地理空間、CTE などで MySQL を上回り、運用ノウハウも揃ってきました。

Q: VACUUM とは？
A: MVCC で発生する不要行 (dead tuple) を回収する作業。Autovacuum が自動実行しますが、大量更新テーブルでは手動 VACUUM ANALYZE を検討。

Q: スーパーユーザのパスワードを忘れた
A: pg_hba.conf を一時的に trust に変更 → 再起動 → ALTER USER postgres PASSWORD 'new' → 戻して再起動。