Default indexes in id column?

Question

I am creating a rails 3 application on Heroku and it uses postgres as a database. In my rails migration, I added indexes to all ID columns. This is mistake? I'm starting to wonder if postgres has index column IDs by default.

If it was a mistake, how can I fix it? If I just write rails to remove indexes from identifier columns, will this fix everything?

Answer 1

According to postgresql documentation :

Adding a primary key will automatically create a unique btree index on the column or group of columns used in the primary key. 

Hovewer, this does not stop you from creating a different index in the same columns. This may make sense if you use some other indexing policy (for example, the GiST index). But, if you are not sure about this, 99.9% say that you just created an identical index.

In fact, this will not affect the functionality of the application at all. The only thing to consider is that indexes are rebuilt during update operations, so some performance problems may occur. Thus, since the manual suggests removing rarely used indexes (last sentence), you better remove these indexes from db.

I am not very familiar with RoR migrations, but I think it’s enough to remove these indexes for migration.

Answer 2

Check with \ d "tablename" which indexes are in your table. All are listed there, including automatically generated ones. If you find duplicates, it is easy enough to remove one (drop the index "indexname" on "tablename"). You will only delete the index with the appropriate name ...