Database performance

[markusbradke]

I noticed that there are no indexes (e.g., b-tree) set in the SQL DDLs (/database).
When the database grows this would lead to performance issues when searching for entries for example.
The database engine would also need to perform a full sequential scan.
Also using a full-text search on the software descriptions would be a good idea.

Would it be good to discuss what kind of searches we want to perform and decide what to index?

[ewan-escience]

This is indeed a good discussion to have.

Do you already have specific columns where an index would help and why? Otherwise, we're happy to hear suggestions as they come up.

On the frontend, we use the ILIKE operator to search some fields. We decided not to use this for the description because of possible performance issues. If full text search for the description is needed, we could think about how to implement this, and all help is welcome. A possible issue with this is that we allow for remote markdown documents that are not saved in the database.

[markusbradke]

I see that the slugs already have unique indexes which will speed up the routing.
For the LIKE operators the normal b-tree indexes will not help, see: https://www.cybertec-postgresql.com/en/postgresql-more-performance-for-like-and-ilike-statements/
But we could keep this in mind for now and implement it later.

For the remote markdown. Maybe we can still store the remote copy in the database and update/harvest remote information on a daily basis (if the checkbox is checked)?

One more point that I noticed. I'm not the biggest fan of enums (especially when the list is long; e.g., the mention types). It might be better to create a separate model. This will also speed up queries.