数据更新 #

TimescaleDB更新特点：

标准SQL
├── 完全兼容PostgreSQL UPDATE语法
├── 支持所有更新操作
└── 无需学习新语法

性能考虑
├── 更新会触发索引维护
├── 压缩数据更新成本高
├── 建议避免频繁更新
└── 考虑使用INSERT替代

最佳实践
├── 时序数据通常不需要更新
├── 修正数据使用DELETE+INSERT
├── 批量更新注意性能
└── 压缩数据先解压再更新

更新性能因素：

索引数量
├── 索引越多，更新越慢
├── 每个索引都需要更新
└── 考虑减少索引

数据压缩
├── 压缩数据更新需要解压
├── 解压+更新+重新压缩
└── 成本较高

分片影响
├── 跨分片更新性能差
├── 单分片更新性能好
└── 考虑更新条件

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

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

-- 插入测试数据
INSERT INTO sensor_data VALUES
    ('2024-01-01 10:00:00+00', 1, 25.5, 60.0, 'active'),
    ('2024-01-01 11:00:00+00', 1, 25.6, 60.5, 'active');

-- 更新单个字段
UPDATE sensor_data
SET temperature = 25.7
WHERE time = '2024-01-01 10:00:00+00'
  AND sensor_id = 1;

-- 更新多个字段
UPDATE sensor_data
SET 
    temperature = 25.8,
    humidity = 61.0
WHERE time = '2024-01-01 10:00:00+00'
  AND sensor_id = 1;

-- 更新状态
UPDATE sensor_data
SET status = 'inactive'
WHERE time < NOW() - INTERVAL '30 days';

-- 基于当前值更新
UPDATE sensor_data
SET temperature = temperature * 1.1
WHERE temperature < 20;

-- 使用CASE更新
UPDATE sensor_data
SET status = CASE
    WHEN temperature > 30 THEN 'high'
    WHEN temperature < 20 THEN 'low'
    ELSE 'normal'
END
WHERE time > NOW() - INTERVAL '24 hours';

-- 使用COALESCE更新NULL值
UPDATE sensor_data
SET humidity = COALESCE(humidity, 50.0)
WHERE humidity IS NULL;

-- 使用子查询更新
UPDATE sensor_data
SET status = 'anomaly'
WHERE temperature > (
    SELECT AVG(temperature) * 1.5
    FROM sensor_data
    WHERE time > NOW() - INTERVAL '24 hours'
);

-- 返回更新的行
UPDATE sensor_data
SET temperature = 26.0
WHERE time = '2024-01-01 10:00:00+00'
  AND sensor_id = 1
RETURNING *;

-- 返回特定列
UPDATE sensor_data
SET temperature = 26.0,
    status = 'updated'
WHERE time = '2024-01-01 10:00:00+00'
  AND sensor_id = 1
RETURNING time, sensor_id, temperature, status;

-- 返回更新前后值
UPDATE sensor_data
SET temperature = 26.0
WHERE time = '2024-01-01 10:00:00+00'
  AND sensor_id = 1
RETURNING 
    temperature as new_temp,
    (SELECT temperature FROM sensor_data WHERE time = '2024-01-01 10:00:00+00' AND sensor_id = 1) as old_temp;

-- 更新特定时间范围
UPDATE sensor_data
SET status = 'archived'
WHERE time < NOW() - INTERVAL '90 days';

-- 更新特定传感器
UPDATE sensor_data
SET status = 'maintenance'
WHERE sensor_id IN (1, 2, 3)
  AND time > NOW() - INTERVAL '7 days';

-- 批量修正数据
UPDATE sensor_data
SET temperature = temperature + 0.5
WHERE sensor_id = 1
  AND time BETWEEN '2024-01-01' AND '2024-01-31';

-- 大批量更新时分批处理
DO $$
DECLARE
    batch_size INTEGER := 10000;
    updated_rows INTEGER;
BEGIN
    LOOP
        UPDATE sensor_data
        SET status = 'processed'
        WHERE status IS NULL
          AND time < NOW() - INTERVAL '30 days'
        LIMIT batch_size;
        
        GET DIAGNOSTICS updated_rows = ROW_COUNT;
        
        EXIT WHEN updated_rows = 0;
        
        COMMIT;
        RAISE NOTICE 'Updated % rows', updated_rows;
    END LOOP;
END $$;

-- 创建校准表
CREATE TABLE calibration (
    sensor_id INTEGER PRIMARY KEY,
    temp_offset DOUBLE PRECISION
);

INSERT INTO calibration VALUES
    (1, 0.5),
    (2, -0.3),
    (3, 0.2);

-- 使用FROM子句更新
UPDATE sensor_data sd
SET temperature = temperature + c.temp_offset
FROM calibration c
WHERE sd.sensor_id = c.sensor_id
  AND sd.time > NOW() - INTERVAL '24 hours';

-- 使用子查询更新
UPDATE sensor_data
SET temperature = temperature + (
    SELECT temp_offset FROM calibration
    WHERE calibration.sensor_id = sensor_data.sensor_id
)
WHERE time > NOW() - INTERVAL '24 hours';

压缩数据更新限制：

直接更新
├── 不支持直接更新压缩数据
├── 需要先解压
└── 更新后重新压缩

更新流程
├── 1. 解压相关分片
├── 2. 执行更新操作
├── 3. 重新压缩分片
└── 成本较高

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

-- 解压需要更新的分片
SELECT decompress_chunk('_hyper_1_1_chunk');

-- 执行更新
UPDATE sensor_data
SET temperature = 26.0
WHERE time BETWEEN '2024-01-01' AND '2024-01-07';

-- 重新压缩
SELECT compress_chunk('_hyper_1_1_chunk');

-- 批量解压、更新、压缩
DO $$
DECLARE
    chunk RECORD;
BEGIN
    FOR chunk IN 
        SELECT chunk_name, range_start, range_end
        FROM timescaledb_information.chunks
        WHERE hypertable_name = 'sensor_data'
          AND is_compressed = true
          AND range_end < NOW() - INTERVAL '7 days'
    LOOP
        -- 解压
        PERFORM decomcompress_chunk(chunk.chunk_name);
        
        -- 更新
        UPDATE sensor_data
        SET status = 'archived'
        WHERE time >= chunk.range_start
          AND time < chunk.range_end;
        
        -- 重新压缩
        PERFORM compress_chunk(chunk.chunk_name);
        
        RAISE NOTICE 'Processed chunk %', chunk.chunk_name;
    END LOOP;
END $$;

-- 查看索引
\di sensor_data

-- 更新前检查索引使用
EXPLAIN ANALYZE
UPDATE sensor_data
SET temperature = 26.0
WHERE time = '2024-01-01 10:00:00+00'
  AND sensor_id = 1;

-- 确保WHERE条件使用索引
-- 创建合适的索引
CREATE INDEX idx_sensor_time ON sensor_data (sensor_id, time);

-- 不推荐：多次单行更新
UPDATE sensor_data SET status = 'active' WHERE time = '2024-01-01 10:00:00+00' AND sensor_id = 1;
UPDATE sensor_data SET status = 'active' WHERE time = '2024-01-01 11:00:00+00' AND sensor_id = 1;
UPDATE sensor_data SET status = 'active' WHERE time = '2024-01-01 12:00:00+00' AND sensor_id = 1;

-- 推荐：批量更新
UPDATE sensor_data
SET status = 'active'
WHERE sensor_id = 1
  AND time >= '2024-01-01 10:00:00+00'
  AND time <= '2024-01-01 12:00:00+00';

-- 大批量更新使用事务
BEGIN;

-- 分批更新
UPDATE sensor_data
SET status = 'archived'
WHERE time < NOW() - INTERVAL '90 days'
  AND status IS NULL
LIMIT 10000;

-- 检查结果
SELECT COUNT(*) FROM sensor_data WHERE status = 'archived';

-- 提交或回滚
COMMIT;
-- 或 ROLLBACK;

-- 对于修正数据，使用DELETE + INSERT可能更快
BEGIN;

-- 删除旧数据
DELETE FROM sensor_data
WHERE time = '2024-01-01 10:00:00+00'
  AND sensor_id = 1;

-- 插入新数据
INSERT INTO sensor_data VALUES
    ('2024-01-01 10:00:00+00', 1, 26.0, 61.0, 'corrected');

COMMIT;

-- 创建唯一约束
ALTER TABLE sensor_data
ADD CONSTRAINT uk_sensor_time UNIQUE (time, sensor_id);

-- 使用ON CONFLICT
INSERT INTO sensor_data (time, sensor_id, temperature, humidity, status)
VALUES ('2024-01-01 10:00:00+00', 1, 26.0, 61.0, 'updated')
ON CONFLICT (time, sensor_id) DO UPDATE
SET 
    temperature = EXCLUDED.temperature,
    humidity = EXCLUDED.humidity,
    status = EXCLUDED.status;

-- 创建版本表
CREATE TABLE sensor_data_versions (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION,
    version INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- 插入新版本而不是更新
INSERT INTO sensor_data_versions (time, sensor_id, temperature, humidity, version)
SELECT time, sensor_id, 26.0, 61.0, version + 1
FROM sensor_data_versions
WHERE time = '2024-01-01 10:00:00+00'
  AND sensor_id = 1
ORDER BY version DESC
LIMIT 1;

-- 查询最新版本
SELECT DISTINCT ON (time, sensor_id)
    time, sensor_id, temperature, humidity, version
FROM sensor_data_versions
ORDER BY time, sensor_id, version DESC;

-- 查看表更新统计
SELECT 
    schemaname,
    tablename,
    n_tup_ins as inserts,
    n_tup_upd as updates,
    n_tup_del as deletes,
    n_tup_hot_upd as hot_updates
FROM pg_stat_user_tables
WHERE tablename = 'sensor_data';

-- 查看更新比例
SELECT 
    tablename,
    n_tup_upd,
    n_tup_ins,
    round(100.0 * n_tup_upd / NULLIF(n_tup_ins + n_tup_upd, 0), 2) as update_ratio
FROM pg_stat_user_tables
WHERE tablename = 'sensor_data';

-- 监控更新执行时间
EXPLAIN ANALYZE
UPDATE sensor_data
SET temperature = 26.0
WHERE time > NOW() - INTERVAL '1 hour';

-- 查看锁等待
SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- 问题：大批量更新超时
-- 解决：分批更新

DO $$
DECLARE
    batch_size INTEGER := 10000;
    updated INTEGER;
BEGIN
    LOOP
        UPDATE sensor_data
        SET status = 'archived'
        WHERE status IS NULL
          AND time < NOW() - INTERVAL '90 days'
        LIMIT batch_size;
        
        GET DIAGNOSTICS updated = ROW_COUNT;
        EXIT WHEN updated = 0;
        
        COMMIT;
    END LOOP;
END $$;

-- 问题：无法更新压缩数据
-- 错误：cannot update a compressed chunk

-- 解决：先解压
SELECT decompress_chunk('_hyper_1_1_chunk');

-- 执行更新
UPDATE sensor_data SET ...;

-- 重新压缩
SELECT compress_chunk('_hyper_1_1_chunk');

-- 问题：更新性能差
-- 解决：

-- 1. 检查索引
\di sensor_data

-- 2. 检查执行计划
EXPLAIN ANALYZE UPDATE ...;

-- 3. 确保WHERE条件使用索引

-- 4. 考虑使用DELETE + INSERT