• Array column support
• Hstore extension support
• UUID support

• Type must be declared (integer, text, etc.)
• Multi-dimensional arrays are supported
• Can be variable or fixed length

CREATE TABLE test (
  id integer,
  title varchar(255),
  body text,
  child_ids integer[]); --This is an array

class CreateTest < ActiveRecord::Migration
    def change
        create_table :test do |t|
        t.title :string
        t.body :text
        t.child_ids :integer, array: true, default: []

        t.timestamps
    end
end

acts_as_taggable_on is great, but it can slow down your queries.

class CreatePosts < ActiveRecord::Migration
  def change
    create_table :posts do |t|
      t.string :title
      t.text :body
      t.integer :tag_array, array: true, default: []

      t.timestamps

    end

    add_index :posts, :tag_array, using: :gin
  end
end

class Post < ActiveRecord::Base
    acts_as_taggable
    before_save :cache_tags

    private
    def cache_tags
        tag_array = self.tag_list
    end
end

Post.where('tag_array @> ?', 'tag')

or

Post.where('tag_array @> ?', ['tag1', 'tag2'])

class EnableHstore < ActiveRecord::Migration
    def change
        enable_extension 'hstore'
    end
end

Then:

class AddMetadataToPosts < ActiveRecord::Migration
    def change
        add_column :posts, :metadata, :hstore

        add_index :posts, :metadata, using: :gin
    end
end

[1] pry(main)> p = Post.first
=> #<Post id: 1, title: "Test", body: "Lorem ipsum dolor sit amet", tag_array: [], metadata: nil>
[2] pry(main) p.metadata = {foo: 'bar', baz: 'quux'}
=> {:foo=>"bar", :baz=>"quux"}
[3] pry(main) p.save
...
=> true
[4] pry(main) p
=> #<Post id: 1, title: "Test", body: "Lorem ipsum dolor sit amet", tag_array: [], metadata: {:foo=>"bar", :baz=>"quux"}>
			

With store_accessor, ActiveRecord lets us treat hstore keys like column attributes.

class Post < ActiveRecord::Base
    store_accessor :metadata, :foo, :baz
end

