ActiveRecord: Better native types mappings for PostgreSQL

After watching, @pvh’s talk about PostgreSQL data types, I started looking what default types ActiveRecord uses for PostgreSQL when running a database migration.

By default, the ActiveRecord PostgreSQL adapter uses serial for primary keys, timestamp without time zone for datetime/timestamp, and varchar for strings (ref).

Based on the suggestions of the talk, You probably want to use bigserial instead of serial, because of the larger amount of serial values (uuids are another option), and always timestamp with time zones.

I was able to change the default through this initializer file in my application:

# config/initializers/active_record_better_native_database_types.rb
require "active_record/connection_adapters/postgresql_adapter"
module ActiveRecord
 module ConnectionAdapters
   class PostgreSQLAdapter
     NATIVE_DATABASE_TYPES.merge!(
       primary_key: "bigserial primary key",
       datetime:  { name: "timestamptz" },
       timestamp: { name: "timestamptz" }
       string:    { name: "text" }
     )
   end
 end
end

NOTE: varchar and text are the same. This change is just personal preference (because is short!). Varchar limit was removed in Rails 4.2.

Unfortunately, this patch is not backward compatible, so it’s better for new applications. Still, you can use these data types in new migrations:

class CreateUsers < ActiveRecord::Migration
 def change
   create_table :persons, id: :bigserial do |t|
     t.text :first_name
     t.text :last_name
     t.column :created_at, :timestamptz, null: false
     t.column :updated_at, :timestamptz, null: false
   end
 end
end

Update: There is an open pull request in the Rails’ GitHub repository to use bigserial as default: https://github.com/rails/rails/pull/24962. Looks like there are plans for timestamptz by default too.

Update 2: Rails 5 has a feature to change the default primary key type when generating database migrations: http://blog.mccartie.com/2015/10/20/default-uuid's-in-rails.html