Getting the size of an index in cockroachDB

General
#1

I have gone through https://www.cockroachlabs.com/blog/index-selection-cockroachdb-2/. Can someone let me know

  1. Is there a way I can get the current size of an index ?
  2. If I create an index by using STORING so are the actual values stored in the index or some pointer to the actual value is stored in the index?
  3. How are indexes stored by cockroach ? Is there any documentation for it that I can refer to?
Index size on disk
#2

Hi tuk!

Thanks for your interest in CockroachDB!

Here are some answers to your questions:

  1. we do not provide a direct measurement of index sizes for now. In general however, an index will be equivalent in size to a table that contains the same indexed columns as primary key, and the storing columns as regular columns. See point 3 below for the reason.

  2. when using STORING, the actual values are stored in the index.

  3. CockroachDB indexes are stored as key/value pairs in RocksDB. The indexed columns form the key, and the primary key value + storing columns form the value.

Does this answer your question?

1 Like
#3

Thanks @knz. Is there any documentation on how indices are actually stored by cockroachDB?

#4

Dear Tuk,

CockroachDB stores indices in RocksDB.
The RocksDB low-level storage format is documented here: https://github.com/facebook/rocksdb/wiki/Rocksdb-BlockBasedTable-Format

Does this answer your question?

#5

If you really want a lot of detail, you might also find this interesting: https://github.com/cockroachdb/cockroach/blob/master/docs/tech-notes/encoding.md

1 Like
#6

Quick follow up question -
Assuming that we have an index other than primary key index, and if we are trying to do a select using this additional index, on one or more columns that are part of primary key, can one skip explicitly"storing" these “select” columns in the index? (since you mentioned that primary key values (columns?) are ALWAYS stored in the index).

ps: I will read the links pointed to - just being lazy at the moment :slight_smile:

#7

Yes, there’s never a need to explicitly store the primary key columns in a secondary index.

1 Like
#8

Yes, that is possible. in that case CockroachDB will automatically look up the additional data from the primary index from every maching row of the secondary index.

#9

Hi, this got me slightly confused - if I am selecting columns that are part of PK using a non-PK index, will CRDB need to consult PK index for every matching row? From your previous answer, I got the impression that actual values of all PK columns will be stored in the index rows (eliminating any further lookup/join from primary index).

#10

I believe @knz misunderstood your question. If you are selecting columns that are part of the PK using a non-PK index, cockroach will not need to consult the PK index to serve that query.

#11

Yes, What Alex says is correct. I misunderstood your question. Apologies.

#12

Thanks for clarifying that!

1 Like