Persistent indexes

Introduction to 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 null.

Accessing Persistent Indexes from the Shell

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 null 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.

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

  1. arangosh> db.ids.ensureIndex({ type: "persistent", fields: [ "myId" ], unique: true });
  2. arangosh> db.ids.save({ "myId": 123 });
  3. arangosh> db.ids.save({ "myId": 456 });
  4. arangosh> db.ids.save({ "myId": 789 });
  5. arangosh> db.ids.save({ "myId": 123 });

Show execution results

  1. { 
  2.   "deduplicate" : true, 
  3.   "fields" : [ 
  4.     "myId" 
  5.   ], 
  6.   "id" : "ids/16367", 
  7.   "isNewlyCreated" : true, 
  8.   "sparse" : false, 
  9.   "type" : "persistent", 
  10.   "unique" : true, 
  11.   "code" : 201 
  12. }
  13. { 
  14.   "_id" : "ids/16370", 
  15.   "_key" : "16370", 
  16.   "_rev" : "_ZHpYs6K--_" 
  17. }
  18. { 
  19.   "_id" : "ids/16374", 
  20.   "_key" : "16374", 
  21.   "_rev" : "_ZHpYs6K--B" 
  22. }
  23. { 
  24.   "_id" : "ids/16377", 
  25.   "_key" : "16377", 
  26.   "_rev" : "_ZHpYs6O--_" 
  27. }
  28. [ArangoError 1210: unique constraint violated - in index 16367 of type persistent over ["myId"]; conflicting key: 16370]

Hide execution results

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

Show execution results

  1. { 
  2.   "deduplicate" : true, 
  3.   "fields" : [ 
  4.     "name.first", 
  5.     "name.last" 
  6.   ], 
  7.   "id" : "ids/16348", 
  8.   "isNewlyCreated" : true, 
  9.   "sparse" : false, 
  10.   "type" : "persistent", 
  11.   "unique" : true, 
  12.   "code" : 201 
  13. }
  14. { 
  15.   "_id" : "ids/16351", 
  16.   "_key" : "16351", 
  17.   "_rev" : "_ZHpYs3O--_" 
  18. }
  19. { 
  20.   "_id" : "ids/16355", 
  21.   "_key" : "16355", 
  22.   "_rev" : "_ZHpYs3S--_" 
  23. }
  24. { 
  25.   "_id" : "ids/16358", 
  26.   "_key" : "16358", 
  27.   "_rev" : "_ZHpYs3S--B" 
  28. }
  29. [ArangoError 1210: unique constraint violated - in index 16348 of type persistent over ["name.first","name.last"]; conflicting key: 16351]

Hide execution results

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.

  1. arangosh> db.names.ensureIndex({ type: "persistent", fields: [ "first" ] });
  2. arangosh> db.names.save({ "first" : "Tim" });
  3. arangosh> db.names.save({ "first" : "Tom" });
  4. arangosh> db.names.save({ "first" : "John" });
  5. arangosh> db.names.save({ "first" : "Tim" });
  6. arangosh> db.names.save({ "first" : "Tom" });

Show execution results

  1. { 
  2.   "deduplicate" : true, 
  3.   "fields" : [ 
  4.     "first" 
  5.   ], 
  6.   "id" : "names/16267", 
  7.   "isNewlyCreated" : true, 
  8.   "sparse" : false, 
  9.   "type" : "persistent", 
  10.   "unique" : false, 
  11.   "code" : 201 
  12. }
  13. { 
  14.   "_id" : "names/16270", 
  15.   "_key" : "16270", 
  16.   "_rev" : "_ZHpYsrC--_" 
  17. }
  18. { 
  19.   "_id" : "names/16274", 
  20.   "_key" : "16274", 
  21.   "_rev" : "_ZHpYsrG--_" 
  22. }
  23. { 
  24.   "_id" : "names/16277", 
  25.   "_key" : "16277", 
  26.   "_rev" : "_ZHpYsrG--B" 
  27. }
  28. { 
  29.   "_id" : "names/16280", 
  30.   "_key" : "16280", 
  31.   "_rev" : "_ZHpYsrK--_" 
  32. }
  33. { 
  34.   "_id" : "names/16283", 
  35.   "_key" : "16283", 
  36.   "_rev" : "_ZHpYsrK--B" 
  37. }

Hide execution results

Query by example using a persistent index

constructs a query-by-example using a persistent indexcollection.byExample(example)

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

  1. { "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

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

will match, but the document

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

will not.

However, if you use

  1. { "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

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

and

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

will match.

Persistent Indexes and Server Language

The order of index entries in persistent indexes adheres to the configuredserver language.If, however, the server is restarted with a different language setting as whenthe persistent index was created, not all documents may be returned anymore andthe sort order of those which are returned can be wrong (whenever the persistentindex is consulted).

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.