database - 如何有效处理同一张表中的冷热数据？

Question

我们有一个大的记录表，在一周内更新了很多，然后它们被冻结并以只读方式存储两年。该应用程序是用hibernate制作的。

每条记录都有一个唯一的 id 和一个状态字段，当记录被冻结时，状态字段设置为 0。ID 是按时间顺序排列的，因为它们来自一个序列。

我们希望以最有效的方式存储热记录：在 SSD 驱动器上，填充因子为 45%，因此更新将是热更新（索引不会改变，因为新元组与前一个在同一页上）。冻结的记录将存储在填充因子为 100 的 HDD 上，因为它们永远不会被修改。请注意，有外键表。它们也必须以相同的方式进行分区，因此当我们分离主记录的分区时，我们也可以分离链接项目的相应分区。

我们必须仅在数据库上处理该问题，而不在应用程序端进行任何更改（休眠映射除外）。

我尝试了几种解决方案：

两级分区

在 ID 和状态上使用范围分区。每周一个新分区分为两个子分区，分别用于状态热和状态冷。

主键必须包括 ID 和状态的所有分区字段。这需要在所有链接项目中添加这些字段，并且必须重写应用程序。

使用奇/偶 ID

我想只对新记录（很热）使用奇数 ID。并在状态更新上添加一个触发器，该触发器会增加 id 以便冻结记录。奇数记录将在热子分区上，偶数记录在冷子分区上。为了拆分奇数甚至偶数，我想到了使用模数/余数分区。

如果将模数应用于原始 ID 字段，这将正常工作，不幸的是它是在哈希上计算的。所以记录的分布是随机的。有没有办法选择散列函数？

使用否定 ID

我现在正在考虑对固定记录使用负 ID。还没有测试它。

有没有官方的方法可以做到这一点？有大量具有这种行为的应用程序。

标签： databasepostgresqlpartitioning

Answer 1

如果要使旧行过期，请不要使用生成的序列id来确定“旧”的含义。相反，添加一个时间戳列。

然后按该时间戳列上的范围进行分区。将相同的时间戳列引入与大表具有外键关系的所有表。

大表的每个分区都按状态列出分区：0 的分区在一个存储速度慢的表空间上，另一个包含所有其他状态的分区在一个存储速度快的表空间上。然后行将在冻结时自动移动到慢速存储。

不要在分区表上定义外键，否则您将无法删除分区。相反，定义各个分区之间的外键约束。

“快速”子分区将随着年龄的增长而变空。您可能无法删除它（如果您在分区级别上有外键），但您可以VACUUM在子分区为空时将其缩小。

然后，您只需删除适当的分区即可使旧数据过期。