--- title: "Measuring How Fast Your IIoT Queries Are Degrading" published: 2026-10-09T08:00:40.000-04:00 updated: 2026-10-09T08:00:40.000-04:00 excerpt: "Stop waiting for the dashboard to get slow. Record latency against row count, fit the curve, and get a date when your query breaches its target." tags: IIoT, PostgreSQL, Industry authors: John Shi --- > **TimescaleDB is now Tiger Data.** Your dashboard renders in well under a second today. The question worth answering is what it will do after another eighteen months of sensor data lands in the same tables. Most teams answer by waiting. A panel gets slow, [someone adds an index](https://www.tigerdata.com/blog/why-adding-more-indexes-eventually-makes-things-worse), it gets fast again, and the cycle repeats. Each fix lowers the curve's starting point and leaves its slope alone. This post measures the slope. You will record query latency against row count, fit a curve, and project it until it crosses your latency target. The output is a date. ## What you will build By the end you will have: - One tracking table and a scheduled procedure recording latency, row count, tag count, and the bloat figure that can read as growth. - A view differencing the cumulative counters into a per-period mean, dropping bad rows. - A fit that says whether each query grows with log(n) or with n. - A degradation coefficient in milliseconds per million rows, and a breach date. ## Why it matters Ingest saturation announces itself with write errors, storage growth as a disk alert. Query degradation creeps, and the team adapts before anyone measures it. It is the third limit of the [Industrial Internet of Things (IIoT) PostgreSQL performance envelope](https://www.tigerdata.com/blog/how-hardware-affects-iiot-workloads). The cause is the cost curve underneath. An indexed point lookup walks a B-tree, so it takes a large multiple of the rows to add a level and cost grows with log(n). An aggregate reads every row in its time range, so when that range grows with the table, cost grows with n. A panel returning in 40 milliseconds at launch scales with the rows in its window. ## Before you start You need: - PostgreSQL 13 or later with [`pg_stat_statements`](https://www.postgresql.org/docs/current/pgstatstatements.html) in `shared_preload_libraries`. - `pg_stat_statements.max` above your statement count. The default of 5000 lets PostgreSQL discard least-executed entries and reset their counters. - A table that is actually growing, and a written latency target per query. Examples use a [TimescaleDB hypertable](https://www.tigerdata.com/docs/use-timescale/latest/hypertables) named `sensor_readings` with time, `tag_id`, value, plus a tag table of one row per sensor. Sample output is from one fleet: 5,000 tags every ten seconds, 43 million rows a day on a base of 1 billion. ## Build the measurement ### Step 1: Pick three queries Three shapes cover how an IIoT workload degrades: 1. A deep single-tag query, one tag over a long range. Your log(n) control. 2. A wide cross-tag query, many tags over a short range. Cost tracks tag count more than rows. 3. A dashboard aggregate over the range your busiest panel requests. This one goes linear. Use the real query text from your application; a cleaned-up version often plans differently. ### Step 2: Record latency against row count One table maps a query identifier to a label and a target; the other holds the series. ```SQL CREATE TABLE tracked_query ( query_label text PRIMARY KEY, queryid bigint NOT NULL, target_ms double precision NOT NULL ); CREATE TABLE degradation_log ( captured_at timestamptz NOT NULL DEFAULT now(), query_label text NOT NULL REFERENCES tracked_query, calls bigint NOT NULL, total_exec_ms double precision NOT NULL, table_rows bigint NOT NULL, distinct_tags bigint, dead_pct numeric, PRIMARY KEY (captured_at, query_label) ); ``` `dead_pct` is there so Step 3 can discard a sample for a named reason. Find the identifiers in `pg_stat_statements` by matching on query text. ```SQL INSERT INTO tracked_query VALUES ('single_tag', -4412037129857003611, 50), ('cross_tag', 7719003248811640022, 200), ('dashboard_agg', 1885730312299301884, 500); ``` The procedure captures the counters and the table's state together. Sum them: `pg_stat_statements` reports one row per role and database, so an unaggregated join returns several rows per label. ```SQL CREATE OR REPLACE PROCEDURE snapshot_degradation() LANGUAGE sql AS $$ INSERT INTO degradation_log ( query_label, calls, total_exec_ms, table_rows, distinct_tags, dead_pct) SELECT t.query_label, sum(s.calls), sum(s.total_exec_time), approximate_row_count('sensor_readings'), (SELECT count(*) FROM tag), (SELECT round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) FROM pg_stat_user_tables WHERE relname = 'sensor_readings') FROM pg_stat_statements s JOIN tracked_query t ON t.queryid = s.queryid GROUP BY t.query_label; $$; ``` On plain PostgreSQL substitute reltuples for `approximate_row_count`. Schedule the analyze first, so every sample sees fresh statistics. Daily suits a fast-growing table. ```SQL SELECT cron.schedule('degradation-analyze', '50 3 * * *', 'ANALYZE sensor_readings'); SELECT cron.schedule('degradation-snapshot', '0 4 * * *', 'CALL snapshot_degradation()'); ``` ### Step 3: Difference the counters `pg_stat_statements` counters are cumulative since the last reset, so `mean_exec_time` gives a [lifetime average that flattens](https://www.tigerdata.com/blog/what-pg_stat_statements-actually-tells-you-about-your-queries) as the sample grows. Difference consecutive snapshots. One event needs excluding: [bloat inflates row count](https://www.tigerdata.com/blog/reading-table-bloat-before-it-reads-you) and scan cost together, which looks like growth. The same view does both jobs. CREATE OR REPLACE VIEW degradation\_clean AS ```SQL CREATE OR REPLACE VIEW degradation_clean AS SELECT * FROM ( SELECT query_label, captured_at, table_rows, dead_pct, distinct_tags, calls - lag(calls) OVER w AS n, (total_exec_ms - lag(total_exec_ms) OVER w) / NULLIF(calls - lag(calls) OVER w, 0) AS mean_ms FROM degradation_log WINDOW w AS (PARTITION BY query_label ORDER BY captured_at) ) s WHERE n > 0 AND mean_ms > 0 AND dead_pct < 20; ``` Checkpoints need no filter: each interval averages thousands of calls, so one landing inside a window barely moves the mean. ### Step 4: Fit the curve PostgreSQL has [linear regression built in](https://www.postgresql.org/docs/current/functions-aggregate.html). Fit latency against row count and against its natural log, then compare the coefficients of determination. ```SQL SELECT query_label, count(*) AS points, regr_r2(mean_ms, table_rows) AS r2_linear, regr_r2(mean_ms, ln(table_rows)) AS r2_log, regr_slope(mean_ms, table_rows) AS ms_per_row FROM degradation_clean GROUP BY query_label; ``` After three months of daily snapshots: ``` query_label | points | r2_linear | r2_log | ms_per_row ----------------+--------+-----------+--------+-------------- single_tag | 88 | 0.9497 | 0.9762 | 0.0000000024 cross_tag | 88 | 0.9049 | 0.8609 | 0.0000000024 dashboard_agg | 88 | 0.9962 | 0.9607 | 0.0000000198 ``` Read the r-squared columns first. Where the log fit wins, ms\_per\_row is an artifact of forcing a straight line through a curve, so ignore it. The dashboard aggregate fits linearly at 0.996 against 0.961, so its cost tracks table size. The cross-tag query also looks linear, which is the trap: its tag count grew too, and anything growing with time correlates with row count. Refit it against tag count. ```SQL SELECT regr_slope(mean_ms, distinct_tags) AS ms_per_tag, regr_r2(mean_ms, distinct_tags) AS r2_tags FROM degradation_clean WHERE query_label = 'cross_tag'; ms_per_tag | r2_tags ------------+--------- 0.0181 | 0.9497 ``` 0.9497 beats its row-count fit, so tag count is the real driver and a row-count projection would be spurious. Its horizon comes from the device forecast. ### Step 5: Get the breach date The degradation coefficient is the slope in milliseconds per million rows, `ms_per_row` times one million. For the dashboard aggregate that is about 0.02, against the eighteen billion rows this fleet adds before it breaches. Solve the same line for the row count where latency equals the target, then convert to a date using the ingest rate the log already holds. ```SQL WITH fit AS ( SELECT query_label, regr_slope(mean_ms, table_rows) AS slope, regr_intercept(mean_ms, table_rows) AS intercept FROM degradation_clean GROUP BY query_label HAVING regr_r2(mean_ms, table_rows) > regr_r2(mean_ms, ln(table_rows)) AND regr_r2(mean_ms, table_rows) > regr_r2(mean_ms, distinct_tags) ), growth AS ( SELECT max(table_rows) AS rows_now, (max(table_rows) - min(table_rows)) / (extract(epoch FROM max(captured_at) - min(captured_at)) / 86400) AS per_day FROM degradation_log ) SELECT t.query_label, round((f.slope * 1e6)::numeric, 4) AS ms_per_mrows, (now() + make_interval(days => greatest(0, (((t.target_ms - f.intercept) / f.slope) - g.rows_now) / g.per_day)::int))::date AS breach_on FROM tracked_query t JOIN fit f USING (query_label) CROSS JOIN growth g; query_label | ms_per_mrows | breach_on ----------------+--------------+------------ dashboard_agg | 0.0198 | 2027-11-22 ``` One row from three tracked queries. That is the normal result: two shapes are fine for years, and you know which one is not, and when. The `HAVING` clause rejects both the curve bending away from linear and the query that really tracks tag count. ## Step 6: Flatten the slope The fix is to stop reading raw rows at query time. A [continuous aggregate](https://www.tigerdata.com/docs/learn/continuous-aggregates) maintains the rollup incrementally, so the dashboard reads a result sized by bucket count and raw readings per bucket stop affecting query time. ```SQL CREATE MATERIALIZED VIEW sensor_readings_hourly WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', time) AS bucket, tag_id, avg(value) AS avg_value FROM sensor_readings GROUP BY bucket, tag_id; SELECT add_continuous_aggregate_policy( 'sensor_readings_hourly', start_offset => INTERVAL '3 days', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour'); ``` Point the panel at `sensor_readings_hourly`, register it, and keep the job running so you can watch the new rate. ## Validate before you act Three checks before anyone acts on the projection. 1. Check the range. Both hypotheses score high over a narrow band, because ln(n) is nearly straight over a short stretch. Fifty percent growth separates them by under a hundredth even with clean data. You need roughly a quadrupling: three times your current rows divided by your daily ingest rate, 69 days for this fleet. 2. Require a gap between the two r-squared values. Several hundredths is a decision; two values within a hundredth mean you need more range. 3. Hold out the most recent snapshot, refit on everything before it, and compare the projection against what you measured. Within ten percent is a model you can extend. ## Take the first snapshot today The one thing you cannot do later is start the series earlier. Create the tables, register your three queries, run CALL `snapshot_degradation()`, and schedule the job. Then put your own wait, three times your row count over your daily ingest rate, on the calendar as the day you run the first fit.