Why should you avoid indexing every payload field 'just in case,' and how would you decide which fields actually need an index?

Every payload index consumes memory and slows down writes, because each upsert must update the index for every indexed field. For a collection with many fields, indexing all of them can multiply the memory footprint and the write cost without providing any benefit for fields that are never filtered on. The right approach is to base the indexing decision on actual query patterns: index the fields that appear in production filters, and leave the rest unindexed. A field that is only returned in the payload but never filtered on does not need an index. A field that is filtered on rarely can be left unindexed if the latency cost of scanning is acceptable. A field that is filtered on frequently and selectively should be indexed with the type that matches the filter. The decision is empirical: instrument the application to log which fields are used in filters, aggregate over a representative period, and index the top fields by frequency and selectivity.

The mechanism that makes indexing expensive is that a payload index is a data structure separate from the payload itself. A keyword index maps each distinct value to a posting list; a datetime index stores sorted timestamps with min/max statistics; a full-text index stores an inverted index of terms. Each of these has its own memory footprint, and each must be updated on every write that affects the indexed field. For a collection with many fields and a high write rate, the index maintenance cost can dominate the write path. The benefit of an index is that it allows the planner to prune segments and to resolve the filter efficiently during traversal, which reduces query latency for filtered queries. The trade-off is therefore: index a field if the query latency benefit exceeds the memory and write-cost penalty. For frequently filtered selective fields, the benefit is large; for rarely filtered or non-selective fields, it is small or negative.