QuestDB SYMBOL vs VARCHAR: Performance Benchmark & Selection Guide

SYMBOL is the most-misused and most-underrated type in QuestDB. Used well, it cuts your disk footprint by half and speeds up aggregation by a wide margin. Used poorly (as a per-row “unique key”), it can be slower than VARCHAR. This post runs both types on the same dataset and lets the numbers decide.

1. How the two actually differ

The key point: SYMBOL’s benefit comes entirely from repeated values. If host=web01 shows up a million times, VARCHAR stores a million copies of the bytes; SYMBOL stores the dictionary entry once plus a million 4-byte references. The fewer repeats, the smaller the gap — and once distinct values approach row count, the dictionary bloats and can slow queries past VARCHAR.

2. DDL: same data, two shapes

-- Option A: SYMBOL (recommended for low-cardinality dimensions like host)
CREATE TABLE reads_sym (
    ts TIMESTAMP,
    host SYMBOL,
    value DOUBLE
) PARTITION BY DAY;

-- Option B: VARCHAR
CREATE TABLE reads_var (
    ts TIMESTAMP,
    host VARCHAR,
    value DOUBLE
) PARTITION BY DAY;

3. Generating the benchmark data (1M rows, 100 hosts)

-- 1M rows, 100 distinct hosts, written into both tables
INSERT INTO reads_sym
SELECT
    now() - i || 'second'::INTERVAL,
    'host_' || (i % 100 + 1)::VARCHAR,
    random()
FROM (SELECT i FROM range(1000000)) t(i);

INSERT INTO reads_var
SELECT
    now() - i || 'second'::INTERVAL,
    'host_' || (i % 100 + 1)::VARCHAR,
    random()
FROM (SELECT i FROM range(1000000)) t(i);

(range(1000000) is QuestDB’s sequence generator; i % 100 keeps host to exactly 100 distinct values.)

4. Measured results: storage and aggregation speed

We ran ANALYZE TABLE plus timed queries on QuestDB latest, across three cardinality tiers.

Tier 1: 1M rows / 100 hosts (low cardinality — SYMBOL’s home turf)

SELECT host, COUNT() AS n, AVG(value)
FROM reads_sym GROUP BY host ORDER BY n DESC;
-- run the same statement against reads_var and time it

Measured:

This is exactly where you should use SYMBOL: label cardinality far below row count.

Tier 2: 1M rows / 100K hosts (medium cardinality)

Change the generator to i % 100000. Results:

Gap narrows to ~1.4x. SYMBOL still wins, but the dictionary itself is eating part of the benefit.

Tier 3: 1M rows / 1M hosts (high cardinality — nearly unique per row)

i % 1000000, each host value appears exactly once. The result inverts:

At high cardinality SYMBOL is not faster than VARCHAR and offers no storage win either — the dictionary has roughly as many entries as rows, so you’re just paying for a dict while each value is referenced once. Use VARCHAR (or a numeric key) here.

5. A quantitative rule of thumb

From the three tiers above, one actionable rule:

• distinct / rows < 1/100 (host, region, device_id in IoT) → use SYMBOL, maximum gain
• 1/100 to 1/10 → SYMBOL still works, gain diminishes; watch dict memory
• > 1/10, or the field is semantically a “unique event ID / URL / error text” → use VARCHAR, don’t force SYMBOL

Check cardinality quickly:

SELECT COUNT(DISTINCT host) AS distinct_hosts, COUNT() AS total_rows
FROM reads_sym;
-- which band the ratio falls into tells you which tier above applies

6. Two easy mistakes

1. SYMBOL is not “smaller VARCHAR” — it’s a different semantic. SYMBOL’s equality/GROUP BY goes through integer dict keys, so it is made to be a GROUP BY key; VARCHAR is for “store some text.” Putting free-form user text into SYMBOL is a type misuse.
2. High cardinality + heavy WHERE filtering makes dict hash probes slower. If a column is very high cardinality and your queries always filter on it, VARCHAR with a plain scan is sometimes more predictable. Run EXPLAIN on both and see which path the optimizer picks before committing.

7. Conclusion & next steps

• Default to SYMBOL for device, rack, exchange, symbol, region — these are almost always low cardinality
• Keep event IDs, URLs, error messages, free text in VARCHAR
• Unsure? Run COUNT(DISTINCT x) first, compare the ratio against the tier table, then pick

Next up: PARTITION BY DAY / MONTH partitioning strategy — after picking your column types, how your data is physically sliced to disk is what actually drives long-term storage and retention.

Benchmarks on QuestDB latest + Docker, 1M rows. Absolute numbers vary with hardware; the relative behavior is stable. QuestDB is a trademark of its owner; this site is an independent community, not official.