Currently, applications create indexes on HAF internal tables that break encapsulation, impact the planner, may cause deadlocks between HAF and apps, and impact HAF speed. HAF needs to have control over indexes on its tables and probably needs an API to create indexes.

Haf indexes are "common resources" that are potentially used by all haf apps (i.e. two or more apps may need the same "shape" index). Since they are common resources, the app should just describe the "shape" of the index it needs (index name should be autogenerated based on the shape). This description should go into a haf table that tracks such index descriptions (e.g. registered_indexes). This should probably be done by extending the current table we already have that tracks haf indexes to create/drop. In other words, we'll track ALL indexes that we need to create/drop in a single table, regardless if they are needed by haf or by a haf app. We may need some canonicalization step to ensure that dups of the index shape don't get created.

An app requests a particular index type by calling create_app_index(app_context, index description). This index description gets upserted to the registered_indexes table. We should allow full flexibility in the index description, probably just a single string description if we can canonicalize it easily. The registered_indexes table should also track all apps that register they need the index (effectively acting as a reference counter for the index, so that we can remove the index if all the apps that need the index get uninstalled). HAF "default" indexes should be marked as needed by "haf" so that such indexes don't get dropped even if all apps are uninstalled (e.g. this could be done by haf itself registering these indexes using create_app_index).

The registered_indexes table will be used to manage creation and destruction of these indexes:

