🔢Cardinality

NEW

Also known as: Data Cardinality

The number of distinct values in a column or rows in a set, used by optimizers and modelers to estimate uniqueness and query selectivity.

DESCRIPTION

Cardinality characterizes uniqueness and variety in data. In databases, high cardinality columns have many distinct values, while low cardinality have few. Query optimizers use column statistics (e.g., n_distinct, histograms) to estimate result sizes and choose efficient plans. Modeling decisions, indexing, and partitioning strategies often depend on cardinality and data skew. Understanding cardinality helps avoid performance pitfalls, poor join strategies, and resource waste. In metrics and observability, high-cardinality tags can explode storage and costs.

ALTERNATIVE DEFINITIONS

PostgreSQL

The planner collects statistics, including the number of distinct values in a column (n_distinct), to estimate cardinalities for query planning.

Explains cardinality estimation via n_distinct and histograms.

Source

Oracle

Cardinality is an estimate of the number of rows returned by a query and of distinct values in columns, used by the optimizer.

Connects cardinality directly to cost-based optimization.

Source

IBM

Cardinality is the number of rows in a table or the number of distinct values in a column.

Covers table-level and column-level cardinality in statistics.

Source

RELATED TERMS

Selectivity Distinct Count Histogram Statistics Query Optimizer Indexing Primary Key Foreign Key Data Skew Join Selectivity Probability Distribution

COMPETING TERMS

High Cardinality Low Cardinality

RELATIONS

• Narrower: High Cardinality

KNOWLEDGE AREAS

Data Storage & Operations Data Modeling Data Engineering Data Warehousing & BI

TARGET AUDIENCE

Data Engineer Data Modeler Data Architect Software Developer BI Specialist

EXTERNAL SOURCES

• Wikipedia - Cardinality
• PostgreSQL - Cardinality statistics for planning
• Oracle - Optimizer statistics and cardinality
• IBM - Db2 cardinality definition

TOP QUESTIONS

Why does Cardinality matter for query performance?

Optimizers rely on cardinality estimates to choose join orders, indexes, and execution strategies; inaccurate estimates cause slow queries.

Category: Requirements

How can you improve Cardinality estimates?

Gather up-to-date statistics, use histograms, analyze data skew, and consider extended statistics on correlated columns.

Category: Best practices

What is high Cardinality versus low Cardinality?

High cardinality means many distinct values (e.g., UUIDs), low cardinality means few (e.g., status codes).

Category: Example

What are common mistakes with Cardinality?

Ignoring statistics maintenance, misinterpreting skew, and relying on defaults without analyzing data distribution are typical errors.

Category: Mistakes

How does Cardinality affect indexing?

High-cardinality columns often benefit from B-tree indexes; low-cardinality may favor bitmap indexes in some systems.

Category: Opinion

What is the risk of high Cardinality in observability systems?

High-cardinality metrics tags can explode storage costs and reduce system responsiveness.

Category: Risk

How is Cardinality estimated in PostgreSQL?

Using n_distinct statistics and histograms collected by ANALYZE to guide the planner.

Category: Example

Does Cardinality relate to normalization?

Yes, normalization and key design influence distinct values and join selectivity across the schema.

Category: Opinion

Can Cardinality change over time?

Yes, data growth, new codes, or changes in keys alter distribution and require refreshed statistics.

Category: Relevancy