Ana içeriğe geç

TimescaleDB

Zaman serisi verileri TimescaleDB hypertable'larında saklanır.

Hypertable'lar​

device_measurements​

Ana ölçüm tablosu. 70+ kolon ile tüm enerji verileri:

  • PK: (device_id, time) (composite)
  • Partition: Haftalık (7 gün)
  • Kolonlar: voltage_l1n/l2n/l3n, current_l1/l2/l3, power_active_l1/l2/l3/total, power_reactive_, power_apparent_, cos_phi_*, energy_active_import/export, frequency, thd_voltage/current, harmonics(JSONB) ve daha fazlası

zigbee_energy_measurements​

  • PK: (time, device_id)
  • Kolonlar: power_w, current_a, voltage_v, energy_kwh, state, link_quality

zigbee_sensor_measurements​

  • PK: (time, device_id, measurement_type)
  • Kolonlar: value_numeric, value_boolean, value_string, battery_level

Politikalar​

Compression (Sıkıştırma)​

90 gün sonra otomatik sıkıştırma:

SELECT add_compression_policy('device_measurements',
  compress_after => INTERVAL '90 days');

Retention (Saklama)​

730 gün (2 yıl) sonra otomatik silme:

SELECT add_retention_policy('device_measurements',
  drop_after => INTERVAL '730 days');

Continuous Aggregates​

Hourly ve daily aggregation'lar:

CREATE MATERIALIZED VIEW device_measurements_hourly
WITH (timescaledb.continuous) AS
SELECT
  device_id,
  time_bucket('1 hour', time) AS bucket,
  AVG(voltage_l1n) AS avg_voltage_l1n,
  MAX(power_active_total) AS max_power,
  ...
FROM device_measurements
GROUP BY device_id, bucket;

Migration Notları​

ÖNEMLİ

Hypertable'lar standart Alembic op.create_table() ile oluşturulamaz. op.execute() ile raw SQL kullanılmalıdır:

# alembic migration
def upgrade():
    op.create_table('device_measurements', ...)
    op.execute("SELECT create_hypertable('device_measurements', 'time')")
    op.execute("""
        SELECT add_compression_policy('device_measurements',
          compress_after => INTERVAL '90 days')
    """)