/timestream-influxdb

Schema and Data Modeling

When to Activate

User asks about tag vs field decisions, cardinality management, table/measurement design, retention policies, or InfluxDB 3 best practices for data modeling.

Workflow

1. Identify the Engine

Schema design differs significantly between V2 and V3. Confirm the engine before advising.

2. Core Concepts

Line protocol (both engines):

measurement,tag1=val1,tag2=val2 field1=1.0,field2="text" timestamp

Tags = indexed, low-cardinality metadata (device_id, region, sensor_type) Fields = values not indexed by default (temperature, cpu_usage, response_time) Timestamp = nanosecond precision, always present

3. Tag vs Field Decision

Ask: "Will I filter or GROUP BY this column frequently?"

• Yes → Tag (indexed, fast lookups)
• No → Field (not indexed by default, stores the actual measurements)

Cardinality rule: The product of all unique tag value combinations = series cardinality.

• V2: Keep below 1M series per bucket. Performance degrades sharply above this.
• V3: No hard limit. High cardinality is handled natively, but tag design still affects query performance and storage efficiency.

4. InfluxDB 3 Schema Best Practices

Table and column limits:

• maxTables default: 4,000 per database. Recommended: keep under ~500 for optimal query performance.
• maxColumnsPerTable default: 200. Each unique tag key or field key counts as a column.
• Both are configurable via parameter groups. Exceeding these limits will reject writes.

Table design:

• One table per logical measurement type (e.g., cpu, memory, http_requests)
• Avoid mega-tables with hundreds of fields — split by domain
• Use meaningful table names (they map to SQL table names)

Partition templates:

• Default: partition by day. Good for most workloads.
• High-volume: partition by hour if writing >1M points/day per table
• Configure via: --partition-template tag:region,tag:host,time:%Y-%m-%d

Deduplication and uniqueness:

• InfluxDB 3 deduplicates on: all tags + timestamp (the "primary key")
• Two points with identical tags and timestamp → last write wins
• To preserve both: add a distinguishing tag or use different timestamps

Field indexing (V3):

• Fields are NOT indexed by default

• For fields used in WHERE clauses, create a field index:

  CREATE INDEX idx_status ON http_requests (status);

Retention:

• V2: Set retention policy per bucket
• V3: Set retention period per database

5. Common Anti-Patterns

Canonical tag examples for HTTP / API logs

For typical HTTP request log schemas, use these as default tags (low-to-moderate cardinality, commonly filtered/grouped):

• method (~10 values: GET, POST, etc.)
• status_code (~20 values: 200, 404, 500, etc.)
• endpoint (the request path — typically <500 unique paths in a well-normalized API; commonly filtered/grouped in error queries)

Note on endpoint cardinality: if the application logs raw paths with embedded IDs (e.g., /users/123 instead of /users/:id), normalize the path before ingestion. The default classification is tag.

Canonical field examples

• response_time_ms, bytes_sent (numeric measurements)
• user_agent, request_id (unbounded cardinality — never tags)

6. Example Schemas

IoT sensor data:

sensors,device_id=d001,location=factory-a,type=temperature value=23.5 1714000000000000000
sensors,device_id=d001,location=factory-a,type=humidity value=45.2 1714000000000000000

Application metrics:

http_requests,method=GET,endpoint=/api/users,status=200 response_time=45.2,bytes=1024 1714000000000000000

Infrastructure monitoring:

cpu,host=web-01,region=us-east-1 usage_user=23.5,usage_system=5.2,usage_idle=71.3 1714000000000000000