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

Cardinality in time-series systems arises from the number and distinct values of indexed dimensions, but the costs differ by database architecture: tag-indexed engines often face memory and series-index limits, while PostgreSQL primarily incurs wider indexes, write amplification, vacuum overhead, and planner errors caused by correlated columns. A benchmark using 100 million correlated fleet readings in PostgreSQL with TimescaleDB found that adding one indexed firmware column increased index size by about 865 MB and reduced insert throughput, costing far more at that scale than adding one million rows, while increasing distinct tag counts alone had little effect on index size or estimate accuracy within the tested range. The analysis argues that these effects are schema decisions rather than unavoidable engine ceilings, recommending normalization of stable descriptors into a metadata table and retaining a narrow readings table indexed mainly by tag identifier and timestamp. This design can substantially reduce storage and indexing costs, improve complex filtered rollups, and confine planner misestimates to a much smaller metadata table, although it requires migration work, metadata joins, handling of changing tag attributes and mixed value types, and may be unnecessary when extended PostgreSQL statistics alone resolve the primary problem.

No tracked trend matches for this post yet.

Use this post, company, and trend context to find content marketing opportunities, perform competitive analysis, or address product feature gaps via the Plushcap MCP server or the Plushcap API.