How to partition by date whilst supporting constraint exclusion on both start/end timestamp columns

[atomaras]

My table contains 2 timestamp columns start_time and end_time.
We know for a fact that:

1. start_time and end_time will never be more than 2h apart.
2. start_time and end_time will never extend beyond the boundaries of single UTC day.

My clients will be sending filters that contain any combination of these two columns.
Examples:
start_time between A and B
start_time > A and end_time < B
end_time between A and B
etc.

I want to range partition this table in daily intervals.

How do I make sure constraint exclusion (partition pruning) works correctly with filters against either or both the
start_time and end_time columns?

/cc @keithf4

I believe it would work If I partitioned by start_time AND changed your apply_constraints script to create a check for the end_time that was not based on min/max of existing data but was simply based on the existing start_time ranges of each partition.

So instead of this logic:

I would like to copy over the start_time boundaries of each partition to the end_time CHECK for that same partition.
Here is the query to find the partition expression per partition from which we can extract the FROM and TO values in order to
set them as the end_time CHECK constaint

Makes sense?

[keithf4]

The extra constraints that apply_constraints provides would be the way to do this since pg_partman currently only supports single column partitioning. That should work for you, but I probably won't be able to adapt that code back into pg_partman at this time since this is very specific to your use-case.

But honestly, this may be a better candidate for using the ranged data type (tstzrange) depending on your usage.

https://www.postgresql.org/docs/13/rangetypes.html
https://www.postgresql.org/docs/13/functions-range.html

This data type isn't currently supported in partitioning as far as I'm aware, but it can make writing queries for time ranges like this significantly easier and provides many more flexible operations (overlap, intersect, right of, left of, etc).

[atomaras]

I am attaching the whole function here so you can play around with it and tweak it more easily

apply_end_time_constraints.txt

[atomaras]

By the way there might be a way to enhance pg_partman to make it more flexible in dealing with constraints.
What if customers could specify the name of a function as well the constraint_cols for example p_constraint_function_name in the part_config that pg_partman could invoke in order to get back the custom CHECK constraint expression to use for each constraint column.
The function arguments would include the table_name, the column_name, the from value and the to value of the partition range.
The function would return the expression that pg_partman should use when creating the constraint.
Of course you would also need to provide a solution for customers like me who want the constraints to be created on every partition
automatically (i.e no "old" partition only logic).

[keithf4]

I'll be honest, my time is a bit limited to look at that more in depth right now. I'm currently trying to solve a bit of a tricky issue in pg_partman already and even that has been delayed - #369

So, I'm not sure when I'll have a chance to help with something like this. If you do happen to find a solution before I have a chance to look, please let me know and share the solution here if you don't mind in case it helps someone else.

Thank you!

[atomaras]

No worries. Managed to create a custom function that traverses all partitioned tables and applies my constraint on each partition.
I'll just have to remember to always call that function right after run_maintenance() is called.

[keithf4]

Could just write a new function/procedure that can take whatever arguments run_maintenance() or run_maintenance_proc() does and then add your custom function call run run after that. Then you only have to worry about calling one function and it takes care of everything.

Glad to hear you got it working!