Эрик Саттервайт, старший бэкенд-архитектор Help.com
TechTalk — это серия технических блогов, написанных инженерами Help.com. Сериал исследует все тонкости различных языков программирования, способы преодоления общих (и нишевых) препятствий, инфраструктуру, новые технологии и функции, а также все, что между ними. В этом посте старший системный архитектор Эрик Саттервайт рассказывает о своем процессе навигации по гибкой схеме в реляционных базах данных.
Реляционные базы данных обычно используют жесткую схему: предопределенные таблицы, содержащие типизированные столбцы, обеспечивающие широкий набор функций, которые в противном случае были бы невозможны. Это одновременно и большая сила, и большая слабость. С одной стороны, строгая типизация позволяет базам данных предоставлять богатый набор операторов, функций и функциональных возможностей для каждого из типов. Для Postgres это обычно представляет собой синтаксис sql типов столбцов для взаимодействия с ними. С другой стороны, это означает, что все данные в таблице однородны, и сделать отклонения или изменения достаточно сложно.
В Help.com мы находимся в процессе переноса ряда приложений из хранилища документов на Postgres. Одной из функций, которые у нас были, были настраиваемые атрибуты, которые эффективно позволяли конечным пользователям связывать произвольные данные с различными объектами. В дополнение к простому переносу возможности хранить определяемые пользователем данные, теперь нам нужно разрешить конечным пользователям запрашивать связанный объект с помощью атрибутов, которые они определили. Это включает в себя возможность сортировки, применения фильтров диапазона (числовых или дат) и выполнения полнотекстового сопоставления фраз по текстовым значениям.
Например, ACME Co может захотеть определить атрибуты своих пользователей следующим образом:
- ip_адрес (текст)
- имя (текст)
- login_date (отметка времени)
- max_sale_value (число)
- total_purchases (число)
- VIP (логическое значение)
Из этих данных они могут захотеть задать произвольные вопросы об этих данных, чтобы найти и получить представление о пользователях. Например:
Найдите всех users, которые вошли в систему within the last 3 months, имеют more than 10 total purchases, но являются not vip и чьи name starts with J
Переведено примерно на SQL:
SELECT * FROM user_attributes WHERE login_date BETWEEN NOW() - INTERVAL '90 DAYS' AND NOW() AND total_purchases > 10 AND name ILIKE 'j%' AND vip = FALSE
Допустим, другая компания, WizzBang LLC, хочет хранить другой набор данных:
- имя (текст)
- номер_телефона (текст)
- dog_lover (логическое значение)
- cat_lover (логическое значение)
- pet_count (число)
- first_pet (отметка времени)
- favourite_dog_breed (текст)
- favourite_cat_breed (текст)
- award_count (число)
Мало того, что данные, которые они хранят, сильно отличаются как физически, так и контекстуально, они также будут задавать совсем другие вопросы, чем ACME. Конечно, обе они являются крупными компаниями с примерно 10 миллионами пользователей. Это означает, что нам понадобится способ индексировать их данные для поддержки запросов, которые им нужно будет выполнять, чтобы приложение не останавливалось. Есть несколько вариантов.
ЭАВ
Дизайн Entity Attribute Value (EAV) существует уже некоторое время. У него есть плюсы и минусы. С другой стороны, в нашей ситуации столбец, в котором хранится фактическое значение, является физическим типизированным столбцом, с которым мы можем выполнять различные операции. Кроме того, поскольку это столбец, мы могли бы проиндексировать его для повышения производительности.
Схемы EAV пытаются сделать жесткую реляционную модель и сделать ее немного более гибкой. Таблица entities определяет базовые вещи (автомобиль). Таблица attributes определяет свойства объекта (вес), а таблица attribute_values содержит значение свойства, связанного с объектом. Для наших нужд основная проблема заключается в том, что существует только один столбец значений, а это означает, что все они должны быть одного типа. Это затрудняет поддержку дат, логических или числовых типов.
JSON (JAY’sun) -н., существительное
Мы можем сериализовать наши данные как JSON, и все будет просто:
- Формат файла с открытым стандартом, в котором используется удобочитаемый текст для передачи данных, состоящих из пар атрибут-значение и типов данных массива.
- Тот парень из фильмов про Борна
JSON
Вы можете подумать: «JSON!» Подумайте еще раз. Если все, что нам нужно было сделать, это сохранить данные, возможно, вы что-то поняли. Но мы должны сделать немного больше с этим, и это становится трудным. Особенно:
- На самом деле это просто текст под капотом, что означает, что мы должны выполнять много ручного приведения типов определенных полей, и при этом не будут использоваться индексы (медленно).
- Чтобы иметь возможность выполнять любой поиск, диапазонные запросы и т. д., нам в любом случае нужно знать, какие данные там находятся. И если нам нужно это контролировать — мы могли бы просто использовать таблицу
- Выполнять объединение значений в столбцах JSON сложно. Мы либо должны добавить столбцы в таблицу, чтобы помочь, либо поместить столбец JSON в исходную таблицу, чтобы избежать необходимости выполнять соединение — как хранилище документов.
Настоящим нарушителем условий сделки являются запросы, нацеленные на определенные поля в структуре JSON. Вам нужно знать, какие данные находятся в поле, и явно приводить их для выполнения любой фильтрации, кроме точного совпадения.
-- SAMPLE TABLE w/ JSON COLUMN CREATE TABLE sample( id SERIAL , word TEXT , json_data JSONB NOT NULL ); CREATE INDEX json_data_gin_indx ON sample USING GIN(json_data);
Затем мы можем вставить некоторые фиктивные данные.
--- INSERT SOME RANDOM DATA INSERT INTO sample (word, json_data)( SELECT encode(gen_random_bytes(10), 'hex') , JSON_BUILD_OBJECT( 'value', iter , 'sample', encode(gen_random_bytes(10), 'hex') ) from generate_series(1, 2000000) as iter ) -- > Affected rows: 2000000 -- > Time: 76.108s
Наконец, мы можем попытаться запросить некоторые значения.
-- TRY TO QUERY FOR SOME VALUES SELECT * FROM sample WHERE json_data->>'value' < 2 -- > ERROR: operator does not exist: text < integer
Хотя оператор ->> дает вам доступ к значениям полей в JSON, доступ к ним осуществляется через строковое представление, что сильно ограничивает способы его запроса. Чтобы обойти эту проблему, вам придется вручную заполнить все поля.
SELECT * FROM sample WHERE CAST(json_data->>'value' AS INTEGER) < 2
Это не очень сложно сделать само по себе, и это сработает на короткое время. Проблема здесь в том, что такой запрос не может использовать индексы. Хотя можно индексировать столбцы JSON с помощью индексов GIN, вы довольно ограничены, когда речь заходит о типах запросов, которые будут использовать индексы. Как только таблица станет достаточно большой, нам понадобится другое решение.
Что нам действительно нужно, так это реальная таблица Postgres с типизированными столбцами, которая дает нам все мощные предикаты запросов и возможности индексирования, которые мы знаем и любим. Мне нужна гибкая схема. Чтобы получить функциональность, необходимую для реализации описанной функции, нам нужно будет переосмыслить это.
Таблица атрибутов
На самом деле это довольно хороший вариант использования наследования таблиц. Табличное наследование PostgreSQL напрямую связано с методами объектно-ориентированного программирования. Таблицы могут наследовать столбцы и атрибуты от общей родительской таблицы. Каждая дочерняя таблица будет иметь все столбцы, добавленные или удаленные из родительской, но по-прежнему сможет определять свои собственные столбцы для создания уникальной схемы. Вот как мы можем создать гибкую схему с физическими таблицами. Мы можем сделать этот процесс в основном автоматическим с помощью триггеров.
-- BASE TABLE CREATE TABLE custom_attributes ( id UUID NOT NULL DEFAULT gen_random_uuid() , user_id UUID NOT NULL , created_at TIMESTAMPTZ DEFAULT NOW()::TIMESTAMPTZ , company_id UUID NOT NULL ) WITH(FILLFACTOR=75);
Достаточно просто. Теперь нам нужно настроить триггер, который генерирует дочернюю таблицу для каждой новой компании, которую мы приглашаем на борт, чтобы у них было место для хранения всех своих пользовательских данных.
-- TRIGGER: when a new company record is inserted -- Generate an attribute table to hold custom data CREATE OR REPLACE FUNCTION generate_custom_attribute_table() RETURNS TRIGGER AS $$ DECLARE tablename TEXT; ident TEXT; company_id TEXT; BEGIN company_id := COALESCE(NEW.id, gen_random_uuid())::TEXT; ident := REPLACE(company_id, E'\-', ''::TEXT); tablename := 'user_attributes_' || ident; EXECUTE FORMAT( ' CREATE TABLE %s ( CONSTRAINT %s_pkey PRIMARY KEY (user_id, company_id) )INHERITS (custom_attributes) ', tablename, tablename ); RETURN NULL; END; $$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER on_company_insert AFTER INSERT ON company FOR EACH ROW EXECUTE procedure generate_custom_attribute_table();
Когда новая запись вставляется в таблицу company, триггер создает таблицу, которая наследуется от таблицы custom_attribute. Это таблица, которая будет содержать физические столбцы для хранения точек данных, определенных компанией. Изначально в этой таблице нет столбцов, отличающихся от базовой таблицы.
Теперь нам понадобится способ, позволяющий администраторам управлять своей схемой, не предоставляя им прямого доступа к внутренней таблице. Для этого нам понадобится каталог, в котором хранятся имена полей и их соответствующие типы, которые можно использовать в качестве общедоступного интерфейса.
Читать сейчас: TechTalk: краткое введение в Golang Sidecars в Kubernetes
Каталог Таблица
Мы будем хранить определения атрибутов, которые наши компании хотят хранить в таблице, из которой мы можем просто добавлять/удалять записи. По сути, все, что нам нужно, это имя поля и тип данных, которые будут храниться: в этом случае мы собираемся ограничить это только полями number, boolean, text и date.
CREATE DOMAIN user_attribute_type AS TEXT CONSTRAINT user_attribute_field_chk NOT NULL CHECK(VALUE IN ('boolean', 'date', 'number', 'text')); CREATE TABLE IF NOT EXISTS user_attribute_catalog ( id UUID NOT NULL gen_random_uuid() , company_id UUID NOT NULL REFERENCES company(id) , field_name text NOT NULL , field_type user_attribute_type , created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()::TIMSTAMPTZ , CONSTRAINT catalog_attribute_name_uniq (company_id, field_name) );
Эта таблица довольно проста: она содержит имя и тип столбца. Когда мы хотим показать все определенные атрибуты для компании, чтобы они могли вносить изменения, мы можем запросить идентификатор компании.
SELECT field_name, field_type FROM user_attribute_catalog WHERE company_id = $1
Последней частью этого будет пара триггеров для добавления и удаления соответствующих столбцов в дочерней таблице, когда записи добавляются или удаляются из таблицы каталога. Вставка записи в таблицу каталога добавляет столбец в соответствующую таблицу атрибутов компании. Тип столбца определяется столбцом field_type вставляемой записи:
-- ADD ATTRIBUTE CREATE OR REPLACE FUNCTION public.add_user_attribute() RETURNS TRIGGER AS $$ DECLARE statements TEXT; tablename TEXT; ident TEXT; organization_id TEXT; field TEXT; BEGIN ident := REPLACE(NEW.company_id::TEXT, E'\-', ''::TEXT); tablename = 'user_attributes_' || ident; field := NEW.field_name || '_' || ident IF NEW.field_type = 'boolean' THEN statements := FORMAT(' ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s BOOLEAN ', tablename, field); ELSEIF NEW.field_type = 'number' THEN statements := FORMAT(' ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s FLOAT8 ', tablename, field); ELSEIF NEW.field_type = 'date' THEN statements := FORMAT(' ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s TIMESTAMPTZ ', tablename, field); ELSEIF NEW.field_type = 'text' THEN statements := FORMAT(' ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s zdb.fulltext CONSTRAINT ua_%s_chk CHECK(char_length(%s) <= 512) ', tablename, field, field, field); ELSE RAISE EXCEPTION 'Unknown attribute type: %s', NEW.field_type; END IF; EXECUTE statements; RETURN NULL; END; $$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER on_insert_user_attribute_fields AFTER INSERT ON user_attribute_fields FOR EACH ROW EXECUTE PROCEDURE add_user_attribute();
Как видно из приведенного выше, наш триггер на самом деле изменяет таблицу, относящуюся к рассматриваемой компании, и выдаст ошибку, если мы столкнемся с каким-либо забавным случаем. Помимо очевидных преимуществ физической таблицы для каждой компании, кроме того, влияние обслуживания может быть локализовано. Если, например, нам нужно было перестроить какие-то индексы, вручную очистить таблицы или что-то в этом роде, мы можем выполнить эти операции над таблицами для конкретных компаний и не штрафовать всех, кто использует наше приложение.
-- REMOVE ATTRIBUTE CREATE OR REPLACE FUNCTION public.remove_user_attribute() RETURNS TRIGGER AS $$ DECLARE tablename TEXT; ident TEXT; field TEXT; BEGIN ident := REPLACE(OLD.company_id::TEXT, E'\-', ''::TEXT); tablename := 'user_attributes_' || ident; field := OLD.field_name || '_' || UUID_TO_IDENT(OLD.id); EXECUTE FORMAT(' ALTER TABLE %s DROP COLUMN IF EXISTS %s ', tablename, field); RETURN NULL; END; $$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER on_delete_user_attribute_fields AFTER DELETE ON user_attribute_fields FOR EACH ROW EXECUTE PROCEDURE remove_user_attribute();
Эти две триггерные функции управляют определением внутренней таблицы, которая будет содержать фактические данные атрибутов. Когда запись вставляется в таблицу каталога, добавляется столбец. И наоборот, когда запись удаляется из каталога, соответствующий столбец удаляется.
Важно отметить, что добавляемый и удаляемый столбец не имеет ограничений и значений по умолчанию. Это позволяет избежать необходимости переписывать целые таблицы и в основном представляет собой изменение метаданных о таблице. Эти операции могут выполняться на производственных таблицах с несколькими миллионами записей за миллисекунды.
Вставка в таблицу компаний создаст таблицу атрибутов специально для этой компании:
INSERT INTO company (id, name) VALUES ('02335c58-a66f-4b53-9801-cb8045e2e848', 'ACME'); # list tables \dt company custom_attributes custom_attribute_catalog user_attributes_02335c58a66f4b539801cb8045e2e848
По умолчанию в нем будут только те столбцы, которые были определены в родительской таблице атрибутов.
# user_attributes_02335c58a66f4b539801cb8045e2e848 +-------------+------+ | Column Name | Type | | ----------- | ---- | | id | UUID | | company_id | UUID | | user_id | UUID | +-------------+------+
Добавление определения поля в каталог добавляет столбец соответствующего типа в дочернюю таблицу, настроенную для конкретной компании.
INSERT INTO user_attribute_catalog (company_id, field_name, field_type) VALUES ( '02335c58-a66f-4b53-9801-cb8045e2e848' , 'fake_number' , 'number' );
Триггер, который мы определили ранее, будет выполнять работу по добавлению столбца в правильную таблицу атрибутов.
# user_attributes_02335c58a66f4b539801cb8045e2e848 +----------------------------------------------+--------+ | Column Name | Type | |----------------------------------------------|--------| | id | UUID | | company_id | UUID | | user_id | UUID | | fake_number_6ad3e88314e04832b39daef8fa7ff730 | DOUBLE | +----------------------------------------------+--------+
Удаление записей для из таблицы каталога дополнительно удалит столбец из таблицы атрибутов.
Индексация
Мы решили проблему гибкой схемы, но у нас все еще есть проблема, связанная с необходимостью выполнения сложных определяемых пользователем запросов к большому набору данных. Нам нужно проиндексировать это, и для этого мы собираемся использовать Elasticsearch через фантастическое расширение ZomboDB.
Это такой же индекс, как и любой другой индекс Postgres, просто он живет в Elasticsearch
После того, как расширение установлено и настроено, нам нужно обновить первый триггер, который генерирует таблицу атрибутов, чтобы добавить индекс Elasticsearch.
BEGIN company_id := COALESCE(NEW.id, gen_random_uuid())::TEXT; ident := REPLACE(company_id, E'\-', ''::TEXT); tablename := 'user_attributes_' || ident; EXECUTE FORMAT( 'CREATE TABLE %s ( CONSTRAINT %s_pkey PRIMARY KEY (user_id, company_id) ) INHERITS (custom_attributes); CREATE INDEX IF NOT EXISTS %s_zdx ON %s USING zombodb((%s.*)) WITH (alias = ''%s'') ' , tablename, tablename, tablename, tablename, tablename, tablename ); RETURN NULL; END;
Вот и все. Самый простой способ представить, как ZomboDB и Postgres работают вместе, таков: это такой же индекс, как и любой другой индекс Postgres, просто он живет в Elasticsearch; Точно так же, как Postgres управляет индексом BTREE на диске, он будет управлять индексом ZomboDB в Elasticsearch. ZomboDB индексирует всю строку, поэтому по мере добавления столбцов и заполнения значений индекс обновляется. Это означает, что мы можем выполнять практически любой поисковый запрос, поддерживаемый Elasticsearch. Мы можем обновить SQL-запрос для набора данных, определенного для ACME, чтобы использовать ZomboDB.
SELECT * FROM user_attributes_02335c58a66f4b539801cb8045e2e848 WHERE user_attributes_02335c58a66f4b539801cb8045e2e848 ==> dsl.limit(25, dsl.must( dsl.term('vip', 'false') , dsl.range(field=>'total_purchases', gt=>10) , dsl.range( field=>'login_date' , lte=>'2018-12-30' , gte=>'2018-09-30' ) , dsl.wildcard('name', 'j*') ) )
Это говорит PG использовать индекс ZomboDB, который делает соответствующий запрос к вашему кластеру Elasticsearch и по-прежнему возвращает результаты из таблицы. Вы по-прежнему можете использовать объединения, процедуры и все остальное, что предоставляет Postgres. Мы получаем строго типизированную гибкую схему в реляционной базе данных, поддерживаемую молниеносно быстрым индексом Elasticsearch.
Первоначально опубликовано на https://www.help.com.
