Is it safe to reorder columns in schema.rb for Rai

Question

Running Rails 4 with Postgres 9.4 in development and production. I've got a large table that has grown by migrations over time. Because of the many different columns on the table, I want to reorder things so that the columns are grouped more logically. In other words, some column elements naturally group together based on what information they capture.

I found a discussion on using after: in a migration to reorder columns (using ALTER TABLE in SQL), at this stack overflow discussion. I then went ahead and set up a migration to do that. After running the migration I notice that my schema.rb file hasn't changed. Looking at the columns in the database (postgres on development), nothing has changed there either.

Further research led me to the Postgres wiki, which states there is currently no support for altering column position in Postgres.

This isn't mission critical, but it would make life easier. My question is:

1. If I simply move lines in the schema.rb file up or down in order to position them as desired, will that cause any problems? Since I expect to provision any new database with rake db:schema:load, it wouldn't seem like that should break anything. Further, since the previous migrations aren't materially changed (just the order of columns they output to schema.rb) everything should be internally consistent, no?

2. If that's a bad idea, can I simply go back into the earlier migrations, add the syntax for after: :some_column to each column element so they correctly set up the schema.rb file?

My assumption is that when I rebuild my production database from scratch with seed data, it will use the schema structure to correctly create the table in the desired order. Right now it's not a real, deployed database with end users, so it doesn't seem like an issue. Using option #1 above seems like the easiest solution, as long as it doesn't break anything.

Answer 1

The order of your columns does not matter to Ruby or Rails.

As to whether it matters to any code you wrote, only you and your tests can answer that. If you're only using ActiveRecord and not doing any straight SQL that references column number, you should be fine.

Answer 2

As a workaround, you can use annotate to document your models in the code and then reorder the created comment there.

Of course, that will probably be overwritten by annotate once you run it again. And it will only make your life easier if you look at the table structure, but it won't help if you manually run SQL queries with SELECT * in your db.

Answer 3

Many people new to postgresql often ask if it has support for altering column positions within a table. Currently it does not; if you want to change column positions, you must either recreate the table, or add new columns and move data. The idea of allowing re-ordering of column position is not one the postgresql developers are against, it is more a case where no one has stepped forward to do the work.

ref

• If I simply move lines in the schema.rb file up or down in order to position them as desired, will that cause any problems?

no. It does nothing with column order in PG.

• If that's a bad idea, can I simply go back into the earlier migrations, add the syntax for after: :some_column to each column element so they correctly set up the schema.rb file?

after: :some_column option does nothing with column order if you use PG