ksevelyar May 5 2016 at 12:54

Наследование таблиц в Postgresql с Ruby On Rails

8 min

13K

Rambler&Co corporate blogPostgreSQL*Ruby*Ruby on Rails*

Мигрируем на Postgres Inheritance

Что это и зачем нужно?

Предположим у вас есть крупное новостное издание, у которого много разных типов материалов.

Для каждого типа материала существует своя модель: Topics::Article, Topics::Online, Topics::NewsItem и так далее. У них будут одинаковыми большинство полей, такие как заголовок, обложка, текст, авторы. Различие только в нескольких специфичных полях, уникальных для каждого типа топика.

Поэтому вам не хочется раскладывать их по отдельным таблицам. Кроме нежелания создавать почти полностью повторяющиеся таблицы, для этого могут быть и несколько других причин. Необходимость сложных выборок с разными комбинациями этих типов, водопады UNION и полиморфизм подключающихся моделей в том числе.

Под катом опыт организации похожих моделей внутри Postgresql, с итогом в виде миграции на наследование таблиц. Стрельба в ногу серебряной пулей тоже присутствует, куда же без нее.

Single Table Inheritance

Первое что приходит в голову, это классический Single Table Inheritance. В Рельсах он автоматически включается, если наследовать одну модель от другой.

Создав одну общую таблицу Topics и добавив в нее служебное поле type можно хранить все классы внутри одной таблицы.

class Topic < ActiveRecord::Base
end

class Topics::Article < Topic
end

class Topics::NewsItem < Topic
end

...

Общая логика (например, публикация материала) уходит в Topic. Специфичная в отнаследованные классы.

У этой простой и обкатанной схемы есть проблемы с масштабированием. Что делать, если типов топиков уже больше пятнадцати и у каждого от двух до десяти уникальных полей?

На этом моменте бд пуристы морщатся как будто бы от сильнейшей зубной боли — воображая себе таблицу, строки которой всегда заполнены не более чем на 15-20%.

STI + Jsonb

Jsonb

В Postgresql c 9.4 есть возможность создавать поля типа jsonb. Как это может нам помочь?
Добавив в topics поле data этого типа мы можем хранить все наши дополнительные поля в ключах json.

Подключить в Рельсах это можно так:

class Topics::Online < Topic
  store_accessor :data, :start_at, :live
end

Теперь можно делать так:

online = Topics::Online.new(live: true)

online.live # => true

Или напрямую обращаться в json:

online['data']['live'] # => true

Проблемы Jsonb

Наслаждение достигнутым успехом быстро омрачается подпорками из костылей.

Преобразование типов

Помимо экзотики вроде массивов и объектов (хешей) jsonb предлагает для всех полей использовать только Number, String и Boolean.
Для других типов полей придется писать дополнительные методы. А если вы предпочитаете с сахаром, то и для этих полей тоже.

Предикаты:

def live?
  live == true
end

Более сложный случай для DateTime:

def start_at
  return Time.zone.parse(super) if super.is_a?(String)
end

Здесь нужно распарсить строку во время и не сломаться. Потому что ломаться нужно на этапе сохранения данных.

Валидация входных значений

Валидаций типа на уровне бд нет, можно легко и непринужденно сохранить такой топик:

online.live = 'Elvis'
online.start_at = 'Presley'

Существующие гемы, например activerecord-typedstore, частично решают проблему парсинга строк, но совсем не справляются с проверкой входных значений. Все нужно закрывать из приложения кастомными валидациями.

Громоздкие запросы

Булево поле:

scope live, -> { where(live: true) }

scope live, -> { where("(params->>'live')::bool = ?", true) }

А теперь даты:

scope :by_range, ->(start_date, end_date) { where(date: start_date..end_date) }

scope :by_range, lambda { |start_date, end_date|
  where(
    "to_date(params->>'date', 'YYYY-MM-DD') BETWEEN ? AND ?",
    start_date, end_date
  )
}

