Perfomance improvement rails postgreql

Question

Rails 5.2, Postgresql

class School < ApplicationRecord
    has_many :teacher_records
    has_many :teachers, through: :teacher_records
end

TeacherRecord model has #teacher_id, #school_id

class TeacherRecord < ApplicationRecord
end

Teacher model

class Teacher < ApplicationRecord
 scope :search_by_full_name ->(query) { where("CONCAT_WS(' ', first_name, last_name) LIKE ?", "%#{query}%") }
end

Recently we imported 150000000 records of teachers from various sources.

Issue: Search by full name takes 40seconds to return the results.

Issue 2: Loading in table with pagination (10 records each page) takes 15 seconds for some small school which has few hundred records.

Please see options here: [PostgreSQL LIKE query performance variations](https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations) — Mike Organek, Aug 14 '20 at 21:02

score 0 · Accepted Answer · answered Aug 27 '20 at 07:11

0

We fix this issue by converting the database into multi-tenant schema.

New schema will be created for each school when signup.

There is a powerful gem apartment to create a multi-tenant database

answered Aug 27 '20 at 07:11

ramamoorthy_villi

1 Answers1