Hello 👋 I'm designing a StarRocks schema for user-facing query-serving workloads, with PostgreSQL as the source of truth and CDC ingestion from PG. Most of our hot-path tables in PostgreSQL use

UUID

primary keys and foreign keys. From the StarRocks docs,

UUID

is not a supported native type, so the obvious mapping is

VARCHAR(36)

. My concern is that using `UUID`s as

VARCHAR

for PK/FK columns in StarRocks could become a bottleneck for: 1. Primary Key table index size / memory usage 2. Join performance on hot paths 3. Distribution / sort-key efficiency I have not copied the real production data yet, so I can't benchmark this immediately. A full load will take days, and if the schema choice is wrong, iterating will be expensive. So I'm trying to decide between two approaches: 1. Keep PostgreSQL `UUID`s as

VARCHAR(36)

in StarRocks 2. Go back to PostgreSQL and introduce numeric shadow keys for all hot PK/FK paths, then replicate those into StarRocks instead. The second option is possible for us, but expensive: • it requires a large backfill • schema changes across many tables • application changes to keep

UUID

<- numeric shadow keys consistent over time Questions: 1. In practice, how bad is

VARCHAR(36)

for

UUID

PK/FK columns in StarRocks Primary Key tables at scale? 2. Have people successfully run hot joins on

UUID-as-string

columns without major issues? Any rules of thumb, production experience, or benchmark guidance would be very helpful. 🙇 @Rocky can you help?

Designing a high-scale StarRocks schema with

UUID

primary and foreign keys is a common challenge. While

VARCHAR(36)

is the most straightforward mapping, your concerns about memory and join performance are well-founded for user-facing, low-latency workloads. Here is the breakdown of the impact and recommendations based on StarRocks' architecture and production experience. 1. In Practice: How "Bad" is

VARCHAR(36)

? Primary Key Index & Memory Usage In a Primary Key (PK) table, the primary key index is the most resource-intensive part. StarRocks keeps a mapping of every PK to its row location. * Memory Footprint: The overhead for an in-memory PK index is roughly

(KeySize + 9 bytes) _ row_count _ replication_factor * 1.5

. * VARCHAR(36): ~45 bytes/row. For 1 billion rows (3 replicas), this is ~200 GB of RAM. * BIGINT (Shadow Key): ~17 bytes/row. For 1 billion rows, this is ~75 GB of RAM. * Persistence: Fortunately, StarRocks now defaults to Persistent Indexes (

enable_persistent_index

=

true

). This moves most of the index to disk (ideally SSD), keeping only a small cache in memory. This makes

VARCHAR(36)

"manageable" even at scale, but you still pay a penalty in index I/O and cache efficiency. Join Performance Joining on 36-byte strings is significantly slower than joining on 8-byte integers. * StarRocks uses Hash Joins. Building and probing a hash table with strings involves calculating a string hash and then doing byte-for-byte comparisons for collisions. * With integers (

BIGINT

), these operations are often vectorized using SIMD instructions. * The "Randomness" Penalty: If your UUIDs are random (v4), you lose "locality." During ingestion, a random UUID PK will cause random I/O for the persistent index. If you use UUID v7 (time-ordered), the performance of string keys improves significantly because new keys are appended to the "hot" end of the index. ────────── 2. The Comparison: Approach 1 vs. Approach 2 Metric | Approach 1:

VARCHAR(36)

| Approach 2: Numeric Shadow Keys Ease of Implementation | Very Easy (Direct mapping) | Hard (Backfill + App Changes) Index Memory | High (~2.5x more than BIGINT) | Low Join Speed | Moderate (String hashing/cmp) | Fast (Vectorized integer join) Ingest Performance | Slower (Random I/O if v4) | Fastest Storage Size | ~36 bytes per row | 8 bytes per row ────────── 3. Recommendations & Rules of Thumb A. The "Third Way": Use

LARGEINT

(128-bit) Instead of adding shadow keys to PostgreSQL, consider ingesting the 128-bit UUID into a StarRocks

LARGEINT

column. * A UUID is exactly 128 bits. StarRocks'

LARGEINT

is a 16-byte signed integer that can store the raw bits of a UUID. * Performance: Joining on

LARGEINT

is significantly faster than

VARCHAR(36)

and consumes ~40% less index memory. * Implementation: During ingestion (e.g., via Flink CDC or Routine Load), you can strip the hyphens and cast the hex string to the numeric equivalent. B. Stick with

VARCHAR(36)

IF: * You use UUID v7 in PostgreSQL (time-ordered). The temporal locality mitigates the index performance hit. * Your data volume is within a range where your BE nodes can comfortably house the persistent index on SSDs. * You are on StarRocks 3.3+, which has highly optimized string processing and global runtime filters that help speed up string joins. C. Go to Shadow Keys (

BIGINT

) IF: * You are doing multi-way joins (3+ tables) on the hot path for every request. * You have billions of rows and are extremely sensitive to memory/SSD costs. * Your UUIDs are random (v4), as the fragmentation in the persistent index will eventually impact ingestion latency. Summary Checklist for Your Choice: 1. Check UUID version: If it's v4 (random), the

VARCHAR

approach will hurt more at scale. If it's v7, it's much safer.

String handling is never as fast as number crunching so the surrogate key is real talk to make your system go faster. Try a small test table of 100 sample rows and get it working both ways. Then try it with 100k and 1M rows and time a join or two each way. Let me know if you want starter/sample code.

Thank you very much! 🙇

Thank you for the suggestions @Tony Wasson I made a POC on converting

UUID

type into

LARGEINT

which is supported by Starrocks. The magic happens in with help of a custom Kafka Connect SMT.