Стратегии реализации столбчатого сжатия в больших базах данных PostgreSQL

Масштабирование базы данных Postgres — это обряд для растущих приложений. Когда вы видите, что ваши таблицы расширяются за счет миллионов или даже миллиардов строк, ваши некогда быстрые запросы начинают тормозить, а растущие затраты на инфраструктуру начинают отбрасывать длинную тень на вашу прибыль. Вы попали в загадку: вы не хотите расставаться со своим любимым PostgreSQL, но, похоже, вам понадобится более эффективный способ работы с растущими наборами данных.

В этой статье мы расскажем вам историю о том, как мы создали гибкий и высокопроизводительный механизм столбчатого сжатия для PostgreSQL, чтобы улучшить его масштабируемость. Объединив столбчатое хранилище со специализированными алгоритмами сжатия, мы можем достичь впечатляющих показателей сжатия, не имеющих аналогов в любой другой реляционной базе данных (+95 %).

Сжимая набор данных, вы можете расширить свои базы данных PostgreSQL. Как мы увидим в этой статье, эта высокоэффективная конструкция сжатия позволяет уменьшить размер больших таблиц PostgreSQL в 10-20 раз. Вы можете хранить гораздо больше данных на дисках меньшего размера (т. е. экономя деньги), одновременно улучшая производительность запросов. Сжатие временной шкалы также полностью изменчиво, что упрощает управление базой данных и операции: вы можете добавлять, изменять и удалять столбцы в сжатых таблицах, а также напрямую ВСТАВИТЬ, ОБНОВИТЬ и УДАЛИТЬ данные.

Добро пожаловать в более масштабируемую версию PostgreSQL!

Обзор контента

• Почему PostgreSQL необходимо сжатие базы данных
• А как насчет ТОСТА?
• Почему сжатие не является встроенным в PostgreSQL? Введение в базы данных, ориентированные на строки и столбцы
• Базы данных, ориентированные на строки или столбцы: что выбрать?
• Создание столбчатого хранилища в строковой базе данных
• Превращение PostgreSQL в гибридное хранилище строк и столбцов
• За кулисами: от строковых данных к сжатым столбчатым массивам
• Эффективный запрос сжатых данных
• Группировка часто запрашиваемых данных по segmentby столбцам
• Определение столбцов segmentby
• Расширенная точная настройка через orderby
• Эволюция сжатия шкалы времени
• Конечный результат: более быстрые запросы, меньше места для хранения больших баз данных PostgreSQL
• Сжатие и многоуровневое хранилище: жизненный цикл хранилища с временной шкалой
• Оставайтесь с PostgreSQL

Почему PostgreSQL необходимо сжатие базы данных

Но прежде чем вдаваться в подробности того, как мы построили сжатие, давайте потратим пару минут на ответ на вопрос: зачем вообще добавлять в PostgreSQL новый механизм сжатия базы данных?

Давайте сначала разберемся в потребностях современных приложений и немного истории программного обеспечения.

Мы любим Postgres: мы считаем, что это лучшая основа для создания приложений, поскольку сочетание надежности, гибкости и богатой экосистемы с ним очень трудно сравнить с любой другой базой данных. Но Postgres родился несколько десятилетий назад, и эта надежность не лишена недостатков.

Сегодня разработчики используют PostgreSQL для гораздо большего, чем традиционный вариант использования OLTP (онлайн-обработка транзакций), которым он наиболее известен. Многие ресурсоемкие и ресурсоемкие приложения, работающие круглосуточно и обрабатывающие постоянно растущие объемы данных, работают на базе PostgreSQL:

• Базы данных PostgreSQL используются для приема огромных объемов потоковых данных датчиков из систем управления дорожным движением, коммунальных сетей и мониторов общественной безопасности.

  
  

• Энергетические компании используют PostgreSQL для хранения и анализа показателей интеллектуальных сетей и возобновляемых источников энергии.

  
  

• В финансовом секторе PostgreSQL лежит в основе систем, отслеживающих рыночные данные в режиме реального времени.

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

  
  

• Postgres даже используется в качестве векторной базы данных для создания новой волны приложений искусственного интеллекта.

  
  

