For larger tables and indexes that are hash-sharded, we can specify the number of initial tablet splits desired as part of the CREATE TABLE DDL statement of the table.
This can be very beneficial to distribute the data of the table across multiple nodes right from the start.
Example:
yugabyte=# CREATE TABLE big_table(c1 INT PRIMARY KEY, c2 TEXT) SPLIT INTO 36 TABLETS;
CREATE TABLE
yugabyte=# SELECT num_tablets FROM yb_table_properties('public.big_table'::regclass);
num_tablets
-------------
36
(1 row)
But what about Materialized Views? Can they be pre-split?
yugabyte=# CREATE MATERIALIZED VIEW big_table_mv AS SELECT * FROM big_table WHERE c2 IS NOT NULL SPLIT INTO 12 TABLETS;
ERROR: syntax error at or near "SPLIT"
LINE 1: ...v AS SELECT * FROM big_table WHERE c2 IS NOT NULL SPLIT INTO...
^
Unfortunately the SPLIT INTO syntax is not supported for the CREATE MATERIALIZED VIEW command.
However, there is a simple trick to let accomplish our goal. We just have to temporarily change the T-Server gFlag ysql_num_shards_per_tserver to the number of tablets we want, create the materialized view, then return the gFlag back to the previous value.
First, which node are we connected to?
yugabyte=# SELECT host, CASE WHEN host::inet = inet_client_addr() THEN '*' ELSE '' END connected FROM yb_servers();
host | connected
------------+-----------
127.0.0.3 |
127.0.0.2 |
127.0.0.1 | *
(3 rows)
Now let’s record the current value of the gFlag ysql_num_shards_per_tserver:
yugabyte=# \! curl -s http://127.0.0.1:9000/varz?raw | grep ysql_num_shards_per_tserver
--ysql_num_shards_per_tserver=-1
Now let’s change the gFlag, create the Materialized View, then switch the gFlag back to the previous value.
yugabyte=# \! yb-ts-cli --server_address=127.0.0.1:9100 set_flag ysql_num_shards_per_tserver 12
yugabyte=# CREATE MATERIALIZED VIEW big_table_mv AS SELECT * FROM big_table WHERE c2 IS NOT NULL;
SELECT 0
yugabyte=# \! yb-ts-cli --server_address=127.0.0.1:9100 -- set_flag ysql_num_shards_per_tserver -1
Finally, let’s verify that we have 36 tablets, 9 per node, for the Materialized View:
yugabyte=# SELECT num_tablets FROM yb_table_properties('public.big_table_mv'::regclass);
num_tablets
-------------
36
(1 row)
Have Fun!
