{"id":107644,"date":"2025-09-23T12:00:00","date_gmt":"2025-09-23T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107644"},"modified":"2025-09-19T07:46:13","modified_gmt":"2025-09-19T07:46:13","slug":"iot-meets-oltp-how-to-handle-backfilling-challenges-in-real-time-systems","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/iot-meets-oltp-how-to-handle-backfilling-challenges-in-real-time-systems\/","title":{"rendered":"IoT Meets OLTP: How to Handle Backfilling Challenges in Real-Time Systems"},"content":{"rendered":"\n

This is the true story of a 64-core SQL Server<\/a> brought down by poor assumptions about its data. A clustered index<\/a> designed for neat, sequential IoT inserts was overwhelmed when the real readings arrived late, out of order, and in bulk. The same risk lurks in any high-write system with unpredictable insert patterns. This article shows what can go wrong and how to avoid it. In essence, it answers the question: how do you manage IoT data in OLTP (online transaction processing)<\/a> systems?<\/p>\n\n\n\n

The Background<\/h2>\n\n\n\n

This isn\u2019t a hypothetical lab setup: this describes what can go wrong at a manufacturing site running a high-end relational database system (RDBMS)<\/a> to record IoT data.<\/p>\n\n\n\n

A manufacturing company rolled out over 10,000 IoT sensors across its factories to track temperature, pressure, humidity, acoustic and vibration data. The data was streamed into a RDBMS, SQL Server<\/a> in this case, not particularly because it was cutting-edge, but because it was already part of their stack. Their MES system ran on it, they had additional licenses, elite-class support from Microsoft<\/a>, and the cost of switching to open-source systems was too high.<\/p>\n\n\n\n

The table design seemed deceptively straightforward: a clustered primary key<\/a> on (SensorId, ReadingTime) as is commonly used with time series<\/a> based workloads. But there was a deviation for this in OLTP RDBMS systems, and they didn\u2019t have a DBA involved during\u00a0design.\u00a0Everything looked fine on frequency samples of 1000 sensors or less, but problems arose when the number of sensors increased.<\/p>\n\n\n\n

However, it wasn\u2019t just the increase in sensors that caused problems, as many of these sensors suffered from power issues, poor GSM connectivity, and delayed data transmission in production usage. The result was the arrival of backfilled inserts with old timestamps arriving hours or days late, and often in considerable bulk. This had an immediate impact on the business in that temperature gauges often showed\u00a0‘stale’ values despite sensor updates, vibration alerts sometimes failed\u00a0to trigger abnormal machinery, and pressure graphs occasionally displayed gaps even though the sensors were online.<\/p>\n\n\n\n

These out-of-order inserts hit earlier parts of the clustered index and triggered massive page splits<\/a>. With no fill factor adjustments or fragmentation checks in place, the system began to struggle. TempDB<\/a> usage climbed, CPU hit 90%, and insert latency shot up. Even with a 64-core server, the database couldn\u2019t keep up.<\/p>\n\n\n\n

Eventually, we looked deeper, monitored closely, and realized what was happening. The clustered index wasn\u2019t the primary problem \u2014 it was the assumption that inserts would always be sequential. With no room left on pages, every backfill caused internal reshuffling, page splits, and performance degradation.<\/p>\n\n\n\n

The Architecture<\/h2>\n\n\n\n

The design of the end-to-end data pipeline was fairly straightforward on paper, as shown below:<\/p>\n\n\n\n

\"The<\/figure>\n\n\n\n

<\/p>\n\n\n\n

It began with individual IoT sensors that were deployed across industrial equipment, continuously measuring parameters such as temperature, vibration, pressure and humidity.<\/p>\n\n\n\n

These sensors didn\u2019t talk to SQL Server directly. Instead, their readings flowed through an Edge Device Gateway \u2014 a middleware layer responsible for temporarily buffering and pre-processing incoming data. This layer played a crucial role: it had the ability to store up to one month of telemetry data locally, which became invaluable when connectivity was lost or sensors went offline. It\u2019s this buffer that allowed delayed data to be backfilled later into the system, but the immediate value of the data in triggering alerts on abnormal readings was lost.<\/p>\n\n\n\n

From the middleware, data was pushed into staging tables within the database \u2014 dynamically created tables named like sensor_n_UUID<\/code>. These were lightweight, per-sensor structures used to isolate incoming data before merging it into the main Telemetry<\/code> table.<\/p>\n\n\n\n

These staging tables served two purposes:<\/p>\n\n\n

\n