Strategies for reducing data to summaries within a table over time?
I would simply call it "aggregation".

As for elegance, you can do this with a single SQL command without another table using a data-modifying CTE:

For (only) hourly aggregation of rows that are older than a week:

WITH del AS (
   DELETE FROM data d
   WHERE  stamp &lt; now() - interval '7 days'   -- "&gt;" would be wrong
   AND   (date_trunc('hour', stamp) &lt;&gt; stamp  -- not truncated yet.
          OR EXISTS (  -- more than one in same hour
             SELECT 1
             FROM   data
             WHERE  date_trunc('hour', stamp) = date_trunc('hour', d.stamp)
             AND    ctid &lt;&gt; d.ctid   -- except self
             )
         )
   RETURNING *
   )
INSERT INTO data(stamp, data, count) 
SELECT date_trunc('hour', stamp), data, sum(count)
FROM   del
GROUP  BY 1,2;


Only replaces rows that need replacing. Repeated calls would not repeat writes.

If you know a lower bound where all rows have been aggregated already, add another WHERE condition to make it faster:

AND stamp &gt; $lower_bound


Obviously, you need an index on stamp or, ideally, a partial index only including rows younger than $lower_bound that would need to be recreated from time to time. Related:


PostgreSQL partial index unused when created on a table with existing data
Add datetime constraint to a PostgreSQL multi-column partial index


Using ctid since no PK is defined.


In-order sequence generation
How do I decompose ctid into page and row numbers?


You may want to VACUUM FULL data; to reclaim table bloat after that. But be aware of implications. Maybe pg_repack is for you:


VACUUM returning disk space to operating system


Aside: DISTINCT and GROUP BY was redundant in your original query. And you were aggregating rows younger than a week instead of older.