How to get the count of hourly inserts on a table in PostgreSQL

Question

I need a setup where rows older than 60 days get removed from the table in PostgreSQL.

I Have created a function and a trigger:

BEGIN
  DELETE FROM table
    WHERE updateDate < NOW() - INTERVAL '60 days';
  RETURN NULL;
END;
$$;

But I believe if the insert frequency is high, this will have to scan the entire table quite often, which will cause high DB load. I could run this function through a cron job or Lambda function every hour/day. I need to know the insert every hour on that table to take that decision.

Is there a query or job that I can setup which will collect the details?

question from:https://stackoverflow.com/questions/65936325/how-to-get-the-count-of-hourly-inserts-on-a-table-in-postgresql

Answer 1

Just to count the number of records per hour, you could run this query:

SELECT  CAST(updateDate AS date) AS day
    ,   EXTRACT(HOUR FROM updateDate) AS hour
    ,   COUNT(*)
FROM _your_table
WHERE   updateDate BETWEEN ? AND ?
GROUP BY
    1,2
ORDER BY
    1,2;

We do about 40 million INSERT's a day on a single table, that is partitioned by month. And after 3 months we just drop the partition. That is way faster than a DELETE.