[Python] Choosing a storage schema for second-by-second telemetry data...

My application receives telemetry packets every second from equipment to an operations center. Each packet is identified by a fixed SID, which defines the data structure, including:

• SID 1: temperature of equipment 1, 2, 3, 4
• SID 2: voltage of batteries 1, 2, 3

Solution Generic EAV is already implemented and running in production, but it generates a very large data volume.

Rewriting the storage layer would be very costly in both development and maintenance.

In Django I can display the latest value of each parameter with nanosecond precision without issues.

However, in Grafana, any attempt to build a historical dashboard (e.g., one month of temperature data) consumes massive resources (CPU/memory) to execute queries.

Objectives:

1. Keep real-time display of the latest values in Django
2. Enable efficient plotting of time series over several months in Grafana without resource exhaustion

Solution 1 Generic EAV, in production:

• 
  A single Django model stores each parameter in an Entity–Attribute–Value table:
  
  # models.py
  class ParameterHK(models.Model):
  id = models.BigAutoField(primary_key=True, db_index=True)
  date_hk_reception = models.DateTimeField(auto_now_add=True, db_index=True)
  sid = models.IntegerField(db_index=True)
  equipment = models.CharField(max_length=500)
  label_parameter = models.CharField(max_length=500, db_index=True)
  unit = models.CharField(max_length=500)
  value = models.IntegerField()
  date_hk_obc = models.IntegerField()
• 
  One row per parameter per second.
  
  
• 
  Works fine for Django queries but causes:
  
  • a huge number of rows.
  • very large indexes.
  • resource-intensive aggregation queries in Grafana.
• 
  No rewrite, but is limited in historical query performance.
  

Solution 2 Wide tables per SID:

• 
  Create a dedicated table for each SID, with one column per parameter. Example for SID 1:
  
  CREATE TABLE hk_sid1 (
  ts TIMESTAMPTZ NOT NULL,
  equip1_temp INTEGER,
  equip2_temp INTEGER,
  equip3_temp INTEGER,
  equip4_temp INTEGER
  );
• 
  Direct reading of each time series column without complex filtering.
  
  
• 
  Faster Grafana reads, but is costly in development and maintenance.
  
  
• 
  Rigid schema, requires migrations on protocol changes and multiple tables to manage.
  

What is a design for a second-by-second data stream stored over months, while keeping Solution 1 in production due to the high cost of a full rewrite?

What alternatives (partitioning, continuous aggregates, downsampling, automatic retention policies, etc.) limit resource usage while maintaining flexibility?

Continue reading...