🤖 Храним большие базы данных PostgreSQL правильно. Базы данных PostgreSQL имеют свойство незаметно расти. Однажды вы замечаете, что запросы замедлились, резервное копирование длится вечность, и никто уже не помнит, что хранится в той таблице на 500 ГБ. В заметке рассмотрим как бороться с разрастанием базы данных, какие у нас для этого есть методы и возможности. 📌 Партиционирование. Если в вашей базе есть одна гигантская таблица, которая продолжает расти, партиционирование может стать спасением. С 10-й версии PostgreSQL поддерживает нативное декларативное партиционирование, а в последних версиях (13 и выше) работать с ним стало намного проще. Вам не нужны никакие расширения - вы можете определить родительскую таблицу и разделить её по диапазону, списку или хешу. Например, для данных, зависящих от времени: CREATE TABLE events ( id serial, created_at timestamptz, payload jsonb ) PARTITION BY RANGE (created_at); Полезность партиционирования заключается в том, что оно улучшает планирование запросов и делает удаление старых данных простым (удаление партиции происходит быстрее и чище, чем операция DELETE). Чтобы существующую таблицу превратить в партицированную, можно сделать так: 1️⃣ Создать партиционированную версию таблицы; 2️⃣ Перенести данные партиями с помощью скриптов; 3️⃣ Добавить триггеры для новых вставок в партиционированную версию; 4️⃣ Поменять местами старую и новую таблицы после завершения миграции. ⚠️ Важно: Создавайте индексы на самой партиционированной таблице - PostgreSQL автоматически создаст соответствующие индексы на всех партициях, включая те, что будут добавлены в будущем. 📌 Сжатие. PostgreSQL использует механизм TOAST (The Oversized-Attribute Storage Technique) для обработки больших значений полей, таких как text, jsonb и bytea. По умолчанию он сжимает их с помощью алгоритма pglz. Начиная с PostgreSQL 14, вы можете явно выбирать алгоритм сжатия для каждого столбца - если ваша сборка включает поддержку LZ4 (--with-lz4), вы можете использовать более быстрый и часто более эффективный алгоритм lz4: CREATE TABLE logs_lz4 ( id serial PRIMARY KEY, message text COMPRESSION lz4 ); 📌 Сжатие резервных копий. Хотя это не относится к хранению таблиц, помните, что pg_dump также поддерживает сжатие. Самый современный метод сжатия - это zstd, а базовые - gzip и lz4: # pg_dump --format=custom --compress=zstd:3 > dump.zstd Это отличный вариант для архивирования "холодных" данных за пределами базы данных с экономией дискового пространства. 📌 Архивация старых данных вне PostgreSQL. Вам не обязательно хранить всё в основном экземпляре PostgreSQL вечно. "Холодные" данные можно переместить в более дешёвые и медленные системы хранения - и PostgreSQL всё ещё сможет их запрашивать. Самый распространённый подход - использование обёрток для сторонних данных (FDW), например: ✅ postgres_fdw для выгрузки старых партиций в другой экземпляр PostgreSQL; ✅ file_fdw для доступа к данным в CSV; ✅ pg_parquet для запросов к файлам Parquet в локальном или облачном хранилище. Мораль сей заметки такова: за вашей базой данных нужно обязательно приглядывать, чтобы со временем не столкнуться с большими проблемами и медленными запросами. Если у вас в базе есть быстро растущие таблицы, то партицирование будет вашим первым помощником. С наступающим Новым Годом!!! 🎄 🎉 #pgmonitor