Как я могу реструктурировать эту большую таблицу PostgreSQL 9.3 для более эффективного индексирования и поиска из приложения Rails?

Я внес конструктивный недостаток в это приложение, над которым я работаю. Проблемная область включает сопоставление Product с разреженной матрицей из 50 возможных атрибутов на Customer. Есть миллионы и миллионы строк. Итак, чтобы переформулировать:

Product has_many ProductAttributes
Customer has_many ProductAttributes

Product будет иметь свой набор ProductAttributes для каждого Customer.

Итак, у меня есть эта таблица из 50 миллионов строк, и подавляющее, подавляющее большинство полей пусты. Больно смотреть. Вот некоторые из моих проблем.

  • Каждый продукт в среднем имеет только около 4 атрибутов.
  • Чтобы сделать таблицу доступной для поиска без учета регистра, я использовал citext во всех столбцах атрибутов.
  • Первые 3 атрибута индексируются, и по ним выполняется поиск с использованием подстановочных знаков.

Я бы предпочел переделать всю эту ситуацию, используя поля HStore или JSON. Но меня беспокоит моя способность выполнять поиск против любого из них. Можно ли выполнять полнотекстовый поиск по любому из них? Ограничат ли меня пары ключ-значение HStore? В настоящее время каждый ProductAttribute может иметь только одно значение, но я могу представить сценарий, в котором я мог бы использовать массив значений по крайней мере в одном из них. Но не убийца сделки.

Если я могу искать в JSON и/или HStore, что лучше использовать, учитывая, что я использую ActiveRecord из приложения Rails 3.2?

Другой альтернативой было бы разбить ProductAttributes на таблицу detail:

Product has_many CustomerProducts
Customer has_many CustomerProducts
CustomerProducts has_many ProductAttributes

Таким образом, если продукт имеет только 3 или 4 атрибута для определенного Customer, в ProductAttributes будет 3 или 4 записи. И я мог бы просто искать столбец значений для ProductAttribute, возвращая родительскую запись CustomerProdct.

Итак, три возможных подхода:

HStore
JSON
Detail table

Спасибо за любую информацию, которую вы можете предложить.


postgresql ruby-on-rails json search hstore
person AKWF    schedule 13.05.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете абсолютно запросить Hstore, и я думаю, что это может быть хорошим решением, кроме перехода к более высокой нормальной форме в вашем дизайне схемы, что может быть сложно, если вы не являетесь человеком базы данных.

Вы можете сделать запрос, например:

Product.where("attributes -> 'Color' = 'Blue'")
Product.where("attributes -> 'Size' LIKE '%L%'") #finds 'Large' and 'Long' for ex.

Посмотрите этот каст Rails, полное раскрытие, это профессиональный каст (стоит $)

Тем не менее, подумайте о том, чтобы попытаться перевести свою схему, возможно, в 3NF, чтобы решить ее более основанным на базе данных способом.

person Chase    schedule 13.05.2014
comment
На самом деле я реформированный администратор базы данных Oracle (20 лет), просто мне показалось дорогим (и сложным) делать от 4 до 7 вставок AR для каждого продукта CustomerProduct, особенно при массовой загрузке. Я проверю RailsCast, я там участник. Но разве функциональность JSON не будет похожа на Hstore plus? Также проверяю textacular gem и могу ли я создавать индексы полнотекстового поиска для отдельных полей JSON. - person AKWF; 14.05.2014
comment
Также нашел это сообщение от парней из Thoughbot: .com/ - person AKWF; 14.05.2014
comment
Хм... Я не использовал textacular, но выглядит круто. Посмотрите на сравнение HStore и Json. быстрое чтение. FWIW, HStore на данный момент работает лучше при поиске. Для меня Hstore отлично подходит для внутренних строковых данных, не являющихся документами. Json отлично подходит для документов, разнородных данных и данных из внешних источников json (очевидно?). Да, я думаю, более высокие нормальные формы требуют больших затрат на пакетную вставку... - person Chase; 14.05.2014
comment
Спасибо, я видел это сегодня. Я был готов к Hstore, но тут увидел его заключительное заявление ;-) - person AKWF; 14.05.2014