Запрос по всем схемам для идентичной таблицы в Postgres

Я использую postgres и у меня есть несколько схем с одинаковыми таблицами, в которые они динамически добавляются кодом приложения.

foo, bar, baz, abc, xyz, ...,

Я хочу иметь возможность запрашивать все схемы, как будто они являются одной таблицей

!!! Я не хочу запрашивать все схемы по одной и объединять результаты. Я хочу "объединить" (не уверен, что это можно считать огромным соединением) таблицы по всем схемам, а затем выполнить запрос.

Например, запрос order by не должен иметь вид

 1. schema_A.result_1
 2. schema_A.result_3
 3. schema_B.result_2
 4. schema_B.result 4

но вместо этого должно быть 

 1. schema_A.result_1
 2. schema_B.result_2
 3. schema_A.result_3
 4. schema_B.result 4

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

SELECT schema_A.table_X.field_1, schema_B.table_X.field_1 FROM schema_A.table_X, schema_B.table_X

Но я хочу, чтобы об этом позаботились в postgresql, в базе данных.

Генерирование запроса с добавлением всех схем (пространств имен) может сделать мои запросы огромными с ~50 полями и ~50 схемами.

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

Я бы также хотел узнать, действительно ли это невозможно с разумной скоростью.

EXTRA:

Я использую django и django-tenants, поэтому я также приму любой ответ, который действительно поможет мне сгенерировать весь запрос и запустить его для получения глобального набора запросов, даже если это будет очень медленно.

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

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

Я призываю вас покаяться сейчас, чем раньше, тем лучше.

После этой гигантской оговорки, основанной на надменной моральной позиции, я понял, что единственная причина, по которой мы здесь отвечаем на вопросы, - это получение воображаемых очков в интернете. Поэтому мой ответ таков: используйте представление, которое объединяет все значения вместе и представляет их так, как будто они взяты из одной таблицы. Я не могу понять смысл "порядка по запросу", поэтому пока просто игнорирую его. Возможно, вы имеете в виду, что хотите получить результаты в определенном порядке; если это так, вы можете добавить константы к каждому операнду SELECT каждого UNION ALL и ORDER BY того постоянного столбца, который выходит из объединения. Но если порядок строк имеет значение, я бы утверждал, что вы демонстрируете еще один симптом плохого проектирования базы данных.

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

Рабочий пример здесь: http://sqlfiddle.com/#!17/c09265/1

с этим кодом создания и заселения схемы:

CREATE Schema Fooey;
CREATE SCHEMA Junk;

CREATE TABLE Fooey.Baz (SomeINteger INT);
CREATE TABLE Junk.Baz (SomeINteger INT);

INSERT INTO Fooey.Baz (SomeInteger) VALUES (17), (34), (51);
INSERT INTO Junk.Baz (SomeInteger) VALUES (13), (26), (39);

CREATE VIEW AllOfThem AS
SELECT 'FromFooey' AS SourceSchema, SomeINteger FROM Fooey.Baz
UNION ALL 
SELECT 'FromJunk' AS SourceSchema, SomeInteger FROM Junk.Baz;

и этот запрос:

  SELECT *
    FROM AllOfThem
ORDER BY SourceSchema;

Почему схемы для каждого арендатора - это плохой дизайн?

Этот дизайн отдает предпочтение лени, а не масштабируемости. Если вы не хотите вносить изменения в свое приложение, вы можете просто захлопнуть соединения с определенной схемой и продолжать работать без каких-либо изменений кода. Добавление большего числа арендаторов означает добавление большего числа схем, что, похоже, вы автоматизировали. Добавление большого количества схем в конечном итоге сделает управление базой данных громоздким (что если у вас тысячи или миллионы арендаторов?), и даже если у вас их всего несколько, динамическая природа списка и проблемы с написанием общесистемных запросов - это проблема, которую вы уже обнаружили.

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

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

В README пакета django-tenants, который вы используете, описывается решение пойти на компромисс в сторону лени, и цитируется whitpaper, в котором описываются многие недостатки и альтернативы этого метода.

Вернуться на верх