PostgreSQL 关系型数据库设计实战：从 users 到 posts 表构建

izx888
3 阅读5分钟

PostgreSQL 关系型数据库设计实战：从 users 到 posts 表构建

（基于 PostgreSQL/psql，使用提供的 users/posts 表结构、约束、外键、事务概念等内容整理）

1. 关系型数据库的核心概念（RDBMS）

关系型数据库（Relational Database Management System）以二维表格形式组织数据，主要特点：

  • 表（Table）：相当于“类”或“实体”，如 users、posts
  • 行（Row / Record / Tuple）：表中的一条数据，相当于“对象实例”
  • 列（Column / Field / Attribute）：表的属性，如 id、name、title
  • 主键（Primary Key）：唯一标识每一行的字段/组合，通常自增
  • 外键（Foreign Key）：建立表与表之间的引用关系，保证参照完整性
  • ACID 事务特性：保证数据操作的可靠性和一致性

PostgreSQL（简称 psql）是目前最接近 SQL 标准的开源关系型数据库之一，支持高级特性（如 JSONB、全文搜索、窗口函数、生成列等），非常适合博客、文章系统、社交、小程序后端等场景。

2. 主键、自增与索引基础

主键是表的“身份证”，必须唯一且非空。

PostgreSQL 中推荐的自增主键写法（现代标准）：

id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • BIGINT：8 字节，范围 ≈ ±9×10¹⁸，远超 INT（21 亿），未来-proof
  • GENERATED BY DEFAULT AS IDENTITY：SQL 标准自增方式，等价于 SERIAL 但更规范
  • PRIMARY KEY：自动创建唯一索引 + 非空约束

为什么不用 SERIAL？
SERIAL 是 PostgreSQL 历史遗留，底层仍是序列（sequence），但 IDENTITY 更符合 SQL:2003 标准，且在某些场景下迁移/复制更友好。

3. 唯一约束（UNIQUE）与索引

唯一约束会自动创建唯一索引，常用于：

  • 用户名（username / name）
  • 邮箱（email）
  • 文章 slug（url 友好标识）

示例：

name VARCHAR(255) NOT NULL UNIQUE

注意事项：

  • UNIQUE 允许 NULL（但 PostgreSQL 中一个字段只能有一个 NULL 被视为不同值）
  • 如果业务不允许重复 NULL，可加额外 CHECK 约束或用 coalesce 技巧
  • 索引占用空间，但极大提升 WHERE name = ? 的查询速度

4. 外键约束（Foreign Key）设计要点

外键建立表间参照完整性，防止“孤儿记录”。

经典文章系统外键示例：

"userId" BIGINT NOT NULL,
CONSTRAINT fk_posts_user FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE

常用 ON DELETE 策略（PostgreSQL 支持）：

策略行为说明适用场景
NO ACTION默认，违反约束时报错最安全
RESTRICT同 NO ACTION（早期区别，现已几乎相同）—
CASCADE主表删除时，自动删除从表相关记录用户删除 → 其所有文章/评论也删
SET NULL主表删除时，从表外键字段置为 NULL文章作者注销 → 文章保留但作者为空
SET DEFAULT主表删除时，从表外键置为默认值较少用

推荐：文章系统大多用 ON DELETE CASCADE 或 SET NULL，视业务是否允许“无主文章”。

性能提醒：

  • 外键会增加 INSERT/UPDATE/DELETE 的检查开销
  • 高并发写入场景可考虑延迟外键检查（DEFERRABLE INITIALLY DEFERRED）

5. users 表完整设计（参考提供结构）

CREATE TABLE users (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name        VARCHAR(255) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,          -- 存储 bcrypt 等哈希值
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

审计字段（created_at / updated_at） 是高级工程师的标配，便于：

  • 数据溯源
  • 增量同步（ETL、CDC）
  • 业务排序（最新注册用户）

触发器自动更新 updated_at（可选高级写法）：

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = CURRENT_TIMESTAMP;
   RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER trig_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

6. posts 表完整设计

CREATE TABLE posts (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    content     TEXT,                           -- 长文本用 TEXT，无长度限制
    "userId"    BIGINT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_posts_user 
        FOREIGN KEY ("userId") 
        REFERENCES users(id) 
        ON DELETE CASCADE
);

为什么 content 用 TEXT 而非 VARCHAR(n)？

  • TEXT 在 PostgreSQL 中没有长度限制，存储效率与 VARCHAR 几乎相同
  • VARCHAR(n) 只在 n ≤ 4000 左右有微小优势，且增加约束负担
  • 现代做法：文章正文一律 TEXT

7. 常见 JOIN 类型对比（文章系统视角）

JOIN 类型返回内容典型使用场景
INNER JOIN两表都匹配的记录查询有作者的文章
LEFT JOIN左表全部 + 右表匹配（无匹配补 NULL）查询所有用户及其文章（即使没发文）
RIGHT JOIN右表全部 + 左表匹配（较少用）—
FULL OUTER JOIN两表所有记录，缺匹配补 NULL对比差异（很少用）

示例：查询所有用户及其文章数量（含 0 篇）

SELECT 
    u.id, u.name, 
    COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p."userId" = u.id
GROUP BY u.id, u.name
ORDER BY post_count DESC;

8. ACID 事务在文章系统中的体现

特性含义文章系统例子
A原子性：全成功或全失败发布文章 + 同时插入 tags、likes 初始记录
C一致性：业务规则始终满足用户发文后，users 表文章计数 +1（如果有）
I隔离性：并发事务互不干扰两人同时点赞同一文章，不会重复计数
D持久性：提交后永久保存文章发布后，即使服务器宕机也不会丢失

隔离级别（PostgreSQL 默认 Read Committed）：

  • 大多数业务够用
  • 高并发计数场景可考虑 Serializable（代价高）

9. psql 常用命令速查

  • \l 或 \list → 列出所有数据库
  • CREATE DATABASE xxx ENCODING='UTF8';
  • \c xxx → 切换数据库
  • \dt → 列出当前库所有表
  • \d users → 查看 users 表结构（含索引、约束）
  • \du → 查看角色/用户
  • \password → 修改当前用户密码

10. 小结与建议

  • 优先使用 BIGINT + IDENTITY 做主键
  • 字符串字段几乎都加 NOT NULL
  • 外键默认带 ON DELETE CASCADE（文章系统常见）
  • 养成加 created_at / updated_at 的习惯
  • content 等长文本一律用 TEXT
  • 索引优先加在 WHERE / JOIN / ORDER BY 频繁出现的字段（name、userId、created_at 等）
  • 生产环境记得开启 WAL、定期 VACUUM ANALYZE、监控死锁

通过以上设计，users 和 posts 表已能支撑一个基础的博客/文章系统，后续可扩展 comments、likes、tags、favorites 等表，保持一致的外键风格和审计字段。

avatar
文章
阅读
粉丝