performance - 保留历史记录和当前概览

Question

我正在使用一小部分网络抓取工具来获取各种设备的当前 GPS 位置。我也想保留历史记录。在不将数据存储两次的情况下执行此操作的最佳方法是什么？现在我有两张 table ，看起来都像这样:

 Column  |            Type             |   Modifiers   | Storage  | Description
---------+-----------------------------+---------------+----------+-------------
 vehicle | character varying(20)       |               | extended |
 course  | real                        |               | plain    |
 speed   | real                        |               | plain    |
 fix     | smallint                    |               | plain    |
 lat     | real                        |               | plain    |
 lon     | real                        |               | plain    |
 time    | timestamp without time zone | default now() | plain    |

一个名为gps，另一个名为gps_log。更新这两者的函数做了两件事:首先它在 gps_log 上执行一个 INSERT，然后它执行一个 UPDATE OR INSERT(一个用户-定义函数)在 gps 上。但是，这导致在我看来，为了其他目的而进行双重存储是毫无意义的，而不是为了轻松 SELECT 访问当前数据。

有没有一种简单的方法只使用 gps_log 并让一个函数只为每个 vehicle 选择最新的条目？请记住，gps_log 目前有 1397150 行，每 15 分钟大约增加 150 行，因此性能可能会成为问题。

通过 Perl DBI 使用 PostgreSQL 8.4。

Answer 1

如果 SELECT 性能是最重要的，那么您当前使用冗余存储的解决方案可能不是一个坏主意。

如果你去掉冗余表，你可以用 multi-column index 帮助 SELECT 性能喜欢:

CREATE INDEX gps_log_vehicle_time ON gps_log (vehicle, time DESC);

假设 vehicle 是您的主键。
会使相应的查询非常快:

SELECT *
FROM   gps_log
WHERE  vehicle = 'foo'
ORDER  BY time DESC
LIMIT  1;

要SELECT 多行或所有行的最后一个条目，use this related technique .

不过，总存储大小可能会增长，因为如果每辆车有很多行，索引将比冗余表(+ 索引)更大。

添加串行列作为代理主键而不是 vehicle 可能有助于存储和性能。特别是如果您有指向它的外键。

另外:不要使用 time 作为列名。它是 PostgreSQL 中的类型名称和 reserved word在每个 SQL 标准中。将 timestamp 列命名为 time 也会产生误导。