Use Postgres Array Column for Rails Many-to-Many Relationship

Question

I've been hearing lately that some many-to-many and one-to-many relationships (without additional join data, eg user-membership-group) can be queried significantly faster in Postgres using array columns of id's rather than join tables see discussion for Node Postgres ORM.

For a many-to-many relationship, choosing which table (or both) to maintain the relationship arrays would depend on which direction the query goes (users<->groups would need array columns on both tables, users->tag would only require the relationship to be maintained in an array column on the users table only.

Several questions:

Are there any gems (Rails or otherwise) that are using the new Postgres array column to maintain relationships?
Does anyone have any benchmarks for comparing a simple join table vs array column on a bi-directional and one directional many-to-many relationships?
To capture the performance improvements with Ruby, very basic functionality would look something like below. Aside from configuration for custom primary keys, method and class names, do you see any obvious improvement to be made from the code below?

```

module ArrayRelationships

 def self.included(base)
   base.extend(ClassMethods)
 end

 module ClassMethods
   # association must be lower case, pluralized name of associated class
   def array_has_many(association)
    define_method(association) do 
      instance_name = association.singularize
      class_name = instance_name.titleize
      class_name.constantize.where(id: self.send("#{instance_name}_ids"))
    end
   end
 end
 end

 class User << ActiveRecord::Base
   include ArrayRelationships
   array_has_many :tags
 end

Of course, the users table would have to have the :tag_ids array field in the database. If we wanted to add the inverse relationship for Tag#users, we would simply add the db field, include ArrayRelationships, and array_has_many :users.

That guy who opened that issue? He's doing it wrong. There's no way to guarantee that the values in the first table's array exist in the table it is referencing. That's why no one does this. Unless you're doing your joins wrong or don't have the appropriate indexes in place, the amount of overhead produced from joining in 2 extra tables in your query is negligible. — cimmanon
– cimmanon, Commented Jul 25, 2015 at 22:04

Legendary · Accepted Answer · 2020-12-22 17:56:26Z

EDITOR: On the page of this gem it says, use it on your own risk

WARNING: This gem is a work in progress and hasn't released yet.

I haven't tried it yet, but appears that someone built a gem to support arrays for associations: https://github.com/marshall-lee/has_array_of. Copying from the README:

How does it work?

Suppose we have a playlist that contains many videos. One video can be included in many playlists. It's a classic many-to-many situation but we implement it differently.

# db/migrate/20141027125227_create_playlist.rb
class CreatePlaylist < ActiveRecord::Migration
  def change
    create_table :playlists do |t|
      t.integer :video_ids, array: true # adding array fields works only starting from Rails 4
      t.index :video_ids, using: :gin   # we add GIN index to speed up specific queries on array
    end
  end
end

# app/models/playlist.rb
class Playlist < ActiveRecord::Base
  has_array_of :videos  # by convention, it assumes that Post has a video_ids array field
end

# app/models/video.rb
class Video < ActiveRecord::Base
  belongs_to_array_in_many :playlists # optional
end

Now we can work with videos like with regular array. It will correctly proxy all changes to video_ids field.

playlist = Playlist.find(1)
playlist.videos = [video1,video2]  # playlist.video_ids = [1, 2]
playlist.videos[0] = video3        # playlist.video_ids[0] = 3
playlist.videos.insert(1, video4)  # playlist.video_ids = [3, 4, 2]
playlist.videos.delete_at(1)       # playlist.video_ids = [3, 2]
playlist.videos.pop                # playlist.video_ids = [3]
# ... and so on

video3.playlists
# => [playlist]

Collectives™ on Stack Overflow

Use Postgres Array Column for Rails Many-to-Many Relationship

1 Answer 1

How does it work?

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

How does it work?

Comments

Your Answer

Sign up or log in

Post as a guest

Related