postgresql - 从 Influx 迁移到 Postgres，需要提示

Question

我使用 Influx 来存储我们的时间序列数据。当它工作时很酷，然后大约一个月后，它停止工作，我不明白为什么。 (类似本期https://github.com/influxdb/influxdb/issues/1386)

也许有一天 Influx 会很棒，但现在我需要使用更稳定的东西。我在考虑Postgres。我们的数据来自许多传感器，每个传感器都有一个传感器 ID。所以我正在考虑按如下方式构建我们的数据:

(pk), sensorId(string), time(timestamp), 值(float)

Influx 是为时间序列数据构建的，因此它可能有一些内置的优化。我是否需要自己进行优化以使 Postgres 高效？更具体地说，我有以下问题:

1. Influx 具有“系列”的概念，并且创建新系列的成本很低。所以我为每个传感器都有一个单独的系列。我应该为每个传感器创建一个单独的 Postgres 表吗？

2. 我应该如何设置索引以加快查询速度？一个典型的查询是:选择 sensor123 最近 3 天的所有数据。

3. 我应该为时间列使用时间戳还是整数？

4. 如何设置保留策略？例如。自动删除超过一周的数据。

5. Postgres 会横向扩展吗？我可以为数据复制和负载平衡设置 ec2 集群吗？

6. 我可以在 Postgres 中进行缩减采样吗？我在一些文章中读到我可以使用 date_trunc。但似乎我无法将它 date_trunc 到特定的时间间隔，例如25 秒。

7. 还有其他我遗漏的注意事项吗？

提前致谢!

更新将时间列存储为大整数比将其存储为时间戳更快。我做错了什么吗？

将其存储为时间戳:

postgres=# explain analyze select * from test where sensorid='sensor_0';

Bitmap Heap Scan on test  (cost=3180.54..42349.98 rows=75352 width=25) (actual time=10.864..19.604 rows=51840 loops=1)
   Recheck Cond: ((sensorid)::text = 'sensor_0'::text)
   Heap Blocks: exact=382
   ->  Bitmap Index Scan on sensorindex  (cost=0.00..3161.70 rows=75352 width=0) (actual time=10.794..10.794 rows=51840 loops=1)
         Index Cond: ((sensorid)::text = 'sensor_0'::text)
 Planning time: 0.118 ms
 Execution time: 22.984 ms

postgres=# explain analyze select * from test where sensorid='sensor_0' and addedtime > to_timestamp(1430939804);

 Bitmap Heap Scan on test  (cost=2258.04..43170.41 rows=50486 width=25) (actual time=22.375..27.412 rows=34833 loops=1)
   Recheck Cond: (((sensorid)::text = 'sensor_0'::text) AND (addedtime > '2015-05-06 15:16:44-04'::timestamp with time zone))
   Heap Blocks: exact=257
   ->  Bitmap Index Scan on sensorindex  (cost=0.00..2245.42 rows=50486 width=0) (actual time=22.313..22.313 rows=34833 loops=1)
         Index Cond: (((sensorid)::text = 'sensor_0'::text) AND (addedtime > '2015-05-06 15:16:44-04'::timestamp with time zone))
 Planning time: 0.362 ms
 Execution time: 29.290 ms

将其存储为大整数:

postgres=# explain analyze select * from test where sensorid='sensor_0';


 Bitmap Heap Scan on test  (cost=3620.92..42810.47 rows=85724 width=25) (actual time=12.450..19.615 rows=51840 loops=1)
   Recheck Cond: ((sensorid)::text = 'sensor_0'::text)
   Heap Blocks: exact=382
   ->  Bitmap Index Scan on sensorindex  (cost=0.00..3599.49 rows=85724 width=0) (actual time=12.359..12.359 rows=51840 loops=1)
         Index Cond: ((sensorid)::text = 'sensor_0'::text)
 Planning time: 0.130 ms
 Execution time: 22.331 ms

postgres=# explain analyze select * from test where sensorid='sensor_0' and addedtime > 1430939804472;


 Bitmap Heap Scan on test  (cost=2346.57..43260.12 rows=52489 width=25) (actual time=10.113..14.780 rows=31839 loops=1)
   Recheck Cond: (((sensorid)::text = 'sensor_0'::text) AND (addedtime > 1430939804472::bigint))
   Heap Blocks: exact=235
   ->  Bitmap Index Scan on sensorindex  (cost=0.00..2333.45 rows=52489 width=0) (actual time=10.059..10.059 rows=31839 loops=1)
         Index Cond: (((sensorid)::text = 'sensor_0'::text) AND (addedtime > 1430939804472::bigint))
 Planning time: 0.154 ms
 Execution time: 16.589 ms

Answer 1

您不应该为每个传感器创建一个表。相反，您可以在表中添加一个字段来标识它属于哪个系列。您还可以有另一个表来描述有关该系列的其他属性。如果数据点可以属于多个系列，那么您将需要一个完全不同的结构。

对于您在 q2 中描述的查询，您的 recorded_at 列上的索引应该有效(time 是 sql 保留关键字，因此最好避免将其作为名称)

您应该使用 TIMESTAMP WITH TIME ZONE 作为您的时间数据类型。

保留由您决定。

Postgres 有多种分片/复制选项。这是一个很大的话题。

不确定我是否理解您对 #6 的目标，但我相信您能想出办法。