PostgreSQL中存储用户每日通知时间的最优表结构设计问询

1 初始ARRAY列方案评估 #

你初步设想的用户表新增ARRAY类型列存储选中时段的方案可以用，适用边界和性能表现如下：

• 适用场景：用户量10万以下、无高并发推送要求的中小项目
• 优势：
  • 实现成本极低，无需额外建表，用户修改通知时段仅需更新单条记录的对应字段
  • 存储开销极小，单用户配置最多仅占用24bit空间，部分数据库支持位存储替换数组进一步压缩
• 性能说明：
  • 如果你用PostgreSQL等支持GIN索引的数据库，给ARRAY列创建GIN索引后，每小时筛选查询可直接走索引，无需全表扫描，查询示例：SELECT * FROM users WHERE notify_hours @> ARRAY[当前小时]::int[]，十万级用户查询耗时在毫秒级
  • 如果你用不支持数组索引的数据库（如低版本MySQL），该方案会触发全表扫描，用户量过万后查询性能会明显下降

2 通用最优方案：独立通知时段配置表 #

全规模场景下最推荐的建模方式，兼容所有关系型数据库，性能和扩展性都拉满：

表结构设计 #

表名：user_notify_hours

• 联合主键设置为(user_id, notify_hour)，同时给notify_hour字段建立普通索引

优势 #

• 查询性能极强：每小时筛选待通知用户仅需执行SELECT DISTINCT user_id FROM user_notify_hours WHERE notify_hour = 当前小时，直接走索引，哪怕千万级配置记录查询耗时也在毫秒级，完全不需要扫描全表
• 兼容性好：不依赖数组、位运算等数据库特有能力，所有关系型数据库都支持
• 扩展性强：后续如果需要给不同时段配置不同的通知类型、通知渠道等规则，仅需新增对应字段即可

注意事项 #

用户修改通知时段时，需要先删除该用户的所有旧配置记录，再批量插入新的配置，单用户小批量写入的性能完全可以满足需求

3 可选极致优化方案：位存储字段 #

如果有极致存储压缩需求可以选该方案，普通场景不推荐：

• 实现方式：用户表新增int(3)类型的notify_hour_mask字段，用24个bit位分别对应0-23点，对应位为1表示该时段需要接收通知，比如1点、6点、23点对应的mask值为(1<<1) + (1<<6) + (1<<23) = 8388738
• 优势：存储开销比ARRAY方案更低，加函数索引后查询性能也很高，查询示例：SELECT * FROM users WHERE notify_hour_mask & (1<<当前小时) > 0
• 缺点：可读性极差，维护成本高，排查问题时需要手动做位运算转译，无特殊需求不建议使用

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