TimescaleDB时序场景：插入或查询阶段填充价格数据间隙？

插入时填充 vs 查询时填充：哪种更优？ #

查询时填充是更优选择，原因如下：

• 存储效率更高：仅保留价格变化的原始记录，避免冗余数据（比如价格数日不变时，无需插入数十条重复的小时记录），契合时间序列数据的存储最佳实践。
• 逻辑复杂度更低：插入流程无需额外判断和批量生成填充数据，减少出错概率；后续若需调整时间粒度（比如从1小时改为30分钟），直接修改查询语句即可，无需回溯修改历史数据。
• 数据准确性更强：原始数据仅保留真实的价格变动记录，填充逻辑在查询时动态执行，不会因插入阶段的逻辑错误导致数据不一致。

仅当面临极端高频查询且完全无法接受任何查询计算开销时，才考虑插入时填充，但这种场景极少，且会带来后续维护的诸多麻烦。

先纠正你的表结构问题 #

你的原表定义中item_id设为SERIAL PRIMARY KEY，意味着每个商品只能有一条记录，完全无法实现“每小时插入同一商品价格”的需求。正确的表结构应为：

CREATE TABLE prices (
    id SERIAL PRIMARY KEY,
    item_id INT NOT NULL,
    price DECIMAL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(item_id, timestamp) -- 避免同一商品同一时间重复插入
);

SELECT create_hypertable('prices', 'timestamp');

TimescaleDB的简便实现方法 #

TimescaleDB提供了原生的time_bucket_gapfill函数，专门用于时间序列的间隙填充，搭配last()聚合函数可轻松实现向前填充前一次价格：

SELECT
    item_id,
    time_bucket_gapfill('1 hour', timestamp, NOW() - INTERVAL '24 hours', NOW()) AS hour,
    last(price) AS filled_price
FROM prices
WHERE item_id = 1234
GROUP BY item_id, hour
ORDER BY hour;

函数说明： #

• time_bucket_gapfill：生成指定时间粒度的连续时间桶，自动填补原始数据中的时间间隙，参数依次为粒度、时间字段、起始时间、结束时间。
• last(price)：在每个时间桶中，若没有对应价格记录，会自动取最近的非空价格值进行填充，实现“用前一次价格补全间隙”的需求。

若需要更灵活的自定义逻辑（比如调整填充范围），也可以用CTE生成连续时间桶后左连接再用窗口函数填充：

WITH hourly_buckets AS (
    -- 生成过去24小时的连续小时桶
    SELECT time_bucket('1 hour', ts) AS hour
    FROM generate_series(
        NOW() - INTERVAL '24 hours',
        NOW(),
        INTERVAL '1 hour'
    ) AS ts
),
item_raw_prices AS (
    -- 按小时聚合目标商品的价格记录
    SELECT
        time_bucket('1 hour', timestamp) AS hour,
        price
    FROM prices
    WHERE item_id = 1234
      AND timestamp >= NOW() - INTERVAL '24 hours'
)
SELECT
    1234 AS item_id,
    h.hour,
    -- 向前填充最近的非空价格
    LAST_VALUE(ip.price IGNORE NULLS) OVER (ORDER BY h.hour) AS filled_price
FROM hourly_buckets h
LEFT JOIN item_raw_prices ip ON h.hour = ip.hour
ORDER BY h.hour;

内容的提问来源于stack exchange，提问作者TreeWater