DEV Community: Tiger Data (Creators of TimescaleDB)

Time-Series Cardinality: Why One More Indexed Column Costs More Than a Million More Rows

Team Tiger Data — Fri, 28 Aug 2026 16:29:32 +0000

Cardinality is a consequence of how many dimensions you index your readings by. Here is where the curve bends, measured, and what you can do about it without leaving Postgres.

Someone adds a firmware_ver column to the sensor table. It is one line of DDL, and it clears review in a minute. Two weeks later, the ingest job is missing its window, a dashboard query that used to return in milliseconds takes seconds, and the on-call engineer is digging through EXPLAIN output to work out when the planner stopped using the index. The ingest rate barely moved. What moved is the index shape: one more indexed dimension, so the number of distinct series the database has to track just multiplied.

You did not hit an engine ceiling. You changed a schema decision you own, which is why the fix is a schema change and not a new database. This piece puts a number on what that one-line change costs, measured against a million more rows from the same baseline, and shows where that cost actually lives: in index width and planner estimates, both of which follow from the schema.

"Tags Are Free": The Default That Works Until It Doesn't

Every mainstream time-series onboarding teaches the same model. Attach every attribute you might want to filter on directly to the reading. In InfluxDB line protocol, they are tags; in Prometheus, they are labels; and in Postgres, they are columns on a wide table. Device, sensor, unit, site, line, firmware. The engine then treats every unique combination of those values as its own series.

At small scale this is the right call. There is no metadata table to design, no join on the read path, and no surrogate key to mint and maintain. A query scoped to a tag is fast because the tag is right there in the index. You get all of that without designing anything up front.

It rests on one assumption: the set of attributes you index by is fixed and known in advance. For a product with a defined metric set, that assumption holds, and the wide table stays the correct choice for its whole life.

An industrial fleet violates the assumption by design. Every integration brings a descriptor someone wants to filter on. A new vendor adds a firmware field. Compliance wants a shift code, and the maintenance team wants a line variant. None of those is a mistake, and each one is a new indexed dimension.

What Cardinality Actually Means, and the Two Ways It Grows

The term is used loosely, usually as a synonym for "how many tags we have," so define it strictly before leaning on it. Cardinality is the number of distinct values one dimension can take. The number of distinct series the database tracks is the product of those counts across every dimension it indexes. Ten thousand assets, a hundred sensors each, ten firmware revisions, and a hundred sites is roughly a billion combinations.

That definition splits growth into two axes that get conflated constantly.

The first is linear. Add values to a dimension you already index, and the series count rises in proportion. A hundred more devices add a hundred devices' worth of series. A sensor that starts reporting 0 to 100 instead of 0 or 1 raises that dimension's cardinality from two to a hundred, and the series count with it. The limiting case is a continuously valued tag, such as a GPS coordinate, where the dimension is effectively unbounded.

The second is multiplicative. Add a new indexed dimension, and the series count is multiplied by that dimension's distinct count. One column, one review comment, and the number of things the database has to track jump by a factor. The schema change that does this looks trivial on the diff, which is the entire reason the failure surprises people.

In index-shape notation, the contrast is one line. (tag_id, value, timestamp) identifies a series by one dimension. (tag_id, device, location, unit, firmware, value, timestamp) identifies it by five.

In Postgres, that multiplication lands somewhere specific, and it is not where the tag-indexed intuition says. A composite B-tree holds one entry per row , not per distinct combination, so index size is roughly rows × entry_width, where entry width is the sum of the indexed column widths plus per-entry overhead.

Change	What it costs in Postgres
One million more rows	`1,000,000 × entry_width`
One more indexed column	`existing_row_count × new_column_width`

That second row is why the article title can be true: its cost scales with every row you already have. Whether it is true depends on the baseline, because on a small enough table, the million new rows outweigh the new column. The benchmark below fixes a baseline and measures both changes from it.

The second cost is the planner's, and it is the sharper one. Postgres assumes column independence when it combines selectivities, and in a production fleet, the dimensions are heavily correlated: a device sits at one site, on one line, running one firmware. Multiply correlated selectivities as though they were independent, and the row estimate collapses, with the error compounding on every correlated predicate you stack. That is how an index scan flips to a sequential scan with no change to the SQL. Extended statistics can patch the estimate without a migration, and "What You Are Trading" below weighs what that buys and what it leaves in place.

The same root cause presents differently depending on the engine, which is why two engineers can describe incompatible symptoms and both be looking at cardinality. A tag-indexed engine holds a series index mapping every unique series key, so memory, startup time, and OOM risk scale with series count, and the only levers are removing a tag dimension or deleting data. A wide Postgres schema fails through ordinary Postgres mechanics instead: indexes bloat on high-cardinality columns, autovacuum runs longer and competes with write I/O, planner statistics drift until an index scan silently becomes a sequential scan, and per-chunk planning overhead grows as wide rows shrink the rows per chunk.

From the inside, both look like the engine hit a wall. That is why the reflex is to re-platform instead of remodel.

The Cliff, Measured: A Cardinality Benchmark

Almost nothing published shows the actual curve, so we measured it. One baseline of a hundred million rows of correlated fleet data on Postgres 17.10 with TimescaleDB 2.29.1, and from it each arm changes exactly one thing. Arm A adds a million rows. Arm B adds one indexed column, firmware_ver, the same column the opening anecdote adds. Arm C is the control, sweeping distinct tag count at fixed total volume: if cardinality by itself degrades Postgres, this is where it shows. Every arm was rerun at three baseline scales, so what follows is the shape of the curve rather than a single point on it.

Arm	Index size delta	Estimate-vs-actual ratio	p95 latency	Insert throughput
Baseline	2.9 GB	9.17x	point 0.2ms / range 0.7ms / rollup 313.5ms	164,956 rows/s
A. +1,000,000 rows	+30.1 MB	9.68x	point 0.3ms / range 0.5ms / rollup 304.6ms	168,113 rows/s
B. +1 indexed column	+864.9 MB	10.42x	point 0.3ms / range 0.6ms / rollup 311.6ms	137,941 rows/s
C. 4k distinct tags	+1.5 MB	10.44x	point 0.5ms / range 0.5ms / rollup 46.9ms	148,073 rows/s
C. 40k distinct tags	+16.0 KB	10.31x	point 0.2ms / range 0.6ms / rollup 251.8ms	160,808 rows/s
C. 200k distinct tags	-128.0 KB	9.44x	point 0.1ms / range 0.4ms / rollup 1,008.3ms	180,675 rows/s

Deltas are against the baseline. Arm B ran a second time with a near-unique independent column instead of firmware_ver, and cost roughly double for the same one-line change, because the two extremes of "add a column" should not be expected to report the same.

One more indexed column cost 28.73 times what a million more rows did. The write path agrees: the column costs about a sixth of insert throughput; the million rows cost none. That multiple belongs to this baseline, though, exactly as the arithmetic above says it must. Run the same two changes on a small enough table, and the inequality inverts.

The orange line is a million more rows: constant near 30 MB at every baseline. The blue line is a one-indexed column: its cost scales with every row the table already holds. They cross near four million rows, below which the column is the cheaper change.

The planner numbers are the ones to bring to a design review. Filtering on two correlated dimensions, site and line, the estimate came out roughly nine times low. Extending that filter along the correlated chain to four collapsed it by three orders of magnitude, to 2,880 times low, on predicates the wide schema invited. The compounding comes from stacking correlated columns rather than from data volume: the two-predicate figure held at every scale we ran.

And the control came back flat. We went looking for the cliff along the cardinality axis, and it is not there. Multiplying the distinct tag count fifty times over left the index where it was, and the estimate ratio with it. The cliff belongs to the tag-indexed engine's in-memory series index, which scales with series count by design and could not produce that flat line. Postgres pays somewhere else, in index width and planner estimates, and both are properties of the schema.

Note the y-axis unit: megabytes. The whole fifty-fold sweep barely moved a 2.9 GB index, and the largest tag count came out slightly below baseline. One added column moved that same index by nearly a gigabyte.

Two honest boundaries. The flat line is measured to 200,000 tags, and industrial fleets reach millions of series, so nothing here licenses extrapolating it indefinitely. And the control's latency and throughput cells track the generator's fleet shape rather than cardinality, which is why the control is read on index size and estimate ratio.

The mechanism was documented in Postgres's behavior before this run. What the run adds is the number on the inequality, the location of the crossover, and a flat line where the folklore promised a cliff.

The Reframe: Cardinality Is a Schema Decision, Not an Engine Ceiling

The wide schema indexes the fact table by the dimensions that describe the series, not by the series itself. It carries every descriptor on every row, forever, and pays for it on every insert and in every planner estimate.

Normalize it. Give each distinct measurement point one surrogate identifier, store its descriptive attributes once in a metadata table, and key the narrow reading table on that identifier alone.

CREATE TABLE tag_metadata (
    tag_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tag_name TEXT NOT NULL UNIQUE, -- 'Line3/Press2/Temp'
    device_id TEXT NOT NULL,
    site TEXT NOT NULL,
    line TEXT NOT NULL,
    firmware_ver TEXT NOT NULL,
    unit TEXT NOT NULL,
    ts_start TIMESTAMPTZ,
    ts_end TIMESTAMPTZ,
    ts_last_seen TIMESTAMPTZ
);

CREATE TABLE sensor_readings_narrow (
    recorded_at TIMESTAMPTZ NOT NULL,
    tag_id BIGINT NOT NULL,
    value DOUBLE PRECISION NOT NULL
) WITH (
    tsdb.hypertable,
    tsdb.partition_column = 'recorded_at'
);

CREATE INDEX ON sensor_readings_narrow (tag_id, recorded_at DESC);

The index collapses from (tag_id, device_id, site, line, firmware_ver, recorded_at) to (tag_id, recorded_at). Nothing was added to fix this schema; four columns were removed because tag_id was the series identity the whole time. The descriptors still exist. They live in tag_metadata, one row per tag, off the write path and out of the fact table's index.

The claim that adding a descriptor cannot multiply the series count follows arithmetically from the strict definition above. If the series count is the product of the counts across indexed dimensions, and there is exactly one indexed dimension, then adding a descriptor multiplies nothing. The series count grows only when you genuinely add tags.

There is a competing strategy, and pretending otherwise would be dishonest. InfluxDB 3 was rebuilt on a columnar, object-storage architecture and is pitched as offering unlimited cardinality. That is a real and different answer to the same problem, and it is a vendor claim rather than an independently benchmarked result. The difference that matters is where the work lands: one path asks you to adopt a new storage engine; the other asks you to run a normalization you already know how to write.

What You Are Trading

Relational metadata is not free. You pay upfront for schema-design work, a one-time migration with a maintenance window in it, and a join on the read path. Here is the other side of that ledger, measured: the same hundred million readings in both shapes, same box, same settings, with tag_metadata counted in full on the narrow side.

Index and total storage for the same hundred million readings, before and after. The narrow bars include tag_metadata in full, heap, and indexes both, which comes to 9.4 MB.

An index two-thirds smaller, and total storage cut roughly in half. Reads move the same way that the wide schema was hurting: the rollup on two correlated dimensions got meaningfully faster, and the four-predicate version got about thirty times faster. Point lookups and short-range scans are a wash, sub-millisecond in both shapes, which is what you would expect from queries that were always driven by the leading key. Writes gained somewhere between a quarter and a third, depending on whether the ingest path resolves tag ids on every insert or works from a cached map. The ceiling becomes ordinary Postgres row-count limits and index behavior rather than an in-memory series index.

One caveat on that ledger. Compression was off on both sides for comparability, so read those numbers as an uncompressed comparison. Turning it on changes both, and the wide table has more to gain in ratio terms, since a descriptor repeated on every row is the easiest thing a columnar format will ever compress. The metadata table needs no such caveat. It holds one row per tag while readings accumulate per tag over time, so the two scale together and the rounding error stays a rounding error.

The tempting claim is that normalizing cures the planner's independence assumption. It does not. The descriptor columns do not disappear. They move into tag_metadata, where Postgres misestimates them by much the same margin as before. What changes is which table pays for the error. Descriptor filters now resolve against a fifty-thousand-row metadata table, where a bad estimate costs close to nothing, while the fact table is reached by resolved tag_ids and estimated almost exactly right. On the wide schema, that same misestimate landed on the hundred-million-row fact table and reached three orders of magnitude, which is what the thirty-fold gap above is made of. The blind spot survives the migration. It stops being expensive.

If misestimation is your only symptom, CREATE STATISTICS will fix it with no migration at all and is the right first move; it just leaves index width, write amplification, and vacuum cost exactly where they were.

The query-rewrite cost is the one you will feel first, and a compatibility view is what bounds it: a view carrying the old wide shape, built by joining the narrow table back to tag_metadata. Without it, normalizing means rewriting every dashboard query on cutover day. With it, existing reads keep working, and the rewrite becomes incremental. The view is a join on the read path and we expected to charge you for it, but measured against querying the narrow table directly it lands within two percent on the rollups and below measurement noise on the sub-millisecond queries. The view is not what will force the schedule.

One constraint this piece does not solve. Industrial tags are not uniformly float. Booleans, integers, strings, and state codes all show up in the same historian, and a single value DOUBLE PRECISION column forces a decision you will hit on day one: multiple typed columns, one table per value type, or a wider union type.

Where to Start

The slowdown you are seeing as the fleet grows is a property of your index shape, and index shape is yours to change. The way across is a migration inside Postgres. You already know how to write it.

Before you plan anything, check one property of your own data: whether each tag has always carried the same descriptors, or whether some of them moved lines and changed firmware along the way. A single scan over your wide table answers it, about a minute against a hundred million rows.

Stable descriptors mean the metadata table falls straight out of the data you already have. Tags that change over their lifetime are still migratable; they just take longer. The reshape itself is a batched, resumable migration that leaves every historical row where it is; we ran it end to end, failure drills included, and it is the subject of a follow-up piece, along with the scan that tells you which case you are in. If you would rather have the hypertable, compression, and policies managed while you do it, that is what Tiger Cloud is for.

14,000x Faster Planning for LIMIT Queries on Hypertables

Team Tiger Data — Thu, 27 Aug 2026 14:18:10 +0000

A very common query against a time-series table is fetching the latest row for a device:

SELECT * FROM metrics WHERE device = 'D001' ORDER BY time DESC LIMIT 1;

This should be instant. The row lives in the newest chunk, that chunk has an index on device, time, and reading it is one index scan. But on a hypertable with a few thousand chunks the query can take tens or hundreds of milliseconds before it returns anything, and nearly all of that time is spent planning rather than executing. It also gets worse as the table grows: planning time scales linearly with the number of chunks, even though the query still only reads a single row.

Across the Tiger Cloud fleet, we spend over 1,000 CPU hours every day planning ORDER BY time LIMIT-style queries.

Where the planning time goes

A hypertable is a PostgreSQL table, and so is each of its chunks. The chunks are child tables of the hypertable, connected through PostgreSQL's table inheritance. As part of planning a query TimescaleDB expands the hypertable into its chunks and builds an append node over their scans.

TimescaleDB prunes chunks before it expands them. It uses the dimension metadata in its catalog to determine which chunks the query's WHERE clause can touch, and only those chunks get expanded. A query like WHERE time > now() - interval '1 hour' only expands the chunks matching the constraint.

The cost appears when nothing prunes, which is whenever the query has no constraint on a dimension column. For every chunk that survives pruning, TimescaleDB opens and locks it, reads its statistics, and plans how to scan it. This is paid once per surviving chunk, so at 10,000 surviving chunks it dominates planning.

Our example query, WHERE device = 'D001' ORDER BY time DESC LIMIT 1, restricts nothing on the time dimension, so nothing prunes and every chunk survives. The LIMIT only bounds how many rows come back.

Why every chunk ends up in the plan

The LIMIT 1 does not let the planner keep only the newest chunk and drop the rest. The planner cannot prove the others are unnecessary.

A chunk can only be left out of the plan if the query's constraints show it can't contribute a row. A LIMIT only bounds how many rows the query returns. Nothing about it guarantees the newest chunk holds the answer. That chunk might be empty, or every row in it might be filtered out by a constraint on a non-dimension column, in which case the matching row is in an older chunk. A correct plan has to be able to fall through to those chunks.

The best available plan is an ordered append over the chunks, producing rows in time order. TimescaleDB's ChunkAppend does this: it visits chunks newest-first and stops as soon as the LIMIT is satisfied. For ORDER BY time DESC LIMIT 1 it scans only one chunk, and the others show up as never executed in EXPLAIN ANALYZE.

The plan contains every chunk. Only plan-time pruning removes chunks from the plan, and it needs a dimension constraint the planner can fold to a constant while planning. The LIMIT's early stop and ChunkAppend's run-time exclusion run at execution, so they skip scanning a chunk but do not remove it from the plan. Run-time exclusion covers constraints whose value is known only at execution, such as a query parameter or a stable expression.

When the query starts, the executor sets up each chunk's part of the plan, including those that are never executed. The chunk count is paid twice, once during planning and once during executor startup; the LIMIT avoids only the scanning.

Leaving the hypertable unexpanded

DeferredChunkAppend is a new custom scan node that skips expansion during planning. It leaves the hypertable as a single relation instead of turning it into an append over chunks. The plan for the query above is just:

Limit
  -> Custom Scan (DeferredChunkAppend) on metrics
       Order: "time" DESC

No append, no per-chunk paths, nothing in the plan that grows with the number of chunks. Planning is constant.

The chunk work moves to execution instead, where the Limit can stop it early.

What happens at execution time

The node fetches chunks one at a time, in the order the query requires. With ORDER BY on the time dimension it takes them in time order, ascending or descending, so a small LIMIT is usually satisfied by the first chunk. Without ORDER BY it takes chunks newest-first. Because it fetches lazily and stops once the LIMIT is satisfied, it touches only the first few chunks. EXPLAIN (ANALYZE) reports a Chunks Visited counter with the number of chunks it opened.

For each chunk the node builds a plain SQL query against the chunk's table and runs it. With a pushed-down limit it looks like this:

SELECT time, device, value FROM _timescaledb_internal._hyper_1_42_chunk
WHERE <pushed-down filter>
ORDER BY time
LIMIT 5;

