How to distribute a table using values ​​from multiple columns?

Question

According to Citus documentation , it's easy to distribute a table using a single column:

SELECT master_create_distributed_table('github_events', 'created_at', 'append');

Is there a way to spread the table using multiple columns? For example, something like:

SELECT master_create_distributed_table('github_events', 'user_id,site_id', 'append');

Answer 1

Distribution by more than one column is not supported on Citus. However, you can create a composite type and split your data into this composite type .

- The contents of the link are given below if the link does not work -

Steps for Hashing Composite Types

• Create a type on the main and all work nodes:

  CREATE TYPE new_composite_type as (project_key text, date text);
  

• CREATE FUNCTION equal_test_composite_type_function(new_composite_type, new_composite_type) RETURNS boolean
  AS 'select $1.project_key = $2.project_key AND $1.date = $2.date;'
  LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT;
  
  -- ... use that function to create a custom equality operator...
  CREATE OPERATOR = (
      LEFTARG = new_composite_type,
      RIGHTARG = new_composite_type,
      PROCEDURE = equal_test_composite_type_function,
      HASHES
  );
  

• -.

  .. , . -, C SQL.

  CREATE FUNCTION new_composite_type_hash(new_composite_type) RETURNS int
  AS 'SELECT hashtext( ($1.project_key || $1.date)::text);'   
  LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT;
  

• BTREE HASH:

  CREATE OPERATOR CLASS new_op_fam_btree_class
  DEFAULT FOR TYPE new_composite_type USING BTREE AS
  OPERATOR 3 = (new_composite_type, new_composite_type);
  
  CREATE OPERATOR CLASS new_op_fam_hash_class
  DEFAULT FOR TYPE new_composite_type USING HASH AS
  OPERATOR 1 = (new_composite_type, new_composite_type),
  FUNCTION 1 new_composite_type_hash(new_composite_type);
  

• .

  CREATE TABLE composite_type_partitioned_table
  (
      id integer,
      composite_column new_composite_type
  );
  
  SELECT master_create_distributed_table('composite_type_partitioned_table','composite_column', 'hash');
  
  SELECT master_create_worker_shards('composite_type_partitioned_table', 4, 1);
  

• INSERT SELECT. , , .

  INSERT INTO composite_type_partitioned_table VALUES  (1, '("key1","20160101")'::new_composite_type);
  INSERT INTO composite_type_partitioned_table VALUES  (2, '("key1","20160102")'::new_composite_type);
  INSERT INTO composite_type_partitioned_table VALUES  (3, '("key2","20160101")'::new_composite_type);
  INSERT INTO composite_type_partitioned_table VALUES  (4, '("key2","20160102")'::new_composite_type);
  
  SELECT * FROM composite_type_partitioned_table WHERE composite_column =  '("key1", "20160101")'::new_composite_type;
  
  UPDATE composite_type_partitioned_table SET id = 6 WHERE composite_column =  '("key2", "20160101")'::new_composite_type;
  
  SELECT * FROM composite_type_partitioned_table WHERE composite_column =  '("key2", "20160101")'::new_composite_type;
  

:

, :

• , copy_to_distributed_table . COPY (SELECT ()::composite_type_field, .... ); , .

• .