一个例子是在旅行期间对汽车进行建模并跟踪其各种属性.例如：
时间戳|速度|旅行距离|温度|等等
存储此数据的最佳方法是什么,以便Web应用程序可以有效地查询字段以查找最长,分钟和绘制每个数据集的时间？
我开始了一种简单的方法来解析数据转储并缓存结果,以便永远不必存储它们.然而,在使用它之后,由于内存限制,此解决方案似乎不会长期扩展,如果要清除缓存,则需要重新解析和重新缓存所有数据.
另外,假设每秒钟跟踪数据的可能性很小,10小时数据集,是否通常建议每隔N秒采样一次截断数据集？
(1)这个项目有多重要,它值得您优化架构？
(2)您的查询访问模式真的会是什么样的？
考虑到这些问题,让我们讨论一些模式选项.
平表
使用平面表的选项与问题(1)有很多关系,如果这不是一个严肃的或大规模的项目,你会发现更容易不去思考模式,以及只需使用一张平台,如：
CREATE flat_table( trip_id integer,tstamp timestamptz,speed float,distance float,temperature float,...);
我推荐这门课程的情况并不多,只有这是一个很小的项目,不能保证你的大部分时间.
维度和事实
因此,如果您已经清除了问题(1)的障碍,并且您想要更高性能的架构,那么这是首先要考虑的选项之一.它包括一些基本的规范化,但从测量的“事实”数量中提取“维度”数量.
基本上,你需要一个表来记录有关旅行的信息,
CREATE trips( trip_id integer,other_info text);
和一个记录时间戳的表,
CREATE tstamps( tstamp_id integer,tstamp timestamptz);
最后所有测量的事实,对维度表的外键引用(即meas_facts(trip_id)引用trip(trip_id)& meas_facts(tstamp_id)引用tstamps(tstamp_id))
CREATE meas_facts( trip_id integer,tstamp_id integer,...);
这看起来似乎没有那么有用,但如果你有数千个并发旅行,那么他们可能每秒都进行一次测量,第二次.在这种情况下,您必须每次为每次旅行重新记录时间戳,而不是仅使用tstamps表中的单个条目.
使用案例：如果您要记录数据的并发行程很多,并且您不介意同时访问所有测量类型,则此情况会很好.
由于Postgres按行读取,任何时候你需要,例如,在给定时间范围内的速度测量,你必须从meas_facts表读取整行,这肯定会减慢查询速度,不过如果数据集你正在工作与不是太大,那么你甚至不会注意到差异.
拆分您的测量事实
为了进一步扩展最后一部分,您可以将测量分解为单独的表,我将显示表格的速度和距离：
CREATE speed_facts( trip_id integer,speed float);
和
CREATE distance_facts( trip_id integer,distance float);
当然,您可以看到如何将其扩展到其他测量.
使用案例：因此,这不会为您提供极大的查询速度,当您查询一种测量类型时,可能只是速度的线性增加.这是因为当您想要查找有关速度的信息时,您只需要读取speed_facts表中的行,而不是在meas_facts表的行中存在的所有额外的,不需要的信息.
因此,您只需阅读有关一种测量类型的大量数据,您就可以获得一些好处.建议您以一秒为间隔的10小时数据的情况下,您只能阅读36,000行,因此您从未真正从中获得显着的好处.但是,如果您要查看大约10个小时的5,000次旅行的速度测量数据,那么现在您正在寻找1.8亿行.只要您一次只需要访问一个或两个测量类型,这种查询的速度线性增加就可以产生一些好处.
数组/ HStore /&土司
您可能不需要担心这部分,但我知道它确实重要的情况.如果您需要访问大量的时间序列数据,并且您知道需要在一个巨大的块中访问所有这些数据,则可以使用将使用TOAST Tables的结构,该结构实质上将您的数据存储在较大的压缩段中.只要您的目标是访问所有数据,这样可以更快地访问数据.
一个示例实现可以是
CREATE uber_table( trip_id integer,tstart timestamptz,speed float[],distance float[],temperature float[],...);
在此表中,tstart将存储数组中第一个条目的时间戳,并且每个后续条目将是下一秒的读数值.这要求您在一个应用程序软件中管理每个数组值的相关时间戳.
另一种可能性是
CREATE uber_table( trip_id integer,speed hstore,distance hstore,temperature hstore,...);
将测量值添加为(键,值)对(时间戳,测量).
使用案例：这个实现可能最好留给对Postgresql更熟悉的人,并且只有当您确定您的访问模式需要批量访问模式时.
结论是什么？
哇,这比我想象的要长得多,抱歉.
