database-architecture

PostgreSQL 18 データベースアーキテクチャ

概要

PostgreSQL 18 では、画期的な変更が導入されています。AIO サブシステムによって I/O 性能が 3 倍になり、ネイティブの UUIDv7 が UUID ライブラリに取って代わり、時間制約によって二時点データモデリングが可能になります。このスキルを使うことで、これらの機能を適切に活用できます。

基本原則： PostgreSQL 18 の強みを活かす設計にしてください。以前のバージョンや他のデータベースのパターンを移植しないでください。

開始時に次のように伝えてください： 「PostgreSQL 18 のベストプラクティスに沿うよう、database-architecture を適用します。」

このスキルを適用する場面

次のパターンのいずれかに触れる場合、このスキルの適用は必須です。

パターン	例
**/migrations/**	migrations/001_create_tables.sql
**/*schema*.sql	db/schema.sql
**/db/**/*.sql	db/functions/calculate.sql
**/*index*.sql	db/indexes.sql
**/models/**	src/models/user.ts
**/*entity*.ts	src/entities/order.entity.ts
**/*model*.py	app/models/product.py

活用すべき PostgreSQL 18 の機能

1. 非同期 I/O（AIO）サブシステム

PostgreSQL 18 の AIO サブシステムにより、I/O 性能が最大 3 倍向上します。この恩恵を受けられるようにスキーマを設計してください。

-- Enable read_stream for sequential scans
-- PG18 automatically uses AIO for:
-- - Sequential scans
-- - COPY operations
-- - Vacuum operations
-- - Index builds

-- Design for larger, sequential access patterns
-- AIO benefits sequential operations more than random access

チェックリスト：

• 頻繁に使われる処理では、シーケンシャルアクセスパターンを優先する
• ランダム I/O を最小限に抑えるようにテーブルを設計する
• パーティショニングを使用して、並列シーケンシャルスキャンを可能にする

2. ネイティブ UUIDv7 サポート

PostgreSQL 18 には、ネイティブの uuidv7() 関数が含まれています。拡張機能ではなく、こちらを使用してください。

-- DEPRECATED: Don't use extensions for UUIDs
-- CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- SELECT uuid_generate_v4();

-- DEPRECATED: Don't use gen_random_uuid() for new tables
-- SELECT gen_random_uuid();

-- CORRECT: Use native UUIDv7 for new primary keys
CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  created_at timestamptz DEFAULT now()
);

-- UUIDv7 benefits:
-- 1. Time-ordered: natural chronological sorting
-- 2. Index-friendly: sequential inserts, no page splits
-- 3. Distributed-safe: no coordination needed
-- 4. Sortable: first 48 bits are millisecond timestamp

既存テーブルの移行パターン：

-- Add new UUIDv7 column alongside existing
ALTER TABLE legacy_table ADD COLUMN id_v7 uuid DEFAULT uuidv7();

-- Backfill with time-ordered UUIDs (preserves order)
UPDATE legacy_table SET id_v7 = uuidv7() WHERE id_v7 IS NULL;

-- For historical data, generate UUIDs that preserve timestamp order
-- Use application code to generate UUIDv7 from original created_at

チェックリスト：

• 新しいテーブルではすべて、主キーに uuidv7() を使用する
• uuid-ossp 拡張機能を新たに使用しない
• 既存の uuid_generate_v4() 列の移行計画を立てる

3. 仮想生成列

PostgreSQL 18 は、仮想（読み取り時に計算される）生成列をサポートしています。

-- STORED: Computed on write, stored on disk (PG12+)
ALTER TABLE products ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || description)) STORED;

-- VIRTUAL: Computed on read, no storage (PG18+)
ALTER TABLE orders ADD COLUMN total_with_tax numeric
  GENERATED ALWAYS AS (subtotal * (1 + tax_rate)) VIRTUAL;

-- When to use VIRTUAL:
-- - Simple calculations
-- - Values that would bloat storage
-- - Infrequently accessed computed values
-- - Values dependent on runtime context

-- When to use STORED:
-- - Expensive computations
-- - Indexed columns (virtual columns cannot be indexed directly)
-- - Frequently accessed values

チェックリスト：

• 単純で、インデックスがほとんど作成されない計算には VIRTUAL を使用する
• インデックスを作成する計算列には STORED を使用する
• 各生成列でその保存形式を使用する理由を文書化する

4. 時間制約（SQL:2011）

PostgreSQL 18 では、時間主キーと時間外部キーが導入されています。

-- Temporal table with validity period
CREATE TABLE product_prices (
  product_id uuid REFERENCES products(id),
  price numeric NOT NULL,
  valid_from timestamptz NOT NULL,
  valid_to timestamptz NOT NULL,

  -- Temporal primary key: unique product per time period
  PRIMARY KEY (product_id, valid_from, valid_to WITHOUT OVERLAPS)
);

-- Temporal foreign key: reference must be valid at point in time
CREATE TABLE order_items (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  order_id uuid REFERENCES orders(id),
  product_id uuid,
  ordered_at timestamptz NOT NULL,

  -- Ensures product_id references a valid price at ordered_at time
  FOREIGN KEY (product_id, PERIOD(ordered_at, ordered_at))
    REFERENCES product_prices (product_id, PERIOD(valid_from, valid_to))
);

二時点パターン：

-- Track both validity time AND transaction time
CREATE TABLE contracts (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  customer_id uuid REFERENCES customers(id),
  terms jsonb NOT NULL,

  -- Validity time: when the contract is effective
  valid_from timestamptz NOT NULL,
  valid_to timestamptz NOT NULL DEFAULT 'infinity',

  -- Transaction time: when we recorded this version
  recorded_at timestamptz NOT NULL DEFAULT now(),
  superseded_at timestamptz NOT NULL DEFAULT 'infinity',

  -- Ensure no overlapping validity periods per customer
  EXCLUDE USING gist (
    customer_id WITH =,
    tstzrange(valid_from, valid_to) WITH &&
  ) WHERE (superseded_at = 'infinity')
);

チェックリスト：

• 時間とともに変化するデータには時間制約を使用する
• 監査要件に応じて、二時点設計を検討する
• 有効期間には WITHOUT OVERLAPS を使用する

5. B-tree インデックスのスキップスキャン

PostgreSQL 18 では B-tree インデックスのスキップスキャンが可能になり、複合インデックスの汎用性が向上します。

-- This index now supports queries on BOTH columns
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- PG17 and earlier: Only efficient for status queries
SELECT * FROM orders WHERE status = 'pending';

-- PG18: Also efficient for date-only queries (skip scan)
SELECT * FROM orders WHERE created_at > '2026-01-01';
-- Skip scan jumps between status values, checking dates in each

インデックス設計への影響：

-- Multi-column indexes are now more valuable
-- Put high-cardinality column second for skip scan benefit
CREATE INDEX idx_events_type_user ON events(event_type, user_id);

-- Both of these are now efficient:
SELECT * FROM events WHERE event_type = 'login';
SELECT * FROM events WHERE user_id = 'abc-123';

チェックリスト：

• 既存のインデックスを見直し、スキップスキャンの機会を探す
• 複数のクエリパターンで役立つ複合インデックスを検討する
• スキップスキャンの効果を得られるよう、カーディナリティの低い列を先頭に置く

スキーマ設計の原則

テーブル設計

-- Standard table template for PG18
CREATE TABLE entity_name (
  -- Primary key: Always UUIDv7
  id uuid PRIMARY KEY DEFAULT uuidv7(),

  -- Foreign keys: Reference with ON DELETE behavior
  parent_id uuid REFERENCES parent_table(id) ON DELETE CASCADE,

  -- Required fields: NOT NULL with sensible defaults
  status text NOT NULL DEFAULT 'pending',

  -- Timestamps: Always timestamptz, never timestamp
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),

  -- Soft delete: Use validity period, not boolean
  deleted_at timestamptz,  -- NULL = not deleted

  -- JSON data: Use jsonb, never json
  metadata jsonb NOT NULL DEFAULT '{}',

  -- Constraints: Named for clarity
  CONSTRAINT entity_name_status_check CHECK (status IN ('pending', 'active', 'completed'))
);

-- Standard indexes
CREATE INDEX idx_entity_name_parent_id ON entity_name(parent_id);
CREATE INDEX idx_entity_name_created_at ON entity_name(created_at);
CREATE INDEX idx_entity_name_status ON entity_name(status) WHERE deleted_at IS NULL;

命名規則

要素	規則	例
テーブル	snake_case、複数形	order_items
列	snake_case	created_at
主キー	id	id uuid
外部キー	{table_singular}_id	order_id
インデックス	idx_{table}_{columns}	idx_orders_status
制約	{table}_{purpose}_check	orders_amount_check
関数	snake_case、動詞を先頭にする	calculate_total()

パーティショニング戦略

-- Time-based partitioning for large tables
CREATE TABLE events (
  id uuid DEFAULT uuidv7(),
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- Automated partition creation (use pg_partman or similar)
-- Or create future partitions in migration

-- UUIDv7 benefit: Partition pruning works because UUIDs are time-ordered
-- Queries on id range can prune partitions

マイグレーションのベストプラクティス

マイグレーションのテンプレート

-- migrations/YYYYMMDDHHMMSS_description.sql

-- Wrap in transaction
BEGIN;

-- Version check
DO $$
BEGIN
  IF current_setting('server_version_num')::int < 180000 THEN
    RAISE EXCEPTION 'Requires PostgreSQL 18 or higher';
  END IF;
END $$;

-- Migration logic here
CREATE TABLE ...;

-- Verify migration
DO $$
BEGIN
  -- Add assertions about expected state
  IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'new_table') THEN
    RAISE EXCEPTION 'Migration verification failed';
  END IF;
END $$;

COMMIT;

安全なスキーマ変更

-- SAFE: Adding nullable column
ALTER TABLE orders ADD COLUMN notes text;

-- SAFE: Adding column with default (PG11+ doesn't rewrite table)
ALTER TABLE orders ADD COLUMN priority int NOT NULL DEFAULT 0;

-- DANGEROUS: Adding NOT NULL to existing column (locks table)
-- Instead, do in steps:
ALTER TABLE orders ADD COLUMN notes_new text;
UPDATE orders SET notes_new = COALESCE(notes, 'none') WHERE notes_new IS NULL;
ALTER TABLE orders ALTER COLUMN notes_new SET NOT NULL;
ALTER TABLE orders DROP COLUMN notes;
ALTER TABLE orders RENAME COLUMN notes_new TO notes;

-- DANGEROUS: Changing column type (rewrites table)
-- Consider: Add new column, migrate data, drop old column

-- SAFE: Creating index concurrently
CREATE INDEX CONCURRENTLY idx_orders_notes ON orders(notes);
-- Note: Cannot be in transaction, requires separate migration step

ダウンタイムゼロのマイグレーションパターン

-- Step 1: Add new column (no lock)
ALTER TABLE orders ADD COLUMN new_status text;

-- Step 2: Backfill in batches (application or background job)
UPDATE orders SET new_status = status WHERE new_status IS NULL LIMIT 10000;
-- Repeat until complete

-- Step 3: Add constraints once backfilled
ALTER TABLE orders ALTER COLUMN new_status SET NOT NULL;

-- Step 4: Add new index concurrently
CREATE INDEX CONCURRENTLY idx_orders_new_status ON orders(new_status);

-- Step 5: Update application to use new column
-- Step 6: Drop old column in future migration

インデックス戦略

インデックスの種類と用途

インデックスの種類	用途	例
B-tree	等価検索、範囲検索、ソート	CREATE INDEX ... ON orders(created_at)
Hash	等価検索のみ（優位性があるケースはまれ）	CREATE INDEX ... USING hash ON lookups(key)
GiST	範囲、幾何データ、全文検索	CREATE INDEX ... USING gist ON events(tstzrange(...))
GIN	配列、JSONB、全文検索	CREATE INDEX ... USING gin ON docs(metadata)
BRIN	非常に大規模で、自然な順序を持つデータ	CREATE INDEX ... USING brin ON logs(created_at)

JSONB インデックス

-- Index specific paths for frequent queries
CREATE INDEX idx_metadata_type ON documents((metadata->>'type'));

-- GIN for flexible key/value queries
CREATE INDEX idx_metadata_gin ON documents USING gin(metadata);

-- GIN with specific operator class for containment queries
CREATE INDEX idx_metadata_path ON documents USING gin(metadata jsonb_path_ops);
-- Supports: metadata @> '{"type": "invoice"}'
-- Smaller index, faster for containment queries

部分インデックス

-- Index only active records
CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending' AND deleted_at IS NULL;

-- Index only non-null values
CREATE INDEX idx_users_email_verified ON users(email)
  WHERE email_verified_at IS NOT NULL;

-- Unique partial index for soft deletes
CREATE UNIQUE INDEX idx_users_email_unique ON users(email)
  WHERE deleted_at IS NULL;

式インデックス

-- Index on function result
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- Index on JSONB expression
CREATE INDEX idx_orders_customer_email ON orders((data->>'customer_email'));

-- Index on date part
CREATE INDEX idx_events_date ON events(date(created_at));

クエリの最適化

Explain Analyze

-- Always use ANALYZE for accurate timing
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'pending';

-- Look for:
-- - Seq Scan on large tables (needs index?)
-- - High actual rows vs estimated rows (stale statistics?)
-- - Buffers: shared hit vs read ratio (memory pressure?)
-- - Loops with high count (N+1 query?)

統計ターゲット

-- Increase statistics for columns with skewed distributions
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;

-- Force statistics update
ANALYZE orders;

-- Check current statistics
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders';

一般的なクエリパターン

-- Pagination: Use keyset, not offset
-- BAD:
SELECT * FROM orders ORDER BY created_at LIMIT 20 OFFSET 10000;
-- GOOD:
SELECT * FROM orders
WHERE created_at < $last_created_at
ORDER BY created_at DESC
LIMIT 20;

-- Counting: Use estimates for UI when exact not needed
-- BAD:
SELECT count(*) FROM large_table WHERE status = 'active';
-- GOOD (for UI display):
SELECT reltuples::bigint AS estimate
FROM pg_class WHERE relname = 'large_table';

-- Existence check: Use EXISTS, not COUNT
-- BAD:
SELECT count(*) > 0 FROM orders WHERE user_id = $1;
-- GOOD:
SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = $1);

データベースアーキテクチャの成果物

スキーマを設計または変更する際は、次の成果物を投稿してください。

## Database Architecture Summary

### Tables Modified/Created

| Table | Change | Rationale |
|-------|--------|-----------|
| orders | Created | New e-commerce functionality |
| order_items | Created | Line items for orders |

### PostgreSQL 18 Features Used

- [ ] UUIDv7 primary keys
- [ ] Virtual generated columns
- [ ] Temporal constraints
- [ ] Skip-scan indexes

### Indexes Added

| Table | Index | Type | Purpose |
|-------|-------|------|---------|
| orders | idx_orders_status_date | B-tree | Skip-scan for status and date queries |
| orders | idx_orders_metadata | GIN | JSONB containment queries |

### Migration Safety

- [ ] All migrations are idempotent
- [ ] No table rewrites on production data
- [ ] Indexes created CONCURRENTLY
- [ ] Backward compatible with current application

### Performance Considerations

- [ ] Query patterns documented
- [ ] Index usage verified with EXPLAIN ANALYZE
- [ ] Partition strategy appropriate for data volume
- [ ] Statistics targets adjusted for skewed columns

**Verified At:** [timestamp]

チェックリスト

データベースアーキテクチャの作業を完了する前に、次の項目を確認してください。

• 新しいテーブルではすべて UUIDv7 主キーを使用している
• すべてのタイムスタンプで timestamp ではなく timestamptz を使用している
• 外部キーに適切な ON DELETE 動作を設定している
• すべての外部キーにインデックスを作成している
• 適切な場合は部分インデックスを使用している
• ゼロダウンタイムでデプロイできる安全なマイグレーションになっている
• クエリパターンを文書化し、EXPLAIN ANALYZE でテストしている
• メリットがある場合は PostgreSQL 18 の機能を活用している
• アーキテクチャの成果物を issue に投稿している

統合

このスキルは次の機能と統合します。

• postgres-rls - RLS はスキーマ設計の上に重ねて適用されます
• postgis - 空間データ型とインデックス
• timescaledb - 時系列拡張機能とハイパーテーブル
• local-service-testing - 実際の PostgreSQL に対するマイグレーションのテスト

参考資料

• PostgreSQL 18 リリースノート
• PostgreSQL 18 の新機能の概要
• UUIDv7 RFC 9562
• Temporal Tables SQL:2011