如何构建TimescaleDB表以高效获取多键最新库存值

针对你数百万行规模的库存表，要按MERCHANT_ID、COUNTRY_CODE和指定PRODUCT_ID列表获取最新库存值，以下是分优先级的性能优化方案：

一、创建针对性复合索引（核心优化） #

当前仅DATE列有索引，完全无法支撑按商户、国家、产品过滤的查询，必须先创建复合B树索引：

CREATE INDEX idx_inventory_latest ON inventory_table 
(MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID, DATE DESC);

索引作用： #

• 前三个列对应你的查询过滤条件，数据库可以快速定位到目标商户、国家下的指定产品组；
• DATE DESC让每个产品组的最新记录排在索引最前面，无需额外排序就能直接获取最新库存，避免全表扫描或大范围数据排序。

二、高效查询写法 #

根据是否需要返回无记录的产品（即即使产品没有库存数据也要显示PRODUCT_ID和NULL库存），分两种写法：

1. 仅返回有记录的产品（高效简洁） #

用PostgreSQL原生的DISTINCT ON语法，配合上面的索引可以做到毫秒级查询：

SELECT DISTINCT ON (MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID)
       MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID, INVENTORY
FROM inventory_table
WHERE MERCHANT_ID = 2
  AND COUNTRY_CODE = 'US'
  AND PRODUCT_ID IN (1,2,3)
ORDER BY MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID, DATE DESC;

2. 强制返回指定产品列表（含无记录产品） #

如果需要即使产品没有库存记录也要出现在结果中（库存显示NULL），可以用VALUES生成目标产品列表，再左连接最新库存查询：

WITH target_products AS (
  SELECT 2 AS MERCHANT_ID, 'US' AS COUNTRY_CODE, UNNEST(ARRAY[1,2,3]) AS PRODUCT_ID
)
SELECT tp.MERCHANT_ID, tp.COUNTRY_CODE, tp.PRODUCT_ID, inv.INVENTORY
FROM target_products tp
LEFT JOIN (
  SELECT DISTINCT ON (MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID)
         MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID, INVENTORY
  FROM inventory_table
  ORDER BY MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID, DATE DESC
) inv ON tp.MERCHANT_ID = inv.MERCHANT_ID
     AND tp.COUNTRY_CODE = inv.COUNTRY_CODE
     AND tp.PRODUCT_ID = inv.PRODUCT_ID;

三、连续聚合视图（高频查询场景终极优化） #

如果你的查询是高频触发，且可以接受分钟级延迟（不需要实时到秒的最新数据），可以创建TimescaleDB的连续聚合视图，预计算每个(MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID)的最新库存：

1. 创建连续聚合视图 #

CREATE MATERIALIZED VIEW inventory_latest_agg
WITH (timescaledb.continuous) AS
SELECT MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID,
       LAST(INVENTORY, DATE) AS INVENTORY,
       MAX(DATE) AS latest_date
FROM inventory_table
GROUP BY MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID;

2. 设置自动刷新策略 #

让聚合视图定期自动刷新，比如每5分钟刷新一次：

SELECT add_continuous_aggregate_policy('inventory_latest_agg',
  start_offset => INTERVAL '1 hour',
  end_offset => INTERVAL '0 minutes',
  schedule_interval => INTERVAL '5 minutes');

3. 查询聚合视图 #

直接查询预计算好的结果，速度比原表查询快一个数量级：

SELECT MERCHANT_ID, COUNTRY_CODE, PRODUCT_ID, INVENTORY
FROM inventory_latest_agg
WHERE MERCHANT_ID = 2
  AND COUNTRY_CODE = 'US'
  AND PRODUCT_ID IN (1,2,3);

如果需要包含无记录的产品，同样用target_products CTE左连接这个聚合视图即可。

方案选择建议 #

• 实时性要求高：优先用「复合索引 + DISTINCT ON查询」，写入时的索引开销在数百万行规模下完全可控；
• 查询频率极高且允许延迟：用「连续聚合视图」，把查询开销转移到后台刷新任务，前台查询几乎无开销。

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