database design - What is Symbol column in QuestDb?

Question

I am trying out QuestDb and it has Symbol column type. As far as I understand it's useful to store small rarely changed string values. At what point it is better to use a separate table for the strings and use an integer key instead? My simplified schema is 2 tables:

Instrument: ISIN, Name

Price: ISIN, PriceValue, Date

Instruments are limited but can grow up to 1m records over time. Should I use Symbol column for ISIN in Price or better of creating Integer Instrument Id and reference it instead?

question from:https://stackoverflow.com/questions/65626576/what-is-symbol-column-in-questdb

Answer 1

Internally Symbol is already stored as separate table. Although table appears to display String values for symbol columns internally column stores 32 bit int. For finances cases ISIN and other tickers should always be symbols. Symbols are optimised for ticker lookups, such as the one below to select entire time series for one day

select isin, ... from tab where isin = 'GB00BH4HKS39' and ts = '2021-01'

Ticker aggregations:

select isin, sum(volume) from tab where ts = '2021-01'

The case for not using symbol type is when you dataset has too many distinct values for the field. I would quantify "too many" as above 100,000 values. At this point performance of code that has to resolve String to Int and vice-versa starts to taper off.