Re^4: Help on selecting and deleting many records in a DB.

[neilwatson]

 
SELECT * FROM agent_log t1
WHERE timestamp < now() - interval '7 days' 
AND timestamp < ( 
   SELECT max(timestamp)
   FROM agent_log t2
   WHERE t2.class=t1.class AND t2.promiser=t1.promiser
    AND t2.hostname=t1.hostname AND t2.ip_address=t1.ip_address
)
ORDER by class,timestamp;

You almost had me there, but I don't think this will keep the maximum timestamp per day.

[vincent_veyron]

Well, it does if you run it every day and clean up afterwards, with a cron job for instance.

If possible, it seems you could solve a big part of your problem using partitioning of your table, probably by day; see:
http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html