Colocated tables

• macOS
• Linux

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 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 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.

What's next?

For more information, see the architecture for colocated tables.