Postgres Temporal / Partitioning Implementation Strategy

Question

I am looking to add VALIDTIME temporal support to my application and would like to get some feedback. It looks like Postgres doesn't have native temporal support yet (I know there are extensions), so I'd have to go with the manual strategy for now. Logically, this is what I'm after:

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
  )
)

Temporal

Current row: UPPER(ValidTS) = '9999-12-31 23:59:59.999999'
Old row: UPPER(ValidTS) <> '9999-12-31 23:59:59.999999'

Temporal DELETE: UPDATE the "Current row" and set UPPER(ValidTS) = current_timestamp
Temporal INSERT: INSERT "Current row"
Temporal UPDATE: temporal DELETE + temporal INSERT (must be done in same transaction)

Temporal SELECT (CURRENT): SELECT "Current row"
Temporal SELECT (AS OF): SELECT ... WHERE ValidTS @> <AS_OF_TS>

Partitioning

Postgres 10 supports native table partitioning, so I'd like to take advantage of that:

partition for current rows (super-quick look up)
partition for history rows (with sub-partitions if needed)

I've been playing around with it and there seems to be some limitations. For example, I can't use a CASE statement like in the example above -- an error about IMMUTABLE expressions. I think it has to do with using TIMESTAMP WITH TIME ZONE. Also, the documentation says you can't define UNIQUE / PRIMARY KEY constraints on the partitioned table.

My questions

How does my temporal setup look? What is your setup? Any tips or things to look out for?
What is your partitioning strategy with temporal data? How do you ensure quick access to current rows? Any things to watch out for?

Laurenz Albe Laurenz Albe · Accepted Answer · 2018-02-22T19:37:27

Some comments on your setup:

~~PostgreSQL cannot automatically move rows between partitions (yet).~~
Support to automatically move rows between partitions has been added in PostgreSQL 11
I'd expect a validfrom and validto timestamp, but I can only see one timestamp in your description. How do you want to implement AS OF?
I don't think it is a good idea to store timestamps as text. Store it as date or timestamptz, then you don't have any problems with non-IMMUTABLE functions. Then I would use infinity and not an arbitrary date like 9999-12-31 for active rows.

I would not partition the table, but rather use indexes. If necessary, you can create partial indexes with ... WHERE validto = 'infinity'. If all queries for current data include the same condition, these indexes will be used. I guess it depends on your queries what would be the best approach. Of course, to speed up sequential scans, only partitioning would help.

Postgres Temporal / Partitioning Implementation Strategy

1 Answers