PostgreSQL

PostgreSQLは、高度な機能と拡張性を持つオープンソースのリレーショナルデータベース管理システム（RDBMS）です。30年以上の開発歴史を持ち、ACID準拠、高い並行性、豊富なデータ型、強力な検索機能を提供しています。

主な特徴

高度なSQL機能

• SQL標準への高い準拠率
• ウィンドウ関数、CTE（共通テーブル式）、LATERAL結合
• 配列、JSON/JSONB、hstore、地理空間データ型
• 正規表現とフルテキスト検索

拡張性とプラグイン

• カスタム関数、集約関数、演算子の作成
• 多言語での関数作成（PL/pgSQL、Python、Perl、Tcl等）
• 豊富な拡張モジュール（PostGIS、pg_stat_statements等）

エンタープライズ機能

• MVCC（多版本並行性制御）
• 論理レプリケーション・ストリーミングレプリケーション
• パーティショニング（レンジ、リスト、ハッシュ）
• 行レベルセキュリティ（RLS）

インストール

Ubuntu/Debian

# 公式APTリポジトリの追加
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

# PostgreSQL 16のインストール
sudo apt-get install postgresql-16 postgresql-client-16

CentOS/RHEL

# 公式YUMリポジトリの追加
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# PostgreSQL 16のインストール
sudo dnf install -y postgresql16-server postgresql16
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

Docker

# PostgreSQL 16の起動
docker run --name postgres-db \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -d postgres:16

# データの永続化
docker run --name postgres-db \
  -e POSTGRES_PASSWORD=mypassword \
  -v postgres_data:/var/lib/postgresql/data \
  -p 5432:5432 \
  -d postgres:16

基本設定

postgresql.conf

# 接続設定
listen_addresses = '*'
port = 5432
max_connections = 100

# メモリ設定
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

# WAL設定
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 1GB
min_wal_size = 80MB

# ログ設定
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'
log_duration = on
log_min_duration_statement = 1000ms

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# 本ローカル接続
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 LAN接続
host    all             all             192.168.1.0/24          md5

# SSL接続
hostssl all             all             0.0.0.0/0               md5

# レプリケーション
host    replication     replicator      192.168.1.0/24          md5

基本操作

データベース管理

-- データベース作成
CREATE DATABASE myapp
  WITH ENCODING 'UTF8'
       LC_COLLATE 'ja_JP.UTF-8'
       LC_CTYPE 'ja_JP.UTF-8'
       TEMPLATE template0;

-- ユーザー作成
CREATE USER appuser WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE myapp TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT CREATE ON SCHEMA public TO appuser;

-- テーブル作成
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) NOT NULL,
    profile JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- インデックス作成
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_profile_gin ON users USING GIN(profile);

JSON操作

-- JSON/JSONBデータの操作
INSERT INTO users (username, email, profile) VALUES
('john_doe', '[email protected]', '{"age": 30, "city": "Tokyo", "skills": ["Python", "SQL"]}');

-- JSON検索
SELECT * FROM users WHERE profile->>'city' = 'Tokyo';
SELECT * FROM users WHERE profile->'skills' ? 'Python';
SELECT * FROM users WHERE profile @> '{"age": 30}';

-- JSON集約
SELECT 
    profile->>'city' as city,
    COUNT(*) as user_count,
    jsonb_agg(username) as usernames
FROM users 
GROUP BY profile->>'city';

パフォーマンス最適化

インデックス戦略

-- B-treeインデックス（デフォルト）
CREATE INDEX idx_users_username ON users(username);

-- 部分インデックス
CREATE INDEX idx_active_users ON users(username) WHERE active = true;

-- 複合インデックス
CREATE INDEX idx_users_city_age ON users((profile->>'city'), (profile->>'age')::int);

-- GINインデックス（JSON、配列、フルテキスト）
CREATE INDEX idx_users_profile ON users USING GIN(profile);

-- GiSTインデックス（地理空間、範囲）
CREATE INDEX idx_location ON locations USING GIST(geom);

クエリ最適化

-- EXPLAIN ANALYZEによる実行計画確認
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM users WHERE profile->>'city' = 'Tokyo';

-- 統計情報の更新
ANALYZE users;

-- バキューム（領域回収）
VACUUM ANALYZE users;

-- 自動バキューム設定
ALTER TABLE users SET (
    autovacuum_vacuum_scale_factor = 0.1,
    autovacuum_analyze_scale_factor = 0.05
);

レプリケーション設定

マスター設定

# postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
synchronous_commit = on

-- レプリケーションユーザー作成
CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'replica_password';

スレーブ設定

# ベースバックアップ作成
pg_basebackup -h master_host -D /var/lib/postgresql/16/main -U replicator -P -W

# スタンバイ設定
echo "standby_mode = 'on'" >> /var/lib/postgresql/16/main/recovery.conf
echo "primary_conninfo = 'host=master_host port=5432 user=replicator password=replica_password'" >> /var/lib/postgresql/16/main/recovery.conf

バックアップ戦略

論理バックアップ

# pg_dump（単一データベース）
pg_dump -h localhost -U postgres -d myapp -f myapp_backup.sql

# pg_dumpall（全データベース）
pg_dumpall -h localhost -U postgres -f all_databases_backup.sql

# カスタム形式（圧縮、並列復元可能）
pg_dump -h localhost -U postgres -d myapp -Fc -f myapp_backup.dump

# 復元
pg_restore -h localhost -U postgres -d myapp -v myapp_backup.dump

物理バックアップ

# pg_basebackup
pg_basebackup -h localhost -D /backup/base -U postgres -P -W

# WALアーカイブ設定
# postgresql.conf
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

# Point-in-Time Recovery (PITR)
pg_ctl stop -D /var/lib/postgresql/16/main
rm -rf /var/lib/postgresql/16/main/*
tar -xzf /backup/base_backup.tar.gz -C /var/lib/postgresql/16/main
echo "restore_command = 'cp /backup/wal/%f %p'" > /var/lib/postgresql/16/main/recovery.conf
echo "recovery_target_time = '2024-01-15 14:30:00'" >> /var/lib/postgresql/16/main/recovery.conf
pg_ctl start -D /var/lib/postgresql/16/main

監視とメンテナンス

基本監視クエリ

-- 接続状況確認
SELECT 
    datname,
    usename,
    client_addr,
    state,
    query_start,
    query
FROM pg_stat_activity 
WHERE state != 'idle';

-- テーブルサイズ確認
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- インデックス使用状況
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

パフォーマンス統計

-- スロークエリ確認（pg_stat_statementsが必要）
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- ロック状況確認
SELECT 
    l.locktype,
    l.database,
    l.relation,
    l.page,
    l.tuple,
    l.virtualxid,
    l.transactionid,
    l.classid,
    l.objid,
    l.objsubid,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted,
    a.usename,
    a.query
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;

高度な機能

パーティショニング

-- レンジパーティション
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

-- パーティション作成
CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- ハッシュパーティション
CREATE TABLE user_data (
    user_id INTEGER,
    data TEXT
) PARTITION BY HASH (user_id);

CREATE TABLE user_data_0 PARTITION OF user_data
    FOR VALUES WITH (modulus 4, remainder 0);

フルテキスト検索

-- tsvector型でのフルテキスト検索設定
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector = 
    to_tsvector('japanese', title || ' ' || content);

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 検索実行
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('japanese', 'PostgreSQL & データベース') query
WHERE search_vector @@ query
ORDER BY rank DESC;

PostgreSQLは、その豊富な機能セット、高い拡張性、堅牢性により、小規模なWebアプリケーションから大規模エンタープライズシステムまで幅広く利用されています。オープンソースでありながらエンタープライズグレードの機能を提供し、継続的な開発により常に最新の技術トレンドに対応しています。