Make better index
Indexes on table have a cost on update. On postgresql, all indexes must be updated when a row is updated even if the update does not change the indexed columns (see [1]). I think we have made the creation of index (with select=True) to easy and so we probably have too much indexes.
My proposal is to remove the attribute 'select' on the fields and to force developer to write code in __setup__ using the TableHandler.
This change will force us to review all the current index (and probably remove many) but also it will make us think if we do not need a more complex indexes (instead of simple column).
Later we could improve the TableHandler.index_action to allow to specify which kind of indexes (btree, trigram etc.) depending on the backend.
For the decision about keeping an index, it will be good to have statistics of production databases. Such statistics can be retrieved from PostgreSQL using this query:
SELECT * FROM pg_stat_all_indexes;
[1] https://eng.uber.com/mysql-migration/
Files
Download | Creator | Timestamp | Type |
---|---|---|---|
unnamed | @albertca | 2016-07-29 15:03:20.798000 UTC | text/plain |
unnamed | @albertca | 2016-07-29 15:07:18.506000 UTC | text/plain |