2

So I read this question, answer and the comments, but it doesn't answer my case, which is what to do when of the columns is a foreign key?

Here is my original migration to create the table in question:

class CreateTemplates < ActiveRecord::Migration[5.1]
  def change
    create_table :templates, id: :uuid do |t|
      t.references :account, type: :uuid, foreign_key: true
      t.string :name
      t.text :info
      t.string :title

      t.timestamps
    end
  end
end

Since account_id is a foreign_key (and identifies the customer) it will appear in almost all (99%) of queries on this table.

Now it has been decided that name should be unique to account, so the model has been updated:

  validates_uniqueness_of :name, scope: [:account]

So once I add the joint index:

add_index :templates, [:name, :account_id], unique: true

should I delete the index on account_id?

I ask because in SQLLite (see this), it seems the answer would be that I don't need the single index on account_id and to create my new index with account_id in the first position:

add_index :templates, [:account_id, :name], unique: true

I'm using postgres, so does the same idea apply?

rmcsharry
  • 5,363
  • 6
  • 65
  • 108

1 Answers1

5

You have to add extra index if it's not the first index.

So if you have this:

add_index :templates, [:name, :account_id], unique: true

then you should not delete the original :account_id foreign key index, since it is second index.

I recommend you to read about index implementations. It's pretty interesting and you can learn a lot from it.

Sean Magyar
  • 2,360
  • 1
  • 25
  • 57
  • So you are saying that Postgres works the same way as the SQLite example I linked to. Which means I could delete the account_id foreign_key index and create a new composite with account_id as the first index...and that would provide a little better performance on inserts because they the db only has to update one index, right? – rmcsharry Nov 22 '17 at 21:47
  • You could do that but it depends how your code uses it. If you first search based on the `account_id` then based on the name everywhere in your code, then yes you could delete it. – Sean Magyar Nov 22 '17 at 22:56