RE: pg12 - partition by column that might have null values

 

>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 query optimization.  Rarely would there be more updated rows than aged/stable rows…in the normal system, having even 3% of the data in churn (updateable) state would be unusual and your description of the data dynamics on this table said that a row updated once, gets the end_date set and then a new row is created.

 

To me, that says, put an index on end_date so you can find/query them quickly, and create partitions on a static date so the rows (and indexes) aren’t always being updated.

 

Mike Sofen