高写入频率下，按客户端/Aspect去重的数据库设计优化咨询

Great question! Let's break down how to fix the 3NF bottleneck here while hitting all your requirements: write optimization, unique value enforcement, and support for occasional deep dives into client-specific values.

First, let's acknowledge your initial idea: storing compressed value strings with a counter is a solid start for reducing write operations, but we can refine it to avoid downsides like cumbersome value extraction and potential concurrency issues. Here are three actionable, balanced solutions:

1. Redis Cache Layer + Relational DB (Best for Existing Tech Stacks) #

This approach leverages Redis's fast in-memory operations for write-heavy workloads, while using your relational DB as a durable source of truth for long-term storage and deep analysis.

How it works: #

• Write Flow:
  • For each incoming AspectReport, use Redis Sets to handle automatic deduplication:
    • Add the value to redis:client:{ClientId}:aspect:{AspectId} (per-client unique values)
    • Add the value to redis:aspect:{AspectId} (global unique values)
  • Both Set operations are O(1) and automatically ignore duplicates, so no pre-write checks needed.
  • Asynchronously sync these Sets to your DB in batches (use a message queue like Kafka/RabbitMQ to buffer writes). For the DB, keep your ClientAspectValues and ApplicationAspectValues tables, but add unique constraints on (ClientId, AspectId, Value) and (AspectId, Value) to enforce deduplication as a safety net.
• Read Flow:
  • For quick ratio calculations (ClientValues.Count / ApplicationValues.Count), use Redis's SCARD command to get the size of each Set instantly—no DB query needed.
  • For occasional deep dives (viewing all values for a client/aspect), fetch directly from the Redis Set. If the data is cold (not in Redis), fall back to querying the DB.

Benefits: #

• Blazing-fast writes: Redis handles the high throughput, and batch syncs reduce DB load.
• Guaranteed uniqueness: Redis Sets + DB unique constraints prevent duplicates at every layer.
• Efficient queries: Ratio stats are available in milliseconds, and deep reads are supported without complexity.

2. Optimized Relational DB Schema (No Extra Dependencies) #

If you don't want to introduce Redis, you can tweak your initial schema to use database-native features for better write performance and queryability.

Revised Schema (Example for PostgreSQL): #

-- Global aspect values (enforce global uniqueness)
CREATE TABLE ApplicationAspectValues (
  AspectId INT,
  Value VARCHAR(255),
  CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (AspectId, Value)
);

-- Client-specific aspect values (with atomic counter and array storage)
CREATE TABLE ClientAspectValues (
  ClientId INT,
  AspectId INT,
  UniqueValues TEXT[] NOT NULL DEFAULT '{}', -- Use array for easy value management
  ValueCount INT NOT NULL DEFAULT 0,
  PRIMARY KEY (ClientId, AspectId)
);

Write Flow: #

• Use atomic database operations to handle writes in a single query:
  • For ApplicationAspectValues: Use INSERT ... ON CONFLICT DO NOTHING to avoid duplicates without pre-checks.
  • For ClientAspectValues: Use a conditional update to append values and increment the count only if the value doesn't already exist:

    INSERT INTO ClientAspectValues (ClientId, AspectId, UniqueValues, ValueCount)
    VALUES ($1, $2, ARRAY[$3], 1)
    ON CONFLICT (ClientId, AspectId) DO UPDATE
    SET 
      UniqueValues = array_append(ClientAspectValues.UniqueValues, $3),
      ValueCount = ClientAspectValues.ValueCount + 1
    WHERE $3 <> ALL(ClientAspectValues.UniqueValues);
    

  This ensures no race conditions and keeps writes to a single row operation.

Benefits: #

• Single-row writes: No multi-table updates, just one query per report.
• Better queryability: Array types let you extract values directly with database functions (no decompression needed).
• Built-in uniqueness: Unique constraints and conditional updates prevent duplicate entries.

3. Columnar Database (For Extreme Scale) #

If your write throughput grows beyond what a relational DB can handle, consider switching to a columnar database like ClickHouse or BigQuery. These are designed for high-volume writes and fast analytical queries.

How it works: #

• Store all AspectReport entries in a single table with columns ClientId, AspectId, Value, Timestamp.
• Use the database's built-in deduplication features:
  • For real-time deduplication, use a MergeTree table in ClickHouse with a primary key of (ClientId, AspectId, Value) to automatically merge duplicates during background processing.
  • For ratio calculations, run a lightweight analytical query:

    SELECT 
      ClientId,
      AspectId,
      COUNT(DISTINCT Value) / (SELECT COUNT(DISTINCT Value) FROM reports WHERE AspectId = r.AspectId) AS value_ratio
    FROM reports r
    GROUP BY ClientId, AspectId;
    

• For deep dives, simply query DISTINCT Value for a specific ClientId and AspectId.

Benefits: #

• Max write throughput: Columnar databases excel at ingesting millions of rows per second.
• No schema complexity: No need for multiple tables—everything lives in one place.
• Fast analytics: Ratio calculations and deep reads are optimized out of the box.

Final Recommendations #

• If you're working with an existing relational DB stack, go with Option 1 (Redis + DB)—it's the most balanced approach for write speed, durability, and query flexibility.
• If you can't add Redis, Option 2 (Optimized DB Schema) is a strong alternative that uses native database features to avoid 3NF bottlenecks.
• For extreme scale (10k+ writes per second), Option 3 (Columnar DB) is the way to go.

All three solutions meet your core requirements: write-optimized operations, guaranteed unique values, and support for occasional deep reads.

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