Faster delete options

[wzrdtales]

Is your feature request related to a problem? Please describe.

Right now the speed of inserting is way faster than deleting rows. We have a table with 12billion+ rows and we want to start deleting everything before date XYZ. However, doing this would take forever, probably it would be faster to create a new table with only the wanted entries, than actually deleting.

Describe the solution you'd like
Support for large delete statements, if necessary with a complete block for the time being of the whole table.

Describe alternatives you've considered

Looping like the docs say, but it takes ages... .

Jira issue: CRDB-14214

[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.}

[ajwerner]

If you use hash-sharded indexes, you can

1. support time-ordered inserts in a more scalable way (which might make it harder to keep up), and\
2. parallelize the deletion of data in each shard.

[wzrdtales]

well I guess that wont help with already existing data?

When you say time-ordered inserts, I would assume that it would be possible to delete those quite fast since it would be able to efficiently find everything before a certain time?

[wzrdtales]

and the biggest problem is probably the locking and retrial of transactions. Issuing fast delete queries by selecting their ids first got us deleting only around 20.000 records per ~6 seconds. As far as I know there is also no way to execute big operations right? With big I mean, delete once 1 million records, and accept blocking the whole table for ~10-40 seconds.

[wzrdtales]

So we ended up renaming the old table, recreating a new one and inserting over the stuff we didn't wanted to delete.
This is certainly something cockroachdb has to improve, performance on deletes. It shouldn't need to take that long.

Take this thought for example:
Why is it possible to quickly erase with truncate a whole table, but not an indexed whole subset of data? So for good archival strategies that include deleting data, currently the only way to do it sustainable is having one table per deletion threshold. That is terrible of course... .

[wzrdtales]

In the end, deletion shouldn't be slower than inserting. I can insert at a rate of way over 100k rows per second, but can't even delete 10k in one.

[michae2]

@wzrdtales thanks for the feedback. Sorry you ran into this, glad you were able to figure out a workaround. We do have some plans to speed up deletes, for example:

• sql: perform fast-path point (not ranged) key-value deletes #53939
• kv: pipeline DeleteRange requests where possible #64723

Regarding your last point, unfortunately I think it is unlikely that SQL DELETE will ever be as fast as SQL INSERT. The implementation of DELETE in cockroach is much more similar to UPDATE than to INSERT. This is not unique to cockroach. Other relational databases get around this problem by supporting DROP PARTITION as a faster way to remove data in bulk. For example, just from a quick search:

• MariaDB
• PostgreSQL
• Oracle
• SQL Server

We have an open issue about supporting DROP PARTITION. Would this have helped in your situation?

[wzrdtales]

there is slight difference with the databases you named. They could finish deletes earlier than cockroachdb, b/c they in doubt can allow locking the table in their favour, while cockroachdb will just stubbornly restart a transaction even without a lot of traffic on the table just b/c it takes too long for it.

I will have a look at drop partition and let you know my opinion

[ajwerner]

The locking you're describing is tracked in #50181

[wzrdtales]

@ajwerner not quite, that is about row locking and unfortunately open for a long time.

[wzrdtales]

@michae2 DROP PARTITION would help in this scenario if we could put a complete week without having too bad performance impact within one partition we could always delete the last week once per week. in 1 week we usually talk about roughly 300 million rows (only that one table, we have a few more of course...)

[michae2]

@vy-ton and @kevin-v-ngo tagging you to note that this is another painful deletes issue. DROP PARTITION might have helped in this case.

@wzrdtales I'm going to close this issue in favor of tracking DROP PARTITION support in #51295 and improved delete performance in the other issues linked above.

[wzrdtales]

And the locking behavior as well is at fault not just a feature like drop partition missing. Sent from Nine

…

-- Best

-- Tobias Gurtzick CEO WizardTales GmbH Additional Contacts: @github | ***@***.*** -- Website: https://www.wizardtales.com ***@***.*** nmlpm: -- coming soon & open source -- Please tag your mails with "request", "issue" or "proposal", unless none of these fit or there already exists a thread.

-- Why should one reach for what one may be capable reaching, that's awfully boring and after all one still be ones yesterday being. -- Sitz der Gesellschaft: Ratingen Kokkolastr. 5 40882 Ratingen Germany Registergericht: Amtsgericht Düsseldorf, HRB 84140 Geschäftsführer: Tobias Gurtzick

________________________________ Von: Michael Erickson ***@***.***> Gesendet: Dienstag, 12. April 2022 21:12 An: cockroachdb/cockroach Cc: Tobias Gurtzick; Mention Betreff: Re: [cockroachdb/cockroach] Faster delete options (Issue #78680) @vy-ton and @kevin-v-ngo tagging you to note that this is another painful deletes issue. DROP PARTITION might have helped in this case. @wzrdtales I'm going to close this issue in favor of tracking DROP PARTITION support in #51295 and improved delete performance in the other issues linked above. — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: ***@***.***>

[ajwerner]

Can you help unpack what you meant by:

@ajwerner not quite, that is about row locking and unfortunately open for a long time.

Are you suggesting that a full-table lock would be better than row locking for the relevant rows? Is the idea that row-locking doesn't capture gaps and those gaps may contain new writes and thus may invalidate the original scan?

[wzrdtales]

other dbs allow you explicitly lock the whole table (a sacrifice the user makes on intention). cockroachdb does not even allow remotely longer running delete queries b/c of locking and forcing them to retry and that is the main issue I see why it takes so long to delete. B/c cockroachdb certainly is capable of searching the rows to delete faster and would certainly be faster in getting them ready for delete when it can just act alone on that table in the meantime without needing to care about locking at all.

Drop partition is more elegant of course since it would avoid that completely for some scenarios. But we already have one datastructure where building up proper partitions would be quite a strange setup, since we would delete only all the hourly records, except the last one of a day (the day might be even updated multiple weeks later and get a new entry) after they are older than two years.

[ajwerner]

Are you aware of the experimental TTL feature we're launching in 22.1? Would that be of interest to you?

[wzrdtales]

no not aware yet

[michae2]

In addition to #50181 I think #55896 and #52768 would also help long-running deletes avoid retries. (Is that right, @ajwerner?)