Читая про EAV натолкнулся на такую штуку, как Sparse columns (в MS SQL Server). С ними можно создать одну гигантскую таблицу (гигантскую как вширь, так и вглубь) с полями Int1, Int2 .. Int1000, Float1, Float2 .. Float1000, NVarchar255_1, NVarchar255_2 .. NVarchar255_1000. При этом колонки с суффиксами больше 3 можно пометить как SPARSE и NULL'ы в них перестанут жрать место за счет небольшого оверхеда для non-NULL'ов. В эту таблицу можно упихать все сотни тысяч таблиц, а маппинг вынести в отдельную таблицу.
Первый вопрос: а как эта же штука поддерживается в других СУБД? Oracle, PostgreSQL, Maria. Допустим, ARRAY из PostgreSQL. Это аналог? Можно ли его использовать в рамках описанной задачи?
Второй вопрос: какие подводные камни с перфомансом следует ожидать при таком подходе?
Третий вопрос: что делать с индексами? Хотелось бы в каждой из мелких таблиц дать возможность выбирать колонку для индексирования, но при таком подходе в индекс рано или поздно попадут все колонки.
Четвертый вопрос: кроме EAV и Sparse columns есть еще варианты?
Re: Sparse columns — поддержка в СУБД и другие вопросы
Здравствуйте, Аноним, Вы писали:
А> Задача такая: надо как-то хранить много (сотни тысяч) таблиц, структуры которых заранее неизвестны.
А> Навели на такую технику: EAV (http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model). Как я понял, надо городить отдельную запись под каждое значение. Что-то оно мне не нравится, запросы все усложняются на ровном месте.
А> Читая про EAV натолкнулся на такую штуку, как Sparse columns (в MS SQL Server). С ними можно создать одну гигантскую таблицу (гигантскую как вширь, так и вглубь) с полями Int1, Int2 .. Int1000, Float1, Float2 .. Float1000, NVarchar255_1, NVarchar255_2 .. NVarchar255_1000. При этом колонки с суффиксами больше 3 можно пометить как SPARSE и NULL'ы в них перестанут жрать место за счет небольшого оверхеда для non-NULL'ов. В эту таблицу можно упихать все сотни тысяч таблиц, а маппинг вынести в отдельную таблицу.
А> Первый вопрос: а как эта же штука поддерживается в других СУБД? Oracle, PostgreSQL, Maria.
В Oracle, например, NULL и так места почти не жрет.
Допустим, ARRAY из PostgreSQL. Это аналог? Можно ли его использовать в рамках описанной задачи?
А> Второй вопрос: какие подводные камни с перфомансом следует ожидать при таком подходе?
Самые разные и многочисленные. Надо понимать, что хороший перфоманс в любой СУБД возможен именно за счет хорошего знания структуры данных. Чем лучше СУБД "знает" структуру, тем лучший перфоманс возможен.
А> Третий вопрос: что делать с индексами? Хотелось бы в каждой из мелких таблиц дать возможность выбирать колонку для индексирования, но при таком подходе в индекс рано или поздно попадут все колонки.
Для EAV кроме стандартных индексов особо ничего не сделаешь. Для способа "одна гигантская таблица + маппинг" для sparse колонок можно применять особые типы индексов (вроде bitmap). Но нужно быть уверенным, что эти колонки в будущем не перестанут быть sparse.
А вообще все зависит от задачи и паттернов доступа к данным. Без этого предметного разговора не получится.
А> Четвертый вопрос: кроме EAV и Sparse columns есть еще варианты?
— XML или JSON колонки и спец. индексы по ним
— дополнительный слой метаданных (генерация таблиц в тот момент, когда структура становится известна)
— NOSQL БД
Здравствуйте, wildwind, Вы писали:
W>А вообще все зависит от задачи и паттернов доступа к данным. Без этого предметного разговора не получится.
Это что-то типа онлайнового Экселя, только колонки типизированы (количество и тип колонок задаются юзером). У каждого юзера юзерское (то есть, небольшое) число документов-таблиц произвольной схемы в распоряжении, документы могут ссылаться друг на друга. Юзерские данные сильно изолированы, но допускается расшаривание документов, небольшое число документов будет расшарено для всех.
А>> Четвертый вопрос: кроме EAV и Sparse columns есть еще варианты?
W>- XML или JSON колонки и спец. индексы по ним
Разве это не самое медленное, что только можно представить?
W>- дополнительный слой метаданных (генерация таблиц в тот момент, когда структура становится известна)
Да, об этом я задумывался, но хорошо ли это — тысячи и тысячи таблиц в одной БД? Да еще и постоянное изменение структуры — юзер колонки может добавлять, менять и удалять. Не быстрее ли будет напрягать ровно одну таблицу, а структуру вовсе не менять?
W>- NOSQL БД
Здравствуйте, Аноним, Вы писали:
А> W>А вообще все зависит от задачи и паттернов доступа к данным. Без этого предметного разговора не получится.
А> Это что-то типа онлайнового Экселя, только колонки типизированы (количество и тип колонок задаются юзером). У каждого юзера юзерское (то есть, небольшое) число документов-таблиц произвольной схемы в распоряжении, документы могут ссылаться друг на друга. Юзерские данные сильно изолированы, но допускается расшаривание документов, небольшое число документов будет расшарено для всех.
OK, но все-таки какие операции будут выполняться над этими данными? Фильтры? Pivot table? Отчеты (какие)? И какие ожидаемые объемы данных?
А> W>- XML или JSON колонки и спец. индексы по ним А> Разве это не самое медленное, что только можно представить?
Как всегда, it depends.
А> W>- дополнительный слой метаданных (генерация таблиц в тот момент, когда структура становится известна)
А> Да, об этом я задумывался, но хорошо ли это — тысячи и тысячи таблиц в одной БД?
Ни хорошо, ни плохо. Тысячи таблиц это не много, вот десятки-сотни тысяч — уже могут быть проблемы. Но они решаются шардингом.
Да еще и постоянное изменение структуры — юзер колонки может добавлять, менять и удалять. Не быстрее ли будет напрягать ровно одну таблицу, а структуру вовсе не менять?
Статьи со словом "never" в заголовке следует воспринимать с известной долей скептицизма, и уж точно не как руководство к действию. Здесь, кстати, была критика этой статьи, можно найти. От таких ошибок в выборе технологии не застрахован никто без достаточного количества опыта. Конечно, если известно, что по данным придется гонять тяжелые реляционные запросы, выбирать NoSQL не стоит.
А в твоем случае все не так однозначно. Если, как и в Excel, данных в одном пользовательском документе немного, то обрабатывать их наверное лучше целиком в памяти. В этом случае NoSQL хранилище подойдет хорошо, таблицы можно представить как коллекции строк.
Если всю обработку возложить на БД, ее будет тяжело масштабировать. Реляционные БД, да еще с разнородной нагрузкой, масштабируются плохо. А точнее — дорого, намного дороже, чем middle tier.
Здравствуйте, wildwind, Вы писали:
А>> Это что-то типа онлайнового Экселя, только колонки типизированы (количество и тип колонок задаются юзером). У каждого юзера юзерское (то есть, небольшое) число документов-таблиц произвольной схемы в распоряжении, документы могут ссылаться друг на друга. Юзерские данные сильно изолированы, но допускается расшаривание документов, небольшое число документов будет расшарено для всех.
W>OK, но все-таки какие операции будут выполняться над этими данными? Фильтры? Pivot table? Отчеты (какие)? И какие ожидаемые объемы данных?
Фильтры — да. Еще агрегирующие функции (что есть — через встроенные операторы типа COUNT и SUM, чего нет — через хранимки). Отчеты — да, наверное, в будущем, юзерские, модулями. Объем данных — ну, на одно юзерское лицо — типичный юзерский, сотни и тысячи строк, таблиц с десяток. Сколько юзеров — очень много, так как предполагается миграция имеющейся юзерской базы.
А>> W>- дополнительный слой метаданных (генерация таблиц в тот момент, когда структура становится известна) А>> Да, об этом я задумывался, но хорошо ли это — тысячи и тысячи таблиц в одной БД?
W>Ни хорошо, ни плохо. Тысячи таблиц это не много, вот десятки-сотни тысяч — уже могут быть проблемы. Но они решаются шардингом.
Что такое шардинг?
W>Да еще и постоянное изменение структуры — юзер колонки может добавлять, менять и удалять. Не быстрее ли будет напрягать ровно одну таблицу, а структуру вовсе не менять? W>Если юзеров тысячи — может быть и не быстрее. Но вообще изменение структуры — операция, не критичная по времени.
А>> W>- NOSQL БД
А>> Тоже думал. Но что-то вот эта заметка заставила задуматься: http://www.sarahmei.com/blog/2013/11/11/why-you-should-never-use-mongodb/
W>Статьи со словом "never" в заголовке следует воспринимать с известной долей скептицизма, и уж точно не как руководство к действию. Здесь, кстати, была критика этой статьи, можно найти. От таких ошибок в выборе технологии не застрахован никто без достаточного количества опыта. Конечно, если известно, что по данным придется гонять тяжелые реляционные запросы, выбирать NoSQL не стоит.
Статья, как я понял, постулирует, что когда вам кажется, что по данным не придется гонять запросы, вам кажется неправильно. Ибо в этом нет практического смысла.
W>А в твоем случае все не так однозначно. Если, как и в Excel, данных в одном пользовательском документе немного, то обрабатывать их наверное лучше целиком в памяти. В этом случае NoSQL хранилище подойдет хорошо, таблицы можно представить как коллекции строк.
W>Если всю обработку возложить на БД, ее будет тяжело масштабировать. Реляционные БД, да еще с разнородной нагрузкой, масштабируются плохо. А точнее — дорого, намного дороже, чем middle tier.
В принципе, потом можно и переписать. Главное, чтоб до 100К юзеров дожить без проблем и легко докидывать функционал.
Re[3]: Sparse columns — поддержка в СУБД и другие вопросы
Здравствуйте, Аноним, Вы писали:
А> Что такое шардинг?
Это способ горизонтального масштабирования. https://en.wikipedia.org/wiki/Shard_%28database_architecture%29
А> Статья, как я понял, постулирует, что когда вам кажется, что по данным не придется гонять запросы, вам кажется неправильно. Ибо в этом нет практического смысла.
Все дело в том, какие именно запросы. Если такие, которые NoSQL БД может выполнять эффективно, то выбор можно делать по другим критериям. А в случае, описанном в статье, разработчики при проектировании не увидели запросы, которые выбранная ими СУБД эффективно выполнять не может.
А> W>Если всю обработку возложить на БД, ее будет тяжело масштабировать. Реляционные БД, да еще с разнородной нагрузкой, масштабируются плохо. А точнее — дорого, намного дороже, чем middle tier.
А> В принципе, потом можно и переписать. Главное, чтоб до 100К юзеров дожить без проблем и легко докидывать функционал.
Обычно "переписать потом" выходит намного дороже.
Я вижу еще пару проблемных моментов. Если функции агрегирования в формулах выполнять не в памяти, а в БД, то не удастся обеспечить приемлемое время реакции, сравнимое с обычным Экселем (или хотя бы с Google Docs). А Эксель с замедленной реакцией пользователям окажется просто не нужен, хоть и облачный.
Кроме того, придется решать многочисленные проблемы с согласованностью данных. Особенно если могут быть ссылки на данные из других, чужих документов.
Здравствуйте, wildwind, Вы писали:
W>Я вижу еще пару проблемных моментов. Если функции агрегирования в формулах выполнять не в памяти, а в БД, то не удастся обеспечить приемлемое время реакции, сравнимое с обычным Экселем (или хотя бы с Google Docs). А Эксель с замедленной реакцией пользователям окажется просто не нужен, хоть и облачный. W>Кроме того, придется решать многочисленные проблемы с согласованностью данных. Особенно если могут быть ссылки на данные из других, чужих документов.
Не совсем понял этот момент. В памяти это как — SELECT a.Int с последующим foreach'ем по датасету? Если не трудно, объясните, пожалуйста, что с чем сопоставляется. Спасибо.
Re[7]: Sparse columns — поддержка в СУБД и другие вопросы
Здравствуйте, Аноним, Вы писали:
А> Не совсем понял этот момент. В памяти это как — SELECT a.Int с последующим foreach'ем по датасету? Если не трудно, объясните, пожалуйста, что с чем сопоставляется. Спасибо.
В памяти — это значит при вычислении формул не делается запросов к БД. Все данные находятся в памяти. Возможно даже на клиенте.
