Atomically reorder huge list of documents in firestore

Question

Lists can be as large as 100000 documents, sorted with an integer field. Due to atomic operation limitations of 500 per transaction or batch write, updating the sorting field needs be done in multiple batches, essentially making this a relatively failure-prone, non-atomic operation. Recovery measures needed in such cases are maintenance-heavy and not ideal.

Is there a better way to atomically reorder a huge list of documents in a firestore database?

Answer 1

Is there a better way to atomically reorder a huge list of documents in a firestore database?

There is no way. You've already identified the key limit in Firestore that makes your request impossible: only up to 500 documents can be part of a batch or transaction. Firestore provides no other method to atomically update more than one document.

Best you can do is create a new collection, copy all the documents into that new collection (making sure they are up to date), then signal to clients that they should use the new collection instead of the old one. That signal (which you implement yourself) is what makes the operation atomic. Unfortunately, you are also effectively locking your database (rejecting writes to the relevant collections) for the duration of the copy and accepting that not all clients will receive the signal at the same time.

Answer 2

An alternative to the approach Doug suggests is to use a more sparse index/order field - leaving yourself space to place documents in.

So start out with your order/index field having values like

• doc1: 10
• doc2: 20
• doc3: 30
• doc4: 40
• doc5: 50

Now if you want to move doc5 between doc2 and doc3, you'd do:

• doc1: 10
• doc2: 20
• doc5: 25
• doc3: 30
• doc4: 40

This approach has it's own downsides of course, like:

• You're always halving the artificially created open space in the "index". The number of times you can do a swap in this scenario is limited of course, but while it works it is going to be highly efficient.
• You'll probably want to periodically (or after X swaps) reindex the documents, at which point you'd use an approach similar to the one Doug described.