Hello all,
I just wanted to find out if indexes affect the storage space of the data ?
I’ve got about 124 double precision fields, that I’ve left as default ( they have an index ) but seeing as I do not need to specifically query those fields ( no join or where clause usage ) could I leave out the index and save any storage space for the table ?
Also If any of the fields are used in window or aggregation functions, would they need an index ?
Many thanks in advance
David
Hi David, indexes take up space, yes.
They can be disabled for specific columns by passing INDEX OFF next to the data type for the relevant columns in the table definition.
While you will not be able to reference these columns in the WHERE clause, most aggregations and even JOINs will still work as CrateDB can leverage the column store for that.
Great, thanks for clarifying.
I’ll turn off the majority of the indexes then as space is disappearing quicker than anticipated…
Thanks!
Using DEFLATE instead of LZ4 for the table codec could also help.
The column store cannot be disabled yet on numeric values directly (open issue: Allow to disable column store on numeric data types (bigint, int, real, double precision) · Issue #11652 · crate/crate · GitHub)
However you can put numeric values in an OBJECT(IGNORED) to indirectly also disable it.
Is there any guideline on how much space there can be saved?
Like on average a index on an bigint field will add X% of storage space used?
There is no generic guideline on this as compression is involved, but for what is worth, on a totally unscientific test I just did with 1 million random bigint numbers, disabling indexing saved me 8.6 MB