If we’ve gotta take the database down for maintenance – perhaps a version upgrade, perhaps upgrading our own code, maybe scaling up the hardware – when’s the best time to do it?
For this week’s query exercise, the business has asked us to identify 3 1-hour periods with the least user activity, in this format:
- Mondays, 1:00-1:59
- Saturdays, 23:00-23:59
- Sundays, 2:00-2:59
Pop open the Stack Overflow database, and for the sake of simplicity for this exercise, we’re just going to look at the users table. We’ll judge load by times when the fewest number of users are created, judging by the users.creationdate column.
Pick a recent 28-day window so that you’re looking at several weeks of trending activity, and come up with your one-hour preferences.
The performance of your query isn’t a concern here – this isn’t a query we’ll be running on a regular basis. We just need to know the quietest times in the database.
You can post your answers in this blog post’s comments, and discuss each others’ ideas. You can also use <pre></pre> tags in your comments to frame code, like this:
<pre>
select stuff from tables
</pre>
We’ll revisit your answers in the next post. Have fun!
2 Comments. Leave new
Nice little exercise. 🙂
select concat(dow_name, 's, ', h::text, ':00-', h::text, ':59') from ( select extract(hour from creationdate) as h, extract(dow from creationdate) as dow, 1 as creation_count from users where creationdate > ('2023-12-03'::date - interval '28 days') ) as creations right join ( select h, dow, trim(to_char(('2024-01-28'::date + dow), 'Day')) as dow_name from generate_series(0, 6) as dow (dow) cross join generate_series(0, 23) as h (h) ) as hours using (h, dow) group by dow, dow_name, h order by coalesce(sum(creation_count), 0) asc limit 3;This is actually reasonably performant, it completes in under 4 seconds for this most current interval. The only tricky join is the right join followed by summing on the possibly null creation_count column, to account for the fact that it’s possible that in some one-hour periods, there were no user creations at all, in which case we want to avoid excluding them — as those periods are exactly what we’re interested in!
Colin – thanks, glad you liked it! Your answer is bang on – the important thing is to understand that there could be 1-hour periods with no users created whatsoever, and those are what we’re interested in. Good job!