Just create a partial index on id column where end_date = infinity (or null
if you really prefer that pattern) and the system can quickly find the rows
that are still most current revision. How many rows do you have in this
table? Or foresee ever having? What took you down the road of partitioning
Not sure how data storage is relevant here, I was only focusing on query
optimization. Lets say that most of the data isnt moving (history data).
However, objects can be changed and therefore new revisions are added and
the previous revisions updated (their end_date is updated). If you run
queries
>but the start time doesnt indicates that the object is the most recent, it
>just indicates when the object was added to your table…
>on each partition I'll create range partition on the end_date so that I can
>search for revisions faster.
I believe you are confusing data storage with que
but the start time doesnt indicates that the object is the most recent, it
just indicates when the object was added to your table. If your queries
involve the start_time I can understand why u set it as a partition column,
otherwise is isnt useful. In most of my queries I query by one of 2 options
From: Mariel Cherkassky
Sent: Wednesday, October 02, 2019 12:37 AM
Whenever I have a new revision of that object, I update the end_time of the
latest revision to be now() and I add a new record of that object with end_date
null.
The null value is used to find most recent revisions of objects..
Whenever I have a new revision of that object, I update the end_time of the
latest revision to be now() and I add a new record of that object with
end_date null.
The null value is used to find most recent revisions of objects..
Thanks for the suggestion of infinity ! I'll try it.
Mariel Cherkassky wrote:
> In PG12 I'm trying to create partitions by range on a date column
> that might be null (indicates it is the most recent version of the
> object). My PK has to include the partition column, therefore I'm
> getting an error that I cant create a primary key with the specific
