Postgres时间 /分区实施策略

Question

我希望在我的应用程序中添加VALIDTIME时间支持，并希望获得一些反馈。看来Postgres还没有本机的时间支持(我知道有扩展(，所以我现在必须采用手动策略。从逻辑上讲，这就是我所追求的：

CREATE TABLE MyPartitionedTable(
    c1 int, 
    ValidTS tstzrange -- VALIDTIME field
)
PARTITION BY LIST (
  (
    CASE WHEN CAST(UPPER(ValidTS) AS DATE) = DATE '9999-12-31' THEN 1 -- Store all current rows in one partition
    ELSE 0 -- Store everything else in another partition
    END
  )
)

临时

当前行： UPPER(ValidTS) = '9999-12-31 23:59:59.999999'
旧行： UPPER(ValidTS) <> '9999-12-31 23:59:59.999999'

时间删除： UPDATE"当前行"和 set UPPER(ValidTS) = current_timestamp
时间插入： INSERT"当前行"
时间更新：时间DELETE 时间INSERT(必须在同一交易中完成(

临时选择(当前(： SELECT "Current row"
临时选择(如(： SELECT ... WHERE ValidTS @> <AS_OF_TS>

分区

Postgres 10支持本地表分区，所以我想利用这一点：

• 当前行的分区(超级Quick查找(
• 历史行的分区(如果需要的话，则有子分区(

我一直在玩它，似乎有一些局限性。例如，我无法在上面的示例中使用CASE语句 - 关于IMMUTABLE表达式的错误。我认为这与使用TIMESTAMP WITH TIME ZONE有关。另外，文档说您不能在分区表上定义UNIQUE/PRIMARY KEY约束。

我的问题

1. 我的时间设置看起来如何？您的设置是什么？有任何提示或事情要注意吗？
2. 您使用时间数据的分区策略是什么？如何确保快速访问当前行？有什么事要注意吗？

Answer 1

在您的设置上的一些评论：

1. PostgreSQL无法在分区之间自动移动行。
   支持在PostgreSQL 11

中添加了在分区之间自动移动行的支持。

2. 我希望validfrom和validto时间戳，但我只能在您的描述中看到一个时间戳。您要如何实现AS OF？

3. 我认为将时间戳作为文本不是一个好主意。将其存储为date或timestamptz，那么您在非IMMUTABLE功能上没有任何问题。然后，我将使用infinity，而不是像9999-12-31那样的任意日期。

我不会划分表，而是使用索引。如有必要，您可以使用... WHERE validto = 'infinity'创建部分索引。如果当前数据的所有查询都包含相同的条件，则将使用这些索引。我想这取决于您的查询，这将是最好的方法。当然，为了加快顺序扫描，只有分区会有所帮助。