First things first, it is unlikely that you need indexes. It is rare for them to be better than parallel scans, and when they are, usually you want to index an additional symbol column so that you can still run queries with parallel scans when you choose.
It might be counterintuitive if you are used to other databases, but for QuestDB, without index is usually better than with index.
I want to understand the INDEX CAPACITY setting better.
INDEX CAPACITY should ideally be set to a value close to this:
the number of rows per unique symbol within a single partition.
Let’s say you are storing sensor data. You have 100 unique sensors, which send data every second. Then you use hourly partitions.
This means you have 100 unique symbols (so SYMBOL CAPACITY should be >= 100) and 3600 rows per symbol. So that would make an index capacity of 3600.
However, as you identify, this takes storage space. You probably won’t notice much difference with a smaller capacity, like 128 or 256, and will save storage this way.
Also, is the CAPACITY automatically expanded if needed?
No, it will just be slower. The same is true for symbols.