Time-Series Cardinality: Why One More Indexed Column Costs More Than a Million More Rows
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...
Copyright of this story solely belongs to hackernoon.com. To see the full text click HERE
