Представление разреженных данных в PostgreSQL

Каков наилучший способ представления разреженной матрицы данных в PostgreSQL? Я вижу два очевидных метода:

  1. Храните данные в одной таблице с отдельным столбцом для каждой мыслимой функции (потенциально миллионы), но со значением по умолчанию для NULL для неиспользуемых функций. Это концептуально очень просто, но я знаю, что с большинством реализаций RDMS это обычно очень неэффективно, поскольку значения NULL обычно занимают некоторое пространство. Тем не менее, я прочитал статью (не могу найти ссылку, к сожалению), которая утверждала, что PG не принимает данные для значений NULL, что делает ее более подходящей для хранения разреженных данных.

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

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

Есть ли другие решения? Какой из них я должен использовать?

    Несколько решений приходят в голову,

    1) Разделите свои функции на группы, которые обычно устанавливаются вместе, создайте таблицу для каждой группы с отношением «один к одному» внешнего ключа к основным данным, присоединитесь только к таблицам, которые вам нужны при запросе

    2) Используйте анти-шаблон EAV, создайте таблицу «feature» с полем внешнего ключа из вашей основной таблицы, а также имя поля и столбец значений и сохраните функции как строки в этой таблице, а не как атрибуты в основной Таблица

    3) Аналогично тому, как это делает PostgreDynamic, создайте таблицу для каждого столбца в своей основной таблице (они используют отдельное пространство имен для этих таблиц) и создают функции для упрощения (а также эффективного индекса) доступа и обновления данных в эти таблицы

    4) создайте столбец в своих первичных данных с помощью XML или VARCHAR и сохраните в нем структурированный текстовый формат, представляющий ваши данные, создайте индексы над данными с функциональными индексами, напишите функции для обновления данных (или используйте функции XML, если вы используют этот формат)

    5) используйте модуль contrib / hstore для создания столбца типа hstore, который может содержать пары ключ-значение и может быть проиндексирован и обновлен

    6) живут с большим количеством пустых полей

    Я предполагаю, что вы думаете о разреженных матрицах из математического контекста: http://en.wikipedia.org/wiki/Sparse_matrix (Методы хранения, описанные для хранения данных (быстрая арифметическая операция), а не постоянное хранилище (низкий диск Применение).)

    Поскольку, как правило, они работают на этих матрицах на стороне клиента, а не на стороне сервера, SQL-ARRAY [] – лучший выбор!

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

    Настроить:

    • Postgres 8.4
    • Матрицы с 400 * 400 элементами с двойной точностью (8 байт) -> 1,28MiB размер исходного материала на матрицу
    • 33% ненулевых элементов -> 427kiB эффективный размер на матрицу
    • усредненный с использованием ~ 1000 различных случайных заполненных матриц

    Конкурирующие методы:

    • Положитесь на сжатие столбцов с автоматическим сервером с помощью SET STORAGE MAIN или EXTENDED.
    • Сохраняйте ненулевые элементы плюс битмап ( bit varying(xx) ), описывающий, где найти ненулевые элементы в матрице. (Одна двойная точность в 64 раза больше одного бита. Теоретически (игнорируя служебные данные) этот метод должен быть улучшен, если <= 98% отличны от нуля ;-).) Сжатие на стороне сервера активируется.
    • Замените нули в матрице на NULL . (СУРБД очень эффективны при хранении NULL.) Сжатие на стороне сервера активируется.

    (Индексирование ненулевых элементов с использованием 2-го индекса-ARRAY [] не очень перспективно и поэтому не проверено).

    Результаты:

    • Автоматическое сжатие
      • никаких дополнительных усилий по внедрению
      • нет сокращенного сетевого трафика
      • минимальные накладные расходы
      • постоянное хранилище = 39% от необработанного размера
    • Битовая карта
      • приемлемые усилия по осуществлению
      • сетевой трафик несколько снизился; зависит от разреженности
      • постоянное хранение = 33,9% от необработанного размера
    • Замените нули нулями
      • некоторые попытки внедрения (API должен знать, где и как установить NULL в ARRAY [] при построении запроса INSERT)
      • никаких изменений в сетевом трафике
      • постоянное хранилище = 35% от необработанного размера

    Вывод: начните с параметра EXTENDED / MAIN. Если у вас есть свободное время, исследуйте свои данные и используйте мою тестовую установку с вашим уровнем разреженности. Но эффект может быть ниже, чем вы ожидаете.

    Я предлагаю всегда использовать сериализацию матрицы (например, строчный порядок) и два целых столбца для матричных размеров NxM. Поскольку большинство API-интерфейсов используют текстовый SQL, вы сохраняете много сетевого трафика и клиентской памяти для вложенных «ARRAY [ARRAY [..], ARRAY [..], ARRAY [..], ARRAY [..], ..]" !!!

    Tebas


     CREATE TABLE _testschema.matrix_dense ( matdata double precision[] ); ALTER TABLE _testschema.matrix_dense ALTER COLUMN matdata SET STORAGE EXTERN; CREATE TABLE _testschema.matrix_sparse_autocompressed ( matdata double precision[] ); CREATE TABLE _testschema.matrix_sparse_bitmap ( matdata double precision[] bitmap bit varying(8000000) ); 

    Вставьте те же матрицы во все таблицы. Конкретные данные зависят от определенной таблицы. Не изменяйте данные на стороне сервера из-за неиспользуемых, но выделенных страниц. Или сделай ВАКУУМ.

     SELECT pg_total_relation_size('_testschema.matrix_dense') AS dense, pg_total_relation_size('_testschema.matrix_sparse_autocompressed') AS autocompressed, pg_total_relation_size('_testschema.matrix_sparse_bitmap') AS bitmap; 

    Значение NULL не будет занимать места, когда оно равно NULL. Это займет один бит в растровом виде в заголовке кортежа, но это будет независимо.

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

    Если вам действительно нужно, что многие, в одной таблице, вам нужно пройти метод EAV, который в основном вы говорите в (2).

    Если в каждой записи имеется только относительно небольшое количество клавиш, я предлагаю вам посмотреть модули «hstore» contrib, которые позволят вам хранить этот тип данных очень эффективно, в качестве третьего варианта. В будущей версии 9.0 она улучшилась, поэтому, если вы немного отстаете от производственного развертывания, вы можете посмотреть прямо на нее. Тем не менее, это стоит того и в 8.4. И он поддерживает некоторые довольно эффективные индексированные поисковые запросы. Определенно стоит посмотреть.

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