- If create_app_index is called before haf is in sync, only the registered_indexes table is changed (the index will get created later once haf enters sync state).
- If create_app_index is called while haf is in sync, the index will be created concurrently (so that haf operation isn't interrupted) if the index doesn't already exist and isn't in the process of being created. It might be best to track the creation state of the index in the registered_indexes table. We need to be sure we can distinguish between an invalid index and an index that is actively being built but not yet finished. There will be a separate psql process per hafd table that is registered to create indexes.
- When a full haf replay is started, all these indexes should be dropped, just like other haf indexes.
- When a haf replay is finished, it will create any of these indexes that don't exist. We can create indexes on multiple tables in parallel.
- Whenever all the registered indexes associated with a table have been created, haf should automatically analyze the table.
- When an app is uninstalled, we will drop the index if nothing else needs it.

Haf should also provide an api call that a HAF app can use to "wait" for all the haf indexes it requested to finish being created: wait_till_registered_indexes_created(app_context). This can be used by the block-processing portion or the server portion of the app to delay processing until the indexes have been created (e.g. it is possible the block processing code doesn't actually need the index and only the app's API calls need them).

If we don't already have a canonicalization solution, we should just skip doing that for now and use a string. We can just manually make sure the same string is used across apps. This should result in apps normally being installed when haf is first installed, which means hived will typically be the creator of the indexes. This is good because it will create them fastest (non-concurrently) and in parallel.

After hived finishes creating indexes, it will call spawn a psql process to call create_registered_indexes_when_requested(). This sql function will call created_registered_indexes_for_table(table_name) using dblink_send_query to asynchronously run the query,  enabling indexes to be added to different tables in parallel. created_registered_indexes_for_table will create all the indexes registered for table_name.


Let's change the name of create_app_index to register_index_dependency. This function doesn't create an index in the new flow, it just puts it in the table of registered indexes, and it can do this at any time (we will probably need some locking mechanism to deal with access by multiple threads that can update a row in this table).

So when do indexes get created?

- When hived leaves massive sync, it will create any registered indexes (similar to what it does now).
- If hived is already in live sync (here defined as hived having finished the index creation using the registered_indexes table), have apps lazily do it via create_dependent_indexes_if_necessary, in which case the indexes will get created lazily near the start of the first container that needs them (if they haven't been previously created and not yet in the process of being created).

What if hived has finished replaying, but is still in the process of creating indexes when a new index dependency is registered? This is an unlikely real-world case, but can be handled in several ways, depending on the implementation of hived's index creation process. In theory, it would be best if we could still add the index to the list of indexes created by hived if hived has not already "finished" with that table (i.e. finished analyzing it after adding indexes to it), but it's not a big deal if the index just gets built later (either by a periodically polling haf process or by the lazy-mechanism of dependencies that need it).

Despite the added complexity, I think it is important to integrate to the current table in hafd that adds/drops indexes rather than creating new tables. Here's some intro to the table:
https://gitlab.syncad.com/hive/haf/-/blob/develop/src/hive_fork_manager/hived_api_impl_indexes.sql
https://gitlab.syncad.com/hive/haf/-/blob/develop/src/hive_fork_manager/hived_api_impl.sql#L345

The command field will just be the entire command to create the index.

I looked at the existing code a bit more, its behavior will need to change some, since previously I think it clears this table as indexes get created, now we want to keep indexes in there once they get "registered", we just want to track their state in the table. Probably this is a first good step, since it will get you familiar with that code. So we need to add a field to the table to track the "status" of the index (missing, creating, created). We also need to track which apps use an index. I was honestly just thinking to track it via a tuple of strings in the row itself, but you could use a second table similar to what you suggested. In any case, I think creating an actual reference count field is overkill, just query on demand if there are any dependencies.

As for tracking the failure of the index creation, I guess the most likely ways this could happen is if the process creating the index gets killed or if the database runs out of space. We could store an error in the latter case, but in the former case, we can't store an error since the process is dead. We could add a new index status state called "invalid". If the process creating the index doesn't die, it could set the field to this state on an error (and we could add an extra field to the table to store the error I suppose). Then it would be up to the admin to analyze the issue to decide what to do: the code would not perform actions on indexes in the "invalid" state.

One problem with the above approach: I think the most likely failure will be when we intentionally shut down whatever process is creating the index. For example, say it is a process in haf and we shut down haf while the indexes are being created. Or say we are using lazy creation by the wait_till function and we shut down that app. In such cases, what we really want to do on the next startup is just delete the partially created indexes and start creating them again. But this is probably not a big problem if we are using haf to create the indexes: in the case of an interruption, the row will be in a "creating" state, not an "invalid" state. When haf gets restarted, it should first check if any indexes are in the "creating" state. Since only haf can be doing the creating, it knows that these are failed indexes, so it can just delete them and mark them as "missing".

The issue is a bit more complicated if we're using the "lazy" creation idea, since it isn't as obvious who was creating the index, so we don't know if it was shut down or still running without writing more clever code (e.g. tracking who is doing the creating, for example). I'm also not sure that shutting down the app will even cause the index creation to fail assuming the create index command has already been issued (as far as I know, it will keep getting created). But this would leave us with a valid index that was still marked as 'creating').

Maybe we could get away with an extra status column. We can check if an index is created or not in the pg_index table and it looks like we can also check the indices that are in the process of being created in the pg_stat_progress_create_index view. That leaves only the invalid state if we need it. One thing I don't like about storing the whole statement as a string is that we don't have access to the index name. And we will need it e.g. to remove the index when the app is removed. I guess we could use a regex for that if needed. Also note that there is a library to parse Postgres query string, but it seems quite an overkill unless we need it for other uses. E.g. it could be used to validate that given query is CREATE INDEX query and to check that two queries are in fact the same query.

Yes, I considered this, but IMO it's better to have a table where an admin can easily check the state of all such indexes with minimal effort.

The existing table already stores the index name: index_constraint_name. I don't know the mechanics of how it gets it right now in existing code: 1) either by specifying it in the call to create the index or if it gets parsed out of the command string. But I have no problem with requiring the user to specify it in the call for now, the same way I suggested we just force users to use the same string for the index command for now (it is easier to implement and there are few people who need to create such indexes).

But there is a problem with index duplications, two separate applications could make a similar index on the third application table, if they are installed on the same HAF instance it is wasting resources. If we store the CREATE INDEX as a string then yes, we need to be careful so that the same index in different apps is represented as the same string. Yes, for now we will ignore this problem of duplication since there are a limited number of users who will be creating such indexes.

## New API Calls

### register_index_dependency(app_context, index_description)
Registers an index dependency for an app. The index description is upserted to the registered_indexes table.

### wait_till_registered_indexes_created(app_context)
Waits for all the registered indexes requested by the app to be created. This can be used to delay processing until the indexes are available.