Persistent indexes

This is an introduction to ArangoDB’s persistent indexes.

It is possible to define a persistent index on one or more attributes (or paths)of documents. The index is then used in queries to locate documents within a given range. If the index is declared unique, then no two documents are allowed to have the same set of attribute values.

Creating a new document or updating a document will fail if the uniqueness is violated. If the index is declared sparse, a document will be excluded from the index and no uniqueness checks will be performed if any index attribute value is not set or has a value of .

ensures that a unique persistent index existscollection.ensureIndex({ type: "persistent", fields: [ "field1", …, "fieldn" ], unique: true })

Creates a unique persistent index on all documents using field1, … _fieldn_as attribute paths. At least one attribute path has to be given. The index willbe non-sparse by default.

All documents in the collection must differ in terms of the indexed attributes. Creating a new document or updating an existing document willwill fail if the attribute uniqueness is violated.

To create a sparse unique index, set the sparse attribute to true:

collection.ensureIndex({ type: "persistent", fields: [ "field1", …, "fieldn" ], unique: true, sparse: true })

In a sparse index all documents will be excluded from the index that do not contain at least one of the specified index attributes or that have a value of in any of the specified index attributes. Such documents willnot be indexed, and not be taken into account for uniqueness checks.

In a non-sparse index, these documents will be indexed (for non-presentindexed attributes, a value of null will be used) and will be taken intoaccount for uniqueness checks.

Show execution results

Hide execution results

{ 
  "deduplicate" : true, 
  "fields" : [ 
    "myId" 
  ], 
  "id" : "ids/74827", 
  "isNewlyCreated" : true, 
  "name" : "idx_1655126002667356160", 
  "selectivityEstimate" : 1, 
  "sparse" : false, 
  "type" : "persistent", 
  "unique" : true, 
  "code" : 201 
}
{ 
  "_id" : "ids/74831", 
  "_key" : "74831", 
  "_rev" : "_Z2KDQFi---" 
}
{ 
  "_id" : "ids/74833", 
  "_key" : "74833", 
  "_rev" : "_Z2KDQFi--A" 
}
{ 
  "_id" : "ids/74835", 
  "_key" : "74835", 
  "_rev" : "_Z2KDQFm---" 
}
[ArangoError 1210: unique constraint violated - in index idx_1655126002667356160 of type persistent over 'myId'; conflicting key: 74831]

arangosh> db.ids.ensureIndex({ type: "persistent", fields: [ "name.first", "name.last" ], unique: true });
arangosh> db.ids.save({ "name" : { "first" : "hans", "last": "hansen" }});
arangosh> db.ids.save({ "name" : { "first" : "jens", "last": "jensen" }});
arangosh> db.ids.save({ "name" : { "first" : "hans", "last": "hansen" }});

Show execution results

Hide execution results

{ 
  "deduplicate" : true, 
  "fields" : [ 
    "name.first", 
    "name.last" 
  ], 
  "id" : "ids/74808", 
  "isNewlyCreated" : true, 
  "name" : "idx_1655126002658967552", 
  "selectivityEstimate" : 1, 
  "sparse" : false, 
  "type" : "persistent", 
  "unique" : true, 
  "code" : 201 
}
{ 
  "_id" : "ids/74812", 
  "_key" : "74812", 
  "_rev" : "_Z2KDQFC---" 
}
{ 
  "_id" : "ids/74814", 
  "_key" : "74814", 
  "_rev" : "_Z2KDQFC--A" 
}
{ 
  "_id" : "ids/74816", 
  "_key" : "74816", 
  "_rev" : "_Z2KDQFG---" 
}
[ArangoError 1210: unique constraint violated - in index idx_1655126002658967552 of type persistent over 'name.first, name.last'; conflicting key: 74812]

ensures that a non-unique persistent index existscollection.ensureIndex({ type: "persistent", fields: [ "field1", …, "fieldn" ] })

Creates a non-unique persistent index on all documents using field1, …fieldn as attribute paths. At least one attribute path has to be given.The index will be non-sparse by default.

To create a sparse unique index, set the sparse attribute to true.

In case that the index was successfully created, an object with the indexdetails, including the index-identifier, is returned.

Show execution results

Hide execution results

{ 
  "deduplicate" : true, 
  "fields" : [ 
    "first" 
  ], 
  "isNewlyCreated" : true, 
  "name" : "idx_1655126002580324354", 
  "selectivityEstimate" : 1, 
  "sparse" : false, 
  "type" : "persistent", 
  "unique" : false, 
  "code" : 201 
}
{ 
  "_id" : "names/74523", 
  "_key" : "74523", 
  "_rev" : "_Z2KDQAW---" 
}
{ 
  "_id" : "names/74525", 
  "_key" : "74525", 
  "_rev" : "_Z2KDQAa---" 
}
{ 
  "_id" : "names/74527", 
  "_key" : "74527", 
  "_rev" : "_Z2KDQAa--A" 
}
{ 
  "_id" : "names/74529", 
  "_key" : "74529", 
  "_rev" : "_Z2KDQAe---" 
}
{ 
  "_id" : "names/74531", 
  "_key" : "74531", 
  "_rev" : "_Z2KDQAe--A" 
}

Selects all documents from the collection that match the specified example and returns a cursor. A persistent index will be used if present.

You can use toArray, next, or hasNext to access theresult. The result can be limited using the skip and _limit_operator.

An attribute name of the form a.b is interpreted as attribute path,not as attribute. If you use

{ "a" : { "c" : 1 } }

as example, then you will find all documents, such that the attributea contains a document of the form {c : 1 }. For example the document

{ "a" : { "c" : 1 }, "b" : 1 }

will match, but the document

will not.

However, if you use

{ "a.c" : 1 },

then you will find all documents, which contain a sub-document in a_that has an attribute _c of value 1. Both the following documents

and

will match.

To fix persistent indexes after a language change, delete and re-create them.Skiplist indexes are not affected, because they are not persisted andautomatically rebuilt on every server start.