Auto Index vs. Manual Index

[ashokgudur]

Hi,

When should I use auto index and when should I explicitly opt for manual indexes and define them manually?

Thanks .

[arekpalinski]

Hi,

The answer is here - https://ravendb.net/docs/article-page/5.3/csharp/indexes/map-indexes#what-can-be-indexed - with the usage of static indexes you have variety of options that aren't available in auto indexes. For example, you can do some computations during the indexing (e.g. concatenation of two fields Name = user.FirstName + " " + user.LastName) or load related documents from different collections and index them as well (with the usage of LoadDocument()).

If you don't need any features like mentioned in the link you can stay with auto indexes and let RavenDB to create the minimal set of indexes that satisfy your queries.

[arekpalinski]

It will let a newly created auto index to process a small batch of documents so there will be some results to return. Afterwards an index will continue and proceed the rest of the documents.

Note that typically when you move to production system you export the definitions of indexes from your staging env (including auto index definitions) and import them on the prod so the indexes will be built before first querying on prod.

[mcrio]

Thanks @arekpalinski . Regardless of the export/import, in case that particular query is not run very often, we can expect inconsistent results during auto-indexing, ie we will get some results but some information like total results will be inaccurate until the auto-index is complete?

[arekpalinski]

An auto index, even if it's idle, constantly process the documents. It doesn't trigger the indexing only when you query it, it's ongoing process.

[mcrio]

I guess the suggested way to go would be to go with a manual index whenever we cannot afford to get inaccurate results. For example, there is a reporting job that kicks in every 5 days and pulls some data based on certain criteria. In this case, the auto-index would be long gone before a new reporting job kicks in, so the job would actually trigger a new auto-index creation and receive part of the data?

[arekpalinski]

You can still use auto indexes. You can extend the time when they are marked as idle and eventually deleted. It's controlled via the following options:

• Indexing.TimeToWaitBeforeMarkingAutoIndexAsIdleInMin
• Indexing.TimeToWaitBeforeDeletingAutoIndexMarkedAsIdleInHrs

[ashokgudur]

w.r.t. Indexing.TimeToWaitBeforeDeletingAutoIndexMarkedAsIdleInHrs
the default value is 72 hrs

That means the auto indexes will also deleted automatically?
I'm in puzzle now. I didn't know this. This will effect my query results (and also the performance)

thanks.

[aqume]

Can I set the value to -1 so that the auto index never gets delete?

[aqume]

Writing all the indexes manually is a huge and comber some effort which is actually made is by giving auto index feature.

But now another issue created by this deletion of auto indexes automatically after few hours /72

I don’t want these auto indexes deleted at all.

What should I do?

[aqume]

I think this is still a huge effort which needs to be done continuously by going behind each and every index.

This approach and settings are completely defeating the purpose of auto indexes.

Auto indexes are created based on the queries we write in code to create business feature’s without knowing underlying database complexity. This is good feature I found in RavenDB.

Developers 100's of queries in code and they change them when required also. Indexes are created automatically to optimise query performance.

Now if one needs to go behind all these queries and mark them according to the settings given above is unnecessary effort which needs DB administration involvement in development team and vice versa.

I think the default values of these settings should be -1. Those who wants to delete them will set them selectively.

Now I realised why my queries are slow and giving wrong results occasionally. I didn’t aware that the auto indexes are deleted after 72 hrs.

[ppekrol]

It is a server-wide or database-wide setting. You do not need to set it up per each index/query (you even can't), this can be set up in Database Configuration view in Studio or via Client API when database is being created. Also changing settings.json is possible, to change it for the whole server and each database that resides on it.

The mechanism that analyzes auto-indexes and queries and at the end merges them is a different one and is not related to those configuration options. You can control it via Indexing.TimeBeforeDeletionOfSupersededAutoIndexInSec (15 by default)

[aqume]

OK. Understood.
We will follow it so via db configuration settings.

thanks

[ashokgudur]

There are two configuration options that you can change either server or database-wide

1. Indexing.TimeToWaitBeforeMarkingAutoIndexAsIdleInMin
2. Indexing.TimeToWaitBeforeDeletingAutoIndexMarkedAsIdleInHrs

You can consider setting them to a higher value or to -1 (infinite)

Is the value -1 (infinite) a valid value. When I tried via the studio I cannot set it -1. However it takes 0 (zero).
Would the value 0 does the same?

[arekpalinski]

It's a RavenDB Studio issue. I opened the ticket to fix it: https://issues.hibernatingrhinos.com/issue/RavenDB-18405

You can still set it via settings.json file (https://ravendb.net/docs/article-page/5.3/csharp/server/configuration/configuration-options#json). This way all databases will have it configured.