postgresql について
開発者やデータベース運用者向けのPostgreSQLスキルです。SQL例、テーブル設計やクエリの指針、性能改善に加え、セキュリティ、バックアップ、監視も扱います。
主な機能
- 01テーブル定義と一般的なクエリ
- 02データ型の指針
- 03クエリ性能とインデックス
- 04セキュリティと接続プール
- 05バックアップ、復旧、監視
活用シーン
- 01PostgreSQLのテーブルとクエリの設計
- 02遅いクエリやロック競合の調査
- 03データベースのセキュリティとバックアップの準備
- 04接続数とレプリケーションの監視
MCP 対応
包括的なPostgreSQLドキュメントの参照先として、`mcp__documentation__fetch_docs`を案内しています。
インストール
コマンドをコピーしてターミナルで実行します。
$
npx skills add https:/インストール後、Claude Code で/postgresqlを入力して読み込まれたか確認します。
同梱ファイルquick-ref/indexes.mdquick-ref/json.md
詳細
PostgreSQL のコア知識
深い知識：包括的なドキュメントについては、technology:
postgresqlを指定してmcp__documentation__fetch_docsを使用してください。
テーブル定義
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created_at DESC);
よく使うクエリ
-- Select with pagination
SELECT * FROM users
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- Join
SELECT u.*, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.id = $1;
-- Aggregate
SELECT
DATE_TRUNC('day', created_at) as day,
COUNT(*) as count
FROM users
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day DESC;
-- Upsert
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;
データ型
| 型 | 用途 |
|---|---|
SERIAL | 自動インクリメント ID |
UUID | 一意の識別子 |
VARCHAR(n) | 可変長文字列 |
TEXT | 長文テキスト |
JSONB | JSON データ（インデックス作成可能） |
TIMESTAMP | 日付と時刻 |
BOOLEAN | 真偽値 |
パフォーマンス
EXPLAIN ANALYZEを使用してクエリを確認します- WHERE／JOIN で使う列にインデックスを追加します
- クエリでは
JSONよりJSONBを使用します - 絞り込みクエリには部分インデックスを使用します
このスキルを使用しない場合
- MySQL 固有の構文 - MySQL データベース（AUTO_INCREMENT、GROUP_CONCAT）には
mysqlスキルを使用します - NoSQL 操作 - ドキュメント型／キー・バリュー型ストアには
mongodbまたはredisスキルを使用します - Oracle PL/SQL - Oracle 固有の手続き型コードには
plsqlスキルを使用します - SQL Server T-SQL - SQL Server 固有の機能には
tsqlスキルを使用します - ORM の抽象化 - フレームワーク固有のスキル（Prisma、TypeORM、Spring Data JPA）を使用します
アンチパターン
| アンチパターン | 問題 | 解決策 |
|---|---|---|
本番環境での SELECT * | 不要なデータを転送し、スキーマ変更時に動作しなくなります | 必要な列を明示的に指定します |
UPDATE／DELETE での WHERE の欠落 | 意図せずすべての行を変更します | 常に WHERE 句を含め、トランザクションを使用します |
| JOIN／WHERE で使う列のインデックスの欠落 | テーブル全体のスキャンが発生し、クエリが遅くなります | 頻繁にクエリされる列にインデックスを追加します |
| インデックス付き列に対する関数の使用 | インデックスを使用できなくなります：WHERE UPPER(email) = 'X' | 関数インデックスを使用するか、クエリを変更します |
LIKE '%pattern' | インデックスを使用できず、全件スキャンになります | LIKE 'pattern%' または全文検索を使用します |
大規模テーブルでの LIMIT の欠落 | アプリケーションのクラッシュやメモリの問題が発生する可能性があります | 常に結果をページ分割します |
| カンマ区切り値の保存 | 効率的にクエリできず、正規化に違反します | 配列型または中間テーブルを使用します |
| 外部キーの欠落 | データ整合性の問題や孤立レコードが発生します | 適切な FK 制約を定義します |
| N+1 クエリ問題 | ループ内で行ごとに 1 つのクエリが実行されます | JOIN またはバッチクエリを使用します |
| 長時間実行されるトランザクション | リソースをロックし、ほかのクエリをブロックします | トランザクションを短く保ち、適切な分離レベルを使用します |
クイックトラブルシューティング
| 問題 | 診断 | 対処法 |
|---|---|---|
| クエリが遅い | EXPLAIN ANALYZE query | インデックスを追加し、クエリを書き換え、統計情報を更新します |
| CPU 使用率が高い | pg_stat_statements で遅いクエリを特定します | 上位のクエリを最適化し、コネクションプーリングを追加します |
| 接続数の上限に達した | SELECT count(*) FROM pg_stat_activity | max_connections を増やし、PgBouncer を使用します |
| ロック競合 | SELECT * FROM pg_locks WHERE NOT granted | トランザクション時間を短縮し、より低い分離レベルを使用します |
| ディスク容量が不足している | SELECT pg_size_pretty(pg_database_size('mydb')) | VACUUM を実行し、古いデータをアーカイブして、ストレージを増やします |
| レプリケーション遅延 | pg_stat_replication を確認します | リソースを増やし、チェックポイント設定を調整します |
| キャッシュヒット率が 95% 未満 | SELECT sum(blks_hit)/sum(blks_hit+blks_read) FROM pg_stat_database | shared_buffers を増やします |
| デッドタプルが蓄積している | SELECT n_dead_tup FROM pg_stat_user_tables | VACUUM を実行し、autovacuum を調整します |
本番環境への対応
セキュリティ設定
-- Create application user with limited privileges
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
-- Row Level Security (RLS)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY users_tenant_isolation ON users
USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- Force SSL connections
-- In postgresql.conf:
-- ssl = on
-- ssl_cert_file = '/path/to/server.crt'
-- ssl_key_file = '/path/to/server.key'
-- Connection with SSL
psql "postgresql://user:pass@host:5432/db?sslmode=require"
コネクションプーリング（PgBouncer）
# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
バックアップと復旧
# Continuous archiving (WAL)
# postgresql.conf:
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
wal_level = replica
# Full backup with pg_basebackup
pg_basebackup -D /backup/base -Ft -Xs -P -U replication
# Point-in-time recovery (recovery.signal)
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2024-01-15 10:00:00'
パフォーマンスのチューニング
-- postgresql.conf recommendations (for 16GB RAM server)
shared_buffers = 4GB # 25% of RAM
effective_cache_size = 12GB # 75% of RAM
maintenance_work_mem = 1GB
work_mem = 64MB
wal_buffers = 64MB
max_connections = 200
checkpoint_completion_target = 0.9
random_page_cost = 1.1 # SSD
-- Query optimization
SET log_min_duration_statement = 1000; -- Log queries > 1s
ANALYZE; -- Update statistics
監視メトリクス
| メトリクス | アラートしきい値 |
|---|---|
| 接続数 | > 80% max_connections |
| キャッシュヒット率 | < 95% |
| レプリケーション遅延 | > 1MB または > 10s |
| デッドタプル率 | > 10% |
| 長時間実行されるトランザクション | > 5 minutes |
| ロック待機イベント | > 10/min |
監視クエリ
-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
-- Cache hit ratio
SELECT
round(100 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) as cache_hit_ratio
FROM pg_stat_database;
-- Table bloat (dead tuples)
SELECT schemaname, relname, n_dead_tup, n_live_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
-- Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 minutes';
-- Replication lag
SELECT client_addr, state,
pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag
FROM pg_stat_replication;
高可用性
# Patroni configuration for HA
scope: postgres-cluster
name: node1
restapi:
listen: 0.0.0.0:8008
etcd:
hosts: etcd1:2379,etcd2:2379,etcd3:2379
postgresql:
listen: 0.0.0.0:5432
data_dir: /data/postgres
parameters:
max_connections: 200
shared_buffers: 4GB
チェックリスト
- SSL/TLS 暗号化が有効になっている
- 最小権限のユーザーアカウント
- コネクションプーリング（PgBouncer）
- WAL アーカイブが設定されている
- pg_dump による定期的なバックアップ
- ポイントインタイムリカバリをテスト済み
- 監視クエリを設定済み
- スロークエリのログ記録が有効になっている
- VACUUM/ANALYZE をスケジュール済み
- レプリケーションを設定済み（HA の場合）
- 接続数の上限を設定済み
- 行レベルセキュリティ（マルチテナントの場合）
リファレンスドキュメント
コメント
Markdown 対応