How can I aggregate data by day and still respect the time zone?

Question

We currently use a pivot table that aggregates information for our users hourly in UTC. The problem we are facing is that this table becomes too large and slows down our system significantly. We have followed all the configuration methods recommended for PostgreSQL, and we are still experiencing slowness.

Our idea was to start aggregation in the afternoon, and not in an hour, but the problem is that we allow our customers to change the time zone that recalculates the data for that day.

Does anyone know a way to store a daily resume, but still respect the numbers and totals when switching time zones?

Answer 1

Summarize the data in the tables with a column based on time and the "day" (date) field, which is the day for this particular summary row. An index on (timeoffset, day, other relevant fields), clustered, if possible (presumably PostgresSQL has clustered indexes?), And everything should be fine.

Answer 2

I assume that you have considered all partitioning considerations, such as user partitioning.

I see several solutions to your problem depending on the usage pattern.

• Aggregate data per day, for each user choice. If you change the time zone, programmatically recount the population for this partner. This is plausible if time zone changes are infrequent and if some delay in the data can be introduced when the user changes the time intervals.

• If you have relatively few measures, you can maintain 24 columns for each measure — each of which describes a daily population for a measurement in a different time zone.

• If time zone changes are frequent, and there are many measures, it looks like 24 different placeholder tables will be the way to go.

Answer 3

I also met this problem. I make this decision: data with a date type uses a local time zone, other data with a date and time type uses a UTC time zone, because the statistics index is local. Another reason - now we have only local data.