How to overcome the 200 composite index limit?

[Richard Scotten]

I'm about to hit the 200 composite index limit for Firestore.

I've been building my app with Firestore for over three years, and this 200 composite index limit is blocking my ability to add new features / queries.

I've contacted the Firebase team for help on the following questions:

1. Why does the 200 composite index limit exists?

2. How can I work around it?  Another Firebase project with a separate Firestore DB?

But they haven't answered my questions.

I can't find anything online about this either.  Not in the Firestore docs or elsewhere.

Does anybody know the answers to these questions?  I'd be grateful for any insight.

[Sam Stern]

Hi Richard,

I don't personally know why the limit exists but I can try to offer a workaround. You may also want to file a ticket with Google Cloud support to ask for a quota raise, although to be honest I have no idea if this is a quota we can actually raise or a hard limit. Can't hurt to ask!

Let's say you have the following query:
db.collection('widgets').where('category', '==', 'foo').where('location', '==', 'bar').orderBy('price', 'desc');

The simple way to index this would be a single compound index with three parts:
(category, asc), (location, asc), (price, desc)

That index matches the query perfectly and will give you the best performance. However you could also serve this query by creating two simpler indexes:

(category, asc), (price, desc)

(location, asc), (price, desc)

The query planner would be able to merge the two indexes and serve your three-clause query. Now I imagine your first question is "how does turning one index into two indexes help me?!?" which is valid! This helps when you have many overlapping equality queries, like this:

db.collection('widgets').where('category', '==', 'foo').where('name', '==', 'foo').where('location', '==', 'bar').where('quality', '==', 'baz').orderBy('price', 'desc');

db.collection('widgets').where('category', '==', 'foo').where('name', '==', 'foo').where('location', '==', 'bar').orderBy('price', 'desc');

db.collection('widgets').where('category', '==', 'foo').where('quality', '==', 'foo').where('location', '==', 'bar').orderBy('price', 'desc');

db.collection('widgets').where('name', '==', 'foo').where('quality', '==', 'foo').where('location', '==', 'bar').orderBy('price', 'desc');

db.collection('widgets').where('category', '==', 'foo').where('location', '==', 'bar').orderBy('price', 'desc');

db.collection('widgets').where('name', '==', 'foo').where('location', '==', 'bar').orderBy('price', 'desc');

db.collection('widgets').where('category', '==', 'foo').orderBy('price', 'desc');

db.collection('widgets').where('name', '==', 'foo').orderBy('price', 'desc');

db.collection('widgets').where('quality', '==', 'foo').orderBy('price', 'desc');

// .. etc

Rather than create a distinct index for each of these, you could serve all of them with this list of simple two-part indexes:

(category, asc), (price, desc)

(location, asc), (price, desc)

(name, asc), (price, desc)

(quality, asc), (price, desc)

Of course there's no such thing as a free lunch! By relying on the query planner to merge simple indexes you will give up some performance. All of this information (and much more) is available in the docs here:

https://cloud.google.com/datastore/docs/concepts/optimize-indexes

I hope that's helpful!

Sam

--
You received this message because you are subscribed to the Google Groups "google-cloud-firestore-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-firestor...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-firestore-discuss/f118617b-3c75-4f5d-a595-d6e12a149e15n%40googlegroups.com.

[Richard Scotten]

Hi Sam, 

Thank you for your timely and detailed response.  I can't tell you how helpful it is to receive that level of support from somebody at Google.

Unfortunately, even if I do optimize my indexes as you suggest, there's still the problem of the 200 index limit blocking my app's growth.

I've asked the Firebase team twice why this limit exists and what the work-around is, and they told me this:

I'm sorry to say that we currently don't have a way to increase the number of composite indexes yet. There is an existing feature request which is being actively discussed by the team. Though, we can't share any specifics at this time, I will add this request on your behalf, so the team can prioritize accordingly.

Also, you linked me to https://cloud.google.com/datastore/docs/concepts/optimize-indexes, which seems like it pertains only to Firestore in Datastore mode.  Perhaps you mean index merging as discussed here?  https://firebase.google.com/docs/firestore/query-data/index-overview#taking_advantage_of_index_merging Or do the index merging rules overlap for Firestore in both Native and Datastore modes?

Thanks again, Sam.

Richard

[Sam Stern]

Hi Richard,

You're right I gave you the wrong link. The reason I didn't even notice is that so many of the same things apply to both modes, which makes sense because these products are closely related. But the Firebase docs link you posted is what I should have sent.

Can you tell me a bit more about how your app uses Firestore? It's pretty rare to find an app with 200 actively used compound indexes, I'd like to hear more about your use case. I won't be able to get the limit raised for you, but maybe through discussion we'll find ways you can get more done with fewer indexes.

- Sam

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-firestore-discuss/7b643c42-7aac-4b09-9af4-d48f0c88c3fcn%40googlegroups.com.

[Richard Scotten]

Hi Sam,

Thanks for clarifying regarding the index merging links.

My app is a multi-tenant ecommerce platform for rentals.  Think Shopify for rentals but without the website builder.  It includes an inventory management system, rental tracking system, billing system, and collects various events on how customers engage with the products for rent.  

I already have at least 20 Firestore collections/subcollections, and want to add more.  My customers need to be able to sort and filter by tens of thousands of products, orders, line items, invoices, generate reports, etc., so I need enough composite indexes to support this to be competitive.  

Right now, we're being very frugal about what kind of sorting and filtering options we offer because we're hitting this limit.  

But some of my customers are complaining that "there are not enough sorting and filtering options."  Because there's so much data, we can't do the sorting and filtering client side.