Each chunk is scanned by a normal planned query, so the node needs no logic of its own for the different chunk states, such as uncompressed, compressed, partially compressed, or ordered; the per-chunk query handles each.

When it applies

DeferredChunkAppend works by stopping once the LIMIT is satisfied, so it is used only when the LIMIT sits directly on top of the hypertable scan. An aggregate, GROUP BY, DISTINCT, a window function, HAVING, a set operation, or a join would sit between the LIMIT and the chunk scans, so the LIMIT would no longer bound how many rows are read from the chunks; those queries keep the append plan. The query must also read a single hypertable and must not have row-level-security policies, since scanning chunks directly would bypass them.

ORDER BY is only supported on the primary dimension in its natural order, with trailing keys allowed, because that's the order the chunk walk can produce without sorting. Ordered mode also requires a hypertable with a single (time) dimension; on a space-partitioned hypertable an ORDER BY query keeps the append plan, while a plain LIMIT with no ordering still uses the node.

The query can optionally have a WHERE clause, as long as it doesn't constrain a dimension column. Constraints on the dimension columns are what ordinary chunk exclusion is for, so those queries keep the append plan. Filters on regular columns are supported and pushed into each per-chunk query.

Numbers

The tables below show how planning time grows with the chunk count. The same data set is built two ways, as a native PostgreSQL declarative-partitioned table and as a TimescaleDB hypertable, at 1, 10, 100, 1,000, and 10,000 chunks, and SELECT * FROM t ORDER BY time LIMIT 1 is run on PostgreSQL 17.7 and 18.3, using release builds of TimescaleDB. Each number is the median planning time over 15 warm runs. The hypertable is measured with DeferredChunkAppend off and on, so the two hypertable rows differ only in the feature.

PostgreSQL 18 planning time (ms)

Configuration / Chunks

100

1,000

10,000

Declarative partitioning

0.042

0.170

1.310

14.3

183

|
|

Hypertable (ChunkAppend)

0.054

0.190

1.300

16.3

202

|
|

Hypertable (DeferredChunkAppend)

0.018

0.017

0.014

0.013

PostgreSQL 17 planning time (ms)

Configuration / Chunks

100

1,000

10,000

Declarative partitioning

0.038

0.140

1.380

24.2

3583

|
|

Hypertable (ChunkAppend)

0.048

0.210

1.270

25.5

3660

|
|

Hypertable (DeferredChunkAppend)

0.016

0.018

0.013

Both plans that expand chunks, native partitioning and the hypertable with the feature off, grow with the chunk count. At 10,000 chunks on PG18 that is 183 ms for native partitioning and 202 ms for the hypertable; on PG17 native partitioning and the hypertable reach 3.6 seconds. DeferredChunkAppend stays around 0.014 ms across the whole range, on both versions.

Execution scales too, because the executor sets up each chunk before the append runs. The append plan's execution grows from 0.009 ms at one chunk to 27 ms at 10,000 on PG18, while DeferredChunkAppend stays around 0.12 ms. Execution is the smaller cost: at 10,000 chunks the append plan spends about 27 ms executing against 200 ms planning on PG18.

This continues work we described in Optimizing queries on TimescaleDB hypertables with thousands of partitions, which cut planning time 15x by speeding up chunk expansion itself. DeferredChunkAppend takes the next step for this query shape: instead of expanding faster, it skips expansion entirely.

Trying it

DeferredChunkAppend ships in TimescaleDB 2.30. It's on by default, behind a GUC:

SET timescaledb.enable_deferred_chunk_append TO on; – default

Run a qualifying LIMIT query and look at the plan:

EXPLAIN (COSTS OFF) SELECT * FROM metrics ORDER BY time DESC LIMIT 1;
EXPLAIN (COSTS OFF) SELECT * FROM metrics WHERE device='D0001' ORDER BY time DESC LIMIT 1;

The plan should show a Custom Scan (DeferredChunkAppend) node in place of an append over the chunks.

Summary

Expanding a hypertable into all its chunks makes sense when a query reads most of the data. A LIMIT query that returns only a few rows doesn't; it was paying that same per-chunk cost for every chunk without needing it. A LIMIT bounds what comes back, not what gets planned; those are separate costs, and mixing them up is what made this query slow in the first place. DeferredChunkAppend is what happens when you stop conflating them: it defers the chunk work to execution, where the LIMIT ends it early, so fetching the latest row takes constant planning time regardless of the number of chunks.

DeferredChunkAppend is already running across the Tiger Cloud fleet, on by default. If you want to see it on your own hypertables, create a Tiger Cloud account and run EXPLAIN on your own ORDER BY time LIMIT queries.

Why We Aren't Ready for C-3PO

Team Tiger Data — Tue, 25 Aug 2026 12:59:27 +0000

When people picture a robot, they picture C-3PO (or if you're a Trekkie, it's Lieutenant Commander Data and his brother Lore): a humanoid that walks into any room, understands what you say, picks up anything, and improvises when things go sideways. Decades of science fiction trained us to treat that as the target, the thing we are all building toward and almost have.

For years the working assumption was that we were waiting on smarter robot brains. The evidence points the other way. The brain is now the fastest-moving part of the stack. The hard problems have moved into the body: dexterity, reliability, and a data problem with no internet-scale shortcut.

The same reordering shows up well beyond humanoids, in drones, autonomous vehicles, and factory automation, anywhere AI leaves the screen and has to act. And the machines that look nothing like C-3PO are already doing real, valuable work at scale, precisely because they refuse to be general.

The companies racing hardest toward the generalist dream are the best evidence for this. Follow their revenue, not their demos.

The brain is moving faster than the body

The reasoning layer has moved further than most people outside the field realize. Modern vision-language-action models (VLAs), which map what a robot sees and is told into what it does, borrow their backbones from the same vision-language models that power frontier multimodal LLMs. Consider how Google DeepMind shipped Gemini Robotics 1.5 in late 2025. They split it into two models. Gemini Robotics-ER 1.5 is the embodied-reasoning model: spatial grounding, multi-step planning, success and progress estimation, native tool calls. It reports state-of-the-art results across 15 academic embodied-reasoning benchmarks, and DeepMind shipped it to developers through the Gemini API. The action model, Gemini Robotics 1.5, the part that issues motor commands, was gated to select partners behind a waitlist.

That split is the tell. A lab ships the part it trusts and holds back the part it doesn't. Here the thinking ships while the doing stays behind a waitlist. The reasoning isn't finished, but it's no longer the part holding robots back.

The frontier of cognitive research is now aimed at the physical-world gap. Yann LeCun left Meta in late 2025 and raised about $1.03B for AMI Labs (announced March 2026) on the thesis that today's language models are the wrong substrate for physical intelligence, and that systems should instead learn how the world behaves from video and interaction. His group's V-JEPA 2 is one instance: trained on over a million hours of internet video plus under 62 hours of robot interaction data, it ran zero-shot on robot arms in two different labs for pick-and-place, with no data collected from either robot. The specific architecture is beside the point. Even the researchers most bullish on cognition are spending their money on the embodiment-and-data gap, not the reasoning one.

You can see the speed gap in the release cadence too. In about a year, embodied reasoning moved fast:

Gemini Robotics 1.5 to ER 1.6, each posting new state-of-the-art on embodied-reasoning benchmarks
π-series VLAs, GR00T, and V-JEPA 2
a new billion-dollar lab founded specifically to push it further

Over the same window, the things that actually gate deployment barely moved:

production robots still grip with two-fingered parallel jaws
they still run behind human supervision
they still earn reliability the slow way, through engineering rather than a model update

When one layer ships a new SOTA every few months and the layer beneath it advances on the timescale of hardware revisions, "the brain is moving faster" stops being a metaphor.

What is actually slowing things down

If intelligence is no longer the binding constraint, something else is. Three things, all of them unglamorous: dexterity, reliability, and data.

Dexterity

Human hands have more than 20 degrees of freedom, and coordinating contact-rich manipulation across them is still unsolved at production reliability. The clearest evidence is what actually ships: despite years of dexterous-hand research, most real-world robotic applications still run on simple parallel-jaw grippers, and in-hand manipulation (repositioning an object within a grasp) remains a seminal open challenge. The benchmarks show how sharply performance falls off: the same learned policies that pick objects reliably, around 80% and up, drop toward zero on contact-rich tasks like standing a cup upright or stacking, where the object has to be shifted within the hand mid-task, and the best policies on real multi-finger hands land near 63% average success across a handful of everyday tasks, a genuine research result and a non-starter for unattended deployment. The bottleneck is not only the control policy but the hardware itself: tendon-driven multi-finger hands break, drift, and are hard to calibrate.

Reliability

Even where the hands are good enough, the bar that gates deployment is not capability but reliability. A manipulation policy that succeeds 80% of the time is a great demo video. A warehouse or an operating room needs to be measured in nines, across millions of cycles, including the long tail of weird edge cases. The gap between "works in the demo" and "works unattended on the night shift" is most of the actual engineering.

Autonomous driving is the clearest illustration outside the lab. A car that follows roads competently has existed for years. What took a decade was the reliability, and proving it. Waymo had to accumulate more than 170 million rider-only miles by the end of 2025 before the safety case was statistically airtight: 92% fewer crashes causing serious or fatal injury than human drivers in the same conditions, measured with a comparison methodology published in peer review at 56.7 million miles. The car could drive years ago. Making it safe across the edge cases took a decade.

Data

Dexterity and reliability are limited by physics and engineering. Data is a different kind of limit, and unlike the first two it has no internet-scale shortcut. Language models had the internet. There is no equivalent corpus of robot actions. Skild AI, one of the most aggressive generalist players, puts it plainly: there is no "internet of robotics."

The workaround is the "data pyramid": a small amount of real robot teleoperation at the top (a human driving the robot by hand to record demonstrations), a large layer of simulated and synthetically generated data in the middle, and web-scale human video at the base. Look at the numbers each lab publishes:

NVIDIA GR00T N1 generated 780,000 synthetic trajectories, the equivalent of roughly 6,500 hours or nine continuous months of human demonstration, in about 11 hours of compute, because collecting that much real robot data was infeasible.

Physical Intelligence's π0.5 draws 97.6% of its training data from sources other than the target mobile manipulator it is trying to control. The robot you actually care about contributes under 3% of what teaches it.

But that is only the data problem you have before a machine ships. A second one starts the moment it does, and it draws far less attention because it is not about training at all. Everyone talks about training data. Far fewer people talk about operational data, the exhaust a machine throws off while it runs: joint states, video, lidar, odometry, and the record of what it tried and how it failed. Training data is scarce and has to be manufactured; operational data is the opposite problem, a firehose that never stops. The internet trains the brain; reality trains the body. And this is not robotics-only: any machine that senses and acts (a drone, an autonomous vehicle, a piece of grid hardware) generates the same flood of data, and the learning loop everyone is counting on runs entirely on it. You collect the exhaust, replay the failures, annotate them, and feed the lessons back into the next model.

That loop is where deployment turns into improvement, and its infrastructure is far less mature than the models it feeds. The live view that flags a robot misbehaving now, the replay you scrub after it fails, and the training set you assemble months later all want the same telemetry, and most stacks keep three copies fighting to stay in sync, which is how the annotation explaining a failure so often never reaches the next model's training set. The exchange formats the field reaches for (LeRobot for training, MCAP for logging) are inputs to that layer, not the layer itself. It is unglamorous, and it decides whether a deployed machine gets better over time or keeps repeating the same failures. Most teams have not hit it yet because most are not running large fleets; the ones who treat telemetry as core infrastructure early are the ones not rebuilding it under load later.

Generalization in the wild

The state of the art in generalist embodiment is π0.5. It can clean a kitchen or tidy a bedroom in homes it never saw in training, running multi-stage tasks of 10 to 15 minutes. That is a real milestone. But the authors are blunt about its limits: it does not always succeed on the first try, and it errs both in high-level semantic deductions and in low-level motor commands. The wider field shows the same gap at scale: Stanford research reported in Fortune found robots scoring nearly 90% success in controlled simulation succeeded at just 12% of real household tasks. Impressive, real, and several reliability orders of magnitude short of what "C-3PO" implies.

Meanwhile, the narrow robots are already working

Constrain the task and every bottleneck above gets smaller. A defined job means a defined environment, a bounded object set, a tractable data problem, and a reliability bar you can actually clear.

Delivery. Zipline has flown more than 125 million autonomous commercial miles and completed over 2 million deliveries, with over 20 million items delivered and zero serious injuries, at a $7.6B valuation as of January 2026. One job, executed at a scale and safety record no humanoid is remotely near: launch, navigate, drop, return. No humanoid has left the demo stage. Zipline left it years ago.

Warehouse pick-and-place. The lesson of 2025 was that reliability beats novelty. Fully autonomous picking across the entire SKU range (a bag of chips, then a bottle of shampoo, then a pair of shoes) is still unsolved at the level that fully replaces a human picker. But constrained, high-volume, low-variability operations (grocery distribution, pallet handling, sortation) scaled faster than almost anyone predicted. Symbotic runs systems for Walmart, Target, and Albertsons against a multibillion-dollar backlog; Covariant's models handle items they have not seen across apparel, pharma, and 3PL. The winners narrowed the problem until it was reliable. At the far end of that strategy sit "dark factories" like Xiaomi's Changping plant, which runs lights-out at roughly 81% line automation by redesigning the environment around the machines: fixed stations, known part positions, engineered tolerances.

Surgery. Surgery isolates the variable better than any other example. Intuitive's da Vinci performed roughly 2.6 million procedures in 2024 and is the gold standard for minimally invasive surgery. It is also Level 0 autonomy: every motion is driven by a human surgeon. Set that against a Johns Hopkins and Stanford result: a VLM trained on about 20 hours of surgical video had a da Vinci autonomously suture, lift tissue, and manipulate a needle on animal tissue, recovering a dropped needle zero-shot. The intelligence for complex surgical subtasks can be learned from roughly twenty hours of demonstration. Lack of intelligence is therefore a weak explanation for why autonomous surgery is not deployed. The real one is that failure is unacceptable, which is why the field pursues "augmented dexterity," automating narrow subtasks under a surgeon ready to take over at any instant. Reliability gates deployment, not intelligence.

Inspection. Boston Dynamics has several thousand Spot units patrolling oil platforms, substations, nuclear sites, and factories, where a single unit replaces hundreds of static sensors walking predetermined routes. Boston Dynamics puts the annual value to a typical manufacturing customer at roughly $252,000, and the breakdown is instructive: about $182,300 of it is averted equipment breakdowns and $32,500 is energy saved by catching compressed-air leaks, while the labor freed up by automating the walkthroughs themselves accounts for only $30,200. These are the vendor's own figures, drawn from what it calls a representative average of real manufacturing customers. Even in the clearest narrow-robot success story on the board, most of the money comes from catching failures early, not from replacing the person who used to walk the route. In April 2026 they shipped the next iteration of that same reasoning line, Gemini Robotics-ER 1.6, into Spot's inspection product, whose instrument-reading capability (reading analog gauges, measuring sight glass fullness) was built through the Google partnership and did not exist in the prior generation. The discipline shows up on the research side too. In a hackathon demo running the previous model in a residential home, the engineers were explicit that the reasoning layer "can't invent new capabilities or control Spot beyond what is available through the API," which "keeps Spot's behavior predictable." The same boundary holds in the field: when conditions degrade and steam obscures a gauge, Spot stops, documents the obstruction, and pings a human. The system knows what it does not know. A frontier reasoning system, a constrained body, strict guardrails, and uncertainty escalated to a human instead of guessed at.

Even the billion-dollar robot brains sell narrow

Tesla is building Optimus, Figure is putting a humanoid on commercial timelines, Physical Intelligence is training cross-embodiment foundation models, and a wave of capital is betting that the split between narrow and general is just a phase that scale will erase. So test the thesis against the strongest member of that camp. If anyone should invalidate it, it is the company pursuing the most aggressive general-purpose vision with the most money behind it. Right now that is Skild.

Skild AI raised about $1.4B at a $14B+ valuation in January 2026 to build the Skild Brain, one foundation model that claims to control quadrupeds, humanoids, arms, and mobile manipulators, with backers from SoftBank to NVIDIA to Samsung. That is the omni-bodied dream, fully funded, by serious people, as a direct rejection of the narrow-versus-general split. Then you follow the revenue. Skild went from zero to roughly $30M in 2025, and the deployments generating it are narrow: security, inspection, delivery, warehouses, data centers, construction. The household and humanoid generalist is explicitly the eventual goal, with enterprise tasks as the first application. Even the cost story points the same direction. NVIDIA's Skild case study sells the Brain running on $4,000 to $15,000 hardware versus $250,000-plus custom systems, which is a pitch about making narrow deployments cheaper, not about a humanoid that does everything in your kitchen.

So the most ambitious "any robot, any task" company on the board, the one that raised $1.4B precisely to build a general robot brain, still earns its near-term revenue from purpose-built machines doing constrained jobs. If any company should have invalidated this thesis, it was this one, and instead its income statement confirms it. For now, the vision is omni-bodied; the income is single-purpose.

The Spot-plus-Gemini pattern, a frontier mind riding a narrow dependable body, is what the next wave looks like, with those bodies widening as dexterity, reliability, and the data loop catch up. The likely path was never "humanoid arrives fully formed." It is increasingly general intelligence on increasingly capable bodies, converging slowly, intelligence leading the way.

C-3PO is still coming. Just not first, and not soon. The intelligence got there first. The body, the reliability, and the data come next. And this was never really about a gold humanoid from a movie. It is about what AI does once it leaves the screen, and for now the answer is a capable mind riding a narrow, dependable body. The robots we are ready for are already flying over our neighborhoods, running our distribution centers, and reading gauges in places no human should have to stand. That is not a lesser future. It is a remarkable one, and it's here.

Get started

This post explains why purpose-built robots are winning first. The next question is architectural: what data layer lets deployed machines become better machines? Schema design, fleet telemetry, replay, retention, operational analytics. That is where the loop either closes or doesn't.

Tiger Data (creators of TimescaleDB) works with teams building production systems on operational time-series data. For managed deployments, explore Tiger Cloud. For regulated, air-gapped, or on-prem environments, contact us about TimescaleDB Enterprise.

