TimeScaleDB物化行太大

Question

摘要

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

描述

名为PAC4200_Metering的表有108个字段，它们都是双精度字段。我想存储表的物化缓存，在其中存储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对象填充为JSON对象，而是尝试将每个键存储在JSON对象中(平均、最小、最大等)。作为类似于"voltage_an_avg“、"voltage_an_min”、"voltage_an_max“的字段，然后以这种方式访问它们，但我仍然遇到同样的限制。

主要问题/解决办法

• 有办法提高这个行的大小限制吗？(如果这是一个很好的实践)
• 是否有更好的方法来存储这些值.

Answer 1

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

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

由于行限制在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的效率。