diff options
Diffstat (limited to 'doc/development')
-rw-r--r-- | doc/development/adding_database_indexes.md | 2 | ||||
-rw-r--r-- | doc/development/migration_style_guide.md | 19 |
2 files changed, 16 insertions, 5 deletions
diff --git a/doc/development/adding_database_indexes.md b/doc/development/adding_database_indexes.md index 3ddb15fa290..01b621b6631 100644 --- a/doc/development/adding_database_indexes.md +++ b/doc/development/adding_database_indexes.md @@ -73,7 +73,7 @@ especially the case for small tables. If a table is expected to grow in size and you expect your query has to filter out a lot of rows you may want to consider adding an index. If the table size is -very small (e.g. only a handful of rows) or any existing indexes filter out +very small (e.g. less than `1,000` records) or any existing indexes filter out enough rows you may _not_ want to add a new index. ## Maintenance Overhead diff --git a/doc/development/migration_style_guide.md b/doc/development/migration_style_guide.md index 2c9ad8c00cf..c15e51c2b9d 100644 --- a/doc/development/migration_style_guide.md +++ b/doc/development/migration_style_guide.md @@ -304,10 +304,16 @@ combining it with other operations that don't require `disable_ddl_transaction!` ## Adding indexes -If you need to add a unique index, please keep in mind there is the possibility -of existing duplicates being present in the database. This means that should -always _first_ add a migration that removes any duplicates, before adding the -unique index. +Before adding an index, consider if this one is necessary. There are situations in which an index +might not be required, like: + +- The table is small (less than `1,000` records) and it's not expected to exponentially grow in size. +- Any existing indexes filter out enough rows. +- The reduction in query timings after the index is added is not significant. + +Additionally, wide indexes are not required to match all filter criteria of queries, we just need +to cover enough columns so that the index lookup has a small enough selectivity. Please review our +[Adding Database indexes](adding_database_indexes.md) guide for more details. When adding an index to a non-empty table make sure to use the method `add_concurrent_index` instead of the regular `add_index` method. @@ -334,6 +340,11 @@ class MyMigration < ActiveRecord::Migration[4.2] end ``` +If you need to add a unique index, please keep in mind there is the possibility +of existing duplicates being present in the database. This means that should +always _first_ add a migration that removes any duplicates, before adding the +unique index. + For a small table (such as an empty one or one with less than `1,000` records), it is recommended to use `add_index` in a single-transaction migration, combining it with other operations that don't require `disable_ddl_transaction!`. |