В результате таблицы Postgres растут очень быстро, а количество строк в таблицах достигает миллиардов — это новая норма в производстве.

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

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

А как насчет ТОСТА?

А как насчет существующего метода TOAST в PostgreSQL? Несмотря на удивительное название 🍞😋, TOAST неэффективен для систематического уменьшения размера больших баз данных PostgreSQL. .

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

Например, если у вас есть база данных размером 1 ТБ, состоящая из небольших кортежей, TOAST не поможет вам систематически превращать этот 1 ТБ в 80 ГБ, независимо от того, насколько тонкую настройку вы пытаетесь выполнить. TOAST автоматически сжимает атрибуты слишком большого размера подряд, если они превышают пороговое значение в 2 КБ, но TOAST не помогает для небольших значений (кортежей), и вы не можете применять более сложные настраиваемые пользователем конфигурации, такие как сжатие всех данных старше одного месяца. в конкретной таблице. Сжатие TOAST строго основано на размере отдельных значений столбца, а не на более широких характеристиках таблицы или набора данных.

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

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

Почему сжатие не является встроенным в PostgreSQL? Введение в базы данных, ориентированные на строки и столбцы

Это краткое упоминание о TOAST также помогает нам понять ограничения PostgreSQL в эффективном сжатии данных. Как мы только что видели, сжатие TOAST обрабатывает данные построчно, но эта построчно-ориентированная архитектура рассеивает однородность, на которой преуспевают алгоритмы сжатия, что приводит к фундаментальному потолку того, насколько оперативным может быть сжатие. Это фундаментальная причина, по которой реляционные базы данных (например, собственный Postgres) часто не справляются с оптимизацией хранилища.

Давайте разберем это. Традиционно базы данных делятся на две категории:

• Базы данных, ориентированные на строки, организуют данные по строкам, при этом каждая строка содержит все данные для определенной записи. Они оптимизированы для обработки транзакций, где вставки, обновления и удаления записей происходят часто, и эффективны для OLTP-систем, где операции включают отдельные записи или небольшие подмножества данных (например, получение всей информации о конкретном клиенте).

  
  

• С другой стороны , базы данных, ориентированные на столбцы (также известные как «столбцы») , организуют данные по столбцам. В каждом столбце хранятся все данные для определенного атрибута в нескольких записях. Обычно они оптимизированы для систем OLAP (онлайн-аналитическая обработка), где запросы часто включают в себя агрегирование и операции над многими записями.

  
  

Проиллюстрируем это примером. Допустим, у нас есть таблица users с четырьмя столбцами: user_id , name , logins и last_login . Если в этой таблице хранятся данные для одного миллиона пользователей, она фактически будет иметь один миллион строк и четыре столбца, физически сохраняя данные каждого пользователя (т. е. каждую строку) на диске последовательно.

В этой настройке, ориентированной на строки, вся строка для user_id = 500 000 хранится непрерывно, что ускоряет извлечение. В результате мелкие и широкие запросы будут выполняться быстрее в хранилище строк (например, «получить все данные для пользователя X»):

 -- Create table CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name VARCHAR(100), logins INT DEFAULT 0, last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Assume we have inserted 1M user records into the 'users' table -- Shallow-and-wide query example (faster in row store) SELECT * FROM users WHERE user_id = 500000;

Напротив, столбчатое хранилище будет хранить все user_id вместе, все имена вместе, все значения входа в систему и т. д., так что данные каждого столбца хранятся на диске последовательно. Эта архитектура базы данных предпочитает глубокие и узкие запросы, например, «вычислить среднее количество входов в систему для всех пользователей»:

 -- Deep-and-narrow query example (faster in column store) SELECT AVG(logins) FROM users;

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

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

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

Но даже если они демонстрируют преимущества в запросах в стиле OLAP и сжимаемости, столбчатые хранилища не лишены недостатков:

• Запросы, извлекающие отдельные строки, гораздо менее производительны (иногда их даже невозможно выполнить).

  
  

• Их архитектура не так хорошо подходит для традиционных транзакций ACID.

  
  

