面向指标数据/分析的动态维度存储方案选型咨询

Let’s break down your problem and walk through the best fit for your specific business context—since you’re focused on multi-tenant flexibility, query performance for OLAP/pivot tables, and avoiding costly reworks down the line.

First, let’s recap your core pain point: your current metrics_values_dimensions table creates massive redundancy because every single time-series data point repeats the same dimension associations (e.g., 150 weekly data points for a single dimension combo would store that combo 150 times). That’s inefficient, so let’s evaluate your three options against your actual business constraints (10-15 dimensions/tenant, 10-15 values/dimension, low-frequency weekly/monthly/yearly data, query flexibility > write speed).

Option 1: Add a MetricsSeries Layer #

The Good #

This is the most scalable, efficient solution for your use case. By moving dimension associations to a MetricsSeries layer (one series = one unique combination of metric + dimensions), you eliminate redundancy entirely—your 150 weekly data points would link to a single series, storing the dimension combo once instead of 150 times. This aligns with standard OLAP time-series designs (think InfluxDB tags or Prometheus labels) and plays perfectly with pivot tables, since you can group by series (and thus dimension combos) directly.

Addressing Your ETL Concern #

Your worry about complex recursive workflows is valid, but for your constrained dimension count (10-15 max per tenant), this is manageable. You don’t need recursion—instead, you can:

1. In your ETL pipeline, group incoming data by its full dimension combo first.
2. For each unique combo, check if a corresponding MetricsSeries exists (using a unique constraint on metric_id + linked dimension values).
3. Create the series if it doesn’t exist, then batch-write all time-series values linked to that series ID.

You can even wrap this logic in a simple application service (e.g., SeriesManager) that handles the combo-to-series mapping, so your ETL code only needs to call this service instead of dealing with raw database operations. For your low data volume, even a basic implementation will work smoothly.

Option 2: Predefined Dimension Slots #

The Bad #

This is a non-starter for your use case. While fixed slots make queries easier upfront, they:

• Break your core requirement of custom dimension flexibility—if a tenant needs more than 5/20 dimensions, you’re stuck.
• Create massive amounts of NULL values, which hurt query performance (databases struggle to index NULLs efficiently, and OLAP filters on NULLs add unnecessary overhead).
• Lock you into a rigid schema that will require costly rework if your tenants’ needs evolve.

Skip this one entirely.

Option 3: JSONB/ARRAY Columns #

The Tradeoffs #

This is great for quick prototypes, but falls short for your long-term needs:

• Query Performance: While PostgreSQL’s JSONB supports GIN indexes for key-value lookups, pivot tables and multi-dimensional grouping will require unpacking the JSONB data, which slows down queries significantly—exactly the opposite of what you need for OLAP analysis.
• Dimension Consistency: Your existing Dimension/DimensionValue tables are designed to enforce reusable, consistent dimensions across tenants. JSONB lets users input arbitrary keys (e.g., "Region" vs "region" vs "REGION"), which breaks that consistency and makes cross-tenant analysis impossible.
• Scalability: As tenants grow, JSONB columns become cluttered with inconsistent keys, and filtering across tenants will require messy, inefficient queries.

This is a quick fix, not a long-term solution.

Final Recommendation: Go with Option 1 (MetricsSeries Layer) #

For your specific constraints, this is the clear winner:

1. Eliminates Redundancy: Perfect for your low-frequency time-series data, where dimension combos rarely change.
2. Supports Your Core Needs: Plays seamlessly with pivot tables and OLAP queries, since you can group by series (and thus dimension combos) directly.
3. Scalable: No hard limits on dimensions, and works as your tenant count and data volume grow.
4. Manageable Complexity: The ETL workflow can be simplified with application-level abstraction, and your low data volume means even a basic implementation will perform well.

Quick Optimizations to Start With #

• Add a unique constraint on MetricsSeries for metric_id + the set of linked dimension values (you can use an array of dimension_value_ids or a hash of the combo to enforce uniqueness).
• Use asynchronous writes for series creation and data ingestion, since you’ve already said write performance is secondary.
• Enforce dimension consistency in your application layer: make sure tenants can only select existing DimensionValue entries when creating metric series, instead of allowing arbitrary input.

内容的提问来源于stack exchange，提问作者lowercase00