How to Tell Whether Your Slow Query Is a Planning or an Execution Problem

Team Tiger Data — Fri, 21 Aug 2026 15:47:50 +0000

Every EXPLAIN ANALYZE prints two numbers at the bottom, and most engineers read only one of them. Planning Time is how long Postgres spent deciding how to answer your query, while Execution Time is how long it spent actually answering it. When latency climbs, those two numbers point to opposite fixes: plan caching and fewer partitions on one side, memory and storage layout on the other.

Getting this backwards is expensive. On a table with 500 daily partitions, planning a simple time-range aggregate takes nine times longer than running it, and the reflex to add another index makes the problem worse. Every new index adds a path the planner has to consider, plus write amplification on a table that was already write-bound. You’ll end up spending weeks optimizing the phase that wasn't slow.

The good news: Postgres tells you which phase is slow, for free, in one command.

Every plan below is real output from PostgreSQL 16.13 against this table, partitioned into 500 daily chunks named device_metrics_YYYYMMDD and holding 2.1 billion rows, with work_mem at 4 MB and shared_buffers at 256 MB:

CREATE TABLE device_metrics (
    ts timestamptz NOT NULL,
    device_id bigint NOT NULL,
    metric text NOT NULL,
    value double precision,
    rack int, -- one rack per 100 devices
    region int -- one region per 500 racks
) PARTITION BY RANGE (ts);

rack and region demonstrate correlated columns in Step 3. Substitute your own table and partition names.

What you will learn

How planning time and execution time differ, and why each has a different fix.
How to read EXPLAIN (ANALYZE,BUFFERS) to attribute wall-clock time to a phase.
The signature of a planning problem: high plan time, hundreds of pruned partitions, a nearly idle scan.
The signature of an execution problem: spills to disk, heavy buffer reads, filters that discard most of what they touch.
The exact commands to run once you know which one you have.

Two clocks, two fixes

The planner runs a full optimization pass on every query it hasn't cached a plan for. It enumerates access paths, pulls cardinality estimates out of pg_statistic, evaluates join orders, and prices each candidate. For a warehouse query with eight joins, that work pays for itself many times over.

Partitioned tables change the math. Before the planner can exclude a partition, it locks the relation, loads its relcache entry, and builds planner state for it. That setup runs for every partition, whether or not pruning later discards it, so the cost scales with how many partitions exist rather than how many survive. The PostgreSQL documentation is direct about it: "Planning times become longer and memory consumption becomes higher when more partitions remain after the planner performs partition pruning."

Execution is a different animal. That time goes to reading heap pages, hashing, sorting, and aggregating. It grows with how much data the plan touches, not with how many plans were considered.

One clock measures the decision. The other measures the work.

Step 1: Read the split

Run the query below with both flags. ANALYZE executes it and reports real timings. BUFFERS reports how many 8 KB blocks each phase touched, including the planner.

EXPLAIN (ANALYZE, BUFFERS)
SELECT device_id, avg(value)
FROM device_metrics
WHERE ts > now() - interval '5 minutes'
GROUP BY device_id;

You may see output like the following:

HashAggregate (cost=5510.70..5632.72 rows=9762 width=16)
                (actual time=6.311..6.696 rows=2202 loops=1)
   Group Key: device_metrics.device_id
   Batches: 1 Memory Usage: 913kB
   Buffers: shared hit=932
   -> Append (cost=0.28..5461.89 rows=9762 width=16)
               (actual time=0.775..4.570 rows=9498 loops=1)
         Subplans Removed: 499
         -> Bitmap Heap Scan on device_metrics_20260729
                       (cost=212.21..1272.31 rows=9263 width=16)
                       (actual time=0.774..3.796 rows=9498 loops=1)
               Recheck Cond: (ts > (now() - '00:05:00'::interval))
               Heap Blocks: exact=898
               Buffers: shared hit=932
 Planning:
   Buffers: shared hit=20063
 Planning Time: 62.913 ms
 Execution Time: 7.202 ms

Read the last two lines first. Execution took 7.2 ms. Planning took 62.9 ms. Postgres spent nine times longer choosing a plan than running one, and the plan it chose was excellent: pruning left one partition out of 500, the bitmap scan hit cache on every block, and the row estimate was within three percent.

Now read the Buffers lines against each other. This is the part people miss. Execution touched 932 blocks. Planning touched 20,063. The planner read 21 times more pages than the query did, and it read them to open and price 500 partitions before discarding 499 of them.

Nothing here is fixable with an index. This is a planning problem.

One note on how pruning reports itself. Subplans Removed appears when Postgres prunes at run time, which is what happens with a cached generic plan or a stable expression like now(). When pruning happens at plan time, excluded partitions never appear in the output at all, so you count the ones that survived.

Step 2: Learn the other signature

Now the contrast: a different query against a single partition, where every number moves the other way:

EXPLAIN (ANALYZE, BUFFERS)
SELECT device_id, avg(value) AS avg_value
FROM device_metrics_20260715
WHERE value > 0.5
GROUP BY device_id
ORDER BY avg_value DESC
LIMIT 20;

After running the query, you may see:

Limit (actual time=1658.814..1658.820 rows=20 loops=1)
   -> Sort (cost=257079.27..258179.28 rows=440004 width=16)
             (actual time=1649.815..1649.818 rows=20 loops=1)
         Sort Key: (avg(value)) DESC
         Sort Method: top-N heapsort Memory: 26kB
         -> HashAggregate (cost=219176.70..245370.92 rows=440004 width=16)
                            (actual time=1189.615..1593.002 rows=492515 loops=1)
               Group Key: device_id
               Planned Partitions: 16 Batches: 17
               Memory Usage: 8337kB Disk Usage: 78856kB
               Buffers: shared hit=13212 read=17686,
                        temp read=8165 written=9857
               -> Seq Scan on device_metrics_20260715
                             (cost=0.00..83422.95 rows=2119083 width=16)
                             (actual time=0.098..410.231 rows=2102555 loops=1)
                     Filter: (value > '0.5'::double precision)
                     Rows Removed by Filter: 2099445
                     Buffers: shared hit=13212 read=17686
 Planning:
   Buffers: shared hit=103
 Planning Time: 0.505 ms
 Execution Time: 1682.995 ms

Planning is a rounding error: 0.5 ms against 1,683 ms of execution. All the time sits in the nodes, and three lines say where.

Buffers: shared read=17686 means 17,686 blocks came off disk, roughly 138 MB, against 13,212 cache hits. Batches: 17 with Disk Usage: 78856kB means the hash aggregate outgrew work_mem and spilled 77 MB to temporary files. Rows Removed by Filter: 2099445 means the scan read 4.2 million rows to keep 2.1 million.

Here's the part worth sitting with: the estimates were good. The planner predicted 2,119,083 rows from the scan and got 2,102,555. It predicted 440,004 groups and got 492,515. Both within 12 percent. This plan is slow despite being correctly planned, which means ANALYZE will do nothing for it. The fix is work_mem or a storage layout that doesn't read four million rows to answer a question about two columns.

A correct plan can still be a slow plan. That's why you read the split before touching anything.

Step 3: When the estimates are the problem

Sometimes they are wrong, and the damage compounds because every choice downstream inherits the error. Divide estimated rows= by actual at each node and find the first divergence.

Seq Scan on device_metrics_20260715 (cost=0.00..132989.21 rows=84 width=0)
                                      (actual time=4.128..350.008 rows=832 loops=1)
   Filter: ((region = 3) AND (rack = 1750))

Eighty-four predicted, 832 returned. The planner treated region and rack as independent and multiplied their selectivities, but every rack belongs to exactly one region. When two columns carry a functional dependency, tell Postgres:

CREATE STATISTICS dm_region_rack (dependencies)
  ON region, rack FROM device_metrics_20260715;

ANALYZE device_metrics_20260715;

The same scan afterward:

Seq Scan on device_metrics_20260715 (cost=0.00..133489.25 rows=841 width=0)
                                      (actual time=4.167..352.177 rows=832 loops=1)

841 predicted against 832 actual. One caveat that costs people an afternoon: dependencies statistics only apply to equality predicates. Rewrite the same filter as rack BETWEEN 1700 AND 1799 and the estimate collapses back to 1, because range predicates fall outside what functional dependencies model. Reach for ndistinct or an expression index there instead.

If estimates stay wrong after ANALYZE, raise the sample size. The default default_statistics_target of 100 samples roughly 30,000 rows, which is thin on a billion-row table. The new target changes nothing until the next ANALYZE, so run both:

ALTER TABLE device_metrics ALTER COLUMN device_id SET STATISTICS 500;
ANALYZE device_metrics;

Step 4: Fix the planning side

Execution fixes are familiar territory. Planning fixes are the ones teams skip, so here's the one that matters. A prepared statement caches the plan across executions. Prepare it once:

DEALLOCATE ALL;

PREPARE recent_avg AS
SELECT device_id, avg(value)
FROM device_metrics
WHERE ts > now() - interval '5 minutes'
GROUP BY device_id

Then execute it several times on that same connection:

EXPLAIN (ANALYZE) EXECUTE recent_avg;

Prepared statements live and die with the session, so PREPARE and EXECUTE must share a connection. Running them through separate psql -c calls, or through a pooler in transaction mode, returns prepared statement "recent_avg" does not exist.

Measured that way, Planning Time was 75.697 ms, then 0.179 ms, then 0.087 ms and stayed there. Execution held steady near 3 ms. The first call pays for the plan and every call after it inherits one, which turns a 79 ms query into a 3 ms query without touching an index.

A statement with no parameters gets a generic plan immediately, because there is nothing to specialize on. With parameters, Postgres builds custom plans for the first five executions, then compares the generic plan's cost against the custom average and switches if it holds up. Force the decision with plan_cache_mode = force_generic_plan. Verify by re-running EXPLAIN (ANALYZE) EXECUTE and watching Planning Time collapse. If it collapses and total latency doesn't move, planning was never your problem.

Caching hides the cost rather than removing it, so look at partition count too. If a typical query touches one partition and the planner starts from 500, the chunk interval is finer than the workload needs. Rolling data older than a month into weekly partitions cuts the count by roughly 7x.

To find which queries deserve this treatment across the whole workload, rank them in pg_stat_statements with pg_stat_statements.track_planning enabled, then sort by total_plan_time against total_exec_time.

When Planning Time is the architecture talking

A query that spends most of its life being planned is not a tuning failure. It's the shape of the workload pressing against a general-purpose planner. Postgres opens and prices every partition separately because it was built for schemas where each table might hold something different. Time-based chunks all hold the same thing, so most of those 20,063 buffer reads are waste.

Tiger Data attacks this at the source. Hypertables record chunk time ranges in a catalog table, so chunk exclusion resolves which chunks a query needs before standard planning begins. The cost tracks the chunks a query matches rather than the chunks that exist. Continuous aggregates go further: a dashboard query hits a small incrementally-updated rollup instead of planning a scan across billions of raw rows. The 62.9 ms in Step 1 is precisely the overhead those layers exist to remove, and Tiger Data’s whitepaper covers how.

Before you add another index, run EXPLAIN (ANALYZE, BUFFERS) on your slowest query and read the last two lines. If planning wins, you've been optimizing the wrong clock. Start a Tiger Data free trial today to use the right architecture to fix your slow query for good.

Continuous Aggregate Refresh, Demystified: Invalidation, Lookback, and Late-Arriving Data

Team Tiger Data — Fri, 14 Aug 2026 12:00:28 +0000

Yesterday the shift dashboard reported 41,900 units across an eight-hour window. Today the same query over the same eight hours reports 43,100. Nobody shipped a code change and nobody edited a row after the fact, so why did the number move? A plant-floor historian had lost its uplink, buffered locally, and flushed overnight, landing rows that carry their original timestamps. The raw table was correct at every point along the way. The pre-computed aggregate behind the dashboard never went back for those rows, so it published one number and later replaced it with another.

Every pre-computation strategy handles a fresh row arriving at the head of the table. They diverge on the two events no evaluation demo covers: data that arrives late for a window already computed, and data that changes after the fact. This piece runs the four common strategies against exactly those events to show why each behaves the way it does, then ends where the choice gets practical: the two offsets that decide, on a TimescaleDB continuous aggregate, whether a late row is ever reconciled at all.

The four ways to pre-compute a time-series aggregate

The industry files all of these under one label, "materialized view refresh," and the label is where the trouble starts: it implies one correctness story where there are four. Pre-computation is a bet about when the work happens: at write time, at schedule time, or at query time. Four designs place that bet differently.

1. Scheduled full recompute

A materialized view plus cron. In vanilla Postgres, REFRESH MATERIALIZED VIEW rebuilds the view from scratch on every run, with no incremental path, so between runs the view is stale by construction. You pay the full rebuild every run, whether one row changed or a million did. That stays affordable exactly as long as a rebuild of the range you care about fits inside the schedule interval.

For a daily rollup over a month of data on modest hardware, it fits for a long time. For a fine-grained rollup over years of raw samples, it stops fitting well before anyone notices, because the failure arrives as a slow schedule slip and not as an error. InfluxDB v2 tasks have the same shape: a scheduled Flux task reads a window of raw data and writes downsampled results, and the documented lever for lateness is an offset that delays the run rather than an invalidation layer that reaches back after it.

2. Insert-triggered incremental views

ClickHouse standard materialized views fire on INSERT into the source table and write the incremental result forward. Each insert carries a tiny increment, which is what makes the design attractive at ingest rates where a recompute is out of the question. It rests on a strong premise: the past never changes. Nothing in the standard view reacts to a backfill or a correction, so reconciling history becomes a manual, resource-heavy operation.

The same system offers an in-family alternative, refreshable materialized views, which re-run the full query on a schedule and atomically swap the destination table. That is strategy 1 again, reached for because strategy 2's premise broke.

3. Streaming dataflow engines

RisingWave and Materialize maintain a view through a dataflow graph updated on every source change, targeting sub-second freshness. Late data is another change event entering the graph, so there is no refresh window to reason about at all.

The tradeoff here is architectural rather than computational: a persistent streaming system running alongside your database, carrying its own consistency model and its own operational surface. If the freshness contract you have to meet is measured in seconds and your team already runs that layer comfortably, this is a defensible choice, and the rest of this piece is not an argument against it.

4. Invalidation-tracked window refresh

This is what a continuous aggregate in TimescaleDB does, and if you have not run one, the plain version is this: you declare a query with a time bucket and a GROUP BY, TimescaleDB stores its results in a table it maintains for you, and a scheduled policy keeps that table current.

The bookkeeping in between is what makes it a distinct strategy. Writes that touch already-summarized time ranges are recorded in an invalidation log, and each scheduled run re-examines a bounded window of time, recomputing only the buckets inside that window that saw activity. The work stays small and stays inside the database you already run. In exchange, someone has to pick the right width for that window.

Four strategies, each defensible for the workload it was designed around. What decides which one fits yours is what your data does after it first arrives.

The test: new data, late data, changed data

Here is how the four designs answer the three events:

Strategy	New data at the head	Late data for a closed window	UPDATE / DELETE on aggregated rows	What it costs
1. Scheduled full recompute	At next run	At next run	At next run	Full recompute, every run, whether or not anything changed
2. Insert-triggered incremental	Instant	Never	Never	Tiny increment per insert
3. Streaming dataflow	Instant	Instant	Instant	Continuous, in a second system you also operate
4. Invalidation-tracked window refresh	At next run	At next run if inside the window , never if outside	Same rule as late data	Recompute of the window only, and only the buckets that changed

Four strategies against the three things that happen to time-series data.

New data unites them; late data divides them

New data is where they all look alike. A row arrives at the head of the table with a current timestamp, and every one of the four picks it up on its own, with nobody intervening. What separates them here is only how long that takes: milliseconds for the two that work continuously, and up to a full schedule interval for the two that work on a timer. That is a freshness difference, and freshness is a dial you already know how to set. It is the only difference this regime exposes. Every evaluation runs here, which is exactly why the differences that matter stay hidden through the evaluation and surface in production six weeks later.

Late data splits them. Scheduled full recompute is right by construction: the next run recomputes everything in range without caring when any of it arrived. That correctness is prepaid on every run, including the runs where nothing was late.

An insert-triggered view sits at the opposite corner. The late INSERT does fire the trigger, but the view has no way to reach back into an aggregate it already wrote for an older timestamp, so historical corrections require a separate, resource-heavy refresh. The design traded that reach away deliberately in exchange for per-insert cheapness.

Streaming dataflow has no boundary to miss, because it never drew one. A late event is a change like any other, and the graph updates. What looks like a free win here is a cost moved rather than removed: the engine pays continuously to stay in a position where late data is unremarkable.

Changed data widens the split. An UPDATE or DELETE against rows that are already summarized is the case an insert-triggered view has no answer to at all, because no insert happened. The aggregate diverges silently until someone triggers a full refresh, and the divergence carries no timestamp to grep for. Full recompute absorbs the change for free, having never trusted its own previous output. Streaming dataflow propagates a retraction through the graph. TimescaleDB logs UPDATE and DELETE activity as invalidations exactly the way it logs INSERTs, so a correction to an old row inherits the same boundary condition as a late insert: inside the window it is fixed, outside the window it is not.

TimescaleDB's is worth looking at in depth, because it sits between those corners. It uses invalidation-tracked window refresh because late data is unbounded in theory and bounded in practice. Recording which buckets a write disturbed costs almost nothing. Re-examining a window wide enough to cover the lateness you actually see costs far less than recomputing all of history to catch it, and it happens inside the database you are already running. The bet is that your late-data envelope is something you can put a number on. What follows is the machinery that reads that number, and the two offsets you write it into.

Window refresh in detail

The bookkeeping starts before any refresh runs. TimescaleDB maintains an invalidation threshold, also called the materialization watermark: a time cutoff behind the hot head of the table. Mutations landing before it are logged as invalidations, because that region has already been summarized and the summary is now suspect. Mutations landing after it need no bookkeeping at all, because nothing has been materialized there yet. The granularity of that log is worth knowing, because it explains why a bulk backfill behaves differently from a trickle of corrections: each transaction logs the minimum and maximum timestamps of the rows it modified, so one transaction spanning a wide range marks every bucket between its endpoints.