• В колоночных магазинах часто невозможно сделать обновления.

  
  

• Рядным магазинам проще воспользоваться преимуществом индекс (например, B-дерево), чтобы быстро найти нужные записи.

  
  

• Благодаря хранилищу строк проще нормализовать набор данных, что позволяет более эффективно хранить связанные наборы данных в других таблицах.

Базы данных, ориентированные на строки или столбцы: что выбрать?

Итак, что лучше: строковое или столбчатое?

Традиционно вы оцениваете компромиссы между обоими вариантами в зависимости от вашей рабочей нагрузки. Если бы вы использовали типичный вариант использования OLTP, вы, вероятно, выбрали бы реляционную базу данных, ориентированную на строки, например PostgreSQL; если бы ваш вариант использования явно был OLAP, вы могли бы склониться к колоночному хранилищу, такому как ClickHouse.

Но что, если ваша рабочая нагрузка на самом деле представляет собой смесь того и другого?

• Запросы вашего приложения, как правило, могут быть мелкими и широкими, при этом отдельный запрос обращается ко многим столбцам данных, а также к данным на множестве различных устройств/серверов/элементов. Например, вы можете использовать визуализацию, ориентированную на пользователя, которая требует отображения последней зарегистрированной температуры и влажности для всех датчиков на конкретном производственном предприятии. Такому запросу потребуется доступ к нескольким столбцам во всех строках, которые соответствуют критериям построения, потенциально охватывая тысячи или миллионы записей.

  
  

• Но некоторые из ваших запросов также могут быть глубокими и узкими: отдельный запрос выбирает меньшее количество столбцов для конкретного датчика за более длительный период. Например, вам также может потребоваться проанализировать температурный тренд для конкретного устройства за последний месяц, чтобы выявить аномалии. Запрос этого типа будет сосредоточен на одном столбце (температура), но потребуется получить эту информацию из большого количества строк, соответствующих каждому временному интервалу в течение целевого периода.

  
  

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

  
  

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

  
  

Эта рабочая нагрузка не является ни OLTP, ни OLAP в традиционном понимании. Вместо этого он включает в себя элементы того и другого. Так что делать?

Перейти на гибрид!

Создание столбчатого хранилища в строковой базе данных

Чтобы обслуживать рабочую нагрузку, подобную предыдущему примеру, одна база данных должна включать следующее:

• Способность поддерживать высокую скорость вставки, легко достигающую сотен тысяч операций записи в секунду.

  
  

• Поддержка вставки просроченных или неупорядоченных данных, а также изменения существующих данных.

  
  

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

  
  

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

  
  

Это то, чего мы стремились достичь, добавляя столбцовое сжатие в TimescaleDB (и, следовательно, в PostgreSQL).

Превращение PostgreSQL в гибридное хранилище строк и столбцов

Как мы упоминали в предыдущем разделе, мы создали TimescaleDB, чтобы расширить PostgreSQL, повысить производительность и масштабируемость, что делает его пригодным для таких ресурсоемких рабочих нагрузок, как данные временных рядов. TimescaleDB реализован как расширение PostgreSQL: при этом он унаследовал все преимущества PostgreSQL, такие как полный SQL, огромную гибкость запросов и моделей данных, проверенную в боевых условиях надежность, ярую базу разработчиков и пользователей, а также одну из крупнейших экосистем баз данных. вокруг.

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

Два наблюдения. Первый, большинство крупных рабочих нагрузок PostgreSQL имеют структуру, подобную временным рядам. , то есть они содержат большое количество добавлений (а не обновлений) со слабо последовательным основным ключом, например меткой времени или идентификатором последовательного события. Во-вторых, такие наборы данных регулярно запрашиваются посредством сканирования или объединения, а не только точечных запросов. Учитывая эти наблюдения, мы разработали новую функцию столбчатого хранения для TimescaleDB (о которой мы подробно расскажем в следующем разделе), которая позволяет нам достичь беспрецедентного уровня сжимаемости.

Фактически, это преобразование строк в столбцы не обязательно применять ко всей базе данных. Как пользователь Timescale, вы можете преобразовать свои таблицы PostgreSQL в гибридные хранилища строк и столбцов, выбирая, какие именно данные сжимать в столбчатой форме. через наш простой API и использовать обе архитектуры хранения в соответствии с требованиями вашего приложения.

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

Чтобы эффективно получить доступ к самым последним данным о температуре, особенно для рабочих запросов, когда вам может потребоваться проанализировать последние показания с разных устройств, вы можете хранить самые последние данные (например, за последнюю неделю) в традиционной несжатой, ориентированной на строки структуре PostgreSQL. . Это поддерживает высокую скорость приема, а также отлично подходит для точечных запросов о последних данных:

 -- Find the most recent data from a specific device SELECT * FROM temperature_data WHERE device_id = 'A' ORDER BY timestamp DESC LIMIT 1; -- Find all devices in the past hour that are above a temperature threshold SELECT DISTINCT device_id, MAX(temperature) FROM temperature WHERE timestamp > NOW() - INTERVAL '1 hour'   AND temperature > 40.0;

Но как только этим данным исполнится несколько дней, мелкие и широкие запросы, подобные предыдущему, больше не выполняются часто: вместо этого более распространены глубокие и узкие аналитические запросы. Таким образом, чтобы повысить эффективность хранения и производительность запросов этого типа, вы можете автоматически преобразовать все данные старше одной недели в столбчатый формат с высокой степенью сжатия. Чтобы сделать это в Timescale, вы должны определить политику сжатия, например:

 -- Add a compression policy to compress temperature data older than 1 week SELECT add_compression_policy('temperature_data', INTERVAL '7 days');

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

 -- Find daily max temperature for a specific device across past year SELECT time_bucket('1 day', timestamp) AS day, MAX(temperature) FROM temperature_data WHERE timestamp > NOW() - INTERVAL '1 year'   AND device_id = 'A' ORDER BY day; -- Find monthly average temperatures across all devices SELECT device_id, time_bucket('1 month', timestamp) AS month, AVG(temperature) FROM temperature_data WHERE timestamp < NOW() - INTERVAL '2 weeks' GROUP BY device_id, month ORDER BY month;

Как мы можем представить «переход» от формата строки к формату столбца? Гипертаблицы Timescale служат для разделения данных на «куски» на основе ключа разделения, такого как метка времени или другой столбец серийного идентификатора. Затем каждый фрагмент сохраняет записи, соответствующие определенному диапазону временных меток или других значений для этого ключа разделения. В приведенном выше примере данные о температуре будут разбиты по неделям, так что последний фрагмент останется в формате строки, а все предыдущие недели будут преобразованы в формат столбца.

Этот гибридный механизм хранения строк и столбцов представляет собой невероятно мощный инструмент для оптимизации производительности запросов в больших базах данных PostgreSQL, при этом значительно сокращая объем хранилища. Как мы увидим далее в этой статье, преобразуя данные в столбчатый формат и применяя специализированные механизмы сжатия, мы не только можем ускорить ваши аналитические запросы, но также достичь степени сжатия до 98 %. Представьте себе, как это повлияет на ваш счет за хранение!

За кулисами: от строковых данных к сжатым столбчатым массивам

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

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

Для иллюстрации представим себе такую таблицу:

 | Timestamp | Device ID | Status Code | Temperature | |-----------|-----------|-------------|-------------| | 12:00:01 | A | 0 | 70.11 | | 12:00:01 | B | 0 | 69.70 | | 12:00:02 | A | 0 | 70.12 | | 12:00:02 | B | 0 | 69.69 | | 12:00:03 | A | 0 | 70.14 | | 12:00:03 | B | 4 | 69.70 |

Чтобы подготовить эти данные к сжатию, Timescale сначала преобразует эти табличные данные в столбчатое хранилище. Учитывая пакет данных (около 1000 строк), данные каждого столбца объединяются в массив, где каждый элемент массива соответствует значению из одной из исходных строк. В результате получается одна строка, в каждом столбце которой хранится массив значений из этого пакета.

 | Timestamp | Device ID | Status Code | Temperature | |------------------------------|--------------------|--------------------|-------------------------------| | [12:00:01, 12:00:01, 12...] | [A, B, A, B, A, B] | [0, 0, 0, 0, 0, 4] | [70.11, 69.70, 70.12, 69....] |

Даже без применения алгоритмов сжатия этот формат немедленно экономит место, значительно сокращая внутренние накладные расходы Timescale на строку. PostgreSQL обычно добавляет около 27 байт служебных данных на строку (например, для управления многоверсионным параллелизмом или MVCC). Таким образом, даже без какого-либо сжатия, если наша схема выше, скажем, 32 байта, то 1000 строк данных из пакета, который раньше занимал [1000 * (32 + 27)] ~= 59 килобайт, теперь занимают [1000 * 32 + 27] ] ~= 32 килобайта в этом формате.

[ Кроме того : идея «группировки» большой таблицы на более мелкие пакеты с последующим сохранением столбцов каждой партии подряд (а не столбцов всей таблицы) на самом деле аналогична подходу к «группам строк» в формате файлов Apache Parquet. Хотя мы осознали это сходство только постфактум!]

Но большим преимуществом этого преобразования является то, что теперь, учитывая формат, в котором аналогичные данные (метки времени, идентификаторы устройств, показания температуры и т. д.) хранятся последовательно, мы можем применять к ним алгоритмы сжатия для каждого типа, чтобы каждый массив сжимался отдельно. . Именно так Timescale достигает впечатляющих показателей сжатия.

Timescale автоматически использует следующие алгоритмы сжатия. Все эти алгоритмы « без потерь », чтобы мы не теряли точность и не вносили неточностей из-за сжатия; любая результирующая декомпрессия прекрасно восстанавливает исходные значения.

• Горилла сжатия для поплавков

  
  

• Дельта-дельта + Простой-8b с кодирование длин серий сжатие временных меток и других целочисленных типов

  
  

• Сжатие словаря всей строки для столбцов с несколькими повторяющимися значениями (+ LZ-сжатие сверху)

  
  

• Сжатие массива на основе LZ для всех остальных типов

  
  

Мы расширили Gorilla и Simple-8b для обработки распаковки данных в обратном порядке, что позволяет нам ускорить запросы, использующие обратное сканирование.

Мы обнаружили, что это сжатие для конкретного типа является весьма эффективным: в дополнение к более высокой сжимаемости некоторые методы, такие как Gorilla и delta-of-delta, могут быть до 40 раз быстрее, чем сжатие на основе LZ во время декодирования, что приводит к значительному повышению производительности запросов. .

При распаковке данных Timescale может работать с этими отдельными сжатыми пакетами, распаковывая их пакет за пакетом и только с запрошенными столбцами. Таким образом, если механизм запросов может определить, что необходимо обработать только 20 пакетов (соответствующих 20 000 исходным строкам данных) из фрагмента таблицы, который изначально включал один миллион строк данных, тогда запрос может выполняться намного быстрее, поскольку он читает и распаковывает данные. гораздо меньше данных. Давайте посмотрим, как он это делает.

Эффективный запрос сжатых данных

Предыдущий формат на основе массива представляет собой проблему: а именно: какие строки база данных должна извлечь и распаковать для разрешения запроса?

Давайте снова возьмем наш пример с данными о температуре. Снова и снова возникает несколько естественных типов запросов: выбор и упорядочивание данных по временным диапазонам или выбор данных по идентификатору устройства (либо в предложении WHERE, либо через GROUP BY). Как мы можем эффективно поддерживать такие запросы?

Теперь, если нам нужны данные за последний день, запрос должен пройти через данные временных меток, которые теперь являются частью сжатого массива. Так должна ли база данных распаковывать целые фрагменты (или даже всю гипертаблицу), чтобы найти данные за последний день?

Или даже если бы мы могли идентифицировать отдельные «пакеты», которые сгруппированы в сжатый массив (описано выше), данные с разных устройств перемежаются друг с другом, поэтому нам нужно распаковать весь массив, чтобы выяснить, включает ли он данные о конкретном устройстве? Хотя этот более простой подход все же может обеспечить хорошую сжимаемость, он не будет столь же эффективным с точки зрения производительности запросов.

