postgresql-table-design

此技能为设计 PostgreSQL 数据库架构提供了严谨的框架。专为软件工程师、数据库管理员及系统架构师所设计，旨在协助建立优先考虑性能、数据完整性及长期维护性的强大数据模型。遵循这些模式，使用者可以避免常见的陷阱，例如隐式转换、不必要的索引膨胀或错误的类型选择，确保 PostgreSQL 实例在高并发负载下保持稳定与可扩展性。

主要功能包括：

• 标准化的标识符与主键选择建议，针对特定情境区分 BIGINT GENERATED ALWAYS AS IDENTITY 与 UUID 的使用时机。
• 规范化 (3NF) 架构设计，并针对读取性能需求提供审慎的去规范化方案。
• 数据类型最佳实践，强调使用 TEXT 取代 VARCHAR(n)、TIMESTAMPTZ 取代 TIMESTAMP，以及使用 NUMERIC 处理精确货币运算。
• 高级索引管理，包括针对 JSONB、数组与几何数据的 GIN 与 GiST 索引应用。
• PostgreSQL 特有功能指导，例如约束管理、UNIQUE NULLS NOT DISTINCT 设置以及域类型 (Domain types) 的使用。
• 关于处理数据行 churn、TOAST 存储以及 MVCC 相关 Vacuum 机制的实务建议。

使用说明与实务提示：

• 除非有严格的存储空间限制，否则应优先使用 BIGINT。
• 外键 (Foreign Key) 字段必须手动建立索引，PostgreSQL 不会自动完成此步骤。
• 使用 CHECK 约束进行长度验证，而非受限的 VARCHAR 长度，以保持架构灵活性。
• 利用范围类型 (daterange, numrange) 来简化调度与版本控制中的重叠查询逻辑。
• 进行全文检索时，务必为 TSVECTOR 与 TSQUERY 指定语言参数，以确保索引性能与正确性。
• 请注意，由于事务回滚或并发操作，标识符序列可能会出现间断，这是预期的行为，无需刻意修复。
• 处理半结构化数据时，优先选用 JSONB 以利用 GIN 索引与高效的二进制存储。
• 若架构需结合向量嵌入功能，请使用 pgvector 类型以支持向量相似度搜索。