Partition retention time based on tenant

[anandsowm]

In a multi-tenant app, where pg_partman and citus extensions are used, is there a way to define the partitioned table's 'retention' period at each 'tenant' level, rather than for the entire table scope? For tenant A, retention can be 12 months, for tenant B, retention can be 24 months, though both are monthly partitions. I do not see a way to achieve that with present part_config table structure, since this may be common need for many multi-tenant SaaS apps, wondering if there is any approach for handling this.

[keithf4]

pg_partman does not have this sort of support at this time.

Even with this sort of support. I'm not sure how you would do that in a single level partition scenario. The retention would essentially be whatever your longest period was. Just as a thought experiment, I think you'd have to sub-partition with the top level being your tenant value and then the sub-partition would be time. You'd then be able to set different retention's per tenant. This would be challenging to do in a general sense in pg_partman, but, if the tenant value is integer-based, you could do the sub-partitioning with pg_partman, but you'd have to have some external process doing the retention per tenant.

[anandsowm]

Thanks for your detailed response. The first level partition by tenant that you mention is what is achieved by sharding using the Citus extension, so I would not want to repeat that at partition as well. If the partman.part_config table can be extended or a child table can be added to it that says for parent_table X, Entity_column, Start_EntityID, End_EntityID, retention_period = 'interval', then for different entity IDs we would be able to set different periods of retention based on a column other than the partitioned column.

[keithf4]

If Citus is already handling the tenant split, then i would think it could potentially just handle the different sets of partitions underneath that as individually configured partition sets in pg_partman. Then they could each have their own retention period. Have you discussed this with the Citus team? This seems like a very specialized use-case and not sure it really fits in pg_partman right now.

[anandsowm]

Yes, I did discuss with citus team as well. They handle partitions at global level, not at the individual tenant level, and so could not handle this use-case at present. See citusdata/citus#5647