Monthly cumulative totals of map tile usage for the last year:
SELECT year, month, max(count) AS cumulative_total FROM (
SELECT time, extract(year from time) AS year,
extract(month from time) AS month,
extract(day from time) AS day,
count FROM tile_metric ORDER BY time DESC) AS q
GROUP BY q.year, q.month ORDER BY q.year desc, q.month DESC LIMIT 12;
Count of expired tiles:
SELECT count(*) FROM tile WHERE expires < now();
Count of unexpired tiles;
SELECT count(*) FROM tile WHERE expires >= now();
Count of expired tiles older than 90 days:
SELECT count(*) FROM tile WHERE expires < now() AND updated < now()::timestamp::date - INTERVAL '90 days';
Delete expired tiles older than 90 days:
DELETE FROM tile WHERE expires < now() AND updated < now()::timestamp::date - INTERVAL '90 days';
Delete all expired tiles:
DELETE FROM tile WHERE expires < now();