Чтобы решить проблему эффективного поиска и распаковки данных для конкретных запросов в столбчатом формате, Шкала времени представляет концепцию столбцов «сегментировать по» и «упорядочивать по». .

Группировка часто запрашиваемых данных по segmentby столбцам

Напомним, что данные в Timescale изначально преобразуются в сжатую столбчатую форму по частям. Чтобы повысить эффективность запросов, которые фильтруются на основе определенного столбца (например, частые запросы по device_id ), у вас есть возможность определить этот конкретный столбец как « compress_segmentby " столбец. Этот подход очень полезен для организации сжатых данных.

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

Таким образом, 1000 строк данных с идентификатором устройства A плотно резервируются перед сохранением в одной сжатой строке, 1000 строк с идентификатором устройства B и т. д. Таким образом, если в качестве столбца segmentby выбран device_id , каждая сжатая строка включает в себя сжатые столбцовые пакеты данных о конкретном идентификаторе устройства, которые хранятся в этой строке в несжатом виде. Timescale дополнительно создает индекс на основе этих значений сегментов внутри сжатого фрагмента.

 | Device ID | Timestamp | Status Code | Temperature | |-----------|--------------------------------|-------------|-----------------------| | A | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 0] | [70.11, 70.12, 70.14] | | B | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 4] | [69.70, 69.69, 69.70] |

Такое непрерывное хранение данных значительно повышает эффективность запросов, фильтруемых по столбцам segmentby . При выполнении запроса, отфильтрованного по device_id , где device_id — это столбец segmentby , Timescale может быстро выбрать (через индекс) все сжатые строки в фрагменте, которые имеют указанные идентификаторы устройства, и быстро пропускает данные (и избегает распаковки). ) данные, не относящиеся к запрошенным устройствам.

Например, в этом запросе Timescale будет эффективно находить и обрабатывать только те сжатые строки, которые содержат данные для device_id A:

 SELECT AVG(temperature) FROM sensor_data WHERE device_id = 'A'   AND time >= '2023-01-01'   AND time < '2023-02-01';

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

Определение столбцов segmentby

Вы можете указать, какие столбцы использовать для сегментации, при первом включении сжатия гипертаблицы. Выбор столбца для использования должен основываться на том, какой столбец или столбцы часто используются в ваших запросах. Фактически, вы можете использовать несколько столбцов для сегментации: например, вместо того, чтобы группировать пакеты по идентификатору устройства, вы можете (скажем) сгруппировать те пакеты, которые имеют одинаковые идентификаторы tenant_id и device_id.

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

Если вы больше не можете создавать 1000 пакетов данных, а вместо этого имеете только пять записей с указанными сегментными ключами в определенном фрагменте, то сжатие вообще не получится!

Но как только вы определили, по каким столбцам вы хотите сегментировать, их легко настроить при включении сжатия в гипертаблице:

 ALTER TABLE temperature_data SET (   timescaledb.compress,   timescaledb.compress_segmentby = 'device_id' );

Расширенная точная настройка через orderby

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

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

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

Timescale решает эту проблему, по сути, «сортируя» данные, подлежащие пакетированию, в соответствии с их меткой времени. Затем он записывает метаданные о минимальной и максимальной временных метках для каждого пакета. При выполнении запроса эти метаданные позволяют механизму запросов быстро определить, какие сжатые строки (пакеты) соответствуют временному диапазону запроса, тем самым уменьшая необходимость полной распаковки.

Эта методология хорошо сочетается с использованием сегментированных столбцов. В процессе сжатия данные сначала группируются по столбцам «сегментно», затем упорядочиваются на основе параметра «orderby» и, наконец, делятся на более мелкие «мини-пакеты», упорядоченные по меткам времени, каждый из которых содержит до 1000 строк.

Сочетание сегментации и упорядочивания TimescaleDB значительно повышает производительность обычных временных рядов и аналитических запросов. Эта оптимизация как по времени (с помощью orderby ), так и по пространству (с помощью segmentby ) гарантирует, что TimescaleDB эффективно управляет и запрашивает крупномасштабные данные временных рядов, предлагая оптимизированный баланс между сжатием и доступностью.

