Materialized views in YSQL are relations that persist the results of a query.
They can be created using the CREATE MATERIALIZED VIEW command, and their contents can be updated using the REFRESH MATERIALIZED VIEW command.
Example:
yugabyte=# CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR);
CREATE TABLE
yugabyte=# INSERT INTO test VALUES (1, 'A'), (2, 'B'), (3, 'C');
INSERT 0 3
yugabyte=# CREATE MATERIALIZED VIEW test_mv AS SELECT * FROM test;
SELECT 3
yugabyte=# SELECT * FROM test_mv ORDER BY c1;
c1 | c2
----+----
1 | A
2 | B
3 | C
(3 rows)
A materialized view in YugabyteDB is a table in itself.
Since there is no way to specify a primary key on a materialized view, YugabyteDB will use the internal
ybrowid column as the PRIMARY KEY and the table will be sharded on ybrowid HASH. We can see this with the following command:
[root@localhost ~]# curl -s http://my_host_ip:7000/table?id=`ysqlsh -Atc "SELECT get_table_id('public', 'test_mv');"` -dump | grep Column -A 2 | sed -e 's/<[^>]*>//g'
ColumnIDType
ybrowid0binary NOT NULL PARTITION KEY
c11int32 NULLABLE NOT A PARTITION KEY
That may or may not be desirable for your particular query workload.
Good news is we can shard a materialized view any way we’d like with indexes!
yugabyte=# CREATE UNIQUE INDEX test_mv_c1_uk ON test_mv(c1);
CREATE INDEX
yugabyte=# \q
[root@localhost ~]# curl -s http://my_host_ip:7000/table?id=`ysqlsh -Atc "SELECT get_table_id('public', 'test_mv_c1_uk');"` -dump | grep Column -A 2 | sed -e 's/<[^>]*>//g'
ColumnIDType
c10int32 NOT NULL PARTITION KEY
ybuniqueidxkeysuffix1binary NOT NULL NOT A PARTITION KEY
Have Fun!
