database-design by skillcreatorai/ai-agent-skills
npx skills add https://github.com/skillcreatorai/ai-agent-skills --skill database-design-- 1NF: 原子值,无重复组
-- 2NF: 对复合键无部分依赖
-- 3NF: 无传递依赖
-- 用户表(规范化)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 地址表(独立实体)
CREATE TABLE addresses (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
street VARCHAR(255),
city VARCHAR(100),
country VARCHAR(100),
is_primary BOOLEAN DEFAULT false
);
-- 当读取性能比写入一致性更重要时
CREATE TABLE order_summaries (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(id),
customer_name VARCHAR(255), -- 从 customers 表反规范化而来
total_amount DECIMAL(10,2),
item_count INTEGER,
last_updated TIMESTAMPTZ DEFAULT NOW()
);
-- B-tree(默认)用于等值和范围查询
CREATE INDEX idx_users_email ON users(email);
-- 复合索引(顺序很重要!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- 针对特定条件创建部分索引
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;
-- 为数组/JSONB 列创建 GIN 索引
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
-- 覆盖索引(包含额外列)
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
-- 检查索引使用情况
SELECT
schemaname, tablename, indexname,
idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- 查找缺失的索引
SELECT
relname, seq_scan, seq_tup_read,
idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY seq_tup_read DESC;
-- 始终使用事务
BEGIN;
-- 添加带默认值的列(在 PG 11+ 中非阻塞)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- 并发创建索引(不锁定表)
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);
-- 分批回填数据
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000;
COMMIT;
1. 添加新列(可为空)
2. 部署同时写入两列的代码
3. 回填旧数据
4. 部署从新列读取的代码
5. 删除旧列
-- 始终使用 EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- 需要关注的关键指标:
-- - 顺序扫描 vs 索引扫描
-- - 实际行数 vs 预估行数
-- - 缓冲区:共享命中 vs 读取
-- 对于大型数据集,使用 EXISTS 替代 IN
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 使用键集(游标)分页替代 OFFSET
SELECT * FROM posts
WHERE created_at < '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;
-- 对复杂查询使用 CTE
WITH active_users AS (
SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);
-- 主键
ALTER TABLE users ADD PRIMARY KEY (id);
-- 带级联操作的外键
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- 检查约束
ALTER TABLE products ADD CONSTRAINT chk_price_positive
CHECK (price >= 0);
-- 唯一约束
ALTER TABLE users ADD CONSTRAINT uniq_users_email UNIQUE (email);
-- 排除约束(无重叠范围)
ALTER TABLE reservations ADD CONSTRAINT excl_no_overlap
EXCLUDE USING gist (room_id WITH =, tsrange(start_time, end_time) WITH &&);
created_at 和 updated_at 时间戳deleted_at)周安装量
183
代码仓库
GitHub 星标数
957
首次出现
2026年1月20日
安全审计
安装于
opencode145
gemini-cli144
codex138
cursor138
github-copilot133
claude-code126
-- 1NF: Atomic values, no repeating groups
-- 2NF: No partial dependencies on composite keys
-- 3NF: No transitive dependencies
-- Users table (normalized)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Addresses table (separate entity)
CREATE TABLE addresses (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
street VARCHAR(255),
city VARCHAR(100),
country VARCHAR(100),
is_primary BOOLEAN DEFAULT false
);
-- When read performance matters more than write consistency
CREATE TABLE order_summaries (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(id),
customer_name VARCHAR(255), -- Denormalized from customers
total_amount DECIMAL(10,2),
item_count INTEGER,
last_updated TIMESTAMPTZ DEFAULT NOW()
);
-- B-tree (default) for equality and range queries
CREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Partial index for specific conditions
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;
-- GIN index for array/JSONB columns
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
-- Covering index (includes additional columns)
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);
-- Check index usage
SELECT
schemaname, tablename, indexname,
idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Find missing indexes
SELECT
relname, seq_scan, seq_tup_read,
idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY seq_tup_read DESC;
-- Always use transactions
BEGIN;
-- Add column with default (non-blocking in PG 11+)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- Create index concurrently (doesn't lock table)
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);
-- Backfill data in batches
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000;
COMMIT;
1. Add new column (nullable)
2. Deploy code that writes to both columns
3. Backfill old data
4. Deploy code that reads from new column
5. Remove old column
-- Always use EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- Key metrics to watch:
-- - Seq Scan vs Index Scan
-- - Actual rows vs Estimated rows
-- - Buffers: shared hit vs read
-- Use EXISTS instead of IN for large sets
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- Pagination with keyset (cursor) instead of OFFSET
SELECT * FROM posts
WHERE created_at < '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;
-- Use CTEs for complex queries
WITH active_users AS (
SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);
-- Primary key
ALTER TABLE users ADD PRIMARY KEY (id);
-- Foreign key with cascade
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- Check constraint
ALTER TABLE products ADD CONSTRAINT chk_price_positive
CHECK (price >= 0);
-- Unique constraint
ALTER TABLE users ADD CONSTRAINT uniq_users_email UNIQUE (email);
-- Exclusion constraint (no overlapping ranges)
ALTER TABLE reservations ADD CONSTRAINT excl_no_overlap
EXCLUDE USING gist (room_id WITH =, tsrange(start_time, end_time) WITH &&);
created_at and updated_at timestampsdeleted_at) for important dataWeekly Installs
183
Repository
GitHub Stars
957
First Seen
Jan 20, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
opencode145
gemini-cli144
codex138
cursor138
github-copilot133
claude-code126
GSAP React 动画库使用指南:useGSAP Hook 与最佳实践
2,700 周安装
Apple Notes CLI 终端命令行工具 - 在 macOS 终端管理 Apple 笔记
761 周安装
video-frames:使用ffmpeg从视频中提取单帧和缩略图的命令行工具
768 周安装
Oracle到PostgreSQL迁移集成测试创建指南 | 数据库迁移测试最佳实践
768 周安装
Oracle到PostgreSQL数据库迁移审查工具:风险评估与验证指南
766 周安装
TikTok营销技能:AI脚本生成、内容策略与自动化发布全流程指南
800 周安装
FlowStudio MCP 调试 Power Automate 云流失败 - 分步诊断与修复指南
806 周安装