---
title: "Building an Ingest Safety Monitor for Your IIoT Pipeline in SQL"
published: 2026-10-16T08:55:08.000-04:00
updated: 2026-10-16T08:55:08.000-04:00
excerpt: "Ingest fails suddenly, not gradually. Sample WAL volume, replica lag, and dead tuples every minute, and get one headroom percentage you can alert on."
tags: IIoT, PostgreSQL
authors: John Shi
---

> **TimescaleDB is now Tiger Data.**

An Industrial Internet of Things (IIoT) ingest pipeline can run for months without an error and then fall behind within hours of a load increase. Once readings arrive faster than PostgreSQL can write them, the backlog grows until something removes the load.

In this guide, you’ll learn how to build a monitor that shows how close you are to that point. It samples the write statistics PostgreSQL already keeps, compares each rate to a ceiling you measured, and reduces the result to one alertable headroom percentage.

## What you will build

-   A procedure that snapshots rows inserted, write-ahead log (WAL) volume, requested checkpoints, replication lag, and dead tuples once a minute.
-   A load test that measures the ceiling for each signal.
-   A view that reports headroom and the signal closest to its limit.
-   Alerts at 70% and 80% of the ceiling, plus an escalation rule for replication lag.

## Why it matters

Ingest is the least forgiving limit in [the IIoT PostgreSQL performance envelope](https://www.tigerdata.com/blog/the-iiot-postgresql-performance-envelope) because it fails suddenly. Once the backlog starts growing, one of two things happens: the message broker's retention expires and readings are lost, or a buffer fills and a component fails.

Headroom decides how fast you recover from a burst. Suppose gateways lose connectivity for ten minutes while you run at 80% of your ceiling, then flush their buffers together. The backlog equals eight minutes of writes at full ceiling, and the remaining 20% of capacity drains it in 40 minutes. At 95% utilization the same outage takes more than three hours to clear.

That arithmetic is why 80% of maximum ingest is the common safety factor. Few teams know where their 80% is, because WAL volume and autovacuum contention [lower the ceiling as data accumulates](https://www.tigerdata.com/blog/postgres-optimization-treadmill).

## Prerequisites

-   PostgreSQL 17 or later, for `pg_stat_checkpointer`. On versions 14 to 16, read `checkpoints_req` from `pg_stat_bgwriter`.
-   TimescaleDB, self-hosted or on [Tiger Cloud](https://www.tigerdata.com/cloud), with a hypertable named sensor\_readings holding time, tag\_id, and value.
-   A role with `pg_read_all_stats`, so `pg_stat_replication` returns replica positions.
-   [`pg_cron`](https://github.com/citusdata/pg_cron) or the TimescaleDB scheduler through [`add_job()`](https://www.tigerdata.com/docs/api/latest/jobs-automation/add_job).
-   [`pgbench`](https://www.postgresql.org/docs/current/pgbench.html).
-   An alerting tool that can run SQL queries and send notifications. This post uses [Grafana](https://grafana.com/docs/grafana/latest/alerting/) as the example, but any tool with those two abilities works.
-   A full-size staging copy of production, since an empty table accepts writes faster.

## Build the monitor

### Step 1: Define safe for your deployment

Write down three numbers: the rows per second your fleet sends at peak, the replication lag your failover plan tolerates, and the longest gateway outage you need to recover from. The outage length sets how much headroom you need, while the other two numbers feed Step 4.

### Step 2: Choose signals that warn early

Rows inserted per second is the weakest signal. At saturation it levels off and looks like a steady fleet while the backlog grows. The monitor tracks it alongside four signals that respond sooner (ordered from most to least warning time):

1.  **WAL generation rate**, from [`pg_stat_wal`](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW). Every insert and index update writes WAL, so this rate climbs before inserts slow.
2.  **Replication lag**. A replica replays WAL with a single process, so it falls behind before the primary does. Without replicas, this signal reads zero.
3.  **Dead tuples**. A rising count of dead rows means aborted batches are outpacing autovacuum.
4.  **Requested checkpoints**. When WAL reaches `max_wal_size` early, PostgreSQL forces a checkpoint, and the [full-page images](https://www.postgresql.org/docs/current/wal-configuration.html) written afterward add more WAL.

### Step 3: Capture snapshots

A hypertable stores its rows in smaller tables called chunks, and PostgreSQL counts inserted and dead rows per chunk. The SQL below creates a snapshot table and a `snapshot_ingest()` procedure that totals those counts across [`show_chunks()`](https://www.tigerdata.com/docs/api/latest/hypertable/show_chunks), then schedules the procedure to run every minute. Run it on production and the staging copy.

```SQL
CREATE TABLE ingest_snapshot (
  captured_at   timestamptz PRIMARY KEY,
  rows_inserted numeric,
  wal_bytes     numeric,
  checkpoints   bigint,
  lag_bytes     numeric,
  dead_tuples   numeric
);

CREATE OR REPLACE PROCEDURE snapshot_ingest()
LANGUAGE sql AS $$
  WITH chunks AS (
    SELECT n_tup_ins, n_dead_tup
    FROM pg_stat_user_tables
    WHERE relid IN (
      SELECT show_chunks('sensor_readings')::oid)
  )
  INSERT INTO ingest_snapshot
  SELECT now(),
    (SELECT sum(n_tup_ins) FROM chunks),
    (SELECT wal_bytes FROM pg_stat_wal),
    (SELECT num_requested FROM pg_stat_checkpointer),
    (SELECT coalesce(max(pg_wal_lsn_diff(
              pg_current_wal_lsn(), replay_lsn)), 0)
       FROM pg_stat_replication),
    (SELECT sum(n_dead_tup) FROM chunks);

  DELETE FROM ingest_snapshot
  WHERE captured_at < now() - interval '30 days';
$$;

SELECT cron.schedule('ingest-snapshot', '* * * * *',
                     'CALL snapshot_ingest()');
```

Note: `pg_cron` runs jobs in the database set by `cron.database_name`, which defaults to `postgres`. For tables in another database, call `cron.schedule_in_database` with that database name as the fourth argument.

Collect two weeks of production snapshots before you enable alerts, so the baseline includes normal daily peaks.

### Step 4: Measure the ceiling

Run the load test on staging while production builds its baseline. The `pgbench` script below inserts 100 readings per transaction. Match the batch size and tag count to your real workload.

```SQL
-- ingest.sql
INSERT INTO sensor_readings (time, tag_id, value)
SELECT clock_timestamp(), (random() * 5000)::int, random()
FROM generate_series(1, 100);
```

Start at your peak rate from Step 1 and raise it by 10% every 15 minutes. At 100 rows per transaction, 500 transactions per second is 50,000 rows per second. The shell loop below runs each rate for 15 minutes:

```Shell
for tps in 500 550 605 666 732 805; do
  pgbench -n -f ingest.sql -c 16 -j 4 -R $tps \
    -T 900 -P 60 staging
done
```

The ceiling is the last step where three conditions hold: the lag figure in pgbench's progress lines, which shows transactions starting late, stays near zero; replication lag returns to zero; and the dead tuple count stays level.

Record each signal's value at that step, using your failover tolerance for `lag_bytes` if it is lower. The SQL below creates an `ingest_ceiling` table with one row per signal. Replace the example values with the results from your load test.

```SQL
CREATE TABLE ingest_ceiling (
  signal  text PRIMARY KEY,
  ceiling double precision NOT NULL
);

INSERT INTO ingest_ceiling VALUES
  ('rows_per_sec',         73200),
  ('wal_bytes_per_sec',    40000000),
  ('checkpoints_per_hour', 12),
  ('lag_bytes',            500000000),
  ('dead_tuples',          20000000);
```

Rerun the test after schema changes and each quarter.

### Step 5: Compute headroom

The view below converts snapshot deltas to rates and divides each by its ceiling. Headroom is 100 minus the highest resulting percentage. Checkpoints are counted over a trailing hour, since one checkpoint in a one-minute window would read as 60 per hour.

```SQL
CREATE OR REPLACE VIEW ingest_headroom AS
WITH d AS (
  SELECT captured_at, lag_bytes, dead_tuples,
    extract(epoch FROM
      captured_at - lag(captured_at) OVER w) AS secs,
    greatest(rows_inserted
      - lag(rows_inserted) OVER w, 0) AS ins,
    greatest(wal_bytes - lag(wal_bytes) OVER w, 0) AS wal,
    greatest(checkpoints - first_value(checkpoints)
      OVER (ORDER BY captured_at RANGE BETWEEN
            interval '1 hour' PRECEDING AND CURRENT ROW),
      0) AS ckpts
  FROM ingest_snapshot
  WINDOW w AS (ORDER BY captured_at)
), used AS (
  SELECT captured_at, signal,
    100 * value / ceiling AS pct
  FROM d
  CROSS JOIN LATERAL (VALUES
    ('rows_per_sec',         ins / secs),
    ('wal_bytes_per_sec',    wal / secs),
    ('checkpoints_per_hour', ckpts),
    ('lag_bytes',            lag_bytes),
    ('dead_tuples',          dead_tuples)
  ) v(signal, value)
  JOIN ingest_ceiling USING (signal)
  WHERE secs > 0
)
SELECT captured_at,
  round(100 - max(pct)) AS headroom_pct,
  (array_agg(signal ORDER BY pct DESC))[1]
    AS limiting_signal
FROM used
GROUP BY captured_at;
```

Use the query below to see the three most recent snapshots:

```SQL
SELECT * FROM ingest_headroom
ORDER BY captured_at DESC LIMIT 3;
```

The output below is an example from a staging load step:

```
captured_at    | headroom_pct |  limiting_signal
------------------+--------------+-------------------
 2026-09-16 14:32 |           26 | wal_bytes_per_sec
 2026-09-16 14:31 |           28 | wal_bytes_per_sec
 2026-09-16 14:30 |           29 | rows_per_sec
```

WAL overtook rows inserted as the limiting signal, the early warning Step 2 describes. Negative headroom means a signal has passed its measured ceiling.

### Step 6: Set alerts

Configure the monitor to warn when headroom stays below 30% and page when it stays below 20%. Alerting on the highest headroom across five minutes keeps one slow snapshot from paging anyone. The query below returns that value:

```SQL
SELECT max(headroom_pct) AS sustained_headroom
FROM ingest_headroom
WHERE captured_at > now() - interval '5 minutes';
```

The escalation rule pages regardless of headroom when replication lag has grown in each of the last five snapshots while WAL kept growing. The query below checks for that pattern:

```SQL
SELECT bool_and(lag_up AND wal_up)::int AS escalate
FROM (
  SELECT lag_bytes > lag(lag_bytes) OVER w AS lag_up,
         wal_bytes > lag(wal_bytes) OVER w AS wal_up
  FROM ingest_snapshot
  WINDOW w AS (ORDER BY captured_at)
  ORDER BY captured_at DESC
  LIMIT 5
) s;
```

Create three alert rules in your alerting tool: `sustained_headroom` below 30 (warning), below 20 (page), and `escalate` above 0 (page). If you're using Grafana, add the database as a PostgreSQL data source first.

## Validate the monitor

On production, set the `rows_per_sec` ceiling to the rate your broker reports right now. Within five minutes, headroom should read close to zero with `rows_per_sec` as the limiting signal, and both headroom alerts should fire. A value far from zero means the snapshot counts the wrong tables. Restore the measured ceiling afterward.

On staging, run `SELECT pg_wal_replay_pause()`; on the replica during a pgbench step. After five snapshots, escalate should return 1. Resume with `pg_wal_replay_resume()`.

## Respond to the running monitor

When your monitor alerts you, try the following actions to give the database time to drain the backlog:

-   Let the broker hold readings if its retention covers your recovery time.
-   Pause columnstore policy and continuous aggregate refresh jobs with [`alter_job(job_id, scheduled => false)`](https://www.tigerdata.com/docs/api/latest/jobs-automation/alter_job). Both write WAL.
-   Raise `max_wal_size` to reduce requested checkpoints. Crash recovery takes longer as a result.

These actions raise the ceiling:

-   Batch inserts into multi-row `INSERT` or `COPY` statements. Fewer commits mean fewer WAL flushes.
-   Drop indexes no query uses. Each index adds WAL to every insert.
-   Size the chunk interval so the newest chunk and its indexes fit in memory.
-   Evaluate [direct compress](https://www.tigerdata.com/docs/build/data-management/write-data/insert), a TimescaleDB tech preview that writes batches straight into the columnstore with less WAL per row. It requires a hypertable without unique constraints.

## Conclusion

Ingest safety is the distance between your current write load and a ceiling you measured. WAL volume, replica replay, dead rows, and forced checkpoints set that ceiling, and it falls as tags, indexes, and background jobs accumulate. Measure that distance every minute and you get an alert while there is still capacity to drain the backlog.

Deploy `snapshot_ingest()` on production today, then run the load test on staging while the two-week baseline fills in. If headroom drops below 30% at your normal daily peak, [start a Tiger Cloud free trial](https://console.cloud.timescale.com/signup) and measure the same workload with direct compress writing batches into the columnstore.