Bird
Raised Fist0

UUID vs Auto-Increment vs Custom ID - Trade-offs at Scale

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🎯
UUID vs Auto-Increment vs Custom ID - Trade-offs at Scale
45 min · senior
senior

You are designing the ID generation strategy for a 50 million daily active user (DAU) startup that requires globally unique identifiers for user-generated content. The system must support high write throughput, ensure IDs are collision-free across distributed services, and meet strict latency requirements (P99 < 200ms). Additionally, IDs must be encrypted at rest and support offline content creation with eventual synchronization. The system should also allow efficient querying and indexing by ID.

Constraints
  • Global uniqueness across distributed services without central bottlenecks
  • P99 latency under 200ms for ID generation and retrieval
  • IDs must be encrypted at rest for compliance
  • Support offline content creation with eventual sync and conflict resolution
  • Efficient indexing and querying by ID in the database
📋
Guided Steps
1/5
Step 1
Choosing the ID Generation Strategy
Which ID generation approach would you choose to ensure global uniqueness and low latency at 50M DAU scale?
💡 Hint
Consider distributed ID generation without a single point of contention and how to avoid collisions.
Key Decision
Use Twitter Snowflake algorithm implemented via a distributed service like Sonyflake or Instagram's ID generator to produce time-ordered, 64-bit unique IDs without central coordination.
Interviewer Expectation
Mid: Names Snowflake or Sonyflake for distributed ID generation. Senior: Explains how Snowflake's time+worker+sequence bits avoid collisions and enable ordering, and why centralized auto-increment fails at scale.
⚠️Common Wrong Answer
Using a centralized auto-increment ID generator leads to a single point of failure and high latency under 50M DAU write load.
Step 2
Handling Encryption of IDs at Rest
How would you ensure IDs are encrypted at rest while maintaining efficient querying and indexing?
💡 Hint
Think about deterministic encryption or tokenization schemes that preserve indexability.
Key Decision
Apply format-preserving encryption (FPE) using AES-FF1 mode on generated IDs before storage, enabling encrypted IDs to remain indexable and searchable in databases like PostgreSQL with B-tree indexes.
Interviewer Expectation
Mid: Mentions encrypting IDs before storage. Senior: Explains why deterministic FPE is needed to preserve indexability and how it avoids full table scans compared to standard encryption.
⚠️Common Wrong Answer
Encrypting IDs with non-deterministic AES-CBC breaks indexing, causing full table scans and high latency.
Step 3
Supporting Offline ID Generation and Sync
How would you design the system to allow clients to generate IDs offline and synchronize them later without collisions?
💡 Hint
Consider embedding client-specific or shard identifiers and conflict resolution strategies.
Key Decision
Use a custom ID format combining client-specific unique prefixes with local counters or timestamps, validated on sync by a conflict resolution service backed by Apache Cassandra for high write throughput and eventual consistency.
Interviewer Expectation
Mid: Suggests client-generated IDs with unique prefixes. Senior: Details how Cassandra's tunable consistency and lightweight transactions help detect and resolve conflicts during sync.
⚠️Common Wrong Answer
Allowing arbitrary client-generated IDs without validation causes collisions and data corruption on sync.
Step 4
Efficient Querying and Indexing by ID
Which database and indexing strategy would you choose to support efficient queries by encrypted IDs at scale?
💡 Hint
Consider databases optimized for high write throughput and secondary indexes on encrypted fields.
Key Decision
Use PostgreSQL with B-tree indexes on deterministically encrypted ID columns, combined with partitioning by ID ranges to distribute load and maintain P99 < 200ms query latency.
Interviewer Expectation
Mid: Names PostgreSQL with indexes. Senior: Explains partitioning strategy and how deterministic encryption enables index usage without performance degradation.
⚠️Common Wrong Answer
Using NoSQL without secondary indexes forces full scans on encrypted IDs, causing high latency.
Step 5
API Design for ID Lifecycle
Design 3 core API endpoints to create IDs, retrieve content by ID, and synchronize offline-generated IDs.
💡 Hint
Think about request/response payloads, idempotency, and conflict feedback.
Key Decision
Define POST /ids/create for server-generated IDs, GET /content/{id} for retrieval, and POST /sync/ids for offline sync with conflict resolution feedback, using REST over HTTPS with JWT authentication.
Interviewer Expectation
Mid: Provides basic CRUD endpoints. Senior: Details idempotency keys, conflict resolution responses, and security considerations in API design.
⚠️Common Wrong Answer
Designing only synchronous create and retrieve endpoints without offline sync support leads to data loss and poor UX.
🔌
API Design Challenge
Sketch 3 core API endpoints for the ID generation and usage system that handle creation, retrieval, and synchronization of IDs.
Hints
  • How does the client request a new ID or create content offline?
  • How does the client synchronize offline-generated IDs with the server?
  • How does the system acknowledge and confirm ID uniqueness and validity?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Distributed ID generation without central bottleneck (Snowflake/Sonyflake)
  • Deterministic encryption for IDs to preserve indexability
  • Offline ID generation with conflict detection and resolution
  • Efficient querying with partitioned PostgreSQL and indexes
  • API design supporting create, retrieve, and sync with conflict feedback
Good to Have - 30%
  • Use of Cassandra lightweight transactions for conflict resolution
  • Security via JWT and HTTPS for API endpoints
  • Partitioning strategies to avoid hotspots
  • Idempotency keys in API design
Bonus - 10%
  • Detailed latency and throughput estimations for each component
  • Discussion of failure modes and recovery strategies
Anti-patterns to Avoid
Centralized auto-increment IDs causing single point of failure and high latency under 50M DAU write load, leading to system bottlenecks and downtime.
Non-deterministic encryption breaking database indexes and causing full table scans, resulting in unacceptable query latency and poor scalability.
Allowing arbitrary client-generated IDs without validation leading to collisions and data corruption during synchronization, compromising data integrity.
Using NoSQL databases without secondary indexes forces full scans on encrypted IDs, causing high latency and poor user experience.
Omitting offline sync support causes data loss and poor user experience when clients operate without connectivity.
✅
Model Answer
▼ Reveal

The system uses a distributed ID generation approach based on the Twitter Snowflake algorithm implemented via Sonyflake to ensure globally unique, time-ordered 64-bit IDs without central bottlenecks. IDs are encrypted at rest using format-preserving encryption (AES-FF1) to maintain indexability in PostgreSQL databases with partitioned tables and B-tree indexes, ensuring P99 query latency under 200ms. Offline ID generation is supported by a custom ID format embedding client-specific prefixes and local counters, with conflict resolution handled by a service backed by Apache Cassandra using lightweight transactions and tunable consistency. The API design includes RESTful endpoints for ID creation, content retrieval, and offline synchronization, secured with JWT authentication and HTTPS, supporting idempotency and conflict feedback.

Key Decisions
  • Use Sonyflake for distributed, collision-free, time-ordered ID generation without central coordination
  • Apply deterministic format-preserving encryption (AES-FF1) to IDs before storage to preserve indexability
  • Design custom offline ID format with client-specific prefixes and local counters to avoid collisions
  • Leverage Apache Cassandra's lightweight transactions and tunable consistency for conflict detection and resolution during sync
  • Implement PostgreSQL with partitioned tables and B-tree indexes on encrypted IDs to maintain low latency queries
  • Design RESTful APIs with idempotency keys and conflict resolution feedback, secured by JWT and HTTPS
  • Twitter Snowflake or Sonyflake for distributed, collision-free, time-ordered ID generation without central bottlenecks
  • Format-preserving encryption (AES-FF1) to encrypt IDs at rest while preserving indexability in PostgreSQL
  • Custom ID format with client-specific prefixes for offline ID generation, validated and reconciled on sync using Apache Cassandra
  • PostgreSQL with partitioned tables and B-tree indexes on encrypted ID columns to maintain P99 < 200ms query latency
  • RESTful API endpoints with idempotency and conflict resolution for create, retrieve, and offline sync operations
  • Conflict resolution service leveraging Cassandra's lightweight transactions and tunable consistency for eventual consistency
  • Use of JWT authentication and HTTPS to secure API endpoints and protect ID confidentiality in transit
  • Partitioning and sharding strategies to distribute load and avoid hotspots in ID generation and querying

Practice

