Hash Sharded Index

[DharmaPriya-V]

Does this adpater support adding hash sharded index with bucket count

Create INDEX ON table_name(column_name) USING HASH WITH (bucket_count = 2);

[BuonOmo]

According to CockroachDB documentation, I'd say yes.

I'd suggest trying and running the query with your setup to see if it's ok, the error is quite verbose and should help you see if you have an issue.

The one I tested on my computed comes directly from the documentation:

ActiveRecord::Base.connection.execute('CREATE TABLE events (
    product_id INT8,
    owner UUID,
    serial_number VARCHAR,
    event_id UUID,
    ts TIMESTAMP,
    data JSONB,
    PRIMARY KEY (product_id, owner, serial_number, ts, event_id),
    INDEX (ts) USING HASH WITH (bucket_count=2)
);')

[DharmaPriya-V]

Yess through raw query we were able to achieve this with active_record connection. But just wanted to understand if there is the direct rails support like add_index

[BuonOmo]

Unfortunately I don't think so as it looks like CRDB does not support the WITH (bucket_count.. in the CREATE INDEX (@rafiss am I right? I saw that through experimenting rather than doc).

So the next syntax doesn't work:

Post.connection.add_index(:posts, :title, using: "HASH WITH (bucket_count=2)")

And actually other ORM syntaxes as well since they all generate the index with CREATE INDEX, even:

  create_table("posts") do |t|
    t.string :title, index: true
    t.text :body
  end

So I don't think we're likely to add such support soon as it would be a deep change in rail's behaviour. Is there any issue that you can't resolve by using the raw query?

[rafiss]

Unfortunately I don't think so as it looks like CRDB does not support the WITH (bucket_count.. in the CREATE INDEX (@rafiss am I right? I saw that through experimenting rather than doc).

This is supported in CRDB. For example:

CREATE INDEX idx_name ON table_name (column_name) USING HASH WITH (bucket_count=8);

[BuonOmo]

Oh okay I saw the difference, I was forgetting the column_name when doing by hand, and the adapter somehow puts the column name after the using statement, which is not supported by CRDB:

CREATE INDEX "index_posts_on_title" ON "posts" USING HASH WITH (bucket_count=2) ("title")

Here's the culprit code (https://github.com/rails/rails/blob/c676398d5ed0afe58ac7547f86547c3e85911af1/activerecord/lib/active_record/connection_adapters/abstract/schema_creation.rb#L113-L114):

          sql << "USING #{index.using}" if supports_index_using? && index.using
          sql << "(#{quoted_columns(index)})"

We'd need to exchange these two lines, but I don't have any idea of the consequences this might have, and it is part of a long method that we'd have to fully override, which may also means deeper complications in the future.

And looking at the CRDB grammar, it would be an issue:

https://www.cockroachlabs.com/docs/stable/sql-grammar#create_index_stmt

The opt_index_access_method is between the table and column name, as per the ruby implementation, but the opt_sharded_index is an exception, written afterwards. @rafiss is this planned on staying as it is?

[rafiss]

Nice find on that discrepancy. In this case, it's unlikely that we would make a change to the syntax, so this will stay as it is.