Блог

Прошу прощения, это длинный пост и вопрос amp; новичок postgres

Хотел узнать, как работают postgres VACCUM и VACCUM FULL, что предпочтительнее для архивирования таблиц объемом 700 ГБ без простоев.

ПОЛНЫЙ СЛУЧАЙ VACCUM:

В этом случае похоже, что память освобождается для ОС после выполнения команды vacuum full, но n_dead_tuples остаются того же размера.

Будет ли этот n_dead_tup повторно использоваться при следующей вставке / обновлении / удалении?

ШАГИ:

 CREATE TABLE Foo (
  id SERIAL PRIMARY KEY,
  x  INTEGER NOT NULL
);

// CREATE TABLE

INSERT INTO Foo (x) VALUES (generate_series(1,1000000));

// INSERT 0 1000000

SELECT pg_size_pretty(pg_relation_size('foo'));

// 35 MB

SELECT n_dead_tup FROM pg_stat_user_tables where relname = 'foo';

// 0

UPDATE Foo SET x = x   1;

// UPDATE 1000000

SELECT pg_size_pretty(pg_relation_size('foo'));

// 69 MB

SELECT n_dead_tup FROM pg_stat_user_tables where relname = 'foo';

// 1000000

VACUUM FULL VERBOSE foo;

// INFO:  vacuuming "public.foo"
// INFO:  "foo": found 1000000 removable, 1000000 nonremovable row versions in 8850 pages
// DETAIL:  0 dead row versions cannot be removed yet.
// CPU 0.07s/0.67u sec elapsed 0.76 sec.
// VACUUM

 SELECT pg_size_pretty(pg_relation_size('foo'));

// 35 MB

SELECT n_dead_tup FROM pg_stat_user_tables where relname = 'foo';

// 1000000
  

СЛУЧАЙ ВАКУУМА

ШАГИ:

То же, что и в описанных выше шагах, за исключением VACCUM FULL, необходимо выполнить приведенные ниже команды, в этом случае похоже, что мертвые кортежи удаляются, но размер не передается в ОС.

 VACUUM VERBOSE foo;

// INFO:  vacuuming "public.foo"
// INFO:  scanned index "foo_pkey" to remove 1000000 row versions
// DETAIL:  CPU 0.01s/0.27u sec elapsed 0.34 sec.
// INFO:  "foo": removed 1000000 row versions in 4425 pages
// DETAIL:  CPU 0.00s/0.02u sec elapsed 0.02 sec.
// INFO:  index "foo_pkey" now contains 1000000 row versions in 8237 pages
// DETAIL:  1000000 index row versions were removed.
// 0 index pages have been deleted, 0 are currently reusable.
// CPU 0.00s/0.00u sec elapsed 0.00 sec.
// INFO:  "foo": found 1000000 removable, 1000000 nonremovable row versions in 8850 out of 8850 pages
// DETAIL:  0 dead row versions cannot be removed yet.
// There were 0 unused item pointers.
// Skipped 0 pages due to buffer pins.
// 0 pages are entirely empty.
// CPU 0.03s/0.52u sec elapsed 0.61 sec.
// VACUUM

SELECT pg_size_pretty(pg_relation_size('foo'));

// 69 MB

SELECT n_dead_tup FROM pg_stat_user_tables where relname = 'foo';

// 0
  

ЯВЛЯЕТСЯ ЛИ АВТОМАТИЧЕСКИЙ ВАКУУМ ЭКВИВАЛЕНТОМ ВАКУУМА?

Заранее спасибо.

Простой VACUUM обычно не освобождает дисковое пространство, как объясняется в документации:

[…] Однако дополнительное пространство не возвращается операционной системе (в большинстве случаев); оно просто остается доступным для повторного использования в той же таблице. […]

Для VACUUM FULL это отличается тем, что он в основном переписывает все данные таблицы, как объясняется в документации:

[…] Этот метод также требует дополнительного дискового пространства, поскольку он записывает новую копию таблицы и не освобождает старую копию до завершения операции. […]

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

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

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