Эволюция сжатия шкалы времени

Первая версия нашей схемы сжатия была выпущена в 2019 году вместе с TimescaleDB 1.5 . Спустя много релизов сжатие Timescale прошло долгий путь.

Одним из основных ограничений нашей первоначальной версии было то, что мы не допускали никаких дальнейших модификаций данных (например, INSERT, UPDATE, DELETE) после того, как данные были сжаты без предварительного ручного распаковывания всего фрагмента гипертаблицы, в котором они находились.

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

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

Сегодня все эти ограничения сняты. Timescale теперь позволяет выполнять полные операции языка манипулирования данными (DML) и языка определения данных (DDL) над сжатыми данными:

• Вы можете ВСТАВИТЬ данные в сжатые фрагменты (с высокой производительностью).

• Вы можете выполнять UPDATE, UPSERT и DELETE.

  
  

• Вы можете добавлять столбцы, в том числе со значениями по умолчанию.

  
  

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

  
  

Чтобы автоматизировать модификацию сжатых данных (чтобы сделать это простым для наших пользователей), мы изменили наш подход к сжатию, введя «промежуточную область» — по сути, перекрывающийся фрагмент, который остается несжатым и в котором мы выполняем операции «над несжатыми данными» под капот.

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

Такая конструкция с использованием промежуточной области делает операции INSERT такими же быстрыми, как и вставка в несжатые фрагменты, поскольку именно это и происходит (когда вы вставляете в сжатый фрагмент, вы теперь записываете в промежуточную область). Это также позволило нам напрямую поддерживать UPDATE, UPSERT и DELETE: когда значение необходимо изменить, движок перемещает соответствующую часть сжатых данных в промежуточную область, распаковывает их, выполняет изменение и (асинхронно) перемещает их снова. в основную таблицу в сжатом виде.

(Эта область данных обычно работает в масштабе сжатых «мини-пакетов» до 1000 значений, которые составляют «строку» в базовом хранилище PostgreSQL, чтобы минимизировать объем данных, которые необходимо распаковать для поддержки изменений.)

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

Конечный результат: более быстрые запросы, меньше места для хранения больших баз данных PostgreSQL

На этом этапе следующий логичный вопрос: каков конечный результат? Как сжатие влияет на производительность запросов и какой размер диска можно сэкономить, используя его?

Производительность запросов до и после сжатия

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

Давайте проиллюстрируем это, выполнив пару запросов к Набор данных такси Нью-Йорка , один из примеров наборов данных, которые мы предоставляем в Timescale. Этот набор данных содержит информацию о поездках на такси, включая время посадки и высадки, местоположение, расстояния, тарифы и многое другое.

Рассмотрим следующий запрос, запрашивающий максимальную сумму тарифа из подмножества набора данных такси в течение определенного периода времени:

 SELECT max(fare_amount) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01';

При запуске несжатого набора данных время выполнения запроса составляет 4,7 секунды. Мы используем небольшой неоптимизированный сервис тестирования и запрашиваем многие миллионы строк, поэтому производительность не самая лучшая. Но после сжатия данных время отклика снижается до 77,074 миллисекунды:

Давайте поделимся еще одним примером. Этот запрос подсчитывает количество поездок с определенным кодом тарифа в течение заданного периода времени:

 SELECT COUNT(*) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01' AND   "RatecodeID" = 99;

При выполнении этого запроса к несжатым данным выполнение этого запроса займет 1,6 секунды. Тот же запрос, выполняемый со сжатыми данными, выполняется всего за 18,953 миллисекунды. И снова мы видим немедленное улучшение! Это всего лишь краткие примеры, но они иллюстрируют, насколько мощным может быть сжатие для ускорения ваших запросов.

Как сжатие Timescale уменьшает размер хранилища PostgreSQL: примеры из реальной жизни

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

Такая экономия места на диске напрямую приводит к экономии денег: Платформа Timescale использует ценообразование на основе использования хранилища. , поэтому, если объем хранилища сокращается, пропорционально уменьшается и ваш счет.