A write whose timestamp lands in an already-materialized bucket is recorded in the invalidation log. Whether it ever gets re-materialized depends on the refresh policy's window. If that bucket falls inside the window when the policy next runs, the bucket is recomputed and the number corrects itself. If it falls outside, the policy never revisits it, and the aggregate keeps reporting the pre-arrival value indefinitely. TimescaleDB's own issue tracker carries a user report of exactly this boundary condition.

The aggregate query still returns, still returns fast, and still returns a plausible number. Nothing in a default policy setup raises "this bucket is stale relative to a write that landed after I last looked at it." The overnight dashboard shift in the opening is that branch resolving the slow way, when a human eventually re-ran something wide enough to sweep the bucket in.

Creating a continuous aggregate in TimescaleDB

Start with where the numbers live.

A continuous aggregate is a materialized view backed by its own hypertable, holding one row per GROUP BY bucket plus a column per aggregate. Here is the canonical shape:

CREATE MATERIALIZED VIEW conditions_summary_hourly
WITH (timescaledb.continuous) AS
SELECT
    device,
    time_bucket(INTERVAL '1 hour', time) AS bucket,
    avg(temperature) AS avg_temp,
    max(temperature) AS max_temp,
    min(temperature) AS min_temp
FROM conditions
GROUP BY device, bucket
WITH NO DATA;

Two parts of that statement are doing the declaring. WITH (timescaledb.continuous) is what makes this a continuous aggregate rather than an ordinary materialized view. The time_bucket call in theGROUP BY is what the continuous machinery then requires, because the bucket is the unit of work it invalidates and recomputes.

WITH NO DATA is a separate decision, and worth being precise about, since it is not the default. It controls the initial backfill at creation time and nothing else. The default, WITH DATA, computes every historical bucket the moment you run the statement, which on a large hypertable is a long blocking build at the worst possible time. WITH NO DATA creates the structure empty and hands the filling to the refresh policy you attach next, plus a manual refresh_continuous_aggregate call for whatever history you want backfilled deliberately. The pre-computation still happens either way. You are choosing when to pay for it.

A refresh run, whether triggered by the policy or by a manual call, is two transactions rather than one: the first briefly blocks writes while it determines the range to materialize and advances the threshold, and the second materializes without blocking writers, so a wide refresh does not hold one long lock.

One refresh cycle. The two offsets are the two edges of the window in the middle box, and everything about staleness is decided by which side of them a bucket falls on.

Which brings the design down to two numbers you set on the policy:

SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
    start_offset => INTERVAL '48 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

Each run refreshes the range from now() - start_offset to now() - end_offset. So start_offset is the lookback window, the distance back in time the policy is willing to reconsider, and end_offset is a deliberate freshness floor that holds the newest, still-filling bucket out of the refresh so it is not recomputed on every run while data is still landing in it. With one-hour buckets, an end_offset of one hour is the smallest value that does that job.

That floor has a consequence worth handing to whoever consumes the dashboard. Worst-case publication delay is roughly schedule_interval + end_offset: a row lands just after a run finishes, waits a full interval for the next one, and is still held back by end_offset when that one goes. With the values above the composite runs to about ninety minutes, triple the schedule interval taken on its own. Hand downstream consumers the composite, because either parameter alone understates what they will see.

Two things widen the envelope you are sizing for. Hierarchical continuous aggregates are built on top of other continuous aggregates, and each tier runs its own refresh policy against the tier below, so a correction lands at the top only after every tier's schedule has fired in turn, later than the top policy alone suggests. And a retention policy that drops raw chunks still inside your start_offset removes the source rows a late correction would have been reconciled from, so size those two against each other deliberately.

One feature looks like it closes the gap, and it does not. Real-time aggregation unions the materialized buckets with a live query over the tail that has not been materialized yet, so a query can read current even between policy runs. It is genuinely useful, but it solves a different problem: it covers data newer than the watermark. A late write carrying an old timestamp lands in a bucket that is already behind the watermark, and if that bucket is outside every policy window, no amount of real-time aggregation surfaces it, because the live query is not looking there. The two features solve adjacent problems, and it is easy to assume the first one closed the second.

-- Real-time aggregation, if you want the un-materialized tail included in reads.
ALTER MATERIALIZED VIEW conditions_summary_hourly
    SET (timescaledb.materialized_only = false);

Note the default flipped: in TimescaleDB v2.13 and later, real-time aggregates are disabled by default, where earlier versions enabled them. A window sized correctly still tells you nothing about whether the policy that reads it is running.

Checking that the policy is doing its job

TimescaleDB already records what you need to track the calculations. Each job's last_successful_finish, compared against its own schedule_interval, tells you whether it is keeping up. The extra join is there because job_stats reports the internal materialization hypertable rather than the name you query:

SELECT ca.view_name,
       j.schedule_interval,
       js.last_successful_finish,
       now() - js.last_successful_finish AS since_last_success,
       js.last_run_status,
       js.total_failures
FROM timescaledb_information.jobs j
JOIN timescaledb_information.job_stats js
  ON j.job_id = js.job_id
JOIN timescaledb_information.continuous_aggregates ca
  ON ca.materialization_hypertable_name = js.hypertable_name
WHERE j.proc_name = 'policy_refresh_continuous_aggregate';

Audit your own policies this week: for each continuous aggregate, compare how far back your writes actually arrive against the start_offset that policy covers. Where the arrivals run wider than the window, that aggregate is already publishing numbers that will never correct themselves, and widening start_offset is the cheapest thing you can do about it. The refresh-policies documentation carries the parameter reference, and Tiger Cloud is one place to try a wider window against a copy of your own data before you touch production.

What pg_stat_statements Actually Tells You About Your Queries

Team Tiger Data — Fri, 07 Aug 2026 04:00:00 +0000

Your slowest query is rarely your most expensive one. A 4-second report that runs twice a day costs your database 8 seconds. A 3-millisecond lookup that runs 40,000 times a minute costs it two minutes of CPU every sixty seconds. Only one of those shows up in a slow query log, and it is the wrong one.

pg_stat_statements settles this argument. It keeps a running total of every top-level statement your server executes, grouped by structure rather than by literal text. Each group is a fingerprint, with one row in the view representing every execution of the same query shape, with the constants stripped out. Every number in this guide is captured from a real instance, and one query on it spent more time being planned than every other statement combined. It runs in 0.2 milliseconds.

What you will learn

How pg_stat_statements normalizes queries into a fingerprint, and what that collapses.
Which columns matter: calls, total_exec_time, mean_exec_time, rows, and the buffer counters.
How to read a real result set and tell an expensive query from a merely slow one.
How to find planning-dominated queries, which mean_exec_time hides completely.
What the extension does not capture, so you know when to stop trusting it.

Before you start

You need PostgreSQL 13 or later. The column names used here landed in version 13; on 12 and earlier they are total_time and mean_time, and planning time is not tracked at all. You need superuser access to edit postgresql.conf and restart the server, and the role you query with needs pg_read_all_stats. Without it the view still returns rows, but every query column reads <insufficient privilege>.

Enable pg_stat_statements

pg_stat_statements allocates a fixed block of shared memory at postmaster startup, so it has to be in shared_preload_libraries. This is not a pure CREATE EXTENSION install. Edit postgresql.conf first, then restart:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_planning = on

Then, in psql:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SHOW shared_preload_libraries;
SELECT count(*) FROM pg_stat_statements;

Check the last two statements, not the first. CREATE EXTENSION succeeds whether or not the library is preloaded, so it proves nothing on its own. SHOW should list the extension, and the count should be non-zero within seconds of normal traffic. If instead you get ERROR: pg_stat_statements must be loaded via shared_preload_libraries, the config edit or the restart did not work.

Three settings change what you see. pg_stat_statements.max caps tracked fingerprints at 5,000 by default, and past that Postgres evicts entries by a decaying usage score, roughly least-recently-used. track_planning is off by default, so total_plan_time reads as zero until you enable it. That one is not optional here; it is where this article's main finding comes from. And track defaults to top, recording only the outermost statement, so if your logic lives in PL/pgSQL functions every nested query is billed to the wrapper. Set it to all.

Reset the counters before you measure

The view accumulates from the last reset. On a long-running server that can mean two years of history spanning a migration, a bad deploy, and a schema change you have since reverted.

SELECT pg_stat_statements_reset();

Rank by aggregate cost, not per-call cost

This query does most of the work. It ranks fingerprints by total execution time and puts the evidence beside each one:

SELECT
    substring(query, 1, 60) AS query_fragment,
    calls,
    round(total_exec_time::numeric / 1000, 1) AS total_sec,
    round(mean_exec_time::numeric, 3) AS mean_ms,
    round(total_plan_time::numeric / 1000, 1) AS plan_sec,
    round(rows::numeric / nullif(calls, 0), 1) AS avg_rows,
    round(100.0 * shared_blks_hit /
          nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

Here is the actual output from an example PostgreSQL 16 instance holding 5 million rows in a device_metrics table across 500 daily partitions, 1.26 GB on disk against 256 MB of shared_buffers, after a mixed workload of 20,000 device lookups, 20,000 metadata lookups, 200 batch inserts, 20 reporting aggregates, and 3 retention deletes:

query_fragment	calls	total_sec	mean_ms	plan_sec	avg_rows	hit_pct
`SELECT date_trunc($1, ts), avg(value) FROM device_metrics WH`	20	251.9	12592.544	0.3	489.8	1.5
`SELECT * FROM device_metrics WHERE device_id = $1 AND ts > n`	20000	4.4	0.221	213	37.8	95.7
`SELECT id, name FROM devices WHERE org_id = $1`	20000	0.9	0.044	0.2	125	100
`INSERT INTO device_metrics (ts, device_id, metric, value, qu`	200	0.8	3.843	0	500	100
`DELETE FROM device_metrics WHERE ts < now() - interval $1`	3	0	16.091	0.1	13510.3	95.4

Two columns decide the question, and they point in different directions. total_sec is what a fingerprint costs your server, while mean_ms is what it costs one user. Read them as a pair, and four cases fall out. High calls with low mean_ms is an application problem: a query in a loop, an ORM N+1, or a dashboard polling faster than anyone reads it, and the fix is batching or caching rather than indexing. Low calls with high mean_ms is a query problem: a missing index, a bad join order, or a scan wider than the result needs. High on both is where you start. Low on both is the healthy majority, and leaving it alone is the correct action.

Every row below is one of those cases.

Row 1 is 251.9 seconds out of 258.0 across every fingerprint on the server, so 98% of execution time belongs to a query called twenty times. Read avg_rows next to hit_pct to see why. It returns 489.8 rows per call, one per day of retention, and does it at a 1.5% cache hit rate. Twenty calls read 2.75 million blocks off disk to produce 9,796 rows.

Row 5 is the trap from the intro. At 16 ms per call the retention DELETE is the second-slowest statement on the box, the first thing an on-call engineer would flag. Three calls, 48 milliseconds total. Ignore it, with one caveat: total_exec_time under-bills any DELETE, because the dead tuples it leaves and the autovacuum passes that clean them up are charged elsewhere. On a partitioned table, DROP TABLE on the oldest partition does the same job in constant time with no vacuum debt.

Use the buffer columns to separate cache misses from CPU

shared_blks_hit counts 8 KB blocks served from the shared buffer cache. shared_blks_read counts blocks that had to come from the OS cache or disk. The hit_pct column above derives from both, and it is worth selecting the raw counts alongside it, because a ratio hides volume. Statements that touched no shared blocks come back blank rather than zero, which is the nullif guard doing its job.

Row 3 sits at 100.0%. It runs 20,000 times, never leaves memory, and costs 0.9 seconds. That is what a healthy fingerprint looks like. Row 4 is the ingest floor, inserting 500 rows per call at 3.8 ms with a perfect hit rate. Neither is worth touching.

Row 1 sits at 1.5%. It scans a 1.26 GB table through a 256 MB cache, so almost nothing it reads is resident, and every block it pulls evicts something another query wanted. That is read amplification, and no index fixes it. The query has to visit every row in the retention window to compute the average.

The 213 seconds mean_ms cannot see

Now look at row 2. It executes in 0.221 milliseconds. By any per-call measure it is the healthiest query in the workload. However, its plan_sec is 213.0.

That is 10.65 milliseconds of planning for 0.221 milliseconds of execution, 48 times more expensive to plan than to run, and 99.7% of all planning time on the server. A slow query log would never show it. mean_exec_time does not include it.

EXPLAIN ANALYZE confirms the ratio and names the cause. Run the query below twice in one session and read the second result. The first pass loads catalog entries for 500 partitions and reports planning time that includes them:

EXPLAIN (ANALYZE, SUMMARY)
SELECT * FROM device_metrics WHERE device_id = 8 AND ts > now() - interval '7 days';

The second result:

Append (cost=0.29..4300.22 rows=539 width=193)
  Subplans Removed: 492
Planning Time: 10.739 ms
Execution Time: 1.354 ms

Subplans Removed: 492 is the tell. The predicate is ts > now() - interval '7 days', and now() is stable rather than constant, so the planner cannot prune at plan time. It builds a subplan for all 500 partitions, then discards 492 of them at execution. Resolving the timestamp in the application and passing a fixed value lets plan-time pruning run instead. Run this in the same session, substituting a real date:

EXPLAIN (ANALYZE, SUMMARY)
SELECT * FROM device_metrics WHERE device_id = 8 AND ts > '2026-07-22'::timestamptz;

And you may see the planning time drop to 0.183 ms. Subplans Removed is gone: same rows, same execution, 59 times less planning.

To fix this, you have to swap now() for a genuine constant. If you use current_date - 7 instead, nothing changes because current_date is stable too and the planner still builds all 500 subplans. Prepared statements and generic plan caching help too. Fewer, larger partitions help more.

Row 2 in the table was visible only because plan_sec happened to sit in the ranking. To sweep for the pattern deliberately, sort by planning time and add the ratio:

SELECT
    substring(query, 1, 60) AS query_fragment,
    calls,
    round(total_plan_time::numeric / 1000, 1) AS plan_sec,
    round(total_exec_time::numeric / 1000, 1) AS exec_sec,
    round((total_plan_time / nullif(calls, 0))::numeric, 3) AS plan_ms_per_call,
    round((100.0 * total_plan_time /
           nullif(total_plan_time + total_exec_time, 0))::numeric, 1) AS plan_pct
FROM pg_stat_statements
WHERE calls > 100
  AND query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_plan_time DESC
LIMIT 10;

Sort by total_plan_time, and do not scan plan_pct on its own. That ratio is a trap: a trivial indexed lookup often plans in more time than it executes because both are microseconds, and on a scratch instance the same column reads 71.8% for a query nobody should touch. plan_ms_per_call is the honest signal. Healthy lookups plan in tens of microseconds. Row 2 planned in 10.65 milliseconds, and multiplied across 20,000 calls that is the 213 seconds. Use plan_pct to confirm what the absolute numbers already flagged, which for row 2 was 98.0%.

When the ranking stops changing

Rows 1 and 2 are both fixable. The pattern underneath them is not.

On a high-frequency time-series workload, total_exec_time, shared_blks_read, and total_plan_time all climb as data volume and partition count grow, even when every query is written correctly. You fix the top fingerprint, it drops to fourth, and two quarters later it is back with the same shape. If your ranking regenerates itself after you fix what is on it, you are measuring architecture, not technique. Columnar storage changes the inputs rather than the query text: a pre-computed rollup answers row 1 without touching 2.75 million blocks. The Optimization Treadmill covers when optimizing further stops paying.

Know the blind spots

Normalization is what makes the ranking possible and also what limits it. WHERE device_id = 42 and WHERE device_id = 9001 collapse into one fingerprint. If one value matches 4 billion rows and another matches an empty range, you get an average that describes neither.

The view also gives you no percentiles, so check max_exec_time before trusting a healthy mean. And it stores no plans. It tells you a query got slower, never that the planner switched from an index scan to a sequential scan. auto_explain fills that gap, but it needs preloading too, and it only logs statements over auto_explain.log_min_duration. Catching a regression in a 0.2 ms query like row 2 means setting that threshold low enough to hurt, so aim it at one fingerprint and turn it off afterward.

One version note: before PostgreSQL 18, IN lists of different lengths produced separate fingerprints, splitting one logical query across several rows. PostgreSQL 18 merges them.

Four more limits are covered above rather than here, because each one distorts a specific number as you read it: track = top bills nested PL/pgSQL queries to the wrapper, pg_stat_statements.max evicts fingerprints once you pass its configured max, track_planning left off makes every total_plan_time read as zero, and total_exec_time under-bills any DELETE because the vacuum work it creates is charged elsewhere.

Sort by planning time next

Reset your statistics, run one peak cycle, and pull the top five fingerprints by total_exec_time. Then run the same query ordered by total_plan_time, because on this instance that second list found the cost center the first one missed. If the same fingerprints keep returning to the top after you fix them, start a Tiger Cloud trial today and run both rankings against columnar storage to see which rows disappear.

How ControlCom Turns 300+ Million Monthly Facility Data Points Into Instant Answers With Tiger Data

Team Tiger Data — Thu, 06 Aug 2026 18:40:28 +0000

ControlCom Connect streams telemetry from thousands of industrial and facility assets into TimescaleDB, powering instant dashboards and an AI assistant, the same platform that caught $160,000 in hidden utility billing errors at one healthcare site and flagged a tier 1 hospital's backup generators left in manual before a power failure could put patients at risk.

A six-person team building a vendor-neutral industrial IoT platform streams telemetry from thousands of facility assets, from PLCs and meters to switchgear and generators, into TimescaleDB. The result: dashboards that re-bucket 300+ million data points a month on the fly, and an AI assistant that answers questions against live and historical readings in real time. That speed shows up across a growing list of catches, among them a healthcare deployment where automated monitoring caught $160,000 in utility billing errors that had gone unnoticed for years, and a tier 1 hospital where the same monitoring flagged backup generators left in manual mode before a routine service call could turn into a life-safety incident.

This is an installment of our Community Member Spotlight series, in which we invite our community members to share their work, spotlight their success, and inspire others with new ways to use TimescaleDB.

Today we hear from Catalin Negru, founder and CEO of ControlCom Technologies, and the team behind ControlCom Connect, an industrial IoT platform that unifies live equipment data across a facility portfolio. Catalin shares how a six-person team of software and electrical engineers evaluated three time-series options before settling on TimescaleDB, and why staying inside Postgres is what lets the platform's AI assistant and living asset graph stay fast as customer data volumes climb.

About ControlCom Connect

The people who keep critical facilities running (healthcare, water and wastewater, prime power, data centers, manufacturing, oil and gas, cold chain) don't get a unified view of the equipment they're responsible for. A SCADA system, a handful of PLCs, a fleet of meters, a generator, and a compressor each speak their own protocol and report into their own app. When a power event hits, the operator's job becomes walking the floor panel by panel, guessing what dropped, what restarted on its own, and what is still down.

ControlCom Connect connects to whatever a facility already owns: SCADA systems, smart devices, controllers, meters, PLCs, HMIs, generators, switchgear, compressors, batteries, cranes, and tractors, plus software sources like APIs, databases, and scripts, over MQTT, Sparkplug B, OPC UA, Modbus, BACnet, Ethernet/IP, HTTPS, and webhooks. Every connected asset is wired into a living graph of the facility, so when an outage hits, the platform traces it downstream in seconds and tells the operator exactly which assets were affected, instead of sending someone to check panels by hand. Live metering and equipment status stream into drag-and-drop dashboards with multi-site comparison, smart notifications route to the right person over SMS, email, Slack, or mobile push with on-call coverage and escalation, and an AI assistant grounded in the organization's own assets and readings answers questions in plain language.

At one healthcare deployment, automated utility monitoring caught more than $160,000 in billing errors that had gone unnoticed until ControlCom's platform started watching the meter data. At a tier 1 hospital, the same kind of continuous monitoring flagged backup generators left in manual mode after a maintenance visit, before a power outage could turn a routine service call into a life-safety incident.

ControlCom Technologies is a six-person team of software and electrical engineers, founded and led by Catalin Negru.

The Challenge

ControlCom Connect's entire platform runs on high-frequency telemetry streaming in from thousands of points across every connected asset, at a rate of 1,000 to 10,000 data points per second, and that volume only grows as customers add sites. Two parts of the product make query speed non-negotiable: the AI assistant has to answer natural-language questions against live and historical readings and return an answer while someone is waiting on it, and the living graph has to resolve relationships and pull time-series data for many points at once, on demand, the moment an outage hits. A user asking their facility a question, or watching the graph resolve an outage, can't sit and stare at a loading indicator.

PostgreSQL was already home for the rest of ControlCom's stack, so the team needed a way to handle time-series at scale without leaving it. - Catalin Negru, Founder & CEO, ControlCom Technologies

Why Tiger Data: Architecture-First From Day One

Catalin found TimescaleDB by googling time-series databases, after the InfluxDB and AWS proofs-of-concept fell short. Because ControlCom Connect was already built on PostgreSQL, TimescaleDB (built on PostgreSQL) was an easy choice. The team kept the database they trusted, their existing tooling, and plain SQL, while gaining hypertables, compression, and fast time-series queries on top. There was no migration to a foreign database, no new query language for the engineering team to learn, and no second connection pool to manage alongside the relational data that already ran the platform.

It is just Postgres, so we kept the database we trusted, our existing tooling, and plain SQL, while gaining hypertables, compression, and fast time-series queries on top. - Catalin Negru, Founder & CEO, ControlCom Technologies

Compression mattered as much as query speed. ControlCom runs a hybrid deployment: a local TimescaleDB on every Edge Server for on-site resilience, and a central TimescaleDB on Tiger Cloud that holds the portfolio-wide record. Keeping cost in check on the cloud side, while ingesting from thousands of points across every customer's fleet, was a factor in the decision from day one.

The ControlCom Connect Stack

ControlCom Connect's pipeline starts at the device: a PLC, controller, meter, or other piece of equipment. Each edge site runs an Edge Server, which also runs a local TimescaleDB that buffers and stores telemetry on site up to a configurable window, so a facility keeps monitoring itself even if it loses its connection upstream. The Edge Server publishes to an MQTT broker, which feeds into Kafka.

Kafka is where the real-time work happens. As readings stream through, ControlCom processes alarms and parses for anomalies the instant they arrive, writes the telemetry into the central TimescaleDB instance on Tiger Cloud, and pushes the update to Redis and out to every connected client in real time. The central TimescaleDB instance is the portfolio-wide source of truth: the AI assistant queries it for natural-language answers, the living asset graph queries it to trace an outage downstream, and the dashboards query it to render live and historical views across every site a customer operates.

ControlCom Connect's data flow: facility equipment publishes through an Edge Server with a local TimescaleDB for on-site resilience, into an MQTT broker and Kafka. Kafka drives alarm and anomaly processing and writes to a central TimescaleDB on Tiger Cloud, which powers live dashboards, the AI assistant, and the living asset graph.

Results: What ControlCom has seen

300+ Million data points a month, re-bucketed instantly

ControlCom routinely pulls from 300+ million data points a month. Users are used to being amazed: flip a setting on a graph, widen or narrow the bucket size, push the start or end date out, and the chart updates instantly. Re-bucketing and re-ranging across that volume on the fly is the kind of workload that would crawl on a general-purpose setup. On TimescaleDB, data is served in real time, so exploring months of high-frequency data feels as snappy as scrolling a single day.

Users are amazed when they flip a setting on a graph, increasing or decreasing the bucket size, or changing the start and end time, and the chart updates instantly. - Catalin Negru, Founder & CEO, ControlCom Technologies

An AI assistant that doesn't feel like a demo

In the industrial sector, the gap shows up the moment something goes wrong. Operators run facilities from a wall of dashboards, sometimes 80 of them, watching for any of 1,500 possible alarms. That works when conditions are calm. But when 20 alarms fire at once, the challenge stops being a lack of data and becomes finding the one signal that explains what happened, fast enough to act on it. That's the gap ControlCom's AI assistant is built to close.

ControlCom's AI assistant answers natural-language questions against both live and historical readings, which means every question triggers a real time-series query: an aggregation, a recent window, a multi-point comparison, run on the fly while the user waits for an answer. With TimescaleDB behind it, those queries come back fast enough that the conversation feels instant instead of leaving the user staring at a loading indicator, which is what makes an AI assistant grounded in live facility data actually usable rather than a demo.

$160,000 in hidden utility billing errors

The clearest proof of what fast time-series queries make possible showed up outside the dashboard entirely. One healthcare deployment's automated utility monitoring surfaced more than $160,000 in billing errors that had gone unnoticed. Nobody was going to find that by eyeballing a meter. It took a platform that could watch every reading, all the time, and flag the anomaly the moment it appeared.

One healthcare deployment, for example, surfaced over $160K in utility billing errors that would otherwise have gone unnoticed. - Catalin Negru, Founder & CEO, ControlCom Technologies

Beyond the balance sheet: Preventing a Tier 1 hospital blackout

Not every win shows up on a balance sheet. At one Tier 1 hospital, a maintenance crew working on the facility's backup generators left them in manual mode after finishing the job and leaving the site. ControlCom's platform caught the anomaly and escalated a notification to the facilities team that the system was out of spec. That catch depends on the same thing that makes the billing win possible: TimescaleDB lets ControlCom continuously track every asset's status across a portfolio, so a state change like a generator sitting in manual doesn't go unnoticed until someone happens to check. Had a power outage hit while the generators sat in manual, the hospital could have lost backup power entirely, with patients on operating tables and millions of dollars in potential damage on the line.

This could have caused millions of dollars in damage if a power outage occurred. You have real people in operating rooms on the operating tables. - Catalin Negru, Founder & CEO, ControlCom Technologies

Looking ahead

ControlCom is pushing further into the real-time side of the platform: deeper stream processing and analytics, with anomaly detection that flags issues the moment a reading arrives instead of on the next dashboard refresh. The AI assistant and the living asset graph are next in line for the same treatment, since both depend on time-series queries staying fast as the underlying data grows.

That data volume is only going in one direction. As ControlCom adds more sites and more equipment for existing customers, and onboards new ones, the ingestion rate climbs across the board. The architecture decision that compounds as it scales isn't any single query. It's that the Edge Server's local TimescaleDB and the central TimescaleDB on Tiger Cloud are the same technology end to end: one set of hypertables, compression, and SQL, from the edge to the portfolio-wide record. No split time-series stack to reconcile as fleets grow, and no separate system to keep affordable as history piles up.

TimescaleDB 2.28: Faster Queries, Lighter Operations, and Better Schema Evolution

Nicole Ghalwash — Wed, 29 Jul 2026 12:30:13 +0000

Time-series analytics at scale creates operational friction. When you're running continuous aggregates, columnar storage, and complex analytical patterns, each new metric, query pattern, and configuration tuning attempt adds complexity. Refreshes block each other, configuration changes require rebuilds, and new aggregates mean recomputing entire rollups.

Over recent TimescaleDB releases, we've prioritized minimizing these hurdles by leveraging bloom filters to bypass redundant processing during high-volume operations on columnar storage. We also expanded vectorized execution across more query patterns and simplified continuous aggregate workflows by combining refresh and compression.

Now with TimescaleDB 2.28, we're making common analytical queries faster without code changes, making continuous aggregate operations less disruptive and more flexible, and eliminating friction when evolving your schema and configuration. The result is faster queries, lighter operations, and the ability to evolve your analytics alongside your application as it scales.

TL;DR

Lighter, more flexible continuous aggregates:

ADD COLUMN on CAggs: Add new aggregations in place without rebuilding
Fine-grained locking: Refreshes no longer serialize unrelated operations
Incremental refresh batching: Break large refreshes into batches instead of one heavy operation
ANALYZE and VACUUM: Maintenance commands now work directly on continuous aggregates

Faster queries on compressed data:

Metadata-driven first() and last() queries: Answer from batch metadata without decompression
Vectorized CASE expressions: Conditional logic stays on the fast path
Batch Sorted Merge for more queries: Lighter-weight sorting on compressed data

Better operational flexibility:

Sparse index retrofitting: Update configuration on existing chunks without recompression
Compression settings clarity: Warnings prevent misconfiguration
GUC for bulk loads: Skip invalidation tracking during migrations
Nullable ORDER BY safety: Fallback to correct compression path

Also: PostgreSQL 15 support ends with 2.28. Plan your upgrade to PG16, PG17, or PG18.

Schema evolution: ADD COLUMN on CAggs

Schema evolution lets you add new aggregated columns without dropping and rebuilding. Before 2.28, adding a metric meant recreating the entire CAgg, recomputing all historical data (hours on large datasets), and breaking downstream consumers. Now you can ADD COLUMN with GENERATED ALWAYS AS and backfill incrementally.

-- Add a new metric to an existing CAgg
ALTER MATERIALIZED VIEW conditions_summary_hourly
  ADD COLUMN max_temp double precision
  GENERATED ALWAYS AS (
    max(temperature)
  ) STORED;

-- Backfill historical data
CALL refresh_continuous_aggregate(
  'conditions_summary_hourly',
  NULL,
  NULL,
  force => true
);

How we made continuous aggregates operational at scale

Real-time dashboards on operational data used to force a choice: refresh frequently and block bulk loads, or refresh less often and accept stale data. Every continuous aggregate refresh acquired a table-wide lock on its materialized hypertable, blocking concurrent operations. In 2.28, we switched to row-level locking on the continuous aggregate catalog entry. Only one refresh processes a CAgg's invalidation log at a time, but the materialized table is free for concurrent operations. Refreshes and bulk loads now run in parallel.

We also made continuous aggregates more flexible. ADD COLUMN on CAggs enables you to evolve your analytical schema in production by adding new aggregations as you learn what data you need, without downtime or rebuilds. Your dashboard design no longer locks you into upfront predictions.

-- Before 2.28: refresh acquired table-wide lock
-- After 2.28: refresh acquires only catalog row lock
CALL refresh_continuous_aggregate(
  'metrics_hourly',
  '2026-01-01',
  '2026-02-01'
);
-- Other refreshes, bulk loads, and DDL can proceed

2.28 also adds two capabilities that make continuous aggregates evolve with your application:

Incremental refresh batching processes large time windows in smaller batches instead of single long-running transactions. Before 2.28, refresh_continuous_aggregate() materialized the entire window atomically, holding resources and blocking vacuums. Large windows could take hours and fail mid-way. Now you can batch them.

   -- Refresh a 30-day window in smaller batches
   CALL refresh_continuous_aggregate(
     'metrics_hourly',
     now() - INTERVAL '30 days',
     now(),
     options => '{
       "buckets_per_batch": 10,
       "max_batches_per_execution": 20
     }'::jsonb
   );

Schema evolution lets you add new aggregated columns without dropping and rebuilding. Before 2.28, adding a metric meant recreating the entire CAgg, recomputing all historical data (hours on large datasets), and breaking downstream consumers. Now you can ADD COLUMN with GENERATED ALWAYS AS and backfill incrementally.

-- Add a new metric to an existing CAgg
ALTER MATERIALIZED VIEW conditions_summary_hourly
  ADD COLUMN max_temp double precision
  GENERATED ALWAYS AS (
    max(temperature)
  ) STORED;

-- Backfill historical data
CALL refresh_continuous_aggregate(
  'conditions_summary_hourly',
  NULL,
  NULL,
  force => true
);

ANALYZE and VACUUM now work on continuous aggregates directly. Maintenance commands automatically maintain accurate planner statistics across the materialized hypertable and all its chunks, ensuring query plans stay optimal.

Faster queries on compressed data

One of the most common queries in time-series workloads is "give me the latest value per series." Dashboards and monitoring systems run it constantly. It's a simple pattern: find the first or last value in a time range. But on compressed data, answering that query used to require decompressing entire batches just to find values that are already in the metadata. Time-sorted batches store first and last values as metadata. But before 2.28, the database decompressed anyway to answer those queries.

In 2.28, TimescaleDB extracts first(value, time) and last(value, time) aggregates directly from batch sparse indexes. No decompression. For "latest reading" queries that consume significant resources at scale, that means meaningful speedup with zero query changes. Here's what that looks like:

-- Define firstlast sparse index
ALTER TABLE metrics SET (
  tsdb.index = 'firstlast(temperature)'
);

-- Note: Use rebuild_sparse_index() to retrofit existing chunks
-- New chunks now answer this query from metadata alone
SELECT
  device_id,
  last(temperature, time)
FROM metrics
WHERE time > now() - INTERVAL '7 days'
GROUP BY device_id;

Beyond first/last, we also extended vectorized execution to cover CASE expressions. Before 2.28, conditional logic in aggregations forced columnar queries to fall back to row-by-row processing, making identical queries perform very differently depending on whether a CASE expression was present.

-- This query now stays fully vectorized
SELECT
  time_bucket('1 hour', ts) AS bucket,
  SUM(
    CASE WHEN status_code >= 500 THEN 1 ELSE 0 END
  ) AS error_count,
  AVG(
    CASE WHEN latency_ms > 1000 THEN latency_ms END
  ) AS slow_avg_latency
FROM requests
WHERE ts > now() - INTERVAL '7 days'
GROUP BY bucket;

Pivot-style queries, error rate calculations, and conditional metrics all stay on the fast vectorized path. For workloads that use conditional aggregations on compressed history, that eliminates the surprise performance cliffs.

Finally, Batch Sorted Merge now applies to more query patterns. ORDER BY queries on unordered compressed chunks without segmentation no longer fall back to expensive external sorts. The planner now replaces those sorts with lightweight metadata merges over pre-sorted batches.

Better operational flexibility and efficiency

Tuning time-series workloads means iterating on compression strategy. But historically, every configuration change (adding a sparse index, adjusting segmentby, optimizing orderby) only applied to future compressions. Existing chunks kept their old settings, creating inconsistent performance and confusion about why newly tuned queries behaved differently.

2.28 eliminates that friction. rebuild_sparse_index lets you retrofit sparse index configuration to existing chunks without recompressing. Sparse indexes are metadata. Updating them shouldn't require rewriting data. Below is the actual code snippet you can leverage:

-- Change sparse index settings on the hypertable
ALTER TABLE metrics SET (
  tsdb.index = 'minmax(value)'
);

-- Retrofit existing chunks without recompression
SELECT _timescaledb_functions.rebuild_sparse_index(
  '_timescaledb_internal._hyper_1_42_chunk'
);

Compression settings changes now emit warnings clarifying that new settings apply only to future compressions. This closes the gap: users assume ALTER TABLE sets configuration across the entire dataset, but it only affects new chunks.

For correctness, nullable ORDER BY columns now safely fall back to decompress-compress during recompression, preventing silent incorrect results when min/max metadata doesn't account for NULLs.

Gapfill row count estimation improved so time_bucket_gapfill queries get more accurate planner statistics and better query plans.

For bulk migrations, a new GUC timescaledb.skip_cagg_invalidation suppresses continuous aggregate invalidation tracking during bulk loads. Migration tools no longer generate useless invalidation entries that trigger expensive refresh storms.

PostgreSQL 15: Final Release and Migration Path

PostgreSQL 15 support ends with TimescaleDB 2.28. Going forward, only PostgreSQL 16, 17, and 18 are supported.

If you're on PG15, plan your upgrade to PG17 or PG18 now. We'll begin upgrading instances in production the week of September 15th, so you should plan your migration path over the next two months to avoid downtime. Postgres upgrades are typically non-disruptive (seconds to minutes of downtime using logical replication or physical backup/restore), and newer Postgres versions bring improvements in query parallelism, vector search optimization, and compression.

Upgrade to 2.28 today

For workloads running continuous aggregates at scale, columnar queries with conditional logic, or iterating on compression configuration, 2.28 removes operational friction: refreshes don't serialize, queries stay vectorized, and tuning doesn't require rewrites.

2.28 is available now. To learn more, check out the full release notes for a complete list of improvements, or try Tiger Cloud for free and experience TimescaleDB 2.28 on your largest hypertables. We welcome your feedback on GitHub. Please note that for Tiger Cloud customers, all improvements are live immediately. For self-hosted deployments, download the latest release and follow the upgrade guide.

The Hidden Cost of Postgres Constraints at Scale

Team Tiger Data — Fri, 24 Jul 2026 14:30:49 +0000