Помимо общей монструозности этот запрос еще и будет медленнее работать, из-за вынужденного использования to_date Постгреса.

Uniq

Постгрес пока не умеет делать обычный DISTINCT (.uniq) для записей с jsonb, нужно делать так:

.select('DISTINCT ON (tags.id) tags.*')

Нет значений по умолчанию

Приходится использовать разные конструкции в before_initialize вместо привычных null: false, default: false в миграции.

Связи

Использовать рельсовые has_many и belongs_to не выйдет. Нужно писать что-то свое.

На этом этапе jsonb получил черную метку и до троллейбуса из буханки хлеба дело не дошло.

Мигрируем на Postgres Inheritance

Наследование таблиц появилось в Постгресе достаточно давно (скорее всего обновлять версию не потребуется) и близко концепции наследования классов.
Только не классов, а таблиц, и не в Руби, а в Постгресе.

У вас по прежнему есть таблица topics, но расширяете ее вы не через набор дополнительных полей в этой самой таблице, а через дополнительные таблицы, содержащие только уникальные для каждого класса поля.

Проще всего показать на примере:

CREATE TABLE topics (
    headline        text,
    author_id       int
);

CREATE TABLE topics_onlines (
    status          char(2)
) INHERITS (topics);

Создав topics_onlines мы можем работать с ней как с обычной таблицей, у которой будут все три поля:

class Topics::Online < Topic
  # headline, author_id, status
end

Это просто, красиво и не требует массивного переписывания кода.

Postgres Inheritance + Rails

SELECT c.tableoid, c.headline, c.author_id FROM topics c

 tableoid | headline  | author_id
----------+-----------+----------
   139793 | Las Vegas |     2174
   139793 | Mariposa  |     1953
   139798 | Madison   |      845

Родительская и дочерняя таблицы линкуются внутри Постгреса через tableoid. Мы не увидими никаких джойнов по tableoid в explain, все это работает внутри Постгреса.

Из приложения topics_onlines будет выглядеть как самая обычная таблица без наследований, содержащая все поля из topics и специфичные для онлайна поля из topics_onlines.

А это значит, что со стороны Рельс нужно только написать миграцию создающую таблицы.
И что наследование таблиц можно использовать с любым фреймворком.

Миграция с STI на PGI

Чтобы воспользоваться всей этой радостью нужно написать миграцию.

Для начала нам потребуется стандартная обертка для sql миграции в Рельсах:

class CreateInheritanceTablesForTopics < ActiveRecord::Migration
  def change
    reversible do |dir|
      dir.up do

      ...

      end
    end
  end

Код дальше вставляется на место многоточия. Чтобы не накидывать простыню кода сразу, я покажу миграцию по порциям.

Триггер для проверки на уникальность по всем таблицам топиков

Создаем, но пока нигде не используем триггер в Postgresql:

CREATE OR REPLACE FUNCTION check_for_topic_dups()
  RETURNS trigger AS
$func$
BEGIN
  PERFORM 1 FROM topics where NEW.id=id;
  IF FOUND THEN
    RAISE unique_violation USING MESSAGE = 'Duplicate ID: ' || NEW.id;
    RETURN NULL;
  END IF;
RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

Триггер вызывает ошибку, если топик с таким id уже существует. Это страховка на тот случай, если что-то пошло не так.

Самое важное ограничение PGI — для всех дочерних таблиц не действуют индексы и ограничения родительской таблицы. То есть в этом плане все действительно ощущается как разные физические таблицы.

Для наших условий у разных типов топиков не может повторяться айдишник, поэтому был добавлен этот триггер. Он опционален и нужен как страховка.

Создание таблиц

Topic.descendants.each do |topic_type|
  sql = <<-SQL
    CREATE TABLE #{topic_type.pgi_table_name} ( CHECK (type='#{topic_type}') )
      INHERITS (topics);
    CREATE RULE redirect_insert_to_#{topic_type.table_name} AS
      ON INSERT TO topics WHERE
        (type='#{topic_type}')
      DO INSTEAD
        INSERT INTO #{topic_type.table_name} VALUES (NEW.*);
    CREATE TRIGGER check_uniquiness_#{topic_type.table_name}
      BEFORE INSERT ON #{topic_type.table_name}
      FOR EACH ROW EXECUTE PROCEDURE check_for_topic_dups();
  SQL
  execute(sql)

  add_index topic_type.table_name, :id
