We have a largish PostgreSQL table that records user's disk usage.
id | bigint | | not null | (unique constraint) time | timestamp without time zone | | not null | quota | bigint | | not null | 0 usage | bigint | | not null | 0 zuser | character varying(20) | | not null |
We present data usage to users when they login, and we report on data usage up the org.
In order for the data to be reliable for users, we poll our storage every five minutes. But for our reporting we only need a smaller sample size. We would like to keep some of the data for internal use as well.
Our plan is to keep the month of data, and the last reading for each Sunday night for everything older than that. Because it's data usage, we don't want an average, we just want their last reading before midnight Sunday turns into Monday.
I have discovered
date_trunc and have even an idea for an algorithm that includes something along the lines of
SELECT date_trunc('week', time), zuser, quota, usage GROUP BY zuser ORDER BY date_trunc('week', time) DESC
But I'm struggling to work out how to just get a single entry, and how to loop over all data back in time. I could do it in Python, but it seems a little clumsy and then it's something that needs to be run regularly.
In my head I had some sort of database trigger - a PostgreSQL event hitting a function every night at midnight that cleans up (NOW()-1 month)? - that would do it all.
But at the moment I'm looking for help on how to down sample each week into the single reading that happens at the end of that week.
----- edit -----
I ended up coming to
SELECT * FROM storage.home WHERE time < date_trunc('month', CURRENT_DATE - interval '1' month) EXCEPT SELECT * FROM storage.home WHERE TIME '24:00:00' - CAST(time AS time) < INTERVAL '6 minutes' AND extract (dow FROM time) = 0;
But when I change that to a DELETE, it fails on the EXCEPT clause because (I presume) it's no longer a
SELECT...EXCEPT SELECT but rather a
DELETE...EXCEPT SELECT which is non grammatical. Searching for
DELETE EXCEPT is not simple due to language.
Is what I should be doing something more like this:
DELETE FROM storage.home WHERE id IN (SELECT id FROM storage.home WHERE time < date_trunc('month', CURRENT_DATE - interval '1' month) EXCEPT SELECT id FROM storage.home WHERE TIME '24:00:00' - CAST(time AS time) < INTERVAL '6 minutes' AND extract (dow FROM time) = 0);
Yes. I took a backup and ran the above. Worked exactly as expected.
I considered the below for the left side of that
EXCEPT because it's more true to needs, but I thought I'd start with the less accurate.
WHERE date_trunc('day', time) = CURRENT_DATE - interval '35 days')