| この記事の要点 |
- 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' → 戻して再起動。
