sql - TimeScaleDB 物化行太大

Question

总结

我遇到了一个问题，即物化 View 的行限制超过了 Postgres 数据库中允许的最大值。

描述

名为 PAC4200_Metering 的表有 108 个字段，它们都是 double 字段。我想存储表的具体化缓存，我在其中存储一个 JSON 对象，其中包含时间段内字段的平均值、最大值、最小值等键。

SELECT
  "deviceId",
  time_bucket('1 hours', "time") as starttime,
  json_build_object(
   'average', avg("voltage_an"),
   'maxvalue', max("voltage_an"),
   'minvalue', min("voltage_an"),
   'sum', sum("voltage_an"),
   'firstvalue', first("voltage_an", "time"),
   'firsttime', min("time" AT TIME ZONE 'UTC'),
   'lasttime', max("time" AT TIME ZONE 'UTC'),
   'lastvalue', last("voltage_an", "time"),
   'sd', stddev_pop("voltage_an") ,
   'countgood', COUNT(*),
   'countbad', 0,
   'countuncertain', 0
  ) AS "voltage_an"
  ...
FROM
  "PAC4200_Metering"
GROUP BY
  "deviceId",
  starttime

错误响应:

INFO:  new materialization range for public.PAC4200_Metering larger than allowed in one run, truncating (time column time) (1568760300000000)
INFO:  new materialization range for public.PAC4200_Metering (time column time) (1568708100000000)
INFO:  materializing continuous aggregate public.PAC4200_Metering_15_minute: new range up to 1568708100000000

ERROR:  row is too big: size 12456, maximum size 8160
CONTEXT:  SQL statement "INSERT INTO _timescaledb_internal._materialized_hypertable_1108 SELECT * FROM _timescaledb_internal._partial_view_1108 AS I WHERE I.starttime >= '-infinity' AND I.starttime < '2019-09-17 08:15:00+00';"
SQL state: 54000

我尝试过的替代方法

我没有填充为 JSON 对象，而是尝试将每个键(平均值、最小值、最大值等)存储为类似于“voltage_an_avg”、“voltage_an_min”、“voltage_an_max”和然后以这种方式访问​​它们，但我仍然遇到同样的限制。

主要问题/解决方案

• 有没有办法增加行大小限制？ (如果这是好的做法)
• 有没有更好的方法来存储这些值...

Answer 1

您不能更改行限制，因为每行必须适合一个页面，即 8K。

由于值是 double 字段，将它们放入 JSON 中可能会丢失数字精度。这是一般 JSON 格式的限制。因此，您需要放弃在连续聚合中使用 JSON 或重新考虑类型，以便它们适合 JSON 的 NUMERIC 字段(单精度)。

由于行限制为 8160 字节，因此可以容纳大约 1000 个字段。如果您避免具体化聚合(稍后可以计算)，这可能足以适合您的所有列。例如，average 可以根据 sum 和 countgood 计算得出。虽然 countbad 和 countuncertain 在您的示例中不包含任何信息。

如果OP查询用于创建连续聚合，可以改写为:

SELECT
  "deviceId",
  time_bucket('1 hours', "time") as starttime,
  max("voltage_an") as maxvalue,
  min("voltage_an") as minvalue,
  sum("voltage_an") as sum,
  first("voltage_an", "time") as firstvalue,
  min("time" AT TIME ZONE 'UTC') as firsttime,
  max("time" AT TIME ZONE 'UTC') as lasttime,
  last("voltage_an", "time") as lastvalue,
  stddev_pop("voltage_an") as sd,
  COUNT(*) countgood,
  ...
FROM
  "PAC4200_Metering"
GROUP BY
  "deviceId",
  starttime

如果 voltage_an 是 108 个不同值之一，并且上面计算了 8 个聚合，则它将是 108*8*8 + 3*8 = 6912 + 24 = 6936 字节最大值。

然后你可以从连续聚合中得到与原始查询相同的结果:

SELECT
  "deviceId",
  starttime,
  json_build_object(
   'average', "sum"/"countgood",
   'maxvalue', "maxvalue",
   'minvalue', "minvalue",
   'sum', "sum",
   'firstvalue', "firstvalue",
   'firsttime', "firsttime",
   'lasttime', "lasttime",
   'lastvalue', "lastvalue",
   'sd', "sd",
   'countgood', "countgood",
   'countbad', 0,
   'countuncertain', 0
  ) AS "voltage_an"
  ...
FROM
  CAGG

也可以定义几个连续聚合，然后将它们连接起来。

我建议仔细考虑需要具体化多少信息，因为没有免费的东西。例如，它占用空间。此外，让每一行占据整个 8K 页面会进一步影响 PostgreSQL 的效率。