[5] pry(main)> p.foo
=> "bar"
[6] pry(main)> p.foo = 'xyzzyx'
=> 'xyzzyx'
[7] pry(main)> p.save
...
=> true
[8] pry(main)> Post.where("metadata -> 'foo' = 'xyzzyx'")
=> [#<Post id: 1, title: "Test", body: "Lorem ipsum dolor sit amet", tag_array: [], metadata: {:foo=>"xyzzyx", :baz=>"quux"}>]
				

You might have noticed this:

add_index :posts, :metadata, using: :gin

Postgres defaults to using btree indices, but provides GiN (Generalized Inverted Index) and GiST (Generalized Search Tree) index types as well.

As of version 4.0.0, ActiveRecord supports the using keyword to specify index types.

Basically, GiN is three times faster on read operations, and three times slower on writes, compared to GiST.

I suggest reading the fine manual for your use case.

(But you'll mostly want GiN.)

UUIDs are pretty useful, and Postgres has an extension for a UUID column type.

We'll have to enable it:

class EnableUuid < ActiveRecord::Migration
    def change
        enable_extension 'uuid-ossp'
    end
end

Now we can even use UUID primary keys:

class CreateComments < ActiveRecord::Migration
    def change
        create_table :comments, id: :uuid do |t|
            t.text :body
            t.references :post

            t.timestamps
        end
    end
end

You'll have to work around some ActiveRecord defaults, like references in migrations (creates integer fk columns), as well as redefining class methods like first and last.

ActiveRecord 4 gives us some nice support, but we can do more.

Enter the schema_plus gem.

Normally, if we wanted to use basic features like views in our DB, it meant writing irreversible SQL migrations and converting schema.rb to SQL.

Not anymore.

This is from actual production code. Don't worry, it's long since dropped:

class CreateStreamItemView < ActiveRecord::Migration
  def up
    execute <<-SQL
      CREATE OR REPLACE VIEW stream_items AS
         SELECT a.id, a.user_id, a.title, a.body, a.private, a.type,
            a.created_at, a.updated_at, a.comments_count, a.hearts_count,
            a.allow_feedback, a.goal_commitment_id, a.caption,
            a.evidence_file_name, a.evidence_file_size, a.evidence_updated_at,
            ( SELECT ARRAY( SELECT v.name
                           FROM ( SELECT ts.tag_id, ts.taggable_id,
                                    ts.taggable_type, ts.tagger_id,
                                    ts.tagger_type, ts.context, t.name
                                   FROM taggings ts
                              JOIN tags t ON ts.tag_id = t.id) v
                          WHERE v.taggable_id = a.id AND v.taggable_type::text = 'Artifact'::text) AS "array") AS tags,
            NULL::integer[] AS committed_user_ids, a.slug
           FROM artifacts a
        UNION
         SELECT g.id, g.creator_id AS user_id, g.title, g.body, g.private,
            'Goal'::character varying AS type, g.created_at, g.updated_at,
            g.comments_count AS comments_count, g.hearts_count AS hearts_count,
            g.allow_feedback, NULL::integer AS goal_commitment_id,
            NULL::character varying AS caption,
            NULL::character varying AS evidence_file_name,
            NULL::integer AS evidence_file_size,
            NULL::timestamp without time zone AS evidence_updated_at,
            ( SELECT ARRAY( SELECT v.name
                           FROM ( SELECT ts.tag_id, ts.taggable_id,
                                    ts.taggable_type, ts.tagger_id,
                                    ts.tagger_type, ts.context, t.name
                                   FROM taggings ts
                              JOIN tags t ON ts.tag_id = t.id) v
                          WHERE v.taggable_id = g.id AND v.taggable_type::text = 'Goal'::text) AS "array") AS tags,
            ( SELECT ARRAY( SELECT v.user_id
                           FROM ( SELECT gc.user_id, gc.goal_id
                                   FROM goal_commitments gc
                              JOIN goals g ON gc.goal_id = g.id) v
                          WHERE v.goal_id = g.id) AS "array") AS committed_user_ids, g.slug
           FROM goals g;
      SQL
  end

  def down
    execute <<-SQL
      drop view stream_items
    SQL
  end
end

It gets better:

class CreatePostsViews < ActiveRecord::Migration
    # We can use the ActiveRecord query interface
    create_view :commented_posts, Post.joins(:comments).where.not(comments: {id: nil})
    # Or SQL
    create_view :uncommented_posts, 'SELECT * FROM posts LEFT OUTER JOIN comments ON comments.post_id = posts.id WHERE comments.id IS NULL'
end

In either case, schema.rb will reflect the change, so we can still use rake db:schema:load and not have to convert our schema to SQL.

Schema Plus does a whole lot more:

• Foreign Key Constraints
• drop_table enhancements, like if_exists and cascade
• Expressions for column defaults, e.g.:
  • t.datetime :seen_at, default: { expr: 'NOW()' }
  • t.datetime :seen_at, , default: { value: "2011-12-11 00:00:00" }

There's still a lot that isn't baked into ActiveRecord, but there are great gems and clever hacks that support additional PG features:

• activerecord-postgis-adapter
  • Support for PostGIS spatial queries
• postgres_ext
  • Additional query methods for array, hstore datatpyes; support for INET/CIDR datatype
• pg_search
  • Support for Postgres full text search
• This gist
  • Proper interval support without coercion to String (as ActiveRecord is wont to do)

There's a lot more to Postgres than I can cover here, and the folks at Heroku have put together a great tour: Postgres - The Bits You Haven't Found Yet

Postgres The Bits You Haven't Found, Heroku's Peter van Hardenberg at Waza 2013 from Waza on Vimeo.

• Postgres is amazing, and you should explore it more
• Don't fear the SQL... O/RM will only get you so far
• That being said, keep hacking on ActiveRecord, and if a PG feature you like isn't supported...

Jason Lewis

Email: jason@anestuary.com

Twitter: @canweriotnow

Github: https://github.com/canweriotnow

Blag: http://decomplecting.org