Your ingest workers are queuing. pg_stat_activity shows lock waits. The blocked query is not a slow SELECT. It's your bulk INSERT, waiting on the devices table.

You added a FOREIGN KEY there months ago. You added a UNIQUE constraint on the readings table to catch duplicates. Both were the right call. At 100 devices and 10,000 rows, you never felt them. At 50K inserts per second, they've become the ceiling.

What you will learn

What Postgres actually executes on every insert to enforce FOREIGN KEY and UNIQUE constraints
Why this overhead is invisible during a PoC and destructive at production ingest rates
Four concrete approaches that preserve data integrity without paying the full constraint cost on every row

Why it matters

The Postgres Optimization Treadmill describes how high-frequency time-series workloads hit architectural ceilings despite correct tuning. MVCC overhead, index write amplification, and WAL volume all compound as data grows. Constraint enforcement layers on top of all of that, and it compounds in the same direction.

Every FOREIGN KEY fires an index lookup against the referenced table on every insert. At 50K inserts/sec, that's 50K random reads per second competing directly with your write path. Every UNIQUE constraint fires an index scan before every insert, on an append-only table where duplicates shouldn't ever occur. Both generate additional WAL records and hold row-level locks during execution. Together, they quietly consume the ingest safety margin between "running well" and "falling behind."

Tracing a single constrained insert

A vanilla insert into a plain table performs two operations: a heap tuple write and a WAL commit record.

A constrained insert does five:

Heap write. The row is written to the 8KB heap page.
B-tree insertion. Every index on the table receives a new entry, traversing from root to leaf and splitting pages as needed.
FK shared-lock acquisition. Postgres acquires a FOR KEY SHARE on the referenced row in the parent table (devices) to verify it exists.
UNIQUE index scan. Postgres scans the unique index to confirm no matching entry already exists before writing.
WAL commit record. The constraint checks generate WAL in addition to the row write itself.

Under concurrent write load at 50K inserts/sec, step 3 is where it breaks down — not through mutual blocking, since FOR KEY SHARE locks are compatible with each other, but through MultiXacts. With only a handful of device rows referenced by thousands of concurrent transactions, each parent row is locked FOR KEY SHARE by many transactions at once, and Postgres must track that shared ownership with a MultiXactID. At this concurrency the churn saturates the MultiXact SLRU caches. pg_stat_activity surfaces this as MultiXact LWLock wait events (only for PostgreSQL 16-18; event names may differ on earlier versions), not as a query performance problem.

Not all constraints carry the same cost. NOT NULL and CHECK constraints evaluate against the row being inserted with no external lookups. They're near-free. FOREIGN KEY and UNIQUE are where the overhead lives, because both require reads against external state on every single insert.

Identifying the problem

Run this query during your next peak ingest window:

SELECT
    pid,
    wait_event_type,
    wait_event,
    query,
    state,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (
        (wait_event_type = 'LWLock' AND wait_event LIKE 'MultiXact%') -- PostgreSQL 16–18: SLRU wait-event names verified on these versions
     OR (wait_event_type = 'Lock' AND wait_event IN ('transactionid', 'tuple'))
      )
ORDER BY duration DESC;

All MultiXact wait events share the MultiXact prefix on PostgreSQL 16-18, so the LIKE 'MultiXact%' filter captures all SLRU contention events regardless of minor version. Look for rows where wait_event starts with MultiXact. That indicates MultiXact SLRU contention from FK checks on hot parent rows - the mechanism described above. Rows where wait_event is transactionid or tuple indicate true row-lock waits from a concurrent update or delete on the parent, a less common but related failure mode.

Four approaches to reduce constraint overhead

These options are ordered by risk and invasiveness. Start with option 1 if you have existing constraints and need a minimal-change fix. Move to option 2 if your duplication window is bounded to recent data. Use options 3 or 4 only if you own the full write path end to end and can enforce integrity outside the database.

1. Defer FK checks to commit time

Postgres supports deferring constraint checks to commit time rather than row time. Inside a bulk-insert transaction, the FK lookup runs once per batch instead of once per row.

First, declare the constraint as deferrable. This is backward-compatible: the constraint still enforces row-by-row in any transaction that does not explicitly defer it.

ALTER TABLE sensor_readings
  DROP CONSTRAINT IF EXISTS sensor_readings_device_id_fkey;

ALTER TABLE sensor_readings
  ADD CONSTRAINT sensor_readings_device_id_fkey
  FOREIGN KEY (device_id) REFERENCES devices(id)
  DEFERRABLE INITIALLY IMMEDIATE;

Then defer it inside each bulk-insert transaction:

BEGIN;

SET CONSTRAINTS sensor_readings_device_id_fkey DEFERRED;

INSERT INTO sensor_readings (ts, device_id, value)
SELECT ts, device_id, value FROM staging_data;

COMMIT;

For a batch of 1,000 rows, this turns 1,000 FK lookups into one check at commit. The integrity guarantee is unchanged: if any device_id in the batch does not exist in devices, the commit fails and the batch rolls back. Transactions that do not call SET CONSTRAINTS ... DEFERRED continue to enforce row-by-row, so this change does not affect other callers.

2. Scope the UNIQUE check to your live data window

If your primary concern is duplicate prevention during a historical bulk load or replay, a partial index eliminates the UNIQUE check for any row whose timestamp falls outside the live window.

ALTER TABLE sensor_readings
  DROP CONSTRAINT IF EXISTS sensor_readings_unique_reading;

CREATE UNIQUE INDEX idx_sensor_readings_recent_unique
  ON sensor_readings (device_id, ts)
  WHERE ts > now() - INTERVAL '7 days';

Postgres evaluates the partial index predicate at INSERT time. A row inserted with ts = now() satisfies ts > now() - 7 days and gets the UNIQUE check. A row inserted during a historical backfill with ts = '2024-01-15' does not satisfy the predicate in 2026 and skips the check entirely. That is the primary benefit: bulk loads of historical data avoid the UNIQUE scan completely.

For ongoing ingestion of fresh data, each new row is added to the partial index as it's inserted, so the index grows over time. To reclaim the size advantage, schedule a weekly rebuild:

REINDEX INDEX CONCURRENTLY idx_sensor_readings_recent_unique;

At 2 years of retention (730 days), a freshly rebuilt 7-day partial index covers roughly 1% of the dataset: 7 / 730 = 0.0096. The index is approximately 1/100th the size of a full-table UNIQUE index on the same columns, which reduces both scan time and per-insert write amplification by the same factor.

3. Validate FK references in the application layer

For workloads where the device set is stable and well-known, validating device_id in the application before inserting removes the per-row database lookup completely.

import psycopg2

# Cache valid device IDs at startup; refresh on a schedule
def load_valid_devices(conn):
    with conn.cursor() as cur:
        cur.execute("SELECT id FROM devices;")
        return {row[0] for row in cur.fetchall()}

# Initialize the cache
conn = psycopg2.connect("dbname=mydb user=postgres host=localhost")
valid_devices = load_valid_devices(conn)

def insert_readings(conn, batch):
    # Filter invalid device IDs before they reach the database
    valid_batch = [r for r in batch if r["device_id"] in valid_devices]
    invalid_count = len(batch) - len(valid_batch)
    if invalid_count > 0:
        # Log or alert; this signals an upstream data quality issue
        print(f"Dropped {invalid_count} rows with unknown device_id")

    with conn.cursor() as cur:
        cur.executemany(
            "INSERT INTO sensor_readings (ts, device_id, value) VALUES (%s, %s, %s)",
            [(r["ts"], r["device_id"], r["value"]) for r in valid_batch],
        )
    conn.commit()

This trades a database-level guarantee for an application-level guarantee. It works when the application owns the write path and invalid device_id values represent an upstream data quality problem rather than a concurrent-write race condition. Once this pattern is in place, the database-level FK is redundant, which makes option 4 available.

4. Drop the FK constraint

If your ingest pipeline already validates device_id before writing to Postgres (as shown in option 3), the database-level FK enforces a guarantee the pipeline already provides. Removing it cuts the per-insert lock acquisition entirely.

ALTER TABLE sensor_readings
  DROP CONSTRAINT IF EXISTS sensor_readings_device_id_fkey;

This eliminates the shared-lock acquisition on devices for every insert and reduces WAL records by removing the per-row constraint check entries. Combined with the partial UNIQUE index from option 2, this recovers measurable ingest headroom without changing hardware. The Postgres Optimization Treadmill article shows that a 50K inserts/sec workload with five indexes already generates 25-50MB/sec of WAL from heap and index writes. Dropping the FK removes additional per-row overhead sitting on top of that baseline.

The tradeoff is real: no FK means no database-level catch for data quality bugs that slip through the pipeline. Only drop the constraint if option 3 is in place and you have monitoring to detect upstream device ID mismatches before they reach the database.

Validating the fix

After applying option 1 or option 4, rerun the detection query during peak load:

SELECT
    pid,
    wait_event_type,
    wait_event,
    query,
    state,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (
        (wait_event_type = 'LWLock' AND wait_event LIKE 'MultiXact%') -- PostgreSQL 16–18
     OR (wait_event_type = 'Lock' AND wait_event IN ('transactionid', 'tuple'))
      )
ORDER BY duration DESC;

MultiXact LWLock waits and any transactionid/tuple row-lock waits tied to the devices table should drop to near zero (MultiXact events only visible on PostgreSQL 16-18). If they persist after applying option 1, confirm the constraint was successfully altered to include DEFERRABLE before the transaction runs SET CONSTRAINTS ... DEFERRED.

After applying option 2, confirm the partial index exists and the full constraint is gone:

SELECT
    i.indexname,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    x.indpred
FROM pg_stat_user_indexes i
JOIN pg_index x USING (indexrelid)
WHERE i.relname = 'sensor_readings';

The indpred column contains the partial index predicate as text. A non-null value confirms the index is partial. The index size should reflect only the data that has been inserted since the last REINDEX. At 2 years of retention with a 7-day partial index freshly rebuilt, expect a size approximately 1/100th that of a full-table index on the same columns.

To verify WAL reduction after option 4, compare wal_bytes from pg_stat_wal before and after dropping the FK:

SELECT wal_records, wal_bytes, pg_size_pretty(wal_bytes) AS wal_size
FROM pg_stat_wal;

After dropping the FK, wal_bytes growth rate should decrease measurably within a few minutes of sustained ingest. On older Postgres versions, check pg_stat_bgwriter for write activity trends instead.

Next step

Run the pg_stat_activity lock detection query during your next peak ingest window. If you see lock waits pointing at your devices table, apply option 1 first: alter the FK to be deferrable and add SET CONSTRAINTS ... DEFERRED to your bulk-insert transactions. It's a two-statement change with no impact on the integrity guarantee and no risk to other callers.

If your ingest rate is still climbing and you're already on the optimization treadmill, the Tiger Data free trial lets you validate ingest headroom on your own data.

What's New in Tiger Cloud: Bigger Performance Gains, Wider Platform Reach, Better Visibility

Nicole Ghalwash — Fri, 17 Jul 2026 18:51:12 +0000

This year, we've focused on improving three areas that define the Tiger Cloud experience:

Scale without splitting your architecture: Compression becomes a performance advantage. UPDATE and DELETE on compressed data run up to 160x faster, summary queries up to 70x faster. Storage scales to 80,000 IOPS and 64 TB on demand.
Spend less time configuring, more time shipping: Tiger Console auto-tunes hypertables, the PostgreSQL Source Connector moves data to Tiger Cloud without custom pipelines, and pg_textsearch brings production-ready BM25 search natively in Postgres.
Production-grade reliability, without the DIY tax: Tiger Cloud handles data residency, network isolation, disaster recovery, and visibility so you don't have to.

This past quarter we shipped deeper query engine optimizations in TimescaleDB, new regions, new enterprise networking options, and a long list of smaller improvements to how Tiger Console works day-to-day. Instead of listing every Tiger Cloud release on its own, here's what it adds up to, and why it matters: you can stay on Postgres as you scale, you'll spend less time configuring and more time shipping, and you get the reliability and visibility that time-series workloads actually need.

Scale without splitting your architecture

The moment analytical queries start competing with transactional ones, teams feel pressure to bolt on a separate analytical database. This quarter's TimescaleDB releases and storage upgrades ensure Postgres keeps scaling for time-series and analytical workloads instead of becoming the reason you re-architect. Here's what shipped, and why it matters.

Run queries and writes directly on compressed data, without the performance tax

Compression used to mean a trade-off: a smaller footprint for slower access. With the release of TimescaleDB v2.26, that trade-off keeps shrinking. Aggregate queries like COUNT, MIN, MAX, and FIRST/LAST now read straight from compressed metadata instead of decompressing full batches, up to 70x faster. Grouping with time_bucket() runs roughly 3.5x faster. Multi-column filters push down directly into compressed scans, cutting unnecessary decompression by half or more.

Writes get the same treatment. TimescaleDB v2.27 lets UPDATE, DELETE, and UPSERT on compressed chunks skip decompressing data that can't match, so selective write operations run up to 160x faster. Query rewriting can automatically route matching aggregations to a continuous aggregate, and continuous aggregate refreshes can compress chunks as part of the same job instead of needing a separate policy.

Continuous aggregates are now more reliable at scale. We fixed three stability issues that were constraining them: a memory leak, query correctness edge cases, and a deadlock during concurrent refreshes. As a result, you can now push continuous aggregates harder without operational workarounds or special handling.

Add full-text search without adding a search engine

As part of the pg_textsearch v1.0.0 release, BM25 full-text search now runs natively inside Postgres, and is production-ready. Add relevance-ranked search to your application without standing up and syncing a separate Elasticsearch cluster. In benchmarks at 138 million documents, pg_textsearch ran up to 6.5x faster than ParadeDB on typical multi-word queries and sustained 8.7x higher concurrent throughput. It ships with an <@> query syntax, a bm25_force_merge() function for segment consolidation, and support for Postgres 17 and 18. One less system in your stack to operate and keep in sync.

Scale storage on demand instead of provisioning for a peak that may not come

Scale plan services can now choose between 16,000 and 40,000 IOPS with up to 1,500 MB/s of throughput. Enterprise plans go up to 80,000 IOPS and 2,000 MB/s, with total capacity up to 64 TB. Changes apply without downtime, and you pay only for the IOPS you use. Size up as your workload grows instead of guessing at peak load today.

Spend less time configuring, more time shipping

None of the above matters much if half your week still goes to console configuration instead of building. The following updates hand more of that time back to you so you can focus on what matters: building your product, not configuring your database.

Build hypertables in a few clicks, without writing SQL

Define hypertable columns directly in Tiger Console instead of writing SQL. Configure a columnstore in the same step. For the best performance, you can enableautomated chunk tuning afterward, so you won't have to manually set chunk intervals.

Move data from Postgres into Tiger Cloud without building your own pipeline

The PostgreSQL Source Connector is now stable and ready for production use. Replicate an existing Postgres database into Tiger Cloud without hand-rolling a migration or sync pipeline. It supports a configurable worker count for the initial data copy, table selection by publication or direct selection, SSH tunneling, and bulk updates for table and schema mappings. Everything you need to bring production data over reliably.

Production-grade reliability, without the DIY tax

Data residency, network isolation, disaster recovery, and visibility into what's happening inside your database are table stakes for any fully-managed platform. The whole point of choosing Tiger Cloud is that you shouldn't have to design, build, and maintain that infrastructure yourself. Here's what shipped this quarter that takes more of that off your plate.

Meet data residency requirements in more places

Tiger Cloud is now available in two additional Azure regions: Germany West Central (Frankfurt) and Southeast Asia (Singapore). Teams with GDPR-sensitive workloads can now keep EU data in-region, and Asia-Pacific teams get local data residency and lower latency, without moving off Azure. For a list of all available regions, click here.

Keep database traffic off the public internet

Private endpoint support is now generally available across every supported AWS and Azure region. AWS PrivateLink connects from your VPC over the AWS private network. Azure Private Link does the same from your VNet over Microsoft's private backbone. Both are configured directly in Tiger Console and included on Scale and Enterprise plans, so database traffic never has to touch the public internet.

Recover easily when a region goes down

Cross-region backup already copies your data to a geographically distant region. Now Enterprise customers can restore directly from that backup in Tiger Console, closing the loop so a regional outage doesn't mean opening a support ticket to get your data back. To learn more, click here.

Catch problems in the tools you already use, before they escalate

Visibility should be simple. You shouldn't have to wait for a problem to surface before you can see it coming. The Tiger Cloud status page now lives at status.tigerdata.com, tied directly into incident response, so you can subscribe and get notified the moment an incident is created, updated, or resolved instead of finding out from a support thread.

Inside the Metrics tab in Tiger Console, a new Queries per Second graph gives a real-time view of throughput, making it easier to spot spikes or drops in query volume.

In the Insights tab, the query deep dive page now tracks CPU, memory, and storage IO (read and write) over time, so you can catch a query's resource footprint trending the wrong way and see its downstream impact on system health before it turns into a bigger problem.

Additionally, you can check the Chunk timeline in the Explorer tab to see how your data is organized across chunks. You can inspect sizes, time ranges, and whether each chunk is in the rowstore or columnstore. This lets you spot organization issues and monitor columnstore job health without running system queries.

Lastly, if you already monitor infrastructure elsewhere, Tiger Cloud now exports telemetry to Azure Monitor, plus PostgreSQL-specific metrics (replication, cache usage, background activity) to Amazon CloudWatch, Datadog, and Prometheus, with system-level disk IO and throughput metrics exported by default. Wherever you already look for problems, Tiger Cloud's data is there too. For a full list of available metrics you can export with Tiger Cloud exporters, click here.

Try Tiger Cloud for free

All of these features are live on Tiger Cloud. If you're already a customer, sign in and check out the latest TimescaleDB releases on your compressed hypertables and the new IOPS options if you're storage-bound.

If you're new to Tiger Cloud, start a free trial and see what a Postgres-native operational analytics database looks like.

The Data Layer for the AI Data Center

Team Tiger Data — Wed, 08 Jul 2026 13:47:46 +0000

This is Part II of a two-part series on the AI data center stack. Part I, AI's Physical Constraints: How AI Rewired the Data Center, explains why AI capacity has become constrained by the physical data center stack: accelerators, memory, cooling, power, time, and water.

