Instance ManagementBulk OperationsData Structure

Creating Indexes for Entities

Configure indexes to optimize data retrieval.

Overview

When correctly configured indexes can significantly accelerate data retrieval by allowing a database to quickly locate data. Keep in mind that adding too many indexes to one Entity can decrease performance. As a result, you can add up to 2 indexes to one Entity and one index can have up to 5 attributes. To achieve the desirable outcome creating indexes should be done with caution only by advanced users. In Fynapse, properly set indexes can reduce the time of getting query results, for example on the Finance Data Service screen.

Note that creating the index might take a while. While the process is running, the Entity for which the indexes are being created will be ‘blocked’, i.e. you will not be able to make any structural changes. Moreover, while the creation process is running, data processing and ingestion may be slower.

General Information

Which Entities Can Have Indexes?

The mechanism for creating indexes for selected attributes is implemented only for Entities stored in FDS. FDS stores only defined Entities, that is Entities created by Fynapse users. The complete list of all Entities with their attributes can be viewed on the Finance Data Service screen (to learn more about this screen, refer to Finance Data Service Screen).

When and How Indexes Can Be Created?

Adding indexes can happen while creating the new Entity or later during its modification via a JSON file uploaded through Bulk Operations > Configuration Data (to learn more about bulk upload, refer to Configuration Data).

Mechanism Overview

  • Up to 2 indexes can be added for one Entity in a single JSON file and single and multi-column indexes can be created
  • The maximum number of attributes in one index is 5
  • Adding, modifying, and deleting indexes does not cause any data loss
  • Creating indexes happens independently from data processing and populating the database
  • Creating indexes, especially on Entities with lots of data can take a lot of time (it can take hours even)
  • The creation process is monitored and if it fails it is automatically restarted

Validations

The system checks if:

  • A particular attribute that will be indexed exists in the database or if the JSON file includes its creation
  • The JSON file does not include deletion of the attribute that will be indexed
  • The JSON file does not modify indexes that are currently being created
  • An index name contains only allowed characters (that is, lower case letters, numbers, and underscores)

You will not be able to upload the JSON file if it includes a configuration that:

  • Modifies the Entity for which indexes are still being created
  • Creates an index on the attribute that does not already exist in the database.

Indexes Configuration Parameters

The following parameters must be provided in the JSON file to add an index:

  • indexName - a name of the index. It must meet the following requirements:
    • Must be unique in the whole database
    • Can contain lower case letters, numbers, and underscores
    • Can have up to 61 characters
  • attributes - names of attributes on which the index will be created

One Entity can have up to 2 indexes and one index can have up to 5 attributes.

1  "entities": [
2    {
3      "namespace": "fynapse",
4      "name": "simpleEntity",
5      "type": "DefinedEntity",
6      "attributes": [
7        {
8          "label": "simpleEntity ID",
9          "mandatory": true,
10          "name": "simpleEntity_id",
11          "type": {
12            "primitive": "UUID",
13            "typeName": "PrimitiveType"
14          }
15        },
16        {
17          "label": "Event Date",
18          "mandatory": true,
19          "name": "event_date",
20          "type": {
21            "primitive": "DATE",
22            "typeName": "PrimitiveType"
23          }
24        },
25        {
26          "label": "Amount",
27          "mandatory": true,
28          "name": "amount",
29          "type": {
30            "primitive": "DECIMAL",
31            "typeName": "PrimitiveType"
32          }
33        }
34      ],
35      "secondaryIndexes": [
36        {
37          "indexName": "event_date_index",
38          "attributes": [
39            "event_date"
40          ]
41        },
42        {
43          "indexName": "amount_index",
44          "attributes": [
45            "amount"
46          ]
47        }
48      ],
49      "primaryKeyAttributeNames": [
50        "custom_id"
51      ]
52    }
53  ]

How to Build Indexes Configuration?

These are the general rules that will help you to prepare index configuration to get particular results:

  • If you do not want to make changes to existing indexes but you want to make other changes to the Entity, delete the secondaryIndexes section.
