Working with persistent indexes

The index types hash and skiplist are aliases for the persistent index type and should no longer be used to create new indexes. The aliases will be removed in a future version.

Create a persistent index

creates a persistent index

POST /_api/index

Query Parameters

  • collection (string, required): The collection name.

Request Body

  • type (string, required): Must be equal to "persistent".

  • name (string, optional): An easy-to-remember name for the index to look it up or refer to it in index hints. Index names are subject to the same character restrictions as collection names. If omitted, a name is auto-generated so that it is unique with respect to the collection, e.g. idx_832910498.

  • fields (array of strings, required): An array of attribute paths.

    The . character denotes sub-attributes in attribute paths. Attributes with literal . in their name cannot be indexed. Attributes with the name _id cannot be indexed either, neither as a top-level attribute nor as a sub-attribute.

    You can expand one array attribute with [*].

  • storedValues (array of strings, optional): The optional storedValues attribute can contain an array of paths to additional attributes to store in the index. These additional attributes cannot be used for index lookups or for sorting, but they can be used for projections. This allows an index to fully cover more queries and avoid extra document lookups. The maximum number of attributes in storedValues is 32.

    It is not possible to create multiple indexes with the same fields attributes and uniqueness but different storedValues attributes. That means the value of storedValues is not considered by index creation calls when checking if an index is already present or needs to be created.

    In unique indexes, only the attributes in fields are checked for uniqueness, but the attributes in storedValues are not checked for their uniqueness. Non-existing attributes are stored as null values inside storedValues.

  • unique (boolean, optional): If true, then create a unique index. Defaults to false. In unique indexes, only the attributes in fields are checked for uniqueness, but the attributes in storedValues are not checked for their uniqueness.

  • sparse (boolean, optional): If true, then create a sparse index. Defaults to false.

  • deduplicate (boolean, optional): The attribute controls whether inserting duplicate index values from the same document into a unique array index will lead to a unique constraint error or not. The default value is true, so only a single instance of each non-unique index value will be inserted into the index per document. Trying to insert a value into the index that already exists in the index will always fail, regardless of the value of this attribute.

  • estimates (boolean, optional): This attribute controls whether index selectivity estimates are maintained for the index. Not maintaining index selectivity estimates can have a slightly positive impact on write performance.

    The downside of turning off index selectivity estimates will be that the query optimizer will not be able to determine the usefulness of different competing indexes in AQL queries when there are multiple candidate indexes to choose from.

    The estimates attribute is optional and defaults to true if not set. It will have no effect on indexes other than persistent.

  • cacheEnabled (boolean, optional): This attribute controls whether an extra in-memory hash cache is created for the index. The hash cache can be used to speed up index lookups. The cache can only be used for queries that look up all index attributes via an equality lookup (==). The hash cache cannot be used for range scans, partial lookups or sorting.

    The cache will be populated lazily upon reading data from the index. Writing data into the collection or updating existing data will invalidate entries in the cache. The cache may have a negative effect on performance in case index values are updated more often than they are read.

    The maximum size of cache entries that can be stored is currently 4 MB, i.e. the cumulated size of all index entries for any index lookup value must be less than 4 MB. This limitation is there to avoid storing the index entries of “super nodes” in the cache.

    cacheEnabled defaults to false and should only be used for indexes that are known to benefit from an extra layer of caching.

  • inBackground (boolean, optional): This attribute can be set to true to create the index in the background, which will not write-lock the underlying collection for as long as if the index is built in the foreground. The default value is false.

Creates a persistent index for the collection collection-name, if it does not already exist. The call expects an object containing the index details.

In a sparse index all documents will be excluded from the index that do not contain at least one of the specified index attributes (i.e. fields) or that have a value of null in any of the specified index attributes. Such documents will not be indexed, and not be taken into account for uniqueness checks if the unique flag is set.

In a non-sparse index, these documents will be indexed (for non-present indexed attributes, a value of null will be used) and will be taken into account for uniqueness checks if the unique flag is set.

Note: Unique indexes on non-shard keys are not supported in a cluster.

Responses

HTTP 200: If the index already exists, then a HTTP 200 is returned.

HTTP 201: If the index does not already exist and could be created, then a HTTP 201 is returned.

HTTP 400: If the collection already contains documents and you try to create a unique persistent index in such a way that there are documents violating the uniqueness, then a HTTP 400 is returned.

HTTP 404: If the collection-name is unknown, then a HTTP 404 is returned.

Examples

Creating a persistent index

shell> curl -X POST --header 'accept: application/json' --data-binary @- --dump - http://localhost:8529/_api/index?collection=products <<EOF
{ 
  "type" : "persistent", 
  "unique" : false, 
  "fields" : [ 
    "a", 
    "b" 
  ] 
}
EOF

HTTP/1.1 201 Created
content-type: application/json
cache-control: no-cache, no-store, must-revalidate, pre-check=0, post-check=0, max-age=0, s-maxage=0
connection: Keep-Alive
content-length: 253
content-security-policy: frame-ancestors 'self'; form-action 'self';
expires: 0
pragma: no-cache
server: ArangoDB
strict-transport-security: max-age=31536000 ; includeSubDomains
x-arango-queue-time-seconds: 0.000000
x-content-type-options: nosniff
Show response body

Creating a sparse persistent index

shell> curl -X POST --header 'accept: application/json' --data-binary @- --dump - http://localhost:8529/_api/index?collection=products <<EOF
{ 
  "type" : "persistent", 
  "unique" : false, 
  "sparse" : true, 
  "fields" : [ 
    "a" 
  ] 
}
EOF

HTTP/1.1 201 Created
content-type: application/json
cache-control: no-cache, no-store, must-revalidate, pre-check=0, post-check=0, max-age=0, s-maxage=0
connection: Keep-Alive
content-length: 248
content-security-policy: frame-ancestors 'self'; form-action 'self';
expires: 0
pragma: no-cache
server: ArangoDB
strict-transport-security: max-age=31536000 ; includeSubDomains
x-arango-queue-time-seconds: 0.000000
x-content-type-options: nosniff
Show response body
❮  Indexes

© ArangoDB - Graph and Beyond