database-architecture

PostgreSQL 18 数据库架构

概述

PostgreSQL 18 带来了变革性改进：AIO 子系统可将 I/O 性能提升 3 倍，原生 UUIDv7 取代 UUID 库，时间约束支持双时态数据建模。本技能可确保你正确利用这些功能。

核心原则： 围绕 PostgreSQL 18 的优势进行设计。不要沿用旧版本或其他数据库的模式。

开始时声明：“我正在应用 database-architecture，以确保遵循 PostgreSQL 18 的最佳实践。”

适用场景

只要涉及以下任意模式，就必须使用本技能：

模式	示例
**/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 主键
• 所有时间戳均使用 timestamptz，而非 timestamp
• 外键设置了恰当的 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