Firestore composite index permutation explosion?

Question

I'm considering migrating a 30 collections, ~750k documents database from mongo to Google Firestore; but I'm running into problems with composite indexes.

There's 12 fields I need to be able to query ad-hoc (ie. Field1=A and Field7=B and Field9=C) - as far as I can see I need a composite index for each combination(?)

That does not seem to scale; not only is it impossible to programatically create indices; but the permutation explosion is real. Each document is fairly large; downloading a large subset and manually filter is not a viable solution.

Am I misunderstanding indexes or are these queries simply not possible in Firestore?

Answer 1

firestore firebase If you are trying to do any of the following queries you will almost certainly run in to problems. [Update] Look at the end of this for some new options available from November 2019 onwards.

Field1=A and Field2=A

or

Field1=A and Field2=B and Field7=D

as the total number of composite indexes is 200, if you are trying to create composite indexes with each combination in.

However, my initial tests show you can do the following.

Create a composite index with Field1 to Field9 in.

This will be used to satisfy all the searches which contain these fields (as long as you do not have any other fields in the .where clause list).

You must also match the DESCENDING OR ASCENDING criteria if you are using order_by as well, but since you are looking at exact match this should not matter.

This works because firestore uses the composite indexes intelligently and looks for an index which has all the fields in and the fact it has more in does not matter as the .where clause does not specify any particular order for these fields and therefore the existing index can be used.

If you look for Index merging (close to the bottom) this is explained.

https://firebase.google.com/docs/firestore/query-data/index-overview

As for programatically creating and deleting indexes, it is now more possible than at the end of last year.

It is possible to dump the existing composite indexes into a file using the firebase tool https://firebase.google.com/docs/cli/

If you do a firebase init and select firestore indexes and rules, then this will result in a file called firestore.indexes.json being created in the current folder / directory which includes all the firestore composite indexes and all exclusions as well. I would recommend backing up this file as it can be used to recreate the indexes if you mess anything up.

This file can be added to and then the command

firebase deploy --only firestore:indexes

will add all composite indexes in the file (new ones will be built and can be seen building in the firebase console in the indexes tab). Existing ones will be untouched.

This does not change the limit of 200 composite indexes. There is also a limit of 100 fields in a list of .where clauses including order_by fields.

You can also delete indexes using the gcloud SDK. Look at the following page for instructions.

https://cloud.google.com/sdk/install

Make sure that you select beta in the install list of options.

This is a good starter for learning about gcloud options

https://cloud.google.com/sdk/gcloud/reference/

The following page describes how to create an index.

https://cloud.google.com/sdk/gcloud/reference/beta/firestore/indexes/composite/create

As an example,

 gcloud beta firestore indexes composite create \
      --collection-group=Events \
      --field-config field-path=tags,array-config=contains \
      --field-config field-path=user_id,order=descending \
      --field-config field-path=timestamp,order=descending

See the reference for more flags that can be used.

Just be aware that running this command will lock the command line until the index creation has complete and this could take 5 or 10 minutes or even longer. If you need to add a lot of indexes, it would be a good idea to run this as a background command for each one, so that you are not single threading through 100 index creation commands.

gcloud can then be used to create composite indexes and also used to delete composite indexes, although they can only be deleted by ID.

This can be found by using the command

gcloud beta firestore indexes composite list

+--------------+---------------------+-------------+-------+------------------------------+------------+--------------+
|     NAME     |   COLLECTION_GROUP  | QUERY_SCOPE | STATE |         FIELD_PATHS          |   ORDER    | ARRAY_CONFIG |
+--------------+---------------------+-------------+-------+------------------------------+------------+--------------+
| CICAgJjUt4gK | MyCollection        | COLLECTION  | READY | fieldStatus                  | ASCENDING  |              |
|              |                     |             |       | lastupdatedTimestamp         | DESCENDING |              |

To just get the Name value run the following command

gcloud beta firestore indexes composite list --format="value(name)" --filter="FIELD_PATHS:Field1"

This will provide a list of composite Index names which can then be used to feed in to a delete command where the index includes the field name of "Field1".

Deletion can be done using the Name (CICAgJjUt4gK in this example) listed by the above command using the following deletion command

gcloud -q [email protected] --project=proj-a73464 beta firestore indexes composite delete CICAgJjUt4gK

where --account is an email address registered against the firebase project and the --project name is your project name and the -q means quiet. These options MUST be at the beginning of the command.

The indexes delete much faster than they are created so if you keep refreshing the firestore console composite index view you should see the count going down.

To create indexes use

gcloud beta firestore indexes composite create --collection-group=COLLECTION_GROUP --field-config=FIELD_CONFIG [--async] [GCLOUD_WIDE_FLAG …]

for creation. Look up the options for this command but they will be similar to the delete command.

[Update November 2019]

Firestore now allows "IN" and "array-contains-any" to be used. It is limited to 10 values for either of them so would not address the 12 needed by the OP, but would address a lot of requirements where status values can be one of 5 values etc.

This blog discusses the new capabilities.

https://firebase.googleblog.com/2019/11/cloud-firestore-now-supports-in-queries.html