Physical Plant Telemetry Is Now a Data-Layer Requirement

In an AI data center, the workload no longer stops at the server boundary. A synchronized training job can move power, cooling, battery response, and power electronics in patterns that look less like conventional facility load and more like part of the compute system itself. The physical plant has become coupled to the computer's execution profile.

This paper starts from the operational consequence: once those systems are coupled, their telemetry has to be correlated, retained, and queried together.

That coupling changes what operational telemetry has to do.

The plant is still operated through established OT patterns: PLCs, protection systems, BMS, PMS, CDU controllers, SCADA, message brokers, and layered networks. None of that goes away. What changes is the correlation requirement. Operators now need to ask questions that cross systems and timescales: what happened to facility load during a training phase transition, which cooling loop reacted first, which racks drove the phase imbalance, whether battery or UPS behavior aligned with GPU power movement, and how a hall-level event propagated into the campus electrical posture.

Those are not dashboard-only questions. They are data-layer questions. The system has to ingest the raw signal, preserve its context, retain it long enough to matter, and make it queryable without moving the data out of the environment where operations run.

The volume is assumed. NVIDIA DCGM exposes GPU telemetry through field identifiers and exporter paths covering clocks, power, thermals, energy, and fabric health, with third-party collector coverage describing thousands of metrics across GPU, MIG, NVLink, NVSwitch, and CPU scopes. The facility side adds liquid-cooling loop temperatures, pressures, flow, pump state, CDU status, leak detection, phase-level power, UPS and battery behavior, vibration, generator state, and grid-interface telemetry. OCP telemetry work catalogs base-building and data-hall points across utility, generation, central plant, power monitoring, cooling, environmental, and liquid-cooling systems.

The harder issue is not only volume. It is a timescale mismatch. GPU power can change in seconds or less. Electrical protection events can unfold in cycles. Cooling loops respond more slowly. Building systems often trend at minute cadence. The data layer has to preserve those cadences without flattening them into averages that erase the sequence operators need to reconstruct. Traditional facility telemetry was often sampled at minute-level cadence because the use case was monitoring, trending, and capacity planning. OCP notes that minute-level power sampling was commonly used to reduce network impact. AI workload telemetry, by contrast, is routinely collected at one-second or finer resolution, and the workload itself moves in synchronized phases. A 756-GPU academic cluster study collected 162 GB of per-second telemetry over 31 days. SemiAnalysis, citing Meta's Llama 3 infrastructure, describes tens of megawatts of instantaneous power fluctuation from synchronized GPU behavior on a 24,000-H100-class cluster with about 30 MW of IT capacity. Meta separately documented two 24,576-GPU clusters used for Llama 3 training. NERC's July 10, 2024 incident review documented approximately 1,500 MW of customer-initiated, voltage-sensitive load reduction after a 230 kV transmission fault sequence, six faults in an 82-second period.

The operational result is simple and uncomfortable: slow facility telemetry and fast workload telemetry now have to live in one data layer, with enough fidelity to be queried together. The data model has to respect OT boundaries, preserve local autonomy, and still support building, campus, and enterprise rollup. It has to fit the systems already in place rather than ask the facility to reorganize itself around a database.

That is the architecture this paper lays out: a PostgreSQL-native time-series layer, implemented with TimescaleDB, present at each Purdue scope, behind SCADA and the operational applications rather than in place of them, integrated through OT protocols, compressed and rolled up the hierarchy on premises, and optionally synchronized to a managed cloud only when policy allows.

Data-Layer Requirements

The reference architecture starts with requirements, because the failure mode is rarely a missing connector or a single slow query. It is a data layer that satisfies one requirement while violating another. For AI data center operations, the following have to hold together.

Sustained high-frequency, high-cardinality ingest. The ingest path must support continuous streams from GPUs, power chain, cooling chain, BMS, PMS, CDU, leak detection, vibration, battery, and grid-interface systems, and keep insert behavior stable as tags, devices, tenants, halls, and derived metrics grow.

Years of history online and affordable. Operators need recent high-resolution data for incident response, but capacity planning, model-based optimization, energy analysis, and failure prediction depend on long histories. Aging data cannot disappear into cold archives that need a separate restore path before they can answer a question.

Concurrent real-time and analytical access. The same operational data estate has to serve live dashboards, alarm investigation, root-cause analysis, fleet comparison, efficiency studies, and long-running analytical queries. Isolation matters, but copying data into disconnected stores for every audience defeats the point of a facility-wide operating record.

Edge-to-enterprise rollup with local autonomy. Data is born locally. A hall must keep recording if the building rollup is unavailable, and a building must keep operating if the campus link is down. Rollup should be delayed, replayed, and reconciled without changing the identity or semantics of the data.

Reliability and durability as baseline behavior. High availability, automatic failover, incremental backup, point-in-time restore, and operational visibility are not convenience features in this environment. They are part of the minimum viable design for operational infrastructure.

Deployment where the data lives. The primary target is on-premises, at the edge, in the data center, or in a customer-managed environment. Air-gapped and intermittently connected sites must be first-class designs, not exceptions.

Reference Architecture: A Time-Series Data Layer at Every Purdue Scope

The AI Data Center Telemetry Stack. A time-series data layer sits behind SCADA, HMI, MQTT, and operational applications at the hall and building scopes. Deterministic control stays local. Operational data rolls up on premises from hall to building to campus to enterprise, with optional managed-cloud synchronization shown as a separate path. Purdue layers are logical operating scopes; actual network segmentation varies by site.

The reference architecture places a time-series data layer at each operational scope of the Purdue model. Treat those scopes as logical, not as strict network boundaries. In many industrial environments Purdue Layers 2 and 3 share a subnet or operational network, so the layers describe responsibility and rollup position, not mandatory physical segmentation. This does not move control into the database. Deterministic control loops stay in PLCs, controllers, protection systems, BMS, PMS, CDU controllers, and SCADA. The database observes, stores, aggregates, and serves. It does not sit in the deterministic control path.

The key architectural choice is consistency: the same time-series technology runs at each scope, scaled to that scope's responsibility, with data rolling up the hierarchy. Rollup becomes a native data architecture instead of a chain of one-off translations between incompatible stores.

Layers 0 and 1: instrumentation, control, and local capture

Field devices and control assets produce the raw state: meters, breaker monitors, rPDUs, UPS telemetry, CDUs, leak sensors, valve positions, pump speeds, pressure transducers, flow meters, temperature and vibration sensors, PLC tags, BMCs, and GPU node telemetry. Collection happens through industrial gateways, collectors, or local agents adjacent to the control network.

Accepted ingress patterns include OPC UA for structured industrial data, Modbus and BACnet where building and equipment systems already expose them, MQTT for brokered publish and subscribe, Redfish for server and hardware management telemetry, and SNMP where power and network equipment still emit it. OCP's telemetry guidance explicitly covers MQTT, Redfish, TLS and mTLS, push and pull, publish and subscribe, Modbus, BACnet, segmentation, and secure data exchange across the OT and IT boundary.

At this layer the local database footprint can be small. Its job is short-horizon buffering, timestamp integrity, store-and-forward behavior, and protection against upstream loss. It should run on industrial PCs or local edge servers without introducing a dependency into the control loop.

Layer 2: hall operations and the hall historian

Layer 2 is the hall-level operating environment. This is where the supervisory and application systems live: SCADA and HMIs, Ignition, MQTT brokers, and the hall-level applications operators work day to day. The time-series data layer does not replace any of them. It sits behind and alongside them as the hall historian: the durable, queryable record of what the hall did.

A hall-level time-series store is the reference pattern, because it gives each hall a complete, durable local record and lets it stay available on its own. Not every deployment starts there. Some facilities run the data layer at the building scope and above and aggregate hall telemetry there, then add hall-level stores as availability requirements, complexity, and the need for local autonomy grow. The architecture supports both: the hall tier scales in where those requirements justify it, while the rollup hierarchy stays the same.

Where deployed, each hall-level time-series store ingests high-frequency telemetry for that hall, receiving it through Ignition gateways, MQTT brokers, OPC UA bridges, protocol gateways, collectors, and application connectors. SCADA remains the supervisory environment, the HMIs remain the operator interface, and MQTT remains the brokered transport where it is already used. Against that, TimescaleDB provides durable high-resolution history, SQL access, compression, continuous aggregates, and local query performance. It serves historical queries behind the local dashboards and preserves operational history during disconnection from upper scopes: if the link to building or campus is unavailable, Layer 2 keeps recording. SCADA and the HMIs remain the operator's real-time supervisory surface; the historian is what they query when the question is what happened, when, and in what order. Order matters here, and so does context: because TimescaleDB is PostgreSQL, writes land in ACID transactions, and the historian can preserve the recorded sequence with the timestamp, source, and quality context needed to reconstruct event order.

This is where high-cardinality ingest matters most. A hall can contain thousands of accelerators, dense liquid-cooling loops, high-frequency power instrumentation, and multiple local systems previously operated in separate views. The Layer 2 historian should answer immediate operator questions: what changed in this hall, which CDU loop moved first, which racks saw the phase imbalance, what the GPU power profile did, and which protection or backup systems responded.

Layers 2.5 and 3: sub-zone, building, and SCADA adjacency

Layers 2.5 and 3 aggregate across halls, rooms, mechanical zones, electrical lineups, and building systems. In practice these scopes often share an operational network with Layer 2 rather than sitting on a separate tier; the distinction is one of rollup responsibility, not necessarily of subnet. This is the integration point for SCADA, BMS, PMS, DCIM, MQTT brokers, message buses, and operational applications.

The data layer sits alongside SCADA, not in place of it. SCADA remains the supervisory interface and control environment at both the hall and building scopes. The time-series layer is the durable, queryable substrate behind real-time views, historical analysis, external reporting, and analytics.

The strongest proof point for this fit is the strategic alliance between Inductive Automation and Tiger Data, the company behind TimescaleDB, announced in April 2026 to modernize the industrial historian. Inductive Automation makes Ignition, a widely deployed industrial application and SCADA platform; TimescaleDB Enterprise (in early access; sign up to become a design partner) is an on-prem PostgreSQL-based time-series database that serves as the historian behind it, for on-premises and edge deployment. For operators already standardizing on Ignition, that makes the database an integrated historian path rather than a parallel system the operations team has to glue together alone.

Layers 4 and 5+: campus, enterprise, and cross-site analytics

At Layer 4 and Layer 5+, data rolls up for campus operations, cross-building comparison, capacity planning, reporting, fleet analytics, and AI or machine-learning workflows. This scope does not need every raw sample forever at the hottest resolution. It needs governed rollups, selected raw windows, and enough fidelity to reconstruct operational behavior when an incident crosses halls, buildings, or grid boundaries.

The rollup itself is the heart of the architecture, and it runs on continuous synchronization between scopes: hall to building, building to campus, campus to enterprise. Where supported, planned synchronization capabilities move selected raw streams, rollups, and metadata upward between TimescaleDB stores, so an upper scope holds a faithful, current view without the lower scope losing autonomy or identity. Critically, this rollup stays inside the operator's own infrastructure. It does not require the cloud.

Moving data from the on-premises hierarchy to Tiger Cloud is a separate concern. For organizations that want managed cross-site analytics, planned synchronization capabilities can provide an optional path to Tiger Cloud, where tiered storage keeps frequently queried data in a high-performance tier and moves older data to object storage. Tiger Cloud documentation describes up to 64 TB in the high-performance tier depending on plan, with a low-cost object storage tier behind it. That path is optional and cloud-bound. The on-premises hierarchy is complete without it: for sites that cannot send operational data out, the full edge-to-enterprise rollup still runs, entirely self-managed.

Operator Scenario: Reconstructing a Load Event

A training job enters a synchronized phase. GPU power draw rises across a hall, phase-level power telemetry moves, one CDU loop responds before the others, and UPS or battery telemetry shows a corresponding event. The hall historian preserves the high-resolution sequence locally. The building rollup shows whether the response crossed halls or stayed local. The campus view shows whether the event aligned with broader electrical posture. The operator does not need five disconnected exports to reconstruct the event. The data layer preserves source, timestamp, unit, quality, and lineage so the question can be asked across systems without losing the order of operations.

Why TimescaleDB Fits This Architecture

The requirements above point to a single system. The data layer has to speak SQL, keep time-series data bounded as it grows, compress years of history into affordable storage, roll up cleanly from edge to enterprise, and run reliably on hardware the operator controls. TimescaleDB is a PostgreSQL extension that does all of that in one engine.

Because it is an extension and not a fork, it keeps standard PostgreSQL clients, drivers, and SQL, and adds hypertables, continuous aggregates, compression, and retention. The data layer fits the tooling operations and analytics teams already run: drivers, BI tools, Grafana, backup tooling, access-control patterns, and the operational knowledge already in the building. There is no proprietary query language to learn and no silo that only one vendor's tools can read.

Hypertables answer the volume problem. They partition by time, so ingest, retention, compression, and queries operate over chunks instead of one ever-growing table that eventually degrades. Continuous aggregates answer the rollup requirement. They maintain incrementally refreshed views, including rollups over rollups, which maps directly onto the Purdue hierarchy: raw hall data rolls into minute, hourly, and daily views; building aggregates roll into campus views; selected metrics feed enterprise models.

Compression is what makes long online retention practical. Tiger Data's IIoT performance work reports common compression ratios of 80 to 95 percent, with a terabyte of raw data compressing to roughly 50 to 100 GB. Hypercore, the hybrid row and columnar engine behind this, lands new data in a row-oriented path for ingest and updates, then moves older data into columnar storage for compression and analytical scans. One table serves both the operator querying the last hour and the analyst scanning the last year.

On ingest, the honest claim is sustained production scale, not a record for peak rows per second on a narrow benchmark. The partitioning model exists to prevent the failure mode where a single PostgreSQL table grows until inserts and queries fall over. Direct Compress, a TimescaleDB 2.21 tech preview from September 2025, compresses data in memory during COPY ingestion and reports up to 40x faster ingestion in its benchmark scenario, with the usual caveat that schema, batching, storage, and workload shape change the result. AI data center telemetry is won by keeping ingest, SQL access, compression, rollup, retention, security, and operations in one reliable system, not by a drag race.

Reliability Where Operations Actually Run

The guarantees you were promised, on your own hardware.

Operations teams have spent years being told that critical data infrastructure should behave like a managed service: high availability, automatic failover, incremental backup, point-in-time restore, operational dashboards, controlled upgrades, and recovery that does not depend on a hero at 3 a.m. The requirement is right. The deployment assumption is wrong. Many AI data center environments cannot make a managed public cloud service the dependency of record for OT data.

TimescaleDB Enterprise is the self-managed answer to that constraint: the open-source TimescaleDB engine plus the operations layer, licensed for on-premises, edge, and customer-managed cloud, and built to run air-gapped. The operations layer is the point. High-availability clustering, automatic failover, fully incremental backups, and point-in-time recovery put managed-grade behavior on hardware the operator owns. A web-based admin console and pre-configured Grafana dashboards make that behavior visible instead of tribal.

Because the engine is the same at every scope, each layer can be operated to the criticality of its function. A Layer 2 hall historian runs locally and stays durable through an upstream outage. A Layer 3 building store runs with replicas and failover. A Layer 4 campus store aggregates across buildings without forcing every hall to depend on the campus link. Backup and restore are part of the operating model, not an export script someone has to remember to run.

Two data paths leave each site, and the distinction matters. On-premises rollup moves selected raw streams, rollups, and metadata up the hierarchy while each local system stays complete on its own. Cloud synchronization is a separate, optional path for organizations that want cross-site analytics in Tiger Cloud. For air-gapped sites, regulated sites, and facilities where policy, latency, or operational independence keeps OT data local, the rollup path runs without the cloud path ever being enabled.

Operating Model and Guardrails

The architecture should be deployed with clear boundaries.

First, keep the database out of the control loop. It can record telemetry and serve queries, but a database issue must never affect trip logic, PLC scans, protective relays, CDU control, or BMS control loops.

Second, keep the storage technology consistent across scopes. When the same time-series engine runs at the hall, the building, the campus, and the enterprise, a query written against a hall historian stays valid against a campus rollup, and identities, units, and retention policies carry upward without reinterpretation. Consistency at this level is what turns rollup from an integration project into a property of the system.

Third, keep identities stable across rollup. A tag, device, rack, CDU, UPS, hall, tenant zone, or GPU should not get a new identity at every layer. The rollup path should preserve source, timestamp, quality, unit, and lineage.

Fourth, design for replay. Local stores need bounded queues and retention windows that let them backfill upstream systems after a link outage. The system should assume disconnection, not merely tolerate it.

Fifth, separate raw, operational, and analytical views. Operators need recent raw and high-resolution data; building and campus teams need rollups; enterprise analytics needs governed datasets. Hypertables, compression policies, retention policies, and continuous aggregates provide the database-level primitives for that separation.

Finally, make reliability visible. HA state, replica lag, backup freshness, restore validation, ingest lag, compression job health, disk headroom, and query saturation belong on the operational dashboard. A database holding operational telemetry becomes part of the facility's own instrumentation.

The Operator's Goal

The data layer is not the goal. Energizing the build is the goal. Keeping the facility stable through synchronized workload behavior is the goal. Running power and cooling close to the real operating envelope, without sacrificing margin blindly, is the goal. Planning the next tranche of capacity with evidence instead of guesswork is the goal.

A time-series layer earns its place only if it serves those outcomes. It has to fit the SCADA and protocol stack already in place. It has to keep years of history online without making storage economics impossible. It has to serve real-time and analytical access without splitting the operating record. It has to roll up from edge to enterprise while letting every local layer keep running when the link drops. And it has to deliver reliability and durability on hardware the operator controls.

That is the reference architecture: TimescaleDB as the PostgreSQL-native time-series data layer, present at each Purdue scope, sitting behind SCADA and the operational applications rather than replacing them, integrated through OT protocols, compressed and rolled up the hierarchy on premises from hall to building to campus to enterprise, operated locally, and optionally synchronized to Tiger Cloud only when policy allows.

The physical plant is now part of the computer. The data layer has to be built like that is true.

Get Started

Interested in managed time-series analytics? Start a free Tiger Cloud trial.

Running on-premises, at the edge, or in an air-gapped environment? TimescaleDB Enterprise is built for those deployments and is accepting design partners.

AI's Physical Constraints: How AI Rewired the Data Center

Team Tiger Data — Thu, 02 Jul 2026 19:34:34 +0000

For most of the cloud era, a server rack was a five to twenty kilowatt object. You could fill a room with them, move air across the front, and the building stayed an ordinary building. A single current AI rack, NVIDIA's GB300 NVL72, draws about 132 to 140 kilowatts, with the GPUs alone accounting for more than a hundred. That is close to an order of magnitude more power in the same floor space as those old racks, and it lands as heat in the same small volume. Past roughly a hundred kilowatts per rack, air stops being able to carry the heat out, and the rack has to be plumbed for liquid. The compute got denser and the building changed with it.

This pattern repeats all the way out to the grid. For about fifteen years, getting more computing power felt like turning a dial. You needed more, you asked for more, and a few seconds later it was there. Spin up a hundred servers for a traffic spike, spin them back down when it passes. Capacity behaved like something continuous, instant, and reversible, a knob you turned rather than a thing you built. A generation of software was designed on that assumption, and it held, because for ordinary workloads the power and hardware involved were small against what the world could supply.

That has changed. Across AI infrastructure projects, the same moment now repeats. A team asks for more, and the answer comes back no. Not "no, that costs more," which everyone understands, but a harder no. "No, those GPUs are not available this quarter." "No, that region has no more power, and will not for years." You can order a GPU in a day; the date that a few hundred megawatts arrives at a site can be four or five years out, and no amount of money moves it sooner. The request that used to be a billing question has become a physical one.

Anyone who has designed, built, or run a data center knows the physical layer was always there. The people who design and build these facilities sized the transformers, ordered the switchgear, planned the cooling, and waited on the utility. What is new is the scale at which AI hits the physical layer. The data centers going up for AI are a different class of build: denser, hotter, hungrier, and more tightly coupled to the grid than the ones that came before.

Data centers always needed chips, memory, cooling, power, and water. Most cloud workloads before the AI surge kept those requirements in a range the existing build could absorb. AI pushes them past the thresholds where the old assumptions hold. It does not create a new kind of physics. It removes the buffer that made the physics easy to ignore.

Each of these limits has been written about on its own. What gets missed is how they connect, and why AI makes them arrive together. AI scaling moves through a physical dependency chain: more accelerators require scarce chip packaging and memory; more memory and compute concentrate heat; concentrated heat changes the rack and the building; the building then needs power the grid may take years to deliver; at that scale, the power itself may have to be buffered inside the facility; and the cooling choices made along the way determine where water becomes a problem. The limits arrive in a predictable order, and the order is the story:

GPUs. The first visible shortage is accelerators, the GPUs that do the AI computation, but the real bottleneck sits around the chip, not in it.
Memory. The accelerator depends on high-bandwidth memory, which pulls on the same finite wafer base as ordinary memory.
Cooling. More compute and memory in the same space means more heat in the same rack, past what air can carry.
Power and time. Liquid cooling moves heat, but every watt still has to come from the grid. First you wait for power to arrive; then, at AI scale, you may need to buffer the workload's own power swings.
Water. Not the national catastrophe the headlines suggest, but a local siting constraint shaped by cooling design.

Let's start with the one everyone already knows.

GPUs: The Bottleneck Is Not the Chip

The first wall everyone notices is GPUs. You cannot get them, you cannot get enough, or the price to rent them has climbed since last year. The figures are not subtle. H100 rental prices rose roughly 40 percent off their late-2025 lows in a matter of months, from about $1.70 to $2.35 per GPU-hour on one-year contracts between October 2025 and March 2026, and on-demand capacity is effectively sold out across GPU types. The pressure reaches the workstation end too. In June 2026 NVIDIA listed its RTX Pro 6000 Blackwell at $13,250, a 55 percent jump over the $8,565 launch price a year earlier, and the reason it gave was the 96 gigabytes of memory on the card in a market where memory is scarce.

The obvious reading is that NVIDIA cannot make enough chips, but that is not where the bottleneck lives. A modern AI accelerator is not one chip but a package: the processor die, stacks of high-bandwidth memory (HBM), and an interposer that wires them together at enormous bandwidth. A faster processor does not help if it cannot be packaged with memory, and advanced packaging capacity is finite, specifically the chip-on-wafer-on-substrate (CoWoS) lines at TSMC. The same analysis that tracked the rental spike named CoWoS packaging and HBM, not the processor, as the choke points. The lead times that stretch GPU orders toward a year are gated there. The chip is not the scarce thing; what surrounds it is.

So the GPU shortage is really a packaging and memory shortage wearing a GPU label. Packaging capacity can expand, but it expands on a manufacturing clock. Memory is the harder half, and the reasons it stays scarce are the next wall.

Memory: Why the Price Will Not Come Down

When a component spikes in price, the reflex is to wait. Shortages end, factories ramp, the price comes back down. That reflex is wrong here, and the reason is structural, not cyclical.

The memory AI systems need comes in two kinds. HBM is the fast, expensive memory stacked right next to the accelerator, where the model's working data lives during computation. Dynamic random-access memory (DRAM) is the ordinary system memory around it. The binding shortage is HBM, and the pressure spills into DRAM because both pull on the same finite wafer base. SK Hynix, the leading maker of HBM, locked up its 2026 HBM output well ahead of the year, and Micron has likewise reported its 2026 HBM sold out. Memory has gone from a rounding error in a machine's bill of materials to the single largest driver of the price on a high-end card.

So why not just make more? Essentially all the world's DRAM comes from three companies: Samsung and SK Hynix in South Korea, and Micron in the United States. When all three make the same allocation call at once, that is the global supply. There is no fourth maker at comparable scale waiting to undercut them.

Adding supply means building a fabrication plant, and a fab is not a factory you stand up in a quarter. It takes years of cleanroom construction, tool installation, and qualification before a single sellable chip comes out. HBM makes the squeeze worse, not better: each gigabyte of it uses about three times the wafer capacity of DDR5, today's standard volume DRAM, so every wafer redirected to the scarce thing makes the common thing scarcer still, and HBM already consumes roughly a quarter of all DRAM wafer output.

There is a second reason, and it is a choice rather than a constraint. The makers are steering wafers toward high-margin AI and enterprise memory and away from everything else, because that is where the money is. A new fab does not automatically reverse that, because the same margin logic governs what it chooses to build. IDC, the market-research firm, projects 2026 DRAM supply growth of only about 16 percent year over year, well below the 20 to 30 percent that was historically normal, even as demand for the AI variety grows far faster. The people running these companies are saying so directly. Intel's chief executive, Lip-Bu Tan, relayed in February 2026 what two of the key memory makers had told him: there is no relief until 2028.

The makers could produce more, but it takes years, only three companies do it at global scale, the AI memory eats three times the wafers, and they earn far more selling to AI than to you. Part of the shortage is physics, the supply is simply years out. Part of it is choice: the capacity that does exist is being pointed at AI, not at you. You have not been priced out for a quarter. You have been outbid.

Cooling: Why the Rack Changed Shape

GPUs and memory are still things you buy, even when you have to wait. The next wall is different. The same density that makes AI systems powerful, more transistors on the die and more memory stacked beside it, becomes heat the moment the hardware enters a building.

Every watt of power a machine draws comes back out as heat. Whatever goes in has to come out, or the machine cooks itself. It sounds too simple to matter, and it is the whole reason the rack changed shape.

For most of the history of computing, taking the heat out meant moving air. Servers in a rack, cool air through the room, and that was enough. At the rack densities of the CPU era, even at the top of the range, the airflow was manageable enough that the building stayed recognizably the same kind of building: rows of racks, cold aisles, hot aisles, chillers, and fans. Anyone who built those rooms knows the envelope.

AI did not make heat new. It made heat dense.

All the capability crammed into the die and the memory turns into heat in the same small volume. The rack densities from the opening, an order of magnitude higher than the CPU era, are really heat-removal figures: every one of those kilowatts has to be carried back out. The next generation on the roadmap, the Vera Rubin systems, is projected to push per-rack density several times higher again, and cooling vendors are already designing for the increase. At those densities, air cooling stops being practical. Bigger fans do not solve the volume problem.

Figure 1: Rack power density by generation. Past roughly 100 kilowatts, air cooling stops working and liquid becomes mandatory. The Rubin Ultra figure is a roadmap projection.

So the machine changed shape. A given volume of air can carry only so much heat away before it has to move faster than is practical, while water carries roughly three to four thousand times as much heat per unit volume as air. Past roughly a hundred kilowatts per rack, that gap stops being an efficiency question and becomes a hard limit, and the model flips: from moving air through a room to carrying heat away in liquid piped directly to the chip. The newest systems do not offer an air-cooled option at all. The GB300 NVL72 is fully liquid-cooled. The rack is no longer just electrical equipment. It now has plumbing.

Capital helps with procurement and retrofits. It does not change the thermal limits of air. This wall is geometry and thermodynamics. And it has a consequence even experienced operators feel: the hardware no longer fits in most existing buildings. A data center built for air, even one finished a couple of years ago, often cannot host these racks without being substantially rebuilt, retrofitted for liquid distribution, higher floor loading, and the plumbing that comes with it. You cannot simply drop the latest GPUs into the footprint you already have. For an operator who has spent a career optimizing airflow, that is the moment it becomes clear this is a different kind of building.

Liquid cooling changes how heat leaves the chip. It does not change where the energy comes from. Every watt still starts at the grid, and that is where the slowest constraint appears.

Power and Time: The Wall Underneath the Walls

What limits AI in the end is not chips, and it is not cooling. It is electricity, and specifically it is time. You can buy a GPU in a day. You cannot buy the specific date on which a few hundred megawatts will be delivered to a site. That is set by a physical system that moves on a timescale of years. Money can fund equipment and alternatives, but it does not make shared grid capacity appear on software time.

The building can be designed and built on one clock; the grid upgrades that let it draw full power often run on a longer one. Before a site can draw full power, the utility has to study and approve the load, the transmission system has to support it, and the substations and lines that feed it have to exist. The industry calls this time-to-power : the interval between choosing a site and being able to draw the load you planned around. For large AI sites, that interval can define the project. The upstream grid work resists money in a way the other constraints do not, because the transmission lines and substations are shared infrastructure that serves everyone on the grid, so a new load cannot simply pay to skip the queue without the wires actually being built. You can finish the building and then wait to turn it all the way on.

The time-to-power gap. You can buy a GPU in a day, but energizing a site takes years, and the build can finish while the power wait continues.

There is another clock running alongside the approval queue: the equipment itself. Even once a project is cleared to connect, the high-voltage transformers and switchgear that tie it to the grid can be in shortage. Lead times for large power transformers have stretched from roughly two years before 2020 to as long as five years now, and industry estimates suggest a meaningful share of planned 2026 data-center capacity could slip for want of power equipment and grid connections. Electrical gear is not the biggest line item in a data center. It can still decide when the building turns on, a reversal any operator who has waited on a transformer order will recognize.

The grid backlog around these projects is large. At the end of 2025, more than 2,000 gigawatts of generation and storage capacity were waiting in line to connect to the US grid, roughly twice the entire installed US power fleet. More waiting to connect than currently exists. That queue is not the same thing as a data-center load request, but it shows the condition of the shared infrastructure every large new load depends on: the wires, substations, studies, and upgrades are all moving on a multi-year clock. Lawrence Berkeley National Laboratory, which tracks those queues, finds the median time from request to commercial operation has roughly doubled, from under two years for projects built in the early 2000s to four to five years now.

This is not abstract, and it is not only an American problem. Ireland is the cleanest example. Dublin had become one of Europe's great data center hubs until the grid could not keep up, and in 2021 the grid operator EirGrid and the Commission for Regulation of Utilities imposed what amounted to a moratorium on new connections in the Dublin area. One Amazon project and two Microsoft projects were among those turned away and relocated to London, Frankfurt, and Madrid. By 2024, data centers were drawing around 21 percent of all the electricity in the country. The moratorium eased only in December 2025, and the new terms show where things are headed: a new facility now has to bring its own power generation or storage rather than simply draw from the grid.

The power industry itself is reorganizing around this demand. In May 2026, NextEra Energy announced a roughly $67 billion all-stock plan to acquire Dominion Energy, the utility behind northern Virginia's data center corridor.

That is the first half of the power story: getting electricity to the site. The second half starts once it arrives, and it is where AI looks least like the loads the grid grew up serving. A large training run is synchronized. Tens of thousands of accelerators compute, pause together to exchange results, and compute again. Power draw follows that loop. A single H100-class GPU draws far less at idle than under compute, so when tens of thousands switch states together, the facility's load can swing by tens of megawatts in seconds or less. Meta reported swings around 30 megawatts on a 24,000-GPU cluster training Llama 3.

A synchronized training cluster swings between compute and pause many times a second. The grid is built to follow the smooth aggregate of many independent users, not one correlated load moving in lockstep.

The grid was built around load diversity, where thousands of independent homes and businesses average into something smooth and predictable. A synchronized training cluster is neither diverse nor smooth, and that is the part that is new even to people who have planned power for a living. The stability problem is broader than training-loop swings: large data-center loads can also behave unexpectedly during grid disturbances. In July 2024, a transmission fault in Northern Virginia caused roughly 1,500 megawatts of data-center load to disconnect itself within 82 seconds, an event the North American Electric Reliability Corporation (NERC), which sets and enforces reliability standards for the North American bulk power system, said the system had never seen at that magnitude.

The fix moves on-site. xAI's Colossus cluster in Memphis installed about 150 megawatts of grid-scale battery storage alongside its power infrastructure. The point of that storage is not how much energy it holds but how fast it can absorb and deliver power. A small, fast store placed in front of a slower supply is a cache. Here the slow backing store is the grid, and the fast store is local batteries and power electronics. Batteries are no longer only backup equipment. In these designs, they can become part of workload control. At AI scale, power stops being merely an input to the computer. It becomes part of the computer's design.

And a design has to be operated. Once batteries, power electronics, cooling loops, and GPUs act as a single system, someone has to watch them as one: how power draw tracks compute, how the batteries answer a training swing, how heat follows the load. Those measurements arrive every second, from equipment that used to belong to three different teams. Read after the fact, they tell you what broke. Read live, they keep the loop stable.

Power is where the earlier constraints converge. The GPU you could not get, the region that was full, the building that needed rebuilding, the batteries now sitting between the workload and the grid: each one traces back to the same place, a power system that has to be built and buffered, on the grid's schedule, not the software team's.

Water: In Proportion

Power is the hardest wall because it sets the clock and, through on-site batteries and power electronics, becomes part of the machine's own design. Water is different. It sits downstream of cooling design and geography, which makes it more local, more variable, and more solvable than the public debate suggests. AI makes the siting choice more visible because the facilities are larger and denser, but the water problem still depends on design. That matters because water draws the most public attention of any of these constraints, and some of the least accurate reporting.

One distinction matters before any number makes sense: water withdrawn is not water consumed. Withdrawal is what a facility takes in; consumption is what it uses up, mostly through evaporation. A facility can withdraw a large volume and return most of it, or consume nearly all of what it takes, depending entirely on the cooling design.

Nationally, the figure is modest. As of 2021, all US data centers combined accounted for roughly 449 million gallons of water a day, about three to four tenths of one percent of total US water withdrawals, far below agriculture or power generation. The headline framing of data centers draining the country's water is not supported by the national figures.

The real issue is local. Roughly 40 percent of US data centers sit in areas of high or extreme water stress, so even a small national share can land hard on a particular community. Stated that way, it is a siting problem, real and solvable, rather than an indictment of the technology.

The cooling design is what sets the consumption. On-site consumption ranges from nearly nothing, for an air-cooled or closed-loop facility, to as much as 70 to 80 percent of what was withdrawn, for an open evaporative one. A single large evaporative facility can use something like five million gallons a day, comparable to a town of fifty thousand people. The same facility, built closed-loop, can use almost none. The high number and the low number describe the same building with two different cooling choices.

This is the constraint the industry is most actively engineering away. Closed-loop systems fill once and recirculate rather than evaporate. The same shift to liquid and direct-to-chip cooling described earlier can cut water needs dramatically, by up to 95 percent in some designs, and immersion cooling can eliminate evaporative water use altogether. Reclaimed wastewater is increasingly used in place of drinking water. Of the five walls in this piece, water is the one where the engineering response is furthest along, which is exactly why it deserves to be described accurately rather than dramatically.

The Reserve Ran Out

The five walls are not five problems. They are one fact seen from five angles. None of this is new physics: the power was always physical, the heat was always real, capacity always took years to build. What changed is that the cloud era ran on a deep reserve of capacity built ahead of demand, and as long as that reserve lasted, the limits underneath stayed out of view. You turned a dial and the reserve answered. AI has drawn that reserve down, and at a scale the old infrastructure was never built to carry, so the limits are back in view all at once.

That is why the data centers rising for AI are a different class of build, and why the people who built the last generation look at the numbers and recognize that the rules they worked under have moved. The accelerator depends on packaging and memory, the rack depends on liquid cooling, and the building depends on power. At this scale, the power itself needs a buffer. The site depends on grid capacity, water choices, and time. The next time a capacity question lands on your desk, ask where it will physically live and how long the power takes, before you ask what it costs. The cloud used to be an abstraction. It has an address now.

The five walls are physical. Operating inside them is not. Once the facility and the computer are one coupled system, running it means reading it as one: GPU power draw, cooling response, battery state, and grid posture, measured together and fast enough to act while the numbers are still true. That is not a facilities dashboard on a five-minute refresh. It is a live, correlated, high-frequency record of a machine that now runs from the silicon to the substation. Capturing that record, and querying it before it goes stale, is its own problem.

Get Started

Operational telemetry only helps if you can query it while it is still true, at the rate it arrives. That is the workload Tiger Data is built for: time-series and event data on Postgres, fresh and correct, without splitting into a second system. Start a free Tiger Cloud trial. Running on-premises, at the edge, or air-gapped? TimescaleDB Enterprise is built for those deployments and is taking design partners.