Postgres переписывает всю строку при обновлении?

Мы запускаем Postgres 9.0 на Windows 2008 Server. Существует большая таблица содержит bytea столбец для хранения двоичных данных размером от 0-5 МБ в каждой строке:

CREATE TABLE files
(
  file_id serial NOT NULL,
  data bytea NOT NULL,
  on_disk boolean,
  CONSTRAINT files_pkey PRIMARY KEY (file_id)
)

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

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

ERROR: 53100: could not write block 92271 of temporary file

2) Наше свободное место за неделю сократилось с ~7 ГБ до 1,5 ГБ, что нехарактерно.

Может ли кто-нибудь подтвердить:

а) Будет ли обновление строки в postgres причиной перезаписи всей строки (включая большие двоичные данные) без освобождения старого пространства? Это объяснило бы наши симптомы

б) это запись во время изменения в другое временное табличное пространство, которое также занимает место? (Можем ли мы принудительно освободить временное пространство?)

c) Есть ли способ, которым мы можем выполнить незначительные обновления логических полей для этой таблицы, БЕЗ перезаписи строки (и жевания дискового пространства) каждый раз?

d) Можем ли мы периодически заставлять postgres освобождать используемое пространство, не переписывая всю таблицу? (Наши известные методы освобождения места включают переписывание таблицы, для которой у нас нет места)

PS: Да, мы переносим наш сервер на хост с большим объемом памяти... это может занять 1-2 месяца.

Источник

2 ответа

Решение

По крайней мере, в 9.3 PostgreSQL не переписывает поля, хранящиеся вне строки в TOAST таблицы, если они хранятся вне строки. Я не знаю, правда ли это в 9.0.

Вы можете увидеть, какое хранилище используется для столбца с \d+ tablename; storage столбец показывает используемый режим. Отдельные кортежи могут храниться в сжатом виде, если они достаточно малы (например, < 2K), даже в extended столбец хранения, в котором кортежи имеют право на внешнее хранение.

Смотрите документацию дляTOAST а также ALTER TABLE ... SET STORAGE,

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

Пространство внутри таблиц / индексов должно быть освобождено для повторного использования автоматически с помощью автоочистки. Удостоверьтесь, что ваш демон autovacuum работает достаточно часто и не имеет слишком большого набора cost_delay. Автовакуум был значительно улучшен с 9.0.

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

Источник

Подбор в) из ваших вопросов:

Есть ли способ, которым мы можем выполнить незначительные обновления булевых полей для этой таблицы БЕЗ перезаписи строки (и жевания дискового пространства) каждый раз?

Как уже объяснял @Craig, столбцы, которые "поддерживают TOAST" и превышают определенный порог, хранятся вне строки в отдельной таблице TOAST для каждой таблицы (отдельные "вилки отношений", отдельные файлы на диске). Итак, 5 МБ bytea Столбец будет оставаться в основном нетронутым в обновлении, если сам столбец не изменяется. Руководство:

Во время операции UPDATE значения неизмененных полей обычно сохраняются как есть; таким образом, ОБНОВЛЕНИЕ строки со значениями вне строки не требует затрат TOAST, если ни одно из значений вне строки не изменяется.

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

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

Источник
Другие вопросы по тегам