Does CREATE TABLE syntax need HASH keyword explicitly?

General
1

We are using YSQL on yugabyte DB

Below table syntax create table with hash sharding:

CREATE TABLE customers (
    customer_id bpchar NOT NULL,
    company_name character varying(40) NOT NULL,
    PRIMARY KEY (customer_id HASH)
);

Does below table syntax creates table with range sharding or hash sharding?

CREATE TABLE customers (
    customer_id bpchar NOT NULL,
    company_name character varying(40) NOT NULL,
    PRIMARY KEY (customer_id)
);
2

@sham_yuga –
By default, the first column of a PRIMARY KEY or INDEX is taken to be the HASH column unless it is denoted as ASC or DESC which would indicate a RANGE column. The default RANGE column order is ASC if unspecified after the first column.

Alan

1 Like
3

@Alan_Caldera

If the primary key is as shown in below table:

CREATE TABLE IF NOT EXISTS public.table1
(
    customer_id uuid NOT NULL,
    item_id uuid NOT NULL,
    kind character varying(100) NOT NULL,
    details character varying(100) NOT NULL,
    CONSTRAINT table1_pkey PRIMARY KEY (customer_id, kind, item_id)
);

then table is created with
first column(customer_id) of PRIMARY KEY indicate a HASH column
and
second & third column(i.e., kind, item_id) of PRIMARY KEY indicate a RANGE column

  1. Is my understanding(above) correct?

  2. Which sharding is applied on below two indexes? hash or range

CREATE UNIQUE INDEX  IF NOT EXISTS  unique_customer_id ON table1(customer_id);

CREATE UNIQUE INDEX  IF NOT EXISTS unique_item ON table1(customer_id, kind) WHERE kind='BAD' OR kind='SLOW';
4

@sham_yuga -

Yes, in the example above, the PRIMARY KEY will be sharded on customer_id, and it will be sorted in kind, item_id order ascending.

In the first index (unique_customer_id), that index will be sharded on the customer_id field. In the second index (unique_item), it will be sharded on customer_id, with the kind field being sorted in ascending order within each customer_id. Your sample query will be slow/bad because you have to scan all customer_id shards for the specified kind field.

Alan

1 Like
5

@Alan_Caldera

So,

  1. for table1, yb_hash_code() with SELECT query can only be applied on customer_id column:

select * from table1 WHERE yb_hash_code(customer_id)>=0 AND yb_hash_code(customer_id) < 4095;

  1. For index unique_customer_id, yb_hash_code() with SELECT query can only be applied on customer_id column:

select * from unique_customer_id WHERE yb_hash_code(customer_id)>=0 AND yb_hash_code(customer_id) < 4095;

  1. For index unique_item , yb_hash_code() with SELECT query can only be applied on item_id column:

select * from unique_item WHERE yb_hash_code(item_id)>=0 AND yb_hash_code(item_id) < 4095;

Correct me

6

yb_hash_code() can only be applied on column(s) that are used for partitioning. You’re correct.

1 Like
7

@dorian_yugabyte

In YCQL, we can find hash ranges through select start_range, end_range from system.partitions;

What is the query to find hash range in postgres(yugabyte DB 2.8.3) using YSQL?

8

It’s not possible yet in YSQL [YSQL] Make queries partition aware & expose tablet info in hash sharding · Issue #2480 · yugabyte/yugabyte-db · GitHub