Colocated tables can dramatically increase the number of relations (tables, indexes, etc.) that can be supported per node while keeping the number of tablets per node low.
In workloads that do very little IOPS and have a small data set, the bottleneck shifts from CPU/disk/network to the number of tablets one can host per node. There are practical limitations to the number of tablets that YugabyteDB can handle per node, even though this number could be very high, depending on the workload pattern. Since each table by default requires at least one tablet without colocation, a database with 5000 relations (tables, indexes, etc.) will have at least 5000 tablets, which increases the CPU/disk/network overhead. If most of these relations are small in size, then it's beneficial to use colocated tables.
Colocating various SQL tables puts all of their data into a single tablet, called the colocation tablet. Note that all the data in the colocation tablet is still replicated across 3 nodes (or whatever the replication factor is).
This tutorial uses the yb-ctl local cluster management utility.
1. Create a universe
$ ./bin/yb-ctl create
2. Create a colocated database
Connect to the cluster using
$ ./bin/ysqlsh -h 127.0.0.1
Create database with
colocated = true option.
yugabyte=# CREATE DATABASE northwind WITH colocated = true;
This will create a database
northwind whose tables will be stored on a single tablet.
3. Create tables
northwind database and create tables using standard
CREATE TABLE command.
The tables will be colocated on a single tablet since the database was created with
colocated = true option.
yugabyte=# \c northwind yugabyte=# CREATE TABLE customers ( customer_id bpchar, company_name character varying(40) NOT NULL, contact_name character varying(30), contact_title character varying(30), PRIMARY KEY(customer_id ASC) ); yugabyte=# CREATE TABLE categories ( category_id smallint, category_name character varying(15) NOT NULL, description text, PRIMARY KEY(category_id ASC) ); yugabyte=# CREATE TABLE suppliers ( supplier_id smallint, company_name character varying(40) NOT NULL, contact_name character varying(30), contact_title character varying(30), PRIMARY KEY(supplier_id ASC) ); yugabyte=# CREATE TABLE products ( product_id smallint, product_name character varying(40) NOT NULL, supplier_id smallint, category_id smallint, quantity_per_unit character varying(20), unit_price real, PRIMARY KEY(product_id ASC), FOREIGN KEY (category_id) REFERENCES categories, FOREIGN KEY (supplier_id) REFERENCES suppliers );
If you go to tables view in master UI, you'll see that all tables have the same tablet.
4. Opt out table from colocation
YugabyteDB has the flexibility to opt a table out of colocation. In this case, the table will use its own set of tablets
instead of using the same tablet as the colocated database. This is useful for scaling out tables that you know are likely
to be large. You can do this by using
colocated = false option while creating table.
yugabyte=# CREATE TABLE orders ( order_id smallint NOT NULL PRIMARY KEY, customer_id bpchar, order_date date, ship_address character varying(60), ship_city character varying(15), ship_postal_code character varying(10), FOREIGN KEY (customer_id) REFERENCES customers ) WITH (colocated = false);
If you go to tables view in master UI, you'll see that
orders table has its own set of tablets.
5. Reading and writing data in colocated tables
You can use standard YSQL DML statements to read and write data in colocated tables. YSQL's query planner and executor will handle routing the data to the correct tablet.
For more information, see the architecture for colocated tables.