Colocated tables
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. Since each table by default requires at least one tablet per node, a YugabyteDB cluster with 5000 relations (tables, indexes) will result in 5000 tablets per node.There are practical limitations to the number of tablets that YugabyteDB can handle per node since each tablet adds some CPU, disk and network overhead. If most or all of the tables in YugabyteDB cluster are small tables, then having separate tablets for each table unnecessarily adds pressure on CPU, network and disk.
To help accommodate such relational tables and workloads, you can colocate SQL tables. Colocating tables puts all of their data into a single tablet, called the colocation tablet. This 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. Note that all the data in the colocation tablet is still replicated across three 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 ysqlsh.
$ ./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
Connect to 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 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.
What's next?
For more information, see the architecture for colocated tables.