数据删除 #

TimescaleDB删除优势：

分片级别删除
├── drop_chunks快速删除整个分片
├── 比DELETE快得多
└── 自动释放磁盘空间

保留策略
├── 自动删除旧数据
├── 配置数据生命周期
└── 定期自动执行

兼容性
├── 标准DELETE语法
├── 支持条件删除
└── 支持级联删除

删除方式选择：

DELETE
├── 删除特定条件数据
├── 删除少量数据
├── 需要精确控制
└── 性能较低

drop_chunks
├── 删除整个分片
├── 删除大量历史数据
├── 数据生命周期管理
└── 性能极高

TRUNCATE
├── 删除所有数据
├── 保留表结构
└── 最快方式

-- 创建示例表
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
);

SELECT create_hypertable('sensor_data', 'time');

-- 删除特定条件数据
DELETE FROM sensor_data
WHERE sensor_id = 1;

-- 删除时间范围数据
DELETE FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days';

-- 删除多条件数据
DELETE FROM sensor_data
WHERE sensor_id = 1
  AND time < NOW() - INTERVAL '30 days';

-- 删除NULL值
DELETE FROM sensor_data
WHERE temperature IS NULL;

-- 返回删除的行
DELETE FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days'
RETURNING *;

-- 返回特定列
DELETE FROM sensor_data
WHERE sensor_id = 1
RETURNING time, sensor_id, temperature;

-- 统计删除数量
DELETE FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days';

SELECT 'Deleted ' || ROW_COUNT || ' rows' as result;

-- 使用子查询删除
DELETE FROM sensor_data
WHERE sensor_id IN (
    SELECT sensor_id FROM inactive_sensors
);

-- 使用EXISTS删除
DELETE FROM sensor_data sd
WHERE EXISTS (
    SELECT 1 FROM sensor_alerts sa
    WHERE sa.sensor_id = sd.sensor_id
      AND sa.alert_type = 'malfunction'
);

-- 使用JOIN删除
DELETE FROM sensor_data
USING sensor_status ss
WHERE sensor_data.sensor_id = ss.sensor_id
  AND ss.status = 'deleted';

-- 分批删除大量数据
DO $$
DECLARE
    batch_size INTEGER := 10000;
    deleted_rows INTEGER;
BEGIN
    LOOP
        DELETE FROM sensor_data
        WHERE time < NOW() - INTERVAL '90 days'
        LIMIT batch_size;
        
        GET DIAGNOSTICS deleted_rows = ROW_COUNT;
        
        EXIT WHEN deleted_rows = 0;
        
        COMMIT;
        RAISE NOTICE 'Deleted % rows', deleted_rows;
    END LOOP;
END $$;

-- 删除90天前的所有分片
SELECT drop_chunks(
    'sensor_data',
    older_than => INTERVAL '90 days'
);

-- 删除指定日期前的分片
SELECT drop_chunks(
    'sensor_data',
    older_than => '2024-01-01'::timestamptz
);

-- 删除指定时间范围
SELECT drop_chunks(
    'sensor_data',
    newer_than => INTERVAL '180 days',
    older_than => INTERVAL '90 days'
);

-- 删除所有分片
SELECT drop_chunks('sensor_data');

-- 完整参数示例
SELECT drop_chunks(
    'sensor_data',                    -- 超表名
    older_than => INTERVAL '90 days', -- 删除此时间之前的分片
    newer_than => NULL,               -- 可选：删除此时间之后的分片
    verbose => TRUE                   -- 显示详细信息
);

-- 查看将被删除的分片（不实际删除）
SELECT 
    chunk_name,
    range_start,
    range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
  AND range_end < NOW() - INTERVAL '90 days';

drop_chunks vs DELETE：

drop_chunks
├── 删除整个分片
├── 速度快
├── 立即释放磁盘空间
├── 不产生WAL日志
└── 适合数据生命周期管理

DELETE
├── 逐行删除
├── 速度慢
├── 需要VACUUM释放空间
├── 产生大量WAL日志
└── 适合精确删除

-- 删除多个超表的旧数据
SELECT drop_chunks(
    ARRAY['sensor_data', 'events', 'logs'],
    older_than => INTERVAL '30 days'
);

-- 查看所有超表
SELECT hypertable_name FROM timescaledb_information.hypertables;

-- 添加保留策略
SELECT add_retention_policy(
    'sensor_data',
    INTERVAL '90 days'
);

-- 查看保留策略
SELECT 
    job_id,
    application_name,
    schedule_interval,
    config
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 查看策略详情
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 修改保留时间
SELECT remove_retention_policy('sensor_data');

SELECT add_retention_policy(
    'sensor_data',
    INTERVAL '180 days'
);

-- 或使用ALTER JOB
SELECT alter_job(
    job_id,
    schedule_interval => INTERVAL '1 day'
) FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
  AND hypertable_name = 'sensor_data';

-- 删除保留策略
SELECT remove_retention_policy('sensor_data');

-- 验证删除
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
  AND hypertable_name = 'sensor_data';

-- 手动执行一次保留策略
SELECT drop_chunks(
    'sensor_data',
    older_than => INTERVAL '90 days'
);

-- 或使用run_job
SELECT run_job(job_id)
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention'
  AND hypertable_name = 'sensor_data';

-- 删除所有数据（保留表结构）
TRUNCATE TABLE sensor_data;

-- 级联截断（删除相关表数据）
TRUNCATE TABLE sensor_data CASCADE;

-- 重置序列
TRUNCATE TABLE sensor_data RESTART IDENTITY;

TRUNCATE特点：

优势
├── 速度最快
├── 立即释放空间
├── 不触发触发器
└── 重置序列

限制
├── 不能有外键引用
├── 不能回滚
├── 不返回删除行数
└── 需要TRUNCATE权限

-- 查看压缩状态
SELECT 
    chunk_name,
    is_compressed,
    range_start,
    range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data';

-- drop_chunks可以直接删除压缩分片
SELECT drop_chunks(
    'sensor_data',
    older_than => INTERVAL '90 days'
);

-- 压缩分片会被直接删除，无需解压

-- 如果必须使用DELETE删除压缩数据
-- 需要先解压

-- 解压分片
SELECT decompress_chunk('_hyper_1_1_chunk');

-- 执行删除
DELETE FROM sensor_data
WHERE time BETWEEN '2024-01-01' AND '2024-01-07'
  AND sensor_id = 1;

-- 重新压缩（如果需要）
SELECT compress_chunk('_hyper_1_1_chunk');

删除方式选择指南：

场景                    推荐方式
─────────────────────────────────────
删除所有数据            TRUNCATE
删除历史数据            drop_chunks
删除特定条件数据        DELETE
自动数据生命周期        保留策略

-- 使用索引加速DELETE
-- 确保WHERE条件使用索引

-- 查看执行计划
EXPLAIN ANALYZE
DELETE FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days';

-- 创建合适的索引
CREATE INDEX idx_time ON sensor_data (time);

-- 分批删除大量数据
DO $$
DECLARE
    batch_size INTEGER := 10000;
BEGIN
    LOOP
        DELETE FROM sensor_data
        WHERE time < NOW() - INTERVAL '90 days'
        LIMIT batch_size;
        
        EXIT WHEN NOT FOUND;
        
        COMMIT;
    END LOOP;
END $$;

-- DELETE后执行VACUUM
DELETE FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days';

-- 执行VACUUM释放空间
VACUUM sensor_data;

-- 或执行VACUUM FULL（锁定表）
VACUUM FULL sensor_data;

-- 自动VACUUM
-- PostgreSQL会自动执行VACUUM
-- 可配置自动VACUUM参数
ALTER TABLE sensor_data SET (
    autovacuum_enabled = true,
    autovacuum_vacuum_scale_factor = 0.1
);

-- 查看表删除统计
SELECT 
    schemaname,
    tablename,
    n_tup_del as deletes,
    n_dead_tup as dead_tuples,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE tablename = 'sensor_data';

-- 查看分片删除情况
SELECT 
    hypertable_name,
    count(*) as chunk_count,
    min(range_start) as oldest_data,
    max(range_end) as newest_data
FROM timescaledb_information.chunks
GROUP BY hypertable_name;

-- 查看保留策略执行历史
SELECT 
    job_id,
    last_run,
    next_run,
    last_run_success,
    total_runs,
    total_failures
FROM timescaledb_information.job_stats
WHERE job_id IN (
    SELECT job_id FROM timescaledb_information.jobs
    WHERE proc_name = 'policy_retention'
);

-- 查看最近删除的分片
SELECT 
    chunk_name,
    range_start,
    range_end,
    is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
ORDER BY range_start DESC
LIMIT 10;

-- 如果使用了事务，可以回滚
BEGIN;
DELETE FROM sensor_data WHERE ...;
-- 发现误删除
ROLLBACK;

-- 如果已提交，需要从备份恢复
-- 使用pg_restore恢复特定表
pg_restore -d tsdb -t sensor_data backup.dump

-- 或使用时间点恢复（PITR）
-- 需要提前配置WAL归档

-- 删除前先查询确认
SELECT count(*) FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days';

-- 使用事务
BEGIN;
DELETE FROM sensor_data WHERE ...;
-- 检查结果
SELECT count(*) FROM sensor_data WHERE ...;
-- 确认无误后提交
COMMIT;
-- 或回滚
ROLLBACK;

-- 使用RETURNING查看删除内容
DELETE FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days'
RETURNING *;

-- 问题：DELETE性能差
-- 解决：使用drop_chunks

-- 不推荐
DELETE FROM sensor_data
WHERE time < NOW() - INTERVAL '90 days';

-- 推荐
SELECT drop_chunks(
    'sensor_data',
    older_than => INTERVAL '90 days'
);

-- 问题：DELETE后磁盘空间未释放
-- 解决：执行VACUUM

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('sensor_data'));

-- 执行VACUUM
VACUUM sensor_data;

-- 或VACUUM FULL
VACUUM FULL sensor_data;

-- 问题：保留策略未自动执行
-- 解决：检查策略配置

-- 查看策略状态
SELECT * FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 手动执行
SELECT run_job(job_id)
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 检查执行日志
SELECT * FROM timescaledb_information.job_stats
WHERE job_id IN (
    SELECT job_id FROM timescaledb_information.jobs
    WHERE proc_name = 'policy_retention'
);