1  "entities": [
2    {
3      "namespace": "fynapse",
4      "name": "simpleEntity",
5      "type": "DefinedEntity",
6      "attributes": [
7        {
8          "label": "simpleEntity ID",
9          "mandatory": true,
10          "name": "simpleEntity_id",
11          "type": {
12            "primitive": "UUID",
13            "typeName": "PrimitiveType"
14          }
15        },
16        {
17          "label": "Event Date",
18          "mandatory": true,
19          "name": "event_date",
20          "type": {
21            "primitive": "DATE",
22            "typeName": "PrimitiveType"
23          }
24        },
25        {
26          "label": "Amount",
27          "mandatory": true,
28          "name": "amount",
29          "type": {
30            "primitive": "DECIMAL",
31            "typeName": "PrimitiveType"
32          }
33        }
34      ],
35      "primaryKeyAttributeNames": [
36        "custom_id"
37      ]
38    }
39  ]
  • If you want to create one new index and still have existing indexes, add this new index and all existing ones to the secondaryIndexes section. The same rule applies to adding many indexes and keeping the existing ones, just add all new indexes and at the same time list all existing ones in the secondaryIndexes section.
1  "entities": [
2    {
3      "namespace": "fynapse",
4      "name": "simpleEntity",
5      "type": "DefinedEntity",
6      "attributes": [
7        {
8          "label": "simpleEntity ID",
9          "mandatory": true,
10          "name": "simpleEntity_id",
11          "type": {
12            "primitive": "UUID",
13            "typeName": "PrimitiveType"
14          }
15        },
16        {
17          "label": "Event Date",
18          "mandatory": true,
19          "name": "event_date",
20          "type": {
21            "primitive": "DATE",
22            "typeName": "PrimitiveType"
23          }
24        },
25        {
26          "label": "Amount",
27          "mandatory": true,
28          "name": "amount",
29          "type": {
30            "primitive": "DECIMAL",
31            "typeName": "PrimitiveType"
32          }
33        }
34      ],
35      "secondaryIndexes": [
36        {
37          "indexName": "event_date_index",
38          "attributes": [
39            "event_date"
40          ]
41        },
42        {
43          "indexName": "amount_index",
44          "attributes": [
45            "amount"
46          ]
47        },
48        {
49          "indexName": "event_date_amount_index",
50          "attributes": [
51            "event_date",
52            "amount"
53          ]
54        }
55      ],
56      "primaryKeyAttributeNames": [
57        "custom_id"
58      ]
59    }
60  ]
  • If you want to add a new index and delete all existing indexes, add only this new one to the secondaryIndexes section.
1  "entities": [
2    {
3      "namespace": "fynapse",
4      "name": "simpleEntity",
5      "type": "DefinedEntity",
6      "attributes": [
7        {
8          "label": "simpleEntity ID",
9          "mandatory": true,
10          "name": "simpleEntity_id",
11          "type": {
12            "primitive": "UUID",
13            "typeName": "PrimitiveType"
14          }
15        },
16        {
17          "label": "Event Date",
18          "mandatory": true,
19          "name": "event_date",
20          "type": {
21            "primitive": "DATE",
22            "typeName": "PrimitiveType"
23          }
24        },
25        {
26          "label": "Amount",
27          "mandatory": true,
28          "name": "amount",
29          "type": {
30            "primitive": "DECIMAL",
31            "typeName": "PrimitiveType"
32          }
33        }
34      ],
35      "secondaryIndexes": [
36        {
37          "indexName": "event_date_amount_index",
38          "attributes": [
39            "event_date",
40            "amount"
41          ]
42        }
43      ]
44    }
45  ]
  • If you want to delete all existing indexes, leave the secondaryIndexes section empty.
1"entities": [
2    {
3      "namespace": "fynapse",
4      "name": "simpleEntity",
5      "type": "DefinedEntity",
6      "attributes": [
7        {
8          "label": "simpleEntity ID",
9          "mandatory": true,
10          "name": "simpleEntity_id",
11          "type": {
12            "primitive": "UUID",
13            "typeName": "PrimitiveType"
14          }
15        },
16        {
17          "label": "Event Date",
18          "mandatory": true,
19          "name": "event_date",
20          "type": {
21            "primitive": "DATE",
22            "typeName": "PrimitiveType"
23          }
24        },
25        {
26          "label": "Amount",
27          "mandatory": true,
28          "name": "amount",
29          "type": {
30            "primitive": "DECIMAL",
31            "typeName": "PrimitiveType"
32          }
33        }
34      ],
35      "secondaryIndexes": [],
36      "primaryKeyAttributeNames": [
37        "custom_id"
38      ]
39    }
40  ]