(1/5)
1. A large-scale messaging platform needs to isolate failures to prevent cascading outages during regional network partitions. Which architectural component best limits the blast radius by decoupling service dependencies?
easy
A. Use synchronous REST calls between microservices with retries
B. Use direct TCP socket connections between services for low latency
C. Implement asynchronous message queues with backpressure and dead-letter queues
D. Rely on a single monolithic database to coordinate all service state

Solution

  1. Step 1: Identify failure isolation mechanism

    Asynchronous message queues decouple services, buffering requests and preventing direct cascading failures during outages.

  2. Step 2: Evaluate alternatives

    Synchronous REST calls (A) cause blocking and cascading failures; monolithic DB (C) is a single point of failure; direct TCP (D) lacks buffering and backpressure.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    Async queues buffer and isolate failures, limiting blast radius [check]
Hint: Async queues buffer failures, blocking calls cascade [check]
Common Mistakes:
2. In a code repository system, which component is best suited to efficiently store and retrieve file diffs to minimize storage space while allowing fast access for merges?
easy
A. Use a relational database with BLOB columns to store entire file versions
B. Use a distributed cache like Redis to store diffs temporarily for quick access
C. Use a content-addressable storage system that stores objects by hash and reuses identical content
D. Use a simple file system directory structure with full copies of each file version

Solution

  1. Step 1: Identify storage needs for diffs

    Storing diffs efficiently requires deduplication and immutability; content-addressable storage (CAS) stores objects by their hash, enabling reuse of identical content and saving space.

  2. Step 2: Evaluate other options

    Relational DB with BLOBs stores full versions, wasting space; Redis is volatile and not durable for persistent storage; file system copies waste space and slow for merges.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    CAS stores objects by hash -> deduplication and fast retrieval [check]
Hint: Content-addressable storage deduplicates by hash [check]
Common Mistakes:
3. In the request flow for generating a user's feed, at step 5 the Feed Aggregator receives multiple candidate posts from different microservices. What does the Feed Aggregator do at this step before sending the feed to the user?
easy
A. It merges, deduplicates, and ranks posts based on relevance scores before returning
B. It immediately returns the raw posts without filtering or ranking
C. It stores all posts in a persistent database before responding to the user
D. It sends posts to the notification service to alert the user before aggregation

Solution

  1. Step 1: Understand Feed Aggregator's role at Step 5

    The Feed Aggregator must combine posts from multiple sources, remove duplicates, and rank them to personalize the feed.

  2. Step 2: Eliminate incorrect options

    It immediately returns the raw posts without filtering or ranking skips ranking, leading to poor UX. It stores all posts in a persistent database before responding to the user delays response unnecessarily. It sends posts to the notification service to alert the user before aggregation misplaces notification logic before feed aggregation.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Feed Aggregator merges and ranks posts -> personalized feed [check]
Hint: Aggregate, dedupe, rank before sending feed [OK]
Common Mistakes:
4. In a system where a cache is checked before querying the database, at Step 3 the Cache Layer receives a read request. What happens if the cache misses at this step?
easy
A. The Cache Layer forwards the request to the Database and waits for the response
B. The Cache Layer returns a null response immediately to the client
C. The Cache Layer retries the cache read multiple times before failing
D. The Cache Layer writes a placeholder value to the cache and returns it

Solution

  1. Step 1: Trace Cache Layer behavior on miss

    On a cache miss, the Cache Layer must fetch data from the Database to serve the request.

  2. Step 2: Understand why other options fail

    Returning null or placeholders breaks correctness; retrying cache reads wastes time without new data.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Cache miss triggers DB fetch before responding [check]
Hint: Cache miss -> fetch from DB before reply [check]
Common Mistakes:
5. If the event queue that propagates write updates to read replicas fails, what is the immediate failure mode observed in the system?
medium
A. Writes to the primary database fail due to backpressure from the queue
B. Read replicas stop receiving updates and serve stale data indefinitely until manual intervention
C. The system automatically switches to synchronous replication to maintain consistency
D. Clients receive errors on read requests due to read replica unavailability

Solution

  1. Step 1: Identify role of event queue

    The event queue asynchronously propagates updates to read replicas.

  2. Step 2: Trace failure impact

    If the queue fails, read replicas stop receiving updates and serve stale data indefinitely.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Queue failure causes stale reads, no automatic failover [check]
Hint: Event queue failure -> stale reads until fixed manually [check]
Common Mistakes:
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard