Что происходит с местом таблицы на диске, после удаления записи таблицы

Что происходит с местом на диске после удаления записи из таблицы

Это вопрос о физическом управлении дисковым пространством в базе данных. После DELETE команды место не всегда освобождается автоматически, это зависит от того, как работает СУБД.

Высокоуровневое объяснение

На уровне Java / Application: DELETE просто удаляет запись логически. Физическое место может:

• Остаться занято (dead space)
• Быть переиспользовано для новых данных
• Быть освобождено явно (команда VACUUM)

Процесс удаления в СУБД

1. Логическое удаление (Soft Delete)

Это самый частый способ:

@Entity
public class User {
    @Id
    private Long id;
    private String name;
    
    @Column(name = "deleted_at")
    private LocalDateTime deletedAt;  // Дата удаления, не NULL
}

// В коде
user.setDeletedAt(LocalDateTime.now(UTC));
userRepository.save(user);

// SQL
UPDATE users SET deleted_at = NOW() WHERE id = 1;

Что происходит с местом на диске: НИЧЕГО — строка остается на диске, просто помечается как удаленная.

2. Физическое удаление (Hard Delete)

// DELETE запрос
DELETE FROM users WHERE id = 1;

Что происходит с местом на диске:

Вариант А: СУБД переиспользует место

• Место в таблице помечается как свободное
• Новые INSERT'ы могут переиспользовать это место
• Файл на диске может не уменьшиться в размере

Вариант Б: Внутренняя фрагментация

• На диске образуется "дыра" (dead space)
• Размер файла таблицы остается прежним или уменьшается (зависит от СУБД)
• Требуется явное "сжатие" таблицы

Как это работает в конкретных СУБД

PostgreSQL

Изначально, DELETE оставляет место занятым:

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR);
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');

-- Размер таблицы (занимает место на диске)
SELECT pg_size_pretty(pg_total_relation_size('users'));
-- Результат: 8192 bytes

DELETE FROM users WHERE id = 2;

-- Размер ВСЕ ЕЩЕ 8192 bytes (место не освобождено!)
SELECT pg_size_pretty(pg_total_relation_size('users'));

Почему? PostgreSQL использует MVCC (Multi-Version Concurrency Control). Удаленные строки остаются в таблице, помеченные как "мертвые" (dead tuples).

Решение: VACUUM

-- Очищает мертвые строки
VACUUM users;  -- Освобождает место для новых данных

-- Или более агрессивно
VACUUM FULL users;  -- Полное переписывание таблицы (медленнее)

MySQL / InnoDB

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');

SELECT COUNT(*) FROM users;  -- 3

DELETE FROM users WHERE id = 2;

SELECT COUNT(*) FROM users;  -- 2

Что происходит с местом:

• InnoDB переиспользует место для новых INSERT'ов
• Размер .ibd файла может не уменьшиться
• Место считается "внутренне свободным" (unused space)

Решение: OPTIMIZE TABLE

-- Полное переписывание таблицы, освобождение места
OPTIMIZE TABLE users;

SQLite

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

DELETE FROM users WHERE id = 2;

Что происходит с местом:

• Место помечается как "свободное" внутри базы
• Размер файла БД может не уменьшиться
• PRAGMA может помочь отследить внутреннее состояние

Решение: VACUUM

VACUUM;  -- Перестраивает БД, освобождает неиспользуемое место

Фрагментация в контексте Java

Когда Java приложение работает с БД:

@Service
public class UserService {
    private final UserRepository userRepository;
    
    public void deleteUserWithManyRecords(Long userId) {
        // Удаляем пользователя и его заказы
        orderRepository.deleteByUserId(userId);  // DELETE в таблице orders
        userRepository.deleteById(userId);        // DELETE в таблице users
        
        // На диске могут остаться "дыры"
        // Если часто делать DELETE, таблица становится фрагментированной
    }
}

// Проблема: производительность SELECT'ов может упасть
// Потому что таблица заполнена "дырами"

Решение 1: Soft Delete (рекомендуется)

@Entity
@Where(clause = "deleted_at IS NULL")  // Lombok + Hibernate
public class User {
    @Id
    private Long id;
    private String name;
    
    @Column(name = "deleted_at")
    private LocalDateTime deletedAt;
    
    public void delete() {
        this.deletedAt = LocalDateTime.now(UTC);
    }
}

// Использование
@Service
public class UserService {
    public void deleteUser(Long userId) {
        User user = userRepository.findById(userId).orElseThrow();
        user.delete();  // Soft delete
        userRepository.save(user);  // UPDATE, не DELETE
    }
}

// SQL
UPDATE users SET deleted_at = NOW() WHERE id = 1;

// Место на диске: ОСТАЕТСЯ занятым, но переиспользуется
// Фрагментация: НИЗКАЯ (нет дыр)

Решение 2: Периодическая очистка (Hard Delete + VACUUM)

@Service
@Scheduled(cron = "0 0 3 * * *")  // 3 часа ночи
public class MaintenanceService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void vacuumDatabase() {
        // Удаляем старые мертвые записи
        jdbcTemplate.execute("DELETE FROM users WHERE deleted_at < NOW() - INTERVAL 90 DAY");
        
        // PostgreSQL
        jdbcTemplate.execute("VACUUM users");
        
        // MySQL
        jdbcTemplate.execute("OPTIMIZE TABLE users");
        
        // SQLite
        jdbcTemplate.execute("VACUUM");
    }
}

Решение 3: Partitioning (для очень больших таблиц)

-- MySQL
CREATE TABLE orders (
    id INT,
    created_at DATETIME,
    ...
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- Удаляем старый раздел (очень быстро)
ALTER TABLE orders DROP PARTITION p2022;

Полный пример с мониторингом

@Component
public class DatabaseHealthCheck {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Scheduled(fixedRate = 86400000)  // Каждый день
    public void checkFragmentation() {
        // Для PostgreSQL
        String query = """
            SELECT 
                schemaname,
                tablename,
                ROUND(100 * (CASE WHEN otta > 0 
                    THEN sml.relpages - otta 
                    ELSE 0 END) / sml.relpages::numeric, 2) AS ratio
            FROM pg_class
            WHERE schemaname = 'public'
            ORDER BY ratio DESC;
        """;
        
        List<Map<String, Object>> results = jdbcTemplate.queryForList(query);
        
        for (Map<String, Object> row : results) {
            Double ratio = (Double) row.get("ratio");
            String tableName = (String) row.get("tablename");
            
            // Если фрагментация > 30%, срабатываем VACUUM
            if (ratio > 30) {
                logger.warn("Table {} has high fragmentation: {}%", tableName, ratio);
                jdbcTemplate.execute("VACUUM " + tableName);
            }
        }
    }
}

Ключевые моменты

1. Логическое удаление (DELETE) помечает место как свободное, но не освобождает дисковое место
2. Фрагментация растет при частых DELETE операциях
3. VACUUM / OPTIMIZE нужны периодически для очистки мертвых строк
4. Soft Delete рекомендуется для приложений, где логика требует восстановления
5. Hard Delete + VACUUM — для GDPR и действительного удаления данных
6. Partitioning — для очень больших таблиц, эффективнее VACUUM

Практический совет

Выбирай подход в зависимости от use case:

• Soft Delete: пользователи, заказы, финансовые записи (нужна история)
• Hard Delete + VACUUM: логи, сессии, временные данные
• Partitioning: аналитические таблицы с огромным объемом

От правильного выбора зависит как производительность, так и возможность восстановления данных.