sql: provide documentation on row-level ttl workarounds

[awoods187]

We don't yet support row-level TTL. This feature is useful in that it cuts down on the effort developers need to take to delete and manage data.

In the meantime, we should document workarounds such as using cron jobs to periodically delete certain data in a performance first manner.

cc @rkruze who was asking about this.

[drewdeally]

@awoods187 any progression on this?

[awoods187]

We haven't prioritized the doc work here given the opportunity cost for other work. Do you want to make a case for increasing the prioritization?

[awoods187]

I'm making this p-1 and grouping it with various delete issues:
#5592
#4819
#4818

[awoods187]

Here's a customer ticket related to needing better recommendations around batch deleting https://cockroachdb.zendesk.com/agent/tickets/5882

[mgartner]

Some guidance on why and how to batch large writes would be great. For example, with Postgres you'd batch writes to avoid holding row-level locks on a huge number of rows for a long time, which would block other concurrent writes to those rows. By batching you reduce the max number of rows locked at any given time, and those locks are held for a shorter time-frame. I'm not sure if CRDB has similar behavior for large writes, but an explanation of this form would be great.

[jordanlewis]

Since @rohany's change that adds crdb_internal_mvcc_timestamp, we can recommend this pattern:

root@127.0.0.1:55288/defaultdb> delete from t where crdb_internal_mvcc_timestamp < 1597173188127148000.0000000000 LIMIT 10000;

[drewdeally]

Very nice Sent from my iPhone

…

On Aug 11, 2020, at 3:23 PM, Jordan Lewis ***@***.***> wrote:  Since @rohany's change that adds crdb_internal_mvcc_timestamp, we can recommend this pattern: ***@***.***:55288/defaultdb> delete from t where crdb_internal_mvcc_timestamp < 1597173188127148000.0000000000 LIMIT 10000; — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.