0

I am trying to index already created columns with over 5 million data in my table. My question is if I add index with the migration will the already created data be indexed as well ? Or do I need to re-index the created data if so how ?

This is my migration

add_index :data_prods, :date_field
add_index :data_prods, :entity_id

Thank you.

Edit I am using PostgreSQL dbms.

thiaguerd
  • 807
  • 1
  • 7
  • 16
Amal
  • 212
  • 2
  • 9

1 Answers1

1

The process of adding an index re-indexes the entire tables contents. A table with 5 million rows may take some time, I suggest testing in a staging environment (with a similar amount of data) to see how long this migration will take, as well as impact to the application.

Re: your comment about improving query times

Indexes will make queries faster, where the indexed columns are commonly referenced in "where" clauses. In your case, any query where you filter by date_field OR entity_id will be faster, but other queries will not be improved. It should be noted that each query will only use 1 index, if the majority of your queries use both date_field AND entity_id at the same time to filter data, you might be better off using a composite index. Id check out this post for further reading on composite indexes. Index on multiple columns in Ruby on Rails

gdxn96
  • 369
  • 5
  • 16
  • Could always use `connection.execute` to create indexes concurrently to help with no down time. Although that would make the creation of the index take longer. – Int'l Man Of Coding Mystery Jan 29 '20 at 13:53
  • @gdxn96 So composite indexing maybe better for me because I am using that combination in most of my queries. – Amal Jan 30 '20 at 06:21
  • @Int'lManOfCodingMystery Can you explain a bit on how to use the connetion.execute with no downtime. – Amal Jan 30 '20 at 06:22
  • @Amal , with `connection.execute` you can run raw SQL commands. So in your index you can do something like `create index concurrently idx_index_name on table_name using btree (column1)` Using the concurrent flag for creating an index will prevent a table lock. https://www.postgresql.org/docs/9.1/sql-createindex.html – Int'l Man Of Coding Mystery Jan 30 '20 at 08:13