postgresql

PostgreSQL Table Design - 数据库表设计专家技能

技能概述

PostgreSQL Table Design 是一个专业的 PostgreSQL 数据库表设计助手，帮助开发者遵循最佳实践进行 schema 设计、数据类型选择、索引规划和性能优化。

适用场景

1. PostgreSQL 表结构设计与建模

当您需要为 PostgreSQL 数据库设计新表或优化现有表结构时，这个技能可以提供专业的数据类型选择建议、约束设计原则和索引优化方案。从用户表、订单表到复杂的时序表设计，都能获得符合 PostgreSQL 最佳实践的指导。

2. 数据类型选择与约束设计

PostgreSQL 提供了丰富的数据类型（JSONB、数组、范围类型等），选择合适的类型对性能和存储至关重要。这个技能能帮助您理解何时使用 BIGINT vs UUID、TIMESTAMPTZ vs TIMESTAMP、TEXT vs VARCHAR 等常见选择，以及如何正确设置 NOT NULL、CHECK、UNIQUE 等约束。

3. 高性能索引与分区规划

当面对大数据量表或高并发查询需求时，这个技能可以指导您设计高效的索引策略（B-tree、GIN、GiST、部分索引、表达式索引等），以及何时使用分区表、行级安全（RLS）等 PostgreSQL 高级特性来提升性能和安全性。

核心功能

1. 数据类型最佳实践

提供 PostgreSQL 专用的数据类型选择指南，涵盖 ID 字段（BIGINT IDENTITY vs UUID）、时间字段（TIMESTAMPTZ）、金额字段（NUMERIC）、字符串（TEXT vs VARCHAR）等常见场景，帮助避免使用被废弃的类型（SERIAL、TIMESTAMP、MONEY 等）。

2. 索引与约束设计

深入讲解 PostgreSQL 索引类型（B-tree、GIN、GiST、BRIN）及其适用场景，强调外键需要手动索引这一易被忽视的要点，以及部分索引、表达式索引、覆盖索引等高级技巧，确保查询性能最优。

3. 性能优化与高级特性

针对不同工作负载（高写入、高更新、时序数据）提供专门的设计建议，涵盖分区表策略、行级安全（RLS）、JSONB 索引优化、TimescaleDB 时序扩展、PostGIS 空间数据、pgvector 向量搜索等高级功能的使用指导。

常见问题

PostgreSQL 建表时主键应该用什么类型？

对于大多数应用场景，推荐使用 BIGINT GENERATED ALWAYS AS IDENTITY 作为主键类型。它自增、高效、占用空间小。只有在需要全局唯一性（如分布式系统）或需要不透明 ID 时才考虑使用 UUID。UUID 会占用更多存储空间且索引效率较低，时序表或日志表甚至可以不设置主键。

PostgreSQL 外键会自动创建索引吗？

不会。这是 PostgreSQL 与其他数据库的一个重要区别。外键约束不会自动创建索引，而缺少索引会导致在父表删除/更新时产生锁等待和性能问题。务必在创建外键后手动为外键列添加索引，这能显著提升 JOIN 查询性能并避免锁争用。

PostgreSQL 存储金额应该用什么数据类型？

必须使用 NUMERIC(p,s) 类型存储金额，绝对不要使用 FLOAT 或 DOUBLE PRECISION。浮点数存在精度问题，可能导致金额计算错误。例如 NUMERIC(10,2) 表示总共 10 位数字，其中 2 位小数，可以存储最大 99999999.99 的金额。PostgreSQL 的 MONEY 类型已不推荐使用。

PostgreSQL 设计表时有哪些常见陷阱？

常见陷阱包括：1) 使用 VARCHAR(n) 而非 TEXT；2) 使用 TIMESTAMP 而非 TIMESTAMPTZ；3) 忘记为外键添加索引；4) 过度使用 UUID 导致索引膨胀；5) 过早反范式化；6) 更新频繁的列与稳定列混在同一表；7) 大字段未考虑 TOAST 存储；8) 唯一约束未注意多 NULL 行为。遵循本技能的最佳实践可避免这些问题。