How are documents indexed in a document collection within the Documents V2 API?

[yhilem]

Hi,
How are documents indexed in a document collection within the Documents V2 API?
I plan to create a collection of more than 15 million documents (missions).
These documents are partitioned by site. And in each site by rounds.
I want to do searches like:

• all the missions of a site having a given status or in a list of status.
• all the missions of a tour in a site
• ...

How to ensure that these queries are efficient if at the time of the creation of the documents we do not have a means of specifying the structure of the primary key?

Thanks

[versaurabh]

Hi @yhilem

1. The internal details of how the Documents V2 API stores the documents within C* are mentioned at https://stargate.io/2020/10/19/the-stargate-cassandra-documents-api.html
2. Regarding the primary key for point lookup, the Documents API allows to provide a custom document-id while writing the documents as mentioned at https://stargate.io/docs/latest/develop/dev-with-doc.html#writing-documents
3. The Documents API provides 3 level of addressing to reach a document viz.
   <namespace> -> <collection> -> <documentId> -> <actual document> and this can be coupled with a variety of ways (including where clause and conditionals) to fetch the documents back as mentioned at https://stargate.io/docs/latest/develop/dev-with-doc.html#reading-documents

Does the above info help you proceed?

Thanks

[yhilem]

Thank you @versaurabh
The references given above I had already read them.
In my case for example the primary key is (site_name, tour_name, mission_id).
I don't know how to specify such a key when I insert a document into the collection.
Without it, the search is a full scan of the entire collection (table). And I doubt it's efficient when the collection contains millions of documents..

[versaurabh]

hi @yhilem, we can use <namespace> -> <collection> -> <documentId> levels, an example below

• each site_name related docs could be stored in a separate collection => a collection per site_name
• followed by the concat(tour_name-mission_id) becoming the document-id within the site_name specific collection
  OR
  another model could be where each site_name is a namespace
• which approach to choose would depend on the cardinality of the (site_name, tour_name, mission_id) combinations.

Does this sound fit the use case for modeling the primary key?

[yhilem]

We have :

• 2000~2300 site
• 60000~55000 tour

The number of rounds per site depends on the density of the site and its geographical perimeter.
If within a namespace we can have about 2500 collections, I can say that it's fine for me.

[versaurabh]

hi @yhilem

• It is not suggested to have more than 200 collections, thus not recommended for site_name
• What could be done is to have the primary key (aka document-id) as concat(site_name-tour_name-mission_id) OR
• A mapping of site_names to a set of say 50 collections : this mapping could be stored within Stargate / C* itself and then using concat(tour_name-mission_id) as the primary key within the corresponding site_name's collection