I can try to optimize our composite indexes using index merging, but we're still eventually going to hit the 200 composite index limit and our app's growth will grind to a halt.  

So I either need the limit raised or I need to know a viable work-around that allows the app to continue growing.

We've thought of a hacky work-around which entails merging two fields into one.  Like if we have a count field and a createdAt field:

count: number, e.g. 1

createdAt: number, e.g. 2342322347987

We could concatenate them using a third field called:

count_createdAt: string

and the value would look like:

00000000001_2342322347987

and then we could use a single-field index on count_createdAt rather than a composite index on count and createdAt.

But this seems absurd.  It's just going to add more overhead in dev time and Firestore costs.

In any case, I'd really like to understand why this limit exists in the first place.  

Thanks again for your help, Sam.

Richard

[Sam Stern]

HI Richard,

Thanks for all of the information. An app with a large matrix of user-specified filters is definitely a weak point for Firestore, and I can definitely understand why that's disappointing since many other databases handle this case better.

The hack you have described, combining fields to use single-field indexes, is actually quite common in the NoSQL world. While in this case it's to get around the somewhat artificial limit of 200 compound indexes, in many cases that type of hack can unlock queries which are simply impossible otherwise. You can use a Cloud Function to keep those synthetic fields in sync each time the document is updated, so it can be mostly set-and-forget (just watch out for infinite loops!).

Many people on the team have seen this thread (and I'll forward it to some others) so your vote for a higher custom index limit or a per-collectiongroup limit is definitely noted. It's something I'd like to see as well, so I'll speak up for it when we're prioritizing feature requests.

If you can't manage to work around this issue by using merge indexes or compound fields (the hack you mentioned) then your best bet will be to use a search service like Elastic, Algolia, etc to power these types of advanced queries. I know that it's very disappointing to be told to set up and pay for yet another service, but I can assure you that will be less pain than trying to use multiple Firestore instances (in multiple projects) to solve your problem. And by using a search service you may be able to enable analysis far beyond what any Firestore query could provide (think fuzzy matching, text operators, weighted/ranked operators, etc).

- Sam

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-firestore-discuss/83f82a96-7a34-4447-94ec-fce84e47cfe5n%40googlegroups.com.

[Richard Scotten]

Hi Sam,

Thanks again for your helpful response and being an advocate for a higher index limit.

I do want to say that this limit is by far the biggest surprise and negative about using Firestore.  I was initially sold on Firestore because of its big data "scalability" (among other things).  I told everyone I knew how amazing this database was.  But now, three years later after investing so much time and money into Firestore and the Firebase ecosystem, this 200 composite index limit is slowing down and will eventually stop our app's growth.  Which seems antithetical to Firestore's "infinitely scalable" or "Global scale" selling point.  Now, when people ask me "how's Firestore" I have to say "Because of this 200 composite index limit, don't try building anything big with it."

Good to know regarding the synthetic fields hack.  I guess that's a direction we'll need to take.

We do replicate our products collection onto Algolia for the public websites because we need the filtering and fuzzy-search capabilities.  But Algolia is expensive because you only get one "index" per collection for the price.  To add one more index requires duplicating the entire collection and doubling the cost.  For 260,000 products on one index we're paying $160 per month.  Adding one more index would then cost $320 per month.  And these are just the publicly visible products; there are 3x as many private products that exist in Firestore.

Aside from being cost-prohibitive, we don't want to use Algolia or ElasticSearch on our backend/Dashboard because we want to leverage the Firestore listeners for realtime updates.  

Last question: Is the decision for making the composite index limit 200 a secret?  Why isn't it 100 or 1,000?  I've asked the Firebase team this multiple times and everyone has consistently evaded answering this question.  I'd be a lot less grumpy about this limit if I understood why it existed.  Does anybody at Firebase actually know the reason?

[Joshua Melcon]

Hi all. 

I think I can help a bit here.  

To answer a few questions: 

• http://cloud/firestore/quotas documents the default 200 limit. 
• The limit can be raised via a support case.  

The limit exists as a way for Firestore to nudge customers away from certain indexing anti patterns and toward better/cheaper/faster ones before raising the limit.  Without having looked closely this use case doesn't appear to be an anti pattern to me -- but I am not an expert in this area. 

It looks like our documentation around this area is insufficient.  I will raise this issue and try to improve the situation.

 

-- Josh

[Richard Scotten]

@Joshua, you're my hero!  

Thank you!  Dissuading index anti-patterns a perfectly good reason for imposing a limit.  Phew! Now I can sleep at night.  This was really stressing me out.

I'm sure there's plenty of optimizing I can do with index merging and synthetic fields before I'll need the limit raised.  I'm just relieved the option exists and that I'm not going to hit a wall down the line.

Thanks again!!

[Ayyappa J]

Were you able to get the limit raised? We are at 180 indexes now and wondering if its possible raise the quota.

Also, unfortunately most of the times google favours to raise limits for current paying customers alone which can be a disappointment but would love to give a shot.

[Patrice Bertrand]

We were hitting the limit a number of years ago, and we had to complement Datastore with ElasticSearch (ES), and we are very happy with it.  Now all complex queries are handled by ES, which returns a set of "document ids" that are in fact ndb.Keys, and we retrieve the actual entities from Datastore.  This takes some development in order to keep ES in sync with Datastore content, acting on post_put_hook() for re-indexing.  But aside from overcomming the 200 limit, this allows for queries that would be totally unthinkable in Datastore, such as aggregate queries completed in < 10ms.