Степень сжатия, которую вы в конечном итоге достигнете, зависит от нескольких факторов, включая тип ваших данных и шаблоны доступа. Но, как видите, сжатие шкалы времени может быть чрезвычайно эффективным. мы даже активно используем его внутри компании, чтобы обеспечить сжатие 96 % в нашем клиентском продукте Insights. .

Наша команда может помочь вам настроить сжатие, чтобы сэкономить как можно больше денег. так что не стесняйтесь обращаться .

«При сжатии мы добились уменьшения [размера диска] в среднем на 97 процентов».

(Майкл Гальярдо, Ndustrial)

«Мы обнаружили, что степень сжатия Timescale просто феноменальна! В настоящее время степень сжатия превышает 26, что резко сокращает дисковое пространство, необходимое для хранения всех наших данных».

(Николя Квинтен, Октава)

«Сжатие Timescale было настолько хорошим, насколько заявлено, что дало нам +90 % экономии [дискового] пространства в нашей базовой гипертаблице».

(Паоло Бергантино, METER Group)

Сжатие и многоуровневое хранилище: жизненный цикл хранилища с временной шкалой

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

Помимо сжатия, теперь у вас есть еще один инструмент, который поможет вам еще больше масштабировать ваши базы данных PostgreSQL на платформе Timescale: вы можете разместить свои старые, редко используемые данные на недорогом уровне объектного хранилища, сохраняя при этом доступ к ним через стандартный уровень. SQL.

Этот недорогой уровень хранения данных имеет фиксированную цену 0,021 доллара США за ГБ/месяц — дешевле, чем у Amazon S3, — что позволяет вам хранить много ТБ в базе данных PostgreSQL за небольшую часть стоимости.

Вот как работает наш сервер многоуровневого хранилища на платформе Timescale и как уровень с низким объемом хранилища сочетается со сжатием:

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

  
  

• Как только ваше приложение перестанет часто обращаться к этим данным, вы сможете автоматически перенести их на более дешевый уровень объектного хранилища , настроив политику многоуровневого хранения. Данные на недорогом уровне хранения остаются полностью доступными для запросов в вашей базе данных, и объем данных, которые вы можете хранить, не ограничен — до сотен ТБ и более. Например, вы можете определить политику многоуровневого хранения, которая переместит все ваши данные старше шести месяцев на недорогой уровень хранения.

  
  

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

Оставайтесь с PostgreSQL

Мы предоставили Postgres эффективный механизм сжатия базы данных, добавив возможности столбчатого сжатия. Это важная функция для масштабирования баз данных PostgreSQL в современном мире с интенсивным использованием данных: сжатие позволяет значительно сэкономить на использовании диска (хранить больше данных по более низкой цене) и повысить производительность (выполнение аналитических запросов к большим объемам за миллисекунды).

Схема сжатия Timescale обеспечивает впечатляющую степень сжатия за счет сочетания лучших в своем классе алгоритмов сжатия с новым методом создания гибридного хранилища строк и столбцов в PostgreSQL. Эта возможность делает объем хранилища Timescale (и, следовательно, PostgreSQL) равным со специально созданными, более ограниченными столбчатыми базами данных.

Но в отличие от многих столбчатых механизмов, Timescale поддерживает транзакционную семантику ACID и прямую поддержку модификаций (INSERT, UPDATE, UPSERT, DELETE) сжатых столбчатых данных. Поскольку старая модель «одна база данных для транзакционных рабочих нагрузок, другая для аналитических» устарела, многие современные приложения запускают рабочие нагрузки, соответствующие обоим шаблонам. Так зачем хранить две отдельные базы данных, если все это можно сделать в PostgreSQL?

Timescale позволяет вам начать с PostgreSQL, масштабировать его с помощью PostgreSQL и продолжать использовать PostgreSQL.

Создать бесплатный аккаунт и попробуйте Timescale сегодня — это займет всего пару секунд, кредитная карта не требуется.

- Авторы сценария Карлота Сото и Майк Фридман .