在 PostgreSQL 中存储价值历史 [英] Store value history in PostgreSQL

问题描述

假设我们需要每天存储温度传感器的读数并保存历史记录.每个度量都是一个元组 day: number, value: string(可能会错过某些日子，这就是我们需要明确存储日期的原因).有数十万个传感器.

Let's say we need to store reading of temperature sensors and keep a history, for every day. Each measure is a tuple day: number, value: string (some days could be missed that's why we need to store the day explicitly). There are hundreds of thousands of sensors.

添加新的度量应该不需要重新读取和重写整个对象，应该是小增量添加.

Adding new measurement should not require re-reading and re-write the whole object, it should be small incremental addition.

而且，同一天可能会有多次阅读.在这种情况下，如果日期相同只应保留当天的最新测量值.

And, there could be multiple reading for the same day. In such case, if day is the same only the latest measurement for that day should be kept.

应该使用什么数据结构?我可以看到以下方式:

What data structure should be used? I can see the following ways:

CREATE TABLE sensor_history (
  sensor_id integer,
  time      integer[],
  value     text[]
);

或

CREATE TABLE sensor_history (
  sensor_id integer,
  history   json/jsonb/hstore
);

推荐答案

为什么不只为每个元组存储一行?例如

Why not just store one row per tuple? e.g.

CREATE TABLE sensor_history (
  sensor_id integer,
  time      integer,
  value     text
);

这篇关于在 PostgreSQL 中存储价值历史的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！