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

Time-series cardinality refers to the number of distinct values a dimension can take. The number of distinct series a database tracks is determined by the product of distinct counts across all indexed dimensions. For example, 10,000 assets, 100 sensors each, 10 firmware revisions, and 100 sites result in roughly a billion combinations.

This definition splits growth into two axes: linear and multiplicative. Adding values to a dimension you already index leads to a linear increase in series count, while adding a new indexed dimension results in a multiplicative increase. The cost of one more indexed column in Postgres is proportional to the existing number of rows and the width of the new column.

This cost scales with every row you already have, which can be true or false depending on the baseline. Adding new indexed dimensions can cause the planner's row estimates to collapse due to correlated selectivities, leading to a sequential scan instead of an index scan. The root cause of this issue is the same across different engines, with tag-indexed engines scaling memory and startup time with series count, while wide Postgres schemas experience index bloat, longer autovacuum times, and planner statistics drift.

The cost of the new column becomes more apparent as the table grows, and the planner's estimates become less accurate due to correlated predicates.