Rails 6.1中如何高效查询指定用户可见的所有Publication？

我正在开发一个Rails 6.1应用，包含以下两个模型的数据库结构：

Publications表结构 #

# == Schema Information
#
# Table name: publications
#
#  allow_comments        :boolean          default(FALSE)
#  created_at            :datetime         not null
#  draft                 :boolean          default(FALSE)
#  filter_query          :jsonb
#  id                    :integer          not null, primary key
#  content               :text             not null
#  user_id               :integer          not null
#  title                 :string
#  updated_at            :datetime         not null

Users表结构 #

# == Schema Information
#
# Table name: users
#  id                    :integer          not null, primary key
#  first_name            :string           not null
#  last_name             :string           not null
#  role_id               :integer          not null
#  some_other_associated_ids               

其中publications.filter_query字段存储Ransack查询参数，用于生成SQL查询筛选可查看该publication的用户，筛选条件涵盖users表及关联模型的几乎所有字段，包含相等、字符串包含、ILIKE等条件（基于PostgreSQL）。

当前痛点：查询某一publication的可见用户很容易，但反向查询指定用户可见的所有publication效率极低——当前采用UNION ALL所有publication的查询结果再过滤user_id的方案，耗时久、内存占用大，甚至导致Puma worker被杀死。

试过的优化方案均无效：

• 按部分字段分组查询，但筛选组合过多，既不实用还易出错，耗时几乎无改善；
• 分线程处理少量publication再聚合结果，但消耗过多数据库连接。

现在需要更高效的解决方案。

1. 预计算用户匹配关系，存储关联表 #

核心逻辑 #

提前计算每个publication对应的可见用户集合，存储到中间关联表，查询指定用户的可见publication时直接关联该表即可。

实现步骤 #

• 创建关联表：

  # 生成迁移文件
  rails generate migration CreatePublicationUserVisibilities publication:references user:references
  rails db:migrate
  

• 触发更新时机：
  • 当publication的filter_query更新时，重新计算符合条件的用户，同步更新关联表；
  • 当用户或其关联模型数据更新时，通过消息队列异步检查所有包含该用户相关筛选条件的publication，更新关联关系。
• 查询优化：

  # 直接通过关联表查询用户可见的publication
  user.publications.joins(:publication_user_visibilities)
  

优缺点 #

• 优点：查询速度极快，依赖关联表索引即可完成；
• 缺点：需要维护关联关系的一致性，数据更新时会有额外开销，适合查询频率远高于数据更新频率的场景。

2. 用PostgreSQL自定义函数动态匹配筛选条件 #

核心逻辑 #

利用PostgreSQL的JSONB处理能力，编写自定义函数将filter_query中的Ransack参数转换为针对指定用户的匹配逻辑，直接批量筛选符合条件的publication。

实现步骤 #

• 创建PostgreSQL自定义函数（需根据实际Ransack参数扩展逻辑）：

  CREATE OR REPLACE FUNCTION user_matches_filter(user_id integer, filter jsonb)
  RETURNS boolean AS $$
  DECLARE
    user_record users%ROWTYPE;
  BEGIN
    SELECT * INTO user_record FROM users WHERE id = user_id;
    
    -- 示例：处理first_name的模糊匹配条件
    IF filter ? 'first_name_cont' THEN
      RETURN user_record.first_name ILIKE '%' || filter->>'first_name_cont' || '%';
    END IF;
    
    -- 扩展处理其他筛选条件（如eq、cont、ilike等）...
    
    RETURN true; -- 默认匹配所有未指定筛选条件的publication
  END;
  $$ LANGUAGE plpgsql STABLE;
  

• 为filter_query创建GIN索引提升匹配效率：

  CREATE INDEX idx_publications_filter_query ON publications USING GIN(filter_query);
  

• 在Rails中调用函数查询：

  Publication.where("user_matches_filter(?, filter_query)", current_user.id)
  

优缺点 #

• 优点：无需维护额外关联表，数据一致性无额外开销；
• 缺点：函数逻辑需覆盖所有可能的Ransack筛选条件，维护成本较高，复杂条件下执行效率略低于预计算方案。

3. 用物化视图预计算可见关系 #

核心逻辑 #

创建物化视图定期预计算所有publication与用户的可见关系，查询时直接读取物化视图数据，平衡查询效率与实时性。

实现步骤 #

• 创建物化视图（需根据filter_query的筛选逻辑完善WHERE条件）：

  CREATE MATERIALIZED VIEW publication_user_visibilities AS
  SELECT p.id AS publication_id, u.id AS user_id
  FROM publications p
  CROSS JOIN users u
  WHERE 
    (p.filter_query->>'first_name_cont' IS NULL OR u.first_name ILIKE '%' || p.filter_query->>'first_name_cont' || '%')
    AND (p.filter_query->>'role_id_eq' IS NULL OR u.role_id = (p.filter_query->>'role_id_eq')::integer)
    AND p.draft = false; -- 排除草稿状态的publication
  

• 为物化视图创建索引：

  CREATE INDEX idx_mv_puv_user_id ON publication_user_visibilities(user_id);
  

• 定时刷新物化视图（用Sidekiq Cron等定时任务）：

  # 定时任务示例
  class PublicationVisibilityRefreshJob
    include Sidekiq::Job
    
    def perform
      ActiveRecord::Base.connection.execute("REFRESH MATERIALIZED VIEW publication_user_visibilities;")
    end
  end
  
  # 配置每小时刷新一次
  PublicationVisibilityRefreshJob.perform_every(1.hour)
  

• 查询时关联物化视图：

  Publication.joins("JOIN publication_user_visibilities puv ON puv.publication_id = publications.id")
             .where("puv.user_id = ?", current_user.id)
  

优缺点 #

• 优点：查询效率接近预计算关联表，无需实时维护关联关系；
• 缺点：数据存在延迟，无法实时反映最新的publication筛选条件或用户数据变化，适合对实时性要求不高的场景。

内容的提问来源于stack exchange，提问作者chincoya