Hi,
I was looking at this video JSON Data Modeling for Success and Performance - Connect.ONLINE 2020: The NoSQL Developer Conference (couchbase.com) at around 42:30
Was wondering why did the speaker mention that we never want to have primary index in production?
Can we avoid having primary index created for the bucket/scope/collection?
Thanks
Hi @Joseph,
Basically a primary index allows you to only find KEYS any sort of N1QL DML statement would then always need to fetch the document to do filtering this is much less efficient than a purpose built index that satisfies a query without needed to dip into KV.
For a much deeper dive into the reasons please read the excellent blog post by @keshav_m What is the Couchbase Primary Index? Learn Primary Uses
Best
Jon Strabala
Principal Product Manager - Server
Hello Joseph
Primary indexes essentially have to scan across all documents in a bucket, you almost never would want to do this for a production OLTP Database workload as it would use an excessive amount of resources and wouldn’t provide ideal latency. The size of a primary index grows with each document in the bucket, which might be into the billions with a production use-case. If your production cluster uses defined indexes on specific fields, these are normally much smaller than indexing every document in a bucket and therefore are less resource intensive. When you write a N1QL query for specific fields and the cluster has to use the primary index, it forces the Query Service to make additional requests to the Data Service nodes and transfer the documents from the buckets to satisfy the request. These additional hops and data transfers can be slow depending on your infrastructure and use-case.
Primary indexes are optional. When a query can be served with just indexed fields, this is called a covering index. Covering indexes are serviced very quickly as they do not need to go back to the Data Service to collect additional information before a query is serviced. And when using memory optimised indexes, this is even done straight from memory.
We have a great blog post that describes when you’d want to use a primary index.
Take a look and let us know if you have any additional questions.
Thanks,
Ian McCloy (Couchbase Principal Product Manager)
Hi @jon.strabala and @ianmccloy,
Thank you for responding to me. I’ll dive in the article that you guys shared, and come back again if I have further questions on it.