Indexes are something that can provide a huge amount of performance advantages if done on fields that are good candidates for being indexed. But indexes can also prove to be of no use or can even harm database performance if the columns that are being indexed are not selected with care. For example, indexing every single column inside a table may not prove to be of any advantage and will unnecessarily eat up disk space, while also making database operations slow.

So, before jumping into how to index a particular field using the ORM we have taken up as an example here, let's first clarify what exactly an index is in this context of databases, (without diving too deeply into how exactly they work), which data structure is used to store indexes, and what algorithms a database uses to query indexes.

Indexes are a copy of selected columns that are stored in special data structures by the database to improve the efficiency of looking for a record inside a database. When looking through an index, a database does not need to traverse through all the records inside a table before it finds a matching record. All a database does here is quickly look into the index and find out whether a particular value is present or not. If present, it just returns the row corresponding to the value. This process can really speed up the performance of a database, sometimes, even by 100 times, if done efficiently.

Now, how do we add indexes to the models that we have already built? Well, it's a very easy task. But first, let's identify which fields we should index.

In our Roles model, we will be mostly querying through the id role, which we can find in the user model. Also, since there won't be too many roles, we can simply ignore indexing any other field inside the role model, because it won't necessarily impact the performance of the database. The same is valid for the activation keys model too, and hence we can also ignore adding indexes to the model.

Now, looking at the user model, we can assume that a search might be made more frequently based upon the id of the user, their username, and their email ID. Also, the user model is a model that will eventually grow considerably in size, and any improvements we can get in terms of data retrieval here will provide a lot of benefits in terms of overall response times for the application.

So, let's add an index to these fields. The following code snippet shows how this can be achieved:

username = Column(String(length=25), unique=True, nullable=False, index=True)
email = Column(String(length=255), unique=True, nullable=False, index=True)

Yes, it was that simple. That's what using an ORM provides us with in terms of increased productivity, while hiding the underlying differences different databases have in terms of creating indexes.

Now, with an understanding of how to optimize models, let's take a look at how we can maintain the consistency of database when a lot of database queries are being executed by the application in parallel.