如何维护双表数据组成的‘virtual column’？时区适配预计算方案咨询

Awesome question—dealing with time zones and avoiding repeated full-table scans is such a common pain point, but there are solid pre-computation strategies to fix this. Let’s walk through the most practical options tailored to your use case (where users update time zones but keep their schedules aligned to local time):

1. 预存储时区偏移量（平衡读写性能） #

Instead of calling timezone conversion functions every time you query, precompute and store the UTC offset in minutes for each user. This turns expensive timezone lookups into simple arithmetic operations.

Implementation Steps: #

• Add an offset column to your users table:

  ALTER TABLE users ADD COLUMN utc_offset_minutes INT NOT NULL;
  

• When a user updates their timezone (e.g., from America/New_York to Europe/London), calculate and update this offset. Most databases have built-in functions to get the offset—for example in PostgreSQL:

  UPDATE users
  SET utc_offset_minutes = EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE timezone) - CURRENT_TIMESTAMP) / 60
  WHERE user_id = 123;
  

• Store your schedule times as local time values (not timezone-aware) in the schedules table:

  CREATE TABLE schedules (
      schedule_id INT PRIMARY KEY,
      user_id INT REFERENCES users(user_id),
      local_start_time TIME NOT NULL, -- e.g., '09:00:00' (local to user's timezone)
      local_end_time TIME NOT NULL
  );
  

• Now query efficiently by combining the local time with the precomputed offset:

  SELECT
      s.schedule_id,
      u.user_id,
      -- Convert local time to UTC using the precomputed offset
      CURRENT_DATE + s.local_start_time + INTERVAL '1 minute' * u.utc_offset_minutes AS utc_start_time
  FROM schedules s
  JOIN users u ON s.user_id = u.user_id
  WHERE EXTRACT(HOUR FROM (CURRENT_DATE + s.local_start_time + INTERVAL '1 minute' * u.utc_offset_minutes)) = 12;
  

• Add indexes on users.utc_offset_minutes and schedules.local_start_time to further speed up the join and filter.

2. 物化视图（极致查询性能，非实时场景） #

If you can tolerate slight delays in data freshness (e.g., daily schedule updates), a materialized view will precompute and store the full joined result set. Querying this is as fast as querying a regular table.

Implementation Steps: #

• Create the materialized view to precompute UTC-aligned schedule times:

  CREATE MATERIALIZED VIEW user_schedules_utc AS
  SELECT
      s.schedule_id,
      u.user_id,
      (CURRENT_DATE + s.local_start_time) AT TIME ZONE u.timezone AT TIME ZONE 'UTC' AS utc_start_time,
      (CURRENT_DATE + s.local_end_time) AT TIME ZONE u.timezone AT TIME ZONE 'UTC' AS utc_end_time
  FROM schedules s
  JOIN users u ON s.user_id = u.user_id;
  

• Add an index on the UTC start time to make your target query lightning fast:

  CREATE INDEX idx_utc_start_time ON user_schedules_utc(utc_start_time);
  

• Refresh the view periodically (e.g., nightly via a cron job) or when critical changes happen:

  REFRESH MATERIALIZED VIEW user_schedules_utc;
  

• Query directly from the materialized view:

  SELECT * FROM user_schedules_utc WHERE EXTRACT(HOUR FROM utc_start_time) = 12;
  

3. 触发器驱动的实时预计算（实时数据场景） #

If you need up-to-the-second data, use triggers to automatically update precomputed UTC times whenever a user’s timezone or a schedule’s local time changes.

Implementation Steps: #

• Add UTC time columns to your schedules table:

  ALTER TABLE schedules ADD COLUMN utc_start_time TIMESTAMP WITH TIME ZONE;
  ALTER TABLE schedules ADD COLUMN utc_end_time TIMESTAMP WITH TIME ZONE;
  

• Create a trigger function to update UTC times when a user’s timezone changes:

  CREATE OR REPLACE FUNCTION update_schedule_utc_on_tz_change()
  RETURNS TRIGGER AS $$
  BEGIN
      -- Update all schedules for the user with the new timezone
      UPDATE schedules
      SET
          utc_start_time = (CURRENT_DATE + local_start_time) AT TIME ZONE NEW.timezone AT TIME ZONE 'UTC',
          utc_end_time = (CURRENT_DATE + local_end_time) AT TIME ZONE NEW.timezone AT TIME ZONE 'UTC'
      WHERE user_id = NEW.user_id;
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER trigger_user_tz_update
  AFTER UPDATE OF timezone ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_schedule_utc_on_tz_change();
  

• Create another trigger to set UTC times when a schedule is inserted or updated:

  CREATE OR REPLACE FUNCTION set_schedule_utc_on_write()
  RETURNS TRIGGER AS $$
  DECLARE
      user_timezone VARCHAR(50);
  BEGIN
      SELECT timezone INTO user_timezone FROM users WHERE user_id = NEW.user_id;
      NEW.utc_start_time = (CURRENT_DATE + NEW.local_start_time) AT TIME ZONE user_timezone AT TIME ZONE 'UTC';
      NEW.utc_end_time = (CURRENT_DATE + NEW.local_end_time) AT TIME ZONE user_timezone AT TIME ZONE 'UTC';
      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER trigger_schedule_write
  BEFORE INSERT OR UPDATE OF local_start_time, local_end_time ON schedules
  FOR EACH ROW
  EXECUTE FUNCTION set_schedule_utc_on_write();
  

• Now query without joining the users table at all:

  SELECT * FROM schedules WHERE EXTRACT(HOUR FROM utc_start_time) = 12;
  

Note: This adds overhead to write operations (especially if users have many schedules), so only use this if real-time data is critical.

4. 索引优化（最小侵入式改进） #

If you don’t want to add new columns or views, optimize your existing schema with indexes to reduce the scope of table scans:

• Add an index on users.timezone to quickly find users in specific time zones.
• Add an index on schedules.local_start_time to filter schedules by their local time window.
• Most databases can use these indexes to avoid full-table scans during the join, even with timezone conversion functions.

Final Recommendation: #

• Choose materialized views if you can tolerate non-real-time data (best for read-heavy workloads).
• Choose trigger-driven precomputation if you need real-time data (accepts higher write overhead).
• Choose precomputed offset columns for a balanced approach (low overhead, good performance for both reads and writes).

内容的提问来源于stack exchange，提问作者John Bachir