end

Создаются таблицы для каждого типа топиков (метамагия здесь больше для компактности, в миграциях так лучше не делать)
Для каждой дочерней таблицы добавляется ограничение на тип топика (в topics_onlines можно вставить только Topics::Online)
При попытке вставить топик с заполненным типом в topics он будет перенаправлен в дочернюю таблицу по типу
Вешаем на каждую таблицу раннее созданный триггер для проверки уникальных айдишников
Создаем индексы для дочерних таблиц

Разумеется, миграцию можно посадить на строгую диету, а все проверки вытащить в сами Рельсы.

Добавляем нативные поля в отнаследованные таблицы

В новые таблицы можно добавлять поля используя стандартные миграции:

Class PopulateTopicsTablesWithFields < ActiveRecord::Migration
  def change
    add_column :topics_onlines, :start_at, :datetime
    add_column :topics_news, :main, :boolean, null: false, default: false
  end
end

Если вы не готовы полностью избавиться от STI, то в классах топиков прописывается нужная таблица:

class Topics::Online < Topic
  self.table_name = :topics_online
end

Осталось только изменить тип схемы на sql:

# config/application.rb
config.active_record.schema_format = :sql

И все готово.

Скорость — PGI vs jsonb

Заключительным этапом было бы интересно оценить производительность. Так как все это затевалось ради удобства разработки, то тестированию скорости PGI было уделено не так много времени, однако какие-то выводы сделать можно.

После миграции были подняты две версии приложения, PGI и старая с jsonb.
Больше 5_000_000 топиков в каждой базе.

Количество всех топиков

Самый синтетический пример:

PGI:

Topics::Topic.count
   (8591.6ms)  SELECT COUNT(*) FROM "topics"
=> 5316226

Jsonb:

Topics::Topic.count
   (8580.1ms)  SELECT COUNT(*) FROM "topics"
=> 5316226

Не удивляйтесь странному числу, топики создавались пока не закончилось место на ssd.

Количество топиков одного типа

PGI:

Gazeta::Topics::Sport::Online.count
*  (219.5ms)  SELECT COUNT(*) FROM "topics_sport_onlines"
   WHERE "topics_sport_onlines"."type" IN ('Gazeta::Topics::Sport::Online')
=> 1000000

Jsonb:

Gazeta::Topics::Sport::Online.count
*  (419.0ms)  SELECT COUNT(*) FROM "topics"
   WHERE "topics"."type" IN ('Gazeta::Topics::Sport::Online')
=> 1000000

Запрос по boolean полю

Индекс не используется из-за высокой селективности.

PGI:

Gazeta::Topics::Sport::Online.megauho.explain
* Gazeta::Topics::Sport::Online Load (1376.2ms)  SELECT "topics_sport_onlines".*
  FROM "topics_sport_onlines"
  WHERE "topics_sport_onlines"."type" IN ('Gazeta::Topics::Sport::Online')
  AND "topics_sport_onlines"."megauho" = $1  [["megauho", "t"]]

Jsonb:

Gazeta::Topics::Sport::Online.megauho.explain
* Gazeta::Topics::Sport::Online Load (5819.6ms)  SELECT "topics".*
  FROM "topics"
  WHERE "topics"."type" IN ('Gazeta::Topics::Sport::Online')
  AND ((topics.params->>'megauho')::bool = 't')

Разница существенная.

Запрос по boolean полю c лимитом

Хотя бы уже пересекается с реальным миром.

PGI:

