Tuning batchSize for bulk deletes of nodes and relationships

To perform a bulk deletion of nodes and relationships in AuraDB on a smaller instance (1GiB or 2GiB Ram), you have to carefully set batchSize.
If you have it set too high (i.e. 10000), you might encounter connection failure issues:

For instance, in Python, you would see this error message :

WebSocket connection failure. Due to security constraints in your web browser, the 
reason for the failure is not available to this Neo4j Driver. Please use your browsers 
development console to determine the root cause of the failure. Common reasons include 
the database being unavailable, using the wrong connection URL or temporary network 
problems. If you have enabled encryption, ensure your browser s configured to trust 
the certificate Neo4j is configured to use. WebSocket `readyState` is: 3

To solve this issue:

In most cases, when we try doing bulk deletes, we end up with the whole heap being filled and a lot of garbage collection pauses. With a small instance of 2GiB memory, it is important to choose the value for batchSize carefully.

If the value for batchSize we are using in the call to apoc.periodic.iterate is quite large, it can fill up the memory before the batch is processed.

The suggestion is to tune the batchSize according to the schema, data and memory size of the instance.

For 2GB instance, the start point could be around 2000 and go up till 4000. You can keep increasing this value as and when you increase your instance size to 8GiB or more.

CALL apoc.periodic.iterate(
"MATCH (n) RETURN n",
"DETACH DELETE n",
{batchSize:2000, parallel:true})

Alternative approaches:

Approach 1.

You can use the apoc.periodic.commit procedure instead of apoc.periodic.iterate. This avoids loading the nodes into memory.

With this procedure, you provide one query that must contain a LIMIT clause and include a RETURN clause at the end of the query. As long as a result is returned, it will keep on iterating.
Please note that this method can be slower.

CALL apoc.periodic.commit(
"MATCH (n) WITH n LIMIT $limit DETACH DELETE n RETURN count(*)",
{limit: 1000}
)
YIELD updates, executions, runtime, batches
RETURN updates, executions, runtime, batches;

Approach 2:

You can resize the instance momentarily until the bulk delete completes, so you can use a bigger batch size and resize it back to its original size afterwards. Please be aware that resizing the instance will incur some extra cost, therefore, plan it accordingly.

Please find the KB to resize the instance here.