We should take a look at the performance of our IS queries and see where we can create indexes to improve performance.
Current Situation
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.
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
Use the pg_stat_statements extension (CREATE EXTENSION IF NOT EXISTS pg_stat_statements;) to make PostgreSQL collect statistics on statements
Consider (temporarily) dropping the custom indexes in the database to see the real performance
Identify slow queries (both average time per query, and total time for frequent queries)
Think about possible indexes that could improve those queries
Create those migrations in the database to observe performance improvements
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.
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
Why do we need this? Who uses it, and when?
This is mostly useful for large deployments (The Things Stack Cloud / Community).
Proposed Implementation
pg_stat_statements
extension (CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
) to make PostgreSQL collect statistics on statementsContributing
Code of Conduct