Skip to main content

Keys and indexes

Indexes are crucial in improving database performance by allowing faster data retrieval. To understand the significance of indexes in Rell, let's start with the basics.

Imagine that you have a large dataset and need to find a specific entry within it. Without an index, searching through all the data can be time-consuming with a worst-case time complexity of O(n). However, when an attribute is indexed in Rell, it dramatically enhances query performance, reducing the time complexity to O(log(n)), thanks to PostgreSQL's underlying binary tree structure.

In Rell, you have two options to mark an attribute for indexing: key and index. While both improve query performance, they serve different purposes:

Key​

A key is an index that enforces a unique constraint on the indexed attribute. This uniqueness constraint guarantees that entities with the same attribute value will not exist in your database, making it less error-prone when creating entries.

Index​

An index is used to improve query performance for non-unique attributes.

note

Note that both keys and indexes introduce extra time in creating entities, so be sure to mark your attributes cautiously.

Key and index example​

Consider the scenario where multiple houses share the same street, each with its unique id. This could be modelled like:

entity house {
    key id: integer;
    index street: text;
}

Now, the following queries would be high performant:

val unique_house = house @ { .id == 10 };
val houses_on_main_street = house @* { .street == "main street" };

Composite index​

To further optimize your database queries, you can add a composite index. This is particularly useful when finding entries based on a combination of attributes. For instance, if your app often queries all houses owned by a specific owner on a particular street, you can create a multi-column index.

index owner, street;

The order of the fields in a multi-column index is crucial for performance. This is how composite indexes work in SQL, where each column precedes the latter. To create the most efficient index, place the most significant attribute first. For instance, if you want to query all houses with exactly four rooms and a floor area greater than 100:

house @ { .number_of_rooms == 4, .floor_area > 100 };

In this case, an optimal index configuration would be:

index number_of_rooms, floor_area;

Composite keys work the same way. If a specific combination of entries can only exist once, adding a composite key will ensure this constraint in the database. Place the attribute yielding the fewest results first to achieve optimal performance.

Practical example

This example is focused on creating the house records and requesting specific house records.

Add the house entity to the src/main/entities.rell file:

src/main/entities.rell
entity house {
  index street;
  number: integer;
  key street, number;
  number_of_rooms: integer;
  number_of_floors: integer;
  floor_area: integer;
  index number_of_rooms, floor_area;
}

To query the newly created house records, add the code below to the src/main/queries.rell file:

src/main/queries.rell
query get_specific_houses(number_of_rooms: integer, floor_area: integer) = house @* { .number_of_rooms == number_of_rooms, .floor_area >= floor_area } ( $.to_struct() );

To interact with the database, we must run the node locally. The --wipe flag is ususlly set when there is a need to delete all records from the database:

chr node start --wipe

Add the new street record:

chr tx create_street "Drottninggatan"

Create three houses using the create_house operation:

chr tx create_house 1 30 4 1 200
chr tx create_house 1 31 4 1 100
chr tx create_house 1 32 4 1 109

At this point, we are ready to request specific houses that are available in the database:

chr query get_specific_houses

The result of the query above is as follows:

result
[
  [
    "floor_area": 200,
    "number": 30,
    "number_of_floors": 1,
    "number_of_rooms": 4,
    "street": 1
  ],
  [
    "floor_area": 109,
    "number": 32,
    "number_of_floors": 1,
    "number_of_rooms": 4,
    "street": 1
  ]
]