Edge AI with .NET, Part 4: Time Series That Outlives the Hardware

A developer outlines a .NET edge-to-cloud time-series architecture for water and power meter fleets, using TimescaleDB hypertables, Hypercore columnstore compression, and continuous aggregates to keep readings tied to logical meter identities rather than device serials. The approach cuts cold-storage costs by 90 to 95 percent and avoids history loss when hardware is swapped or upgraded. The writeup covers the PostgreSQL schema, EF Core mapping, and an anomaly worker, and notes TimescaleDB 2.29.0's incremental and concurrent continuous-aggregate refresh.

You have a fleet of water and power meters, each running a small .NET edge agent that sends readings every minute. The hardware will fail, be replaced, or be upgraded. The readings must not. If your schema ties a reading to a device serial number rather than a logical meter identity, you lose history the moment you swap hardware. If you store readings in a plain PostgreSQL table, you pay ten times the storage cost and wait ten times as long for rolling-window queries. TimescaleDB now shipped by TigerData, renamed from Timescale Inc. on June 17, 2025 solves both the storage and the query problem. Version 2.29.0, released July 28, 2026, adds incremental and concurrent continuous-aggregate refresh, vectorised time bucket from 2.26 onward, and and the Hypercore columnstore engine introduced in 2.18. All of them matter for the patterns below. This article covers the schema, the EF Core mapping, and the anomaly worker. Parts 1 to 3 covered the edge agent; here we focus on what happens after the reading lands in the cloud database. The first rule: readings reference a logical meter , not a device serial. Keep a meters lookup table with a surrogate UUID, the physical serial, and install/remove timestamps. When hardware is swapped, insert a new meters row, and the old readings keep their original meter id . -- Locations and meters CREATE TABLE locations location id UUID PRIMARY KEY DEFAULT gen random uuid , address TEXT NOT NULL ; CREATE TABLE meters meter id UUID PRIMARY KEY DEFAULT gen random uuid , location id UUID NOT NULL REFERENCES locations location id , device serial TEXT NOT NULL, meter type TEXT NOT NULL CHECK meter type IN 'water','power' , installed at TIMESTAMPTZ NOT NULL, removed at TIMESTAMPTZ ; -- Raw readings hypertable CREATE TABLE readings time TIMESTAMPTZ NOT NULL, meter id UUID NOT NULL, value DOUBLE PRECISION NOT NULL -- litres or watt-hours ; SELECT create hypertable 'readings', 'time', chunk time interval = INTERVAL '7 days' ; -- Hypercore columnstore compression after 7 days SELECT add columnstore policy 'readings', after = INTERVAL '7 days', segmentby = ARRAY 'meter id' ; -- Tariff periods for cost calculation CREATE TABLE tariff periods tariff id UUID PRIMARY KEY DEFAULT gen random uuid , valid from TIMESTAMPTZ NOT NULL, valid until TIMESTAMPTZ, -- NULL means current tariff eur per kwh NUMERIC 10,6 NOT NULL ; A 7-day chunk interval suits medium-frequency meter data one reading per minute is 1,440 rows per day per meter . Hypercore's columnstore compresses cold chunks by 90 to 95 percent, so a year of data from a hundred meters costs roughly what a week would in plain PostgreSQL. Stack the aggregates. An hourly rollup feeds a daily rollup, which feeds a monthly rollup. Each is its own hypertable, refreshed incrementally. The WITH timescaledb.continuous flag and real time aggregate option mean your anomaly queries always see the latest raw data without a forced refresh. -- Hourly rollup CREATE MATERIALIZED VIEW readings hourly WITH timescaledb.continuous, timescaledb.materialized only = false AS SELECT time bucket '1 hour', time AS bucket, meter id, SUM value AS total, MIN value AS min val, MAX value AS max val, COUNT AS sample count FROM readings GROUP BY bucket, meter id WITH NO DATA; SELECT add continuous aggregate policy 'readings hourly', start offset = INTERVAL '2 hours', end offset = INTERVAL '1 hour', schedule interval = INTERVAL '1 hour' ; -- Daily rollup sourced from hourly CREATE MATERIALIZED VIEW readings daily WITH timescaledb.continuous, timescaledb.materialized only = false AS SELECT time bucket '1 day', bucket AS bucket, meter id, SUM total AS total, MIN min val AS min val, MAX max val AS max val, SUM sample count AS sample count FROM readings hourly GROUP BY time bucket '1 day', bucket , meter id WITH NO DATA; SELECT add continuous aggregate policy 'readings daily', start offset = INTERVAL '2 days', end offset = INTERVAL '1 day', schedule interval = INTERVAL '1 day' ; Monthly rollups follow the same pattern using '1 month' and sourcing readings daily . EF Core has no native understanding of hypertable DDL. The YC.EntityFrameworkCore.TigerData.TimescaleDB package targeting EF Core 10 / Npgsql 10, requires TimescaleDB 2.23+ exposes a Fluent API that injects the correct migrationBuilder.Sql ... calls into generated migrations. For bare projects, execute raw SQL directly in Up . The Reading entity is straightforward; the important thing is that MeterId is the foreign key, not a serial string: // Reading.cs public class Reading { public DateTimeOffset Time { get; set; } public Guid MeterId { get; set; } public double Value { get; set; } public Meter Meter { get; set; } = null ; } // Meter.cs public class Meter { public Guid MeterId { get; set; } public Guid LocationId { get; set; } public string DeviceSerial { get; set; } = string.Empty; public string MeterType { get; set; } = string.Empty; public DateTimeOffset InstalledAt { get; set; } public DateTimeOffset? RemovedAt { get; set; } public Location Location { get; set; } = null ; public ICollection<Reading Readings { get; set; } = ; } // MeterDbContext.cs relevant excerpt protected override void OnModelCreating ModelBuilder modelBuilder { modelBuilder.Entity<Reading e = { e.HasKey r = new { r.Time, r.MeterId } ; e.Property r = r.Time .HasColumnType "timestamptz" ; e.HasOne r = r.Meter .WithMany m = m.Readings .HasForeignKey r = r.MeterId ; } ; modelBuilder.Entity<Meter e = { e.HasKey m = m.MeterId ; e.Property m = m.RemovedAt .HasColumnType "timestamptz" ; } ; // If not using the community package, call the hypertable // DDL from the migration's Up method via migrationBuilder.Sql } Important gotcha: TimescaleDB cannot apply certain schema changes in place on an existing hypertable, it has to recreate the table. Design your schema before production data volumes grow large; adding a column after millions of rows have been ingested is a heavy operation. Also: the 2.27.x bloom-filter bug caused compressed int2 / SMALLINT columns to silently miss matching rows in SELECT queries. The workaround is to drop the affected sparse indexes manually before upgrading. On 2.28+ this is resolved, but avoid SMALLINT on heavily compressed columns until you have confirmed your version. A single BackgroundService polls every minute and runs three independent checks: Leak detection. A water meter with non zero flow for six consecutive hours. Use readings hourly with real time aggregate so no explicit refresh is needed: public class AnomalyWorker MeterDbContext db, ILogger<AnomalyWorker logger, IAlertService alerts : BackgroundService { protected override async Task ExecuteAsync CancellationToken ct { using var timer = new PeriodicTimer TimeSpan.FromMinutes 1 ; while await timer.WaitForNextTickAsync ct { await CheckLeaksAsync ct ; await CheckPowerSpikesAsync ct ; await CheckSilentMetersAsync ct ; } } private async Task CheckLeaksAsync CancellationToken ct { var cutoff = DateTimeOffset.UtcNow.AddHours -6 ; // readings hourly is a cagg, queried like a normal table var leaking = await db.Database .SqlQuery<Guid $""" SELECT h.meter id AS "Value" FROM readings hourly h JOIN meters m ON m.meter id = h.meter id WHERE m.meter type = 'water' AND m.removed at IS NULL AND h.bucket = {cutoff} AND h.min val 0 GROUP BY h.meter id HAVING COUNT = 6 """ .ToListAsync ct ; foreach var meterId in leaking await alerts.RaiseAsync meterId, "LeakDetected", ct ; } private async Task CheckPowerSpikesAsync CancellationToken ct { // 7-day baseline: flag today if today's total avg + 2 stddev var spikes = await db.Database .SqlQuery<Guid $""" WITH baseline AS SELECT meter id, AVG total AS avg total, STDDEV total AS std total FROM readings daily WHERE bucket = NOW - INTERVAL '8 days' AND bucket < NOW - INTERVAL '1 day' GROUP BY meter id , today AS SELECT meter id, SUM total AS day total FROM readings hourly WHERE bucket = date trunc 'day', NOW GROUP BY meter id SELECT t.meter id AS "Value" FROM today t JOIN baseline b ON b.meter id = t.meter id WHERE t.day total b.avg total + 2 COALESCE b.std total, 0 """ .ToListAsync ct ; foreach var meterId in spikes await alerts.RaiseAsync meterId, "PowerSpike", ct ; } private async Task CheckSilentMetersAsync CancellationToken ct { var threshold = DateTimeOffset.UtcNow.AddMinutes -15 ; var silent = await db.Database .SqlQuery<Guid $""" SELECT m.meter id AS "Value" FROM meters m WHERE m.removed at IS NULL AND NOT EXISTS SELECT 1 FROM readings r WHERE r.meter id = m.meter id AND r.time = {threshold} """ .ToListAsync ct ; foreach var meterId in silent await alerts.RaiseAsync meterId, "SilentMeter", ct ; } } The silent-meter check queries the raw hypertable rather than an aggregate, because chunk pruning on r.time = threshold limits the scan to one or two recent chunks regardless of how many years of history exist. Tariffs change. A reading from 2023 must be costed at the 2023 rate, not today's. Join through tariff periods using a lateral range condition: SELECT d.meter id, d.bucket, d.total / 1000.0 AS kwh, tp.eur per kwh, d.total / 1000.0 tp.eur per kwh AS eur cost FROM readings daily d JOIN tariff periods tp ON d.bucket = tp.valid from AND tp.valid until IS NULL OR d.bucket < tp.valid until WHERE d.meter id = @meterId ORDER BY d.bucket; Store valid until = NULL for the active tariff. When a new tariff starts, set valid until on the previous row to the change timestamp; insert the new row. That gives you an append only audit trail, so you never lose what a customer paid in an earlier period. TimescaleDB 2.29 removed PostgreSQL 15 support; if your managed database is on PG15, you are on the 2.28.x train until you upgrade the engine. The Hypercore columnstore is now the recommended default for new installs, so enable it from day one. Retrofitting compression onto a large existing hypertable is possible but slow. The community EF Core packages both YC.EntityFrameworkCore.TigerData.TimescaleDB and CmdScale.EntityFrameworkCore.TimescaleDB are thin wrappers that emit raw SQL migrations. They save repetitive boilerplate, but you should read the generated migration SQL before applying it, especially the compress segmentby and chunk time interval settings, which are hard to change once data is flowing.