PostgreSQL删除除最旧记录之外的所有记录

SELECT DISTINCT ON (devicename,objectid) *
FROM   tbl
ORDER  BY devicename,objectid,ts DESC;

细节和解释in this related answer.

从您的示例数据中,我得出结论,您将删除原始表的大部分内容.它可能更快到TRUNCATE表(或者DROP& recreate,因为你应该添加一个代理pk列)并将剩余的行写入其中.这也将为您提供一个prestine表,以最适合您的查询的方式隐式聚类(排序),并保存VACUUM必须要做的工作.它总体上可能更快：

我还强烈建议在您的表中添加一个代理主键,最好是serial列.

BEGIN;

CREATE TEMP TABLE tmp_tbl ON COMMIT DROP AS
SELECT DISTINCT ON (devicename,ts DESC;

TRUNCATE tbl;
ALTER TABLE tbl ADD column tbl_id serial PRIMARY KEY;

-- or,if you can afford to drop & recreate:
-- DROP TABLE tbl;
-- CREATE TABLE tbl (
--   tbl_id serial PRIMARY KEY
--,address text
--,devicename text
--,objectid text
--,ts timestamp);

INSERT INTO tbl (address,devicename,ts)
SELECT address,ts
FROM   tmp_tbl;

COMMIT;

在交易中完成所有操作,以确保您不会在中途失败.

只要您的temp_buffers设置足以容纳临时表,这就很快.否则系统将开始将数据交换到磁盘,性能会下降.您可以为当前会话设置temp_buffers,如下所示：

SET temp_buffers = 1000MB;

所以你不要浪费你通常不需要temp_buffers的RAM.必须在会话中第一次使用临时对象之前.更多信息,请参见this related answer.

此外,由于INSERT在事务中遵循TRUNCATE,因此在Write Ahead Log上将很容易 – 提高性能.

考虑使用CREATE TABLE AS替代路由：

> What causes large INSERT to slow down and disk usage to explode?

唯一的缺点：你需要在桌子上独家锁定.这可能是具有大量并发负载的数据库中的问题.

最后,永远不要使用时间戳作为列名.它是每个SQL标准中的reserved word和PostgreSQL中的类型名称.您可能已经注意到我将该列重命名为ts.