密码存储系统表设计：带唯一约束的表按日期排序分页方案咨询

一、credentials表拆分方案的可行性及优化 #

你提出的拆分双表的方案完全可行，这种按查询场景拆分表的思路在数据库设计中十分常见，能很好解决唯一性校验与高效排序分页的矛盾需求。

拆分后的表设计参考 #

1. 唯一性校验表（credentials_unique）
   仅保留用于唯一性约束的核心字段和关联ID，减少存储开销：

create table credentials_unique
(
    user_login      text,
    resource_name   text,
    resource_login  text,
    id              uuid,
    primary key (user_login, resource_name, resource_login)
);

作用：快速校验同一用户下同一资源的登录账户是否重复，同时通过id关联到完整凭证数据。

2. 排序分页表（credentials_by_created）
   以user_login为分区键，created_at desc为聚类键，加入id避免同一时间创建的凭证出现主键冲突：

create table credentials_by_created
(
    user_login              text,
    created_at              timestamp,
    id                      uuid,
    resource_name           text,
    resource_login          text,
    changed_at              timestamp,
    password_security_level int,
    resource_password       text,
    primary key (user_login, created_at desc, id)
);

作用：直接按用户分区，通过created_at降序快速获取数据，完美支持分页查询。

关键注意事项 #

• 数据一致性：新增/更新/删除凭证时，需保证两个表的操作原子性。关系型数据库可通过事务包裹操作；分布式数据库可使用批量写入机制避免数据不一致。
• 替代方案（关系型数据库适用）：如果数据量不大，也可以不拆分表，给credentials表添加(user_login, created_at desc)的复合索引，通过索引实现高效排序分页。但数据量较大时，索引维护成本会显著提升，拆分表的方案更优。

二、credentials_history表多表设计的合理性及改进 #

你打算对历史表采用类似多表设计的思路是合理的，因为历史数据的查询场景往往多样，单表难以覆盖所有高效查询需求。

优化后的历史表设计 #

1. 基础历史表（保留原设计）
   保留原表用于按凭证ID查询历史：

create table credentials_history
(
    credential_id     uuid,
    resource_password text,
    changed_at        timestamp,
    primary key (credential_id, changed_at)
);

2. 按用户维度的历史表（credentials_history_by_user）
   满足“查询某用户所有密码修改历史”的需求：

create table credentials_history_by_user
(
    user_login        text,
    changed_at        timestamp,
    credential_id     uuid,
    resource_name     text,
    resource_login    text,
    resource_password text,
    primary key (user_login, changed_at desc, credential_id)
);

3. 按资源维度的历史表（credentials_history_by_resource）
   满足“查询某用户某资源的密码修改历史”的需求：

create table credentials_history_by_resource
(
    user_login        text,
    resource_name     text,
    changed_at        timestamp,
    credential_id     uuid,
    resource_login    text,
    resource_password text,
    primary key (user_login, resource_name, changed_at desc, credential_id)
);

改进建议 #

• 历史数据的写入：历史数据属于追加式数据（一般不会修改/删除），写入时只需同时写入所有相关表即可，一致性风险较低。
• 存储优化：如果历史数据量极大，可以考虑按时间分区（如按月份）进一步优化查询性能和存储成本。

额外安全提示 #

所有密码（用户密码、资源密码）必须加密存储，禁止明文保存。推荐使用bcrypt、Argon2等慢哈希算法，避免彩虹表攻击。

内容的提问来源于stack exchange，提问作者ief234