How to delete rows in Parse that satisfy some conditions?

Question

Is there any way to effectively delete rows in Parse that do something like this SQL statement?

DELETE FROM table WHERE delete_me = 1

I've tried this, but it's very slow:

var query = new Parse.Query('table');
query.equalTo('delete_me', 1);

query.each(function(obj) {

    return obj.destroy();

}).then(function() {
    // Done
}, function(error) {
    // Error
});

Wan

Parse officially said that Objects can be deleted one by one only, once they've been queried. We do not delete objects that match a query. – Wan

Answer 1

Almost there: find() will get the objects satisfying the delete criteria, then destroyAll() will destroy them all.

var query = new Parse.Query('table');
query.equalTo('delete_me', 1);
query.find().then(function(results) {
    return Parse.Object.destroyAll(results);
}).then(function() {
    // Done
}, function(error) {
    // Error
});

Edit - to delete a table with more than 1k, it takes a little extra work with promises. The idea is to cursor through the table, grouping finds in batches of 1k (or some smaller increment), execute those finds concurrently using Promise.when(), then destroy the results concurrently the same way...

var query = new Parse.Query('table');
query.equalTo('delete_me', 1);
query.count().then(function(count) {
    var finds = [];
    for (var i=0; i<count; i+=1000) {
        finds.push(findSkip(i));
    }
    return Parse.Promise.when(finds);
}).then(function() {
    var destroys = [];
    _.each(arguments, function(results) {
        destroys.push(Parse.Object.destroyAll(results));
    });
    return Parse.Promise.when(destroys);
}).then(function() {
    // Done
}, function(error) {
    // Error
});

// return a promise to find 1k rows starting after the ith row
function findSkip(i) {
    var query = new Parse.Query('table');
    query.limit(1000);
    query.equalTo('delete_me', 1);
    query.skip(i);
    return query.find();
}

Edit 2 - This might be faster, but you'd need to discover empirically:

// return a promise to delete 1k rows from table, promise is fulfilled with the count deleted
function deleteABunch() {
    var query = new Parse.Query('table');
    query.limit(1000);
    query.equalTo('delete_me', 1);
    query.find().then(function(results) {
        return Parse.Object.destroyAll(results).then(function() {
            return results.length;
        });
    });
}

function deleteAll() {
    return deleteABunch().then(function(count) {
        return (count)? deleteAll() : Parse.Promise.as();
    });
}

Quotation

I've tried this one too, but 1) find() can only retrieve at most 1000 results 2) it's still slow, delete 17~19 rows per minute – Wan

1) yes, 1k at a time (I'll make an edit about that), 2) your timing result is much worse than I've seen. Do you have any beforeDelete hooks running? – Quotation

Ah, I mean 17~19 per second. I need to delete 500k rows, so it will take about 7 hours... – Wan

Still two slow. Also, your question gives no hint of your real question, which is really all about scale. – Quotation

I run it on a background job, and the doc says that "Apps may have one job running concurrently per 20 req/s in their request limit." so I think 17~19 is reasonable. I don't think it's about scale, because 500k is not a really big number actually. – Wan

The other constraint you'll run into is timeout. You may need to arrange the job to do a small batch, say 5k in each run, and have it run more frequently. – Quotation

I think your code do exactly the same thing as mine - delete at most 20 rows in one second. I'll accept this answer if I can't find a better one. Thanks! – Wan

You should try it. I get closer to 10x. The op code is starting 1k individual operations in the each loop. There are only two delete methods in the sdk – Quotation

I've tried and it deleted 1000 rows in about 50 seconds by using destroyAll() in a Background job. Can you share your run time for deleting 1000 rows in case I did something stupid? – Wan

I sure will, but it might take a couple days. There are testers hitting my test database right now, and I don't want to disrupt them. Maybe I'll make a quick app that just creates, then deletes a bunch. – Quotation

It works fast now (the same code, don't know what happened), but got an error "This application performed 1802 requests within the past minute, and exceeded its request limit." I guess it's a new question. Thanks! – Wan

That's a good solution if you need/want to use only parse to access to database but when you have millions of rows it takes too long. I've made an answer related to it here https://mcmap.net/q/2029728/-delete-multiple-records-on-parse where i suggest to use the database power to do it as fast as possible. – Sogdian

Answer 2

The 1802 request thing is the rate-limit (30/sec). The next idea is to batch the work into smaller transaction-count promises and run them serially, keeping the rate low but stretching them out over time. That's the gist of my suggestion above in a couple of forms (before I understood that you have ~500k rows).

Unfortunately, parse enforces a 10sec timeout limit, too. I think about ~1k rows deleted per sec is achievable, but I fear your 500k table will not yield to any method on the free tier. I think you have only these alternatives:

(a) throttle on the client - use some form of setTimeout(), to perform small enough, short enough batches. (This is how my app handles it, because the heavy work is done only by admins, and I can instruct them to not reload a page.).

(b) deploy your own node server which basically implements idea (a), calling parse.com in small enough steps to keep it happy but places no computational burden on the client.

(c) a parse.com background job that wakes periodically and nibbles away at it. You only get one of these on the free tier, and I imagine most of the time it will just wake frequently and waste electricity.

(d) pay.

I'll be able to do some actual code/test late today. If I learn anything new I'll post here. Best of luck.

Quotation

I use a background job (and replace setTimeout() with Parse.Promise() since there is no setTimeout() there) and it works well now. Thanks. – Wan