REPLACE view performance improvement needed and "AS OF SYSTEM TIME" supported

[GlennFawcett-doordash]

Is your feature request related to a problem? Please describe.
We are using "CREATE or REPLACE VIEW" to workaround lack of TTL functionality. When we create or replace a view, it causes performance to drop for about a minute!!! Need a smooth transition when replacing the view.

Describe the solution you'd like
Would like a smooth transition from previous view to the new view. It doesn't have to be transactional but should have a clean transition without the massive drop in performance for 1 minute. Additionally, Would like like to be able to create a view on tables with as of system time. The multiple tables than make up the view are used for reporting.

Describe alternatives you've considered
TTL or True partitioning are not available so a structure of views per-month were our best last choice.

Epic: CRDB-9838

Jira issue: CRDB-7138

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

cc @vy-ton

[vy-ton]

Thanks for the ping @rafiss. This came up in a user interview so we should investigate for a future milestone.

[rafiss]

The feature to create a view based on a follower read seems reasonable. Though the qps dropping down to zero when creating a view normally is definitely not expected. To clarify, is the behavior from the screenshot from a regular VIEW or a MATERIALIZED VIEW?

It seems like something that should be handled as a support item, since zero qps is definitely quite bad.

[vy-ton]

Confirmed that screenshot is for a VIEW

[rafiss]

Relates to the request in #50753 and the root issue is #35712

[mgartner]

It sounds like this is no longer needed now that we support TTL and we have the other two issues tracking the root issue.