PostgreSQL

UZU プロジェクトにおける PostgreSQL データベースの設計と管理。

概要

PostgreSQL は、UZU プロジェクトのメインデータベースとして使用される、高機能なオープンソースリレーショナルデータベースです。複数のサービスで独立したデータベースを管理しています。

データベース構成

サービス別データベース

• uzu_development: メインアプリケーション用
• account_service: アカウント管理サービス用
• mdms_jp: マダミス.jp 用（独立 DB）

ポート割り当て

.ports ファイルで各サービスのポートを管理：

BACKEND_DB_PORT=6001      # メインDB
ACCOUNT_DB_PORT=6002      # アカウントサービス
MDMS_JP_DB_PORT=6004      # マダミス.jp

接続方法

Docker 環境での接続

# psqlコマンドでの接続
PGPASSWORD=password psql -h localhost -p 6001 -U postgres -d uzu_development

# 環境変数設定
export DATABASE_URL="postgresql://postgres:password@localhost:6001/uzu_development"

TablePlus での接続

1. 新規接続作成
2. 以下の情報を入力：
   • Host: localhost
   • Port: .portsファイル参照
   • User: postgres
   • Password: password
   • Database: 対象の DB 名

スキーマ管理

Ent ORM (Go)

バックエンドでは Ent ORM を使用：

# スキーマ生成
go generate ./ent

# マイグレーション実行
go run ./cmd/migrate

Prisma (JavaScript)

フロントエンド・Node.js アプリでは Prisma を使用：

# スキーマ同期
npx prisma generate

# マイグレーション作成
npx prisma migrate dev --name migration_name

# マイグレーション適用
npx prisma migrate deploy

データベース操作

よく使うクエリ

-- テーブル一覧
\dt

-- テーブル構造確認
\d table_name

-- データ件数確認
SELECT COUNT(*) FROM table_name;

-- 最新データ確認
SELECT * FROM table_name ORDER BY created_at DESC LIMIT 10;

バックアップとリストア

# バックアップ
pg_dump -h localhost -p 6001 -U postgres uzu_development > backup.sql

# リストア
psql -h localhost -p 6001 -U postgres uzu_development < backup.sql

パフォーマンス最適化

インデックス設計

-- 頻繁に検索される列にインデックス作成
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_scenarios_status ON scenarios(status);

-- 複合インデックス
CREATE INDEX idx_plays_user_scenario ON plays(user_id, scenario_id);

クエリ最適化

-- 実行計画の確認
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- スロークエリの特定
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

トラブルシューティング

接続エラー

# Dockerコンテナ確認
docker ps | grep postgres

# ログ確認
docker logs app_db_1

# ポート確認
lsof -i :6001

データベースリセット

# 開発環境でのリセット
task backend:seed

# Dockerボリューム削除（完全リセット）
docker volume rm app_db_volume

ロック解除

-- アクティブなロックを確認
SELECT * FROM pg_locks WHERE granted = false;

-- 長時間実行中のクエリを確認
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- プロセスを強制終了
SELECT pg_terminate_backend(pid);

セキュリティ

アクセス制御

• 本番環境では強力なパスワードを使用
• ロール別のアクセス権限設定
• SSL/TLS 接続の強制

監査ログ

-- 監査ログ有効化
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

関連ドキュメント

• データベースデバッグ
• バックエンドデータベースデバッグ