Gazeta::Topics::Sport::Online.megauho.limit(1000).explain
* Gazeta::Topics::Sport::Online Load (9.1ms)  SELECT  "topics_sport_onlines".*
  FROM "topics_sport_onlines"
  WHERE "topics_sport_onlines"."type" IN ('Gazeta::Topics::Sport::Online')
  AND "topics_sport_onlines"."megauho" = $1 LIMIT 1000  [["megauho", "t"]]

Jsonb:

Gazeta::Topics::Sport::Online.megauho.limit(1000).explain
* Gazeta::Topics::Sport::Online Load (23.7ms)  SELECT  "topics".*
  FROM "topics"
  WHERE "topics"."type" IN ('Gazeta::Topics::Sport::Online')
  AND ((topics.params->>'megauho')::bool = 't') LIMIT 1000

Разница есть.

PGI для запроса с низкой селективностью

Поиск по индексу, вернет 123 записи из миллиона, Index Scan.

PGI:

Gazeta::Topics::Sport::Online.megauho.megauho_by_date('2015-12-26').explain

* Gazeta::Topics::Sport::Online Load (6.0ms)  SELECT "topics_sport_onlines".*
  FROM "topics_sport_onlines" WHERE "topics_sport_onlines"."type"
  IN ('Gazeta::Topics::Sport::Online')
  AND "topics_sport_onlines"."megauho" = $1
  AND (topics_sport_onlines.date = '2015-12-26')  [["megauho", "t"]]

QUERY PLAN
----------
* Index Scan using index_type_megauho_date on topics_sport_onlines
   (cost=0.42..42.12 rows=20 width=682)
   Index Cond: (((type)::text = 'Gazeta::Topics::Sport::Online'::text)
   AND (megauho = true) AND ((date)::text = '2015-12-26'::text))
   Filter: megauho
(3 rows)

Jsonb:

Gazeta::Topics::Sport::Online.megauho.megauho_by_date('2015-12-26').explain

* Gazeta::Topics::Sport::Online Load (7.7ms)  SELECT "topics".*
  FROM "topics" WHERE "topics"."type"
  IN ('Gazeta::Topics::Sport::Online')
  AND ((topics.params->>'megauho')::bool = 't')
  AND (topics.params->>'date' = '2015-12-26')

QUERY PLAN
----------
* Index Scan using index_type_megauho_date on topics
   (cost=0.56..217.61 rows=27 width=948)
   Index Cond: (((type)::text = 'Gazeta::Topics::Sport::Online'::text)
   AND ((params ->> 'date'::text) = '2015-12-26'::text))
   Filter: ((params ->> 'megauho'::text))::boolean
(3 rows)

PGI быстрее.
Чем больше выборка — тем быстрее.
Для запросов использующих индексы, PGI все еще быстрее, но разница уже менее ощутима.

Правильно готовим jsonb

Для задачи полноценного расширения моделей jsonb может оказаться крайне токсичным. Разумеется, PGI тоже имеет свои ограничения, но для наших задач они были преодолены за один раз, во время миграции.

И все же jsonb может быть полезен для слабо структурированных данных из внешнего источника. Эти поля даже не нужно определять через store_accessor, их можно просто сохранять как есть, а дальше отдельный класс Builder соберет из них что-то полезное.

Для нас такими данными стали спортивные трансляции, забираемые из внешнего апи.

Tags:

Hubs:

Наследование таблиц в Postgresql с Ruby On Rails

Single Table Inheritance

STI + Jsonb

Jsonb

Проблемы Jsonb

Преобразование типов

Валидация входных значений

Громоздкие запросы

Uniq

Нет значений по умолчанию

Связи

Мигрируем на Postgres Inheritance

Postgres Inheritance + Rails

Миграция с STI на PGI

Триггер для проверки на уникальность по всем таблицам топиков

Создание таблиц

Добавляем нативные поля в отнаследованные таблицы

Скорость — PGI vs jsonb

Количество всех топиков

Количество топиков одного типа

Запрос по boolean полю

Запрос по boolean полю c лимитом

PGI для запроса с низкой селективностью

Правильно готовим jsonb

Articles

Information