Раздутие индексов в PostgreSQL
Частые операции обновления, вставки и удаления данных в таблицах PostgreSQL приводит к тому, что индексы начинают занимать больше места на диске.
Почему это происходит
При обновлении, вставке, удалении строк база данных не просто меняет старые значения на новые. Вместо этого создаётся новая версия строки, а старая сохраняется для обеспечения целостности данных в рамках текущих транзакций.
К чему это приводит
- Индексы занимают больше места на диске
- Происходит замедление запросов - необходимо обрабатывать неактуальные данные
- Увеличивается нагрузка на память и процессор
Пример
Рассмотрим небольшой пример:
-- Создадим таблицу
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
-- и добавим индекс для поиска
CREATE INDEX idx_users_email ON users USING btree (email);
-- Вставим несколько строк
INSERT INTO users (name, email) VALUES ('name1', 'name1@mail.ru');
INSERT INTO users (name, email) VALUES ('name2', 'name2@mail.ru');
INSERT INTO users (name, email) VALUES ('name3', 'name3@mail.ru');
INSERT INTO users (name, email) VALUES ('name4', 'name4@mail.ru');
INSERT INTO users (name, email) VALUES ('name5', 'name5@mail.ru');
INSERT INTO users (name, email) VALUES ('name6', 'name6@mail.ru');
INSERT INTO users (name, email) VALUES ('name7', 'name7@mail.ru');
-- подгрузим расширение, которое позволит посмотреть что у нас в индексах
CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- здесь выведем текущее состояни
SELECT * FROM pgstattuple('idx_users_email');
-- теперь обновим значение полей
UPDATE users SET email = 'name11@mail,ru' WHERE id = 1;
UPDATE users SET email = 'name12@mail,ru' WHERE id = 2;
UPDATE users SET email = 'name13@mail,ru' WHERE id = 3;
UPDATE users SET email = 'name14@mail,ru' WHERE id = 4;
UPDATE users SET email = 'name15@mail,ru' WHERE id = 5;
UPDATE users SET email = 'name16@mail,ru' WHERE id = 6;
UPDATE users SET email = 'name17@mail,ru' WHERE id = 7;
-- и проверим еще раз, результаты будут другие :)
SELECT * FROM pgstattuple('idx_users_email');
Решение проблемы
- Перестраивать индекс через REINDEX
- VACUUM FULL - но здесь аккуратно, потому что происходит блокировка таблицы
- Регулярная очистка мертвых записей, с помощью Autovacuum
Спасибо за внимание!
Начать дискуссию