Pseudo-partitioning

[aodhan-domhnaill]

Describe the feature

Incremental materialization that breaks a single table up into multiple tables based on the value in a column.

Describe alternatives you've considered

Using multiple models for each value won't work, because if a new value gets added to the column, then it won't automatically create the partition

Additional context

Not specific to a given database, because it simply creates multiple tables for the values in the column.

I'm not sure how ref would work. It's possible that a view could be created that unions the partition tables in a way that helps the query optimizer drop unused partitions. For example, union while hard coding the partition column values (untested).

Additionally, a strategy for table naming would be needed.

Who will this benefit?

Anyone who needs partitions.

Are you interested in contributing this feature?

Yes, but I need guidance.

[jtcohen6]

Thanks @aidan-plenert-macdonald! There's an existing feature request for this: #1637

That issue has been open for a while, and it's still very much an open question for us. I think this functionality would be tricky to implement—for the reasons you mention, among others—but it's an important one, and I'd be interested in taking another look after v1.0 (so, possibly next year).

I'm going to close this issue in the meantime, to keep the conversation centralized over there.