3/19/2019 - 4:47 AM

PostgreSQL index tips

Index order matters!

For exapmle polymor, Rails 4 or older created the index such as

add_index "http_responses", ["originator_type", "originator_id"], name: "index_http_responses_on_originator_id_and_originator_type", using: :btree

it's certainly slower than

add_index "http_responses", ["originator_id", "originator_type"], name: "index_http_responses_on_originator_id_and_originator_type", using: :btree

Algorthm concurrently

And when you try to remove the indexes on production, it might be locked because it's using. Try to add index by data migration such as

add_index :tables, ["originator_id", "originator_type"], algorithm: :concurrently 

by append algorithm: :concurrently to avoid potential crushes.

The caveat is that concurrent indexes must be created outside a transaction. By default, ActiveRecord migrations are run inside a transaction.