---
title: "Time-Series Cardinality: Why One More Indexed Column Costs More Than a Million More Rows"
published: 2026-08-28T12:29:32.000-04:00
updated: 2026-08-28T14:23:13.000-04:00
excerpt: "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 planne"
tags: Time Series Data, PostgreSQL
authors: Damaso Sanoja
---

> **TimescaleDB is now Tiger Data.**

_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](https://www.tigerdata.com/learn/designing-your-database-schema-wide-vs-narrow-postgres-tables). 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](https://www.tigerdata.com/blog/ignition-and-timescaledb-perfect-pairing#third-convert-the-table-to-a-hypertable). 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](https://www.tigerdata.com/blog/what-is-high-cardinality#high-cardinality-example-industrial-iot), 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](https://www.postgresql.org/docs/current/btree.html) 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](https://www.tigerdata.com/blog/write-amplification-in-postgres-the-3-4x-tax-on-every-insert#the-anatomy-of-a-single-insert).

| 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](https://www.postgresql.org/docs/current/multivariate-statistics-examples.html) 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](https://www.postgresql.org/docs/current/sql-createstatistics.html) 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](https://www.tigerdata.com/blog/how-different-databases-handle-high-cardinality-data#influxdb-and-the-tsi) 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](https://docs.influxdata.com/influxdb/v2/write-data/best-practices/resolve-high-cardinality/). 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](https://www.tigerdata.com/blog/why-adding-more-indexes-eventually-makes-things-worse), [planner statistics drift](https://www.postgresql.org/docs/current/routine-vacuuming.html) until an index scan silently becomes a sequential scan, and [per-chunk planning overhead](https://www.tigerdata.com/docs/learn/hypertables/sizing-hypertable-chunks#too-many-chunks) 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.](https://assets.tigerdata.com/blog/2026/08/time-series-cardinality-1-1.png)

__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.](https://assets.tigerdata.com/blog/2026/08/data-src-image-20299adf-ae9d-4d18-b906-573f6ba55b5a.png)

__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](https://www.tigerdata.com/blog/best-practices-for-time-series-metadata-tables), and key the narrow reading table on that identifier alone.

```SQL
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](https://www.tigerdata.com/blog/unified-namespace-historian-schema#identity-belongs-to-the-namespace-not-the-table). 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](https://www.influxdata.com/blog/embracing-observability-influxdb-3-0/). 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.](https://assets.tigerdata.com/blog/2026/08/time-series-cardinality-3-1.png)

__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](https://arxiv.org/pdf/2204.09795) 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_id`s 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](https://www.postgresql.org/docs/current/sql-createstatistics.html) will fix it with no migration at all and is the right first move; it just leaves index width, write amplification, and [vacuum cost](https://www.postgresql.org/docs/current/planner-stats.html) 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](https://www.tigerdata.com/blog/how-relational-complexity-crushes-real-time-dashboards#the-join-explosion-problem) on cutover day. With it, existing reads keep working, and the rewrite becomes incremental. The view is [a join on the read path](https://www.tigerdata.com/blog/materialized-views-the-timescale-way#views-hide-complexity) 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](https://www.tigerdata.com/blog/unified-namespace-historian-schema#what-the-unified-namespace-changes). 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](https://www.tigerdata.com/blog/self-hosted-timescaledb-vs-tiger-cloud-decision#what-does-tiger-cloud-manage-and-what-do-i-still-own) while you do it, that is what [Tiger Cloud](https://www.tigerdata.com/cloud) is for.