データベース
PostgreSQL
概要
PostgreSQLは、30年以上の開発実績を持つ世界で最も先進的なオープンソースのリレーショナルデータベース管理システムです。高い拡張性、標準準拠、堅牢性で知られ、エンタープライズレベルの機能を提供しています。
詳細
PostgreSQLは1986年にカリフォルニア大学バークレー校で始まったプロジェクトから発展し、現在では世界中で広く使用されています。ACID特性を完全にサポートし、完全なSQL標準準拠、外部キー、結合、ビュー、トリガー、ストアドプロシージャに対応しています。また、JSON、XML、配列などの非構造化データ型もサポートし、NoSQLの機能も提供しています。
PostgreSQLは以下の特徴を持ちます：
- 完全なACID特性（原子性、一貫性、分離性、持続性）
- マルチバージョン同時実行制御（MVCC）
- 豊富なデータ型（JSON、XML、配列、範囲型など）
- 全文検索機能
- 地理情報システム（PostGIS拡張）
- カスタム関数とストアドプロシージャ
- 複数のインデックス型（B-tree、Hash、GiST、SP-GiST、GIN、BRIN）
- パーティショニング機能
- レプリケーションとクラスタリング
メリット・デメリット
メリット
- 高い信頼性: ACID特性とMVCCによる堅牢なトランザクション処理
- 標準準拠: SQL標準に最も準拠したデータベースの一つ
- 高い拡張性: カスタムデータ型、関数、演算子の作成が可能
- 豊富な機能: 地理情報、全文検索、JSON処理など多彩な機能
- コストパフォーマンス: オープンソースで商用利用も無料
- 活発なコミュニティ: 継続的な開発とサポート
デメリット
- 複雑性: 豊富な機能により学習コストが高い
- メモリ使用量: 大量のメモリを消費する場合がある
- 設定の複雑さ: 最適なパフォーマンスを得るための調整が必要
- 初期設定: デフォルト設定では最適化されていない場合がある
主要リンク
書き方の例
インストール・セットアップ
# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib
# Red Hat/CentOS
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb
# macOS (Homebrew)
brew install postgresql
brew services start postgresql
# Docker
docker run --name postgres-db -e POSTGRES_PASSWORD=mypassword -p 5432:5432 -d postgres
基本操作（CRUD）
-- データベース接続
psql -U username -d database_name
-- テーブル作成
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- データ挿入（Create）
INSERT INTO users (name, email) VALUES
('田中太郎', '[email protected]'),
('佐藤花子', '[email protected]');
-- データ読み取り（Read）
SELECT * FROM users;
SELECT * FROM users WHERE name LIKE '田中%';
-- データ更新（Update）
UPDATE users SET email = '[email protected]'
WHERE name = '田中太郎';
-- データ削除（Delete）
DELETE FROM users WHERE id = 1;
データモデリング
-- 外部キー制約
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
amount DECIMAL(10,2) NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- インデックス作成
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(order_date);
-- ビュー作成
CREATE VIEW user_orders AS
SELECT u.name, u.email, o.amount, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;
インデックス・最適化
-- 複合インデックス
CREATE INDEX idx_user_date ON orders(user_id, order_date);
-- 部分インデックス
CREATE INDEX idx_active_users ON users(id) WHERE active = true;
-- 実行計画の確認
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
-- 統計情報の更新
ANALYZE users;
-- バキューム実行
VACUUM ANALYZE users;
実用例
-- JSON型の使用
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
attributes JSONB
);
INSERT INTO products (name, attributes) VALUES
('ノートPC', '{"brand": "Dell", "cpu": "Intel i7", "ram": "16GB"}');
SELECT * FROM products
WHERE attributes->>'brand' = 'Dell';
-- 配列型の使用
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(200),
tags TEXT[]
);
INSERT INTO articles (title, tags) VALUES
('PostgreSQL入門', ARRAY['database', 'postgresql', 'sql']);
SELECT * FROM articles
WHERE 'postgresql' = ANY(tags);
-- ウィンドウ関数
SELECT
name,
amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) as rank
FROM orders o
JOIN users u ON o.user_id = u.id;
ベストプラクティス
-- トランザクション管理
BEGIN;
INSERT INTO users (name, email) VALUES ('新規ユーザー', '[email protected]');
INSERT INTO orders (user_id, amount) VALUES (LASTVAL(), 1000.00);
COMMIT;
-- ストアドプロシージャ
CREATE OR REPLACE FUNCTION get_user_order_total(user_id_param INTEGER)
RETURNS DECIMAL AS $$
BEGIN
RETURN (
SELECT COALESCE(SUM(amount), 0)
FROM orders
WHERE user_id = user_id_param
);
END;
$$ LANGUAGE plpgsql;
-- パーティショニング
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- 接続プール設定
-- postgresql.conf
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB