Analyze IS query performance and create indexes to improve

[htdvisser]

Summary

We should take a look at the performance of our IS queries and see where we can create indexes to improve performance.

Current Situation

1. We have database indexes on some ID and EUI fields. I think that in some of our production deployments we have some additional indexes that are not present in the schemas in the repository.
2. We recently merged a new implementation of the IS storage layer. This implementation runs slightly different queries than the old implementation, so existing indexes may not longer be appropriate once we get rid of the old implementation.

Why do we need this? Who uses it, and when?

This is mostly useful for large deployments (The Things Stack Cloud / Community).

Proposed Implementation

1. Use the pg_stat_statements extension (CREATE EXTENSION IF NOT EXISTS pg_stat_statements;) to make PostgreSQL collect statistics on statements
2. Consider (temporarily) dropping the custom indexes in the database to see the real performance
3. Identify slow queries (both average time per query, and total time for frequent queries)
4. Think about possible indexes that could improve those queries
5. Create those migrations in the database to observe performance improvements
6. Write migrations to create the indexes

Contributing

• [X] I can help by doing more research.
• [ ] I can help by implementing the feature after the proposal above is approved.
• [ ] I can help by testing the feature before it's released.

Code of Conduct

• [X] I agree to follow TTN's Community Code of Conduct.

[KrishnaIyer]

At the moment we don't see performance issues. Let's get back to this when we notice slow queries.