Postgres VACUUM и bloat на собеседовании Data Engineer

Зачем VACUUM

Postgres MVCC — UPDATE / DELETE не remove rows immediately. Stay as «dead tuples». VACUUM cleans up.

Без vacuum — table bloated, indexes bloated, queries slow.

Dead tuples

UPDATE users SET name = 'New' WHERE id = 42;

Под капотом:

1. Old row marked dead (xmin / xmax).
2. New row inserted с new timestamp.
3. Reads с newer snapshots see new row, older — old.

После all transactions older чем dead row's xmax — dead row safely vacuumed.

Autovacuum

Background process. Triggered когда:

• Dead tuples > autovacuum_vacuum_threshold (default 50) + scale_factor (20%).

ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.05);

Per-table tuning часто helpful.

Long-running transactions block vacuum — dead tuples can't be removed (still visible somewhere). One forgotten BEGIN — bloat grows.

VACUUM FULL

Rewrites entire table compactly. Reclaims disk space.

Caveat. ACCESS EXCLUSIVE lock — entire table locked.

Used когда:

• Table sigificantly bloated.
• Maintenance window OK.

Alternative — pg_repack. Online repack без exclusive lock.

pg_repack -t my_table -d my_db

Bloat measurement

SELECT
  relname,
  pg_size_pretty(pg_relation_size(oid)) AS size,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

dead_ratio > 20% — bloat issue.

Связанные темы

• Транзакции и MVCC для DE
• EXPLAIN и план запроса для DE
• Connection pooling для DE
• Postgres extensions для аналитики DE
• Подготовка к собесу Data Engineer

FAQ

Это официальная информация?

Нет. Статья основана на документации Postgres.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.