INTERLEAVE IN PARENT

Warning:

INTERLEAVE IN PARENT is disabled by default, and will be permanently removed from CockroachDB in a future release. We do not recommend interleaving tables or indexes in new clusters.

For details, see below.

How interleaved tables work

When tables are interleaved, data written to one table (known as the child) is inserted directly into another (known as the parent) in the key-value store. This is accomplished by matching the child table's Primary Key to the parent's.

Interleave prefix

For interleaved tables to have Primary Keys that can be matched, the child table must use the parent table's entire Primary Key as a prefix of its own Primary Key––these matching columns are referred to as the interleave prefix. It's easiest to think of these columns as representing the same data, which is usually implemented with Foreign Keys.

Tip:
To formally enforce the relationship between each table's interleave prefix columns, we recommend using Foreign Key constraints.

For example, if you want to interleave orders into customers and the Primary Key of customers is id, you need to create a column representing customers.id as the first column in the Primary Key of orders—e.g., with a column called customer. So the data representing customers.id is the interleave prefix, which exists in the orders table as the customer column.

Key-value structure

When you write data into the child table, it is inserted into the key-value store immediately after the parent table's key matching the interleave prefix.

For example, if you interleave orders into customers, the orders data is written directly within the customers table in the key-value store. The following is a crude, illustrative example of what the keys would look like in this structure:

/customers/1
/customers/1/orders/1000
/customers/1/orders/1002
/customers/2
/customers/2/orders/1001
/customers/2/orders/1003
...
/customers/n/
/customers/n/orders/<order belonging to n>

By writing data in this way, related data is more likely to remain on the same key-value range, which can make it much faster to read from and write to. Using the above example, all of customer 1's data is going to be written to the same range, including its representation in both the customers and orders tables.

Interleaved hierarchy

Interleaved tables typically work best when the tables form a hierarchy. For example, you could interleave the table orders (as the child) into the table customers (as the parent, which represents the people who placed the orders). You can extend this example by also interleaving the tables invoices (as a child) and packages (as a child) into orders (as the parent).

The entire set of these relationships is referred to as the interleaved hierarchy, which contains all of the tables related through interleave prefixes.

Syntax

CREATE opt_persistence_temp_table TABLE IF NOT EXISTS table_name ( table_definition ) INTERLEAVE IN PARENT table_name ( name_list ) opt_partition_by_table WITH ( storage_parameter , ) ON COMMIT PRESERVE ROWS opt_locality

Parameters

Parameter Description
CREATE TABLE ... For help with this section of the syntax, CREATE TABLE.
opt_persistence_temp_table Defines the table as a session-scoped temporary table. For more information, see Temporary Tables.

Note that the LOCAL, GLOBAL, and UNLOGGED options are no-ops, allowed by the parser for PostgresSQL compatibility.

Support for temporary tables is experimental.
INTERLEAVE IN PARENT table_name The name of the parent table you want to interleave the new child table into.
name_list A comma-separated list of columns from the child table's Primary Key that represent the parent table's Primary Key (i.e., the interleave prefix).
opt_partition_by An Enterprise-only option that lets you define table partitions at the row level. You can define table partitions by list or by range. See Define Table Partitions for more information.
WITH storage_parameter A comma-separated list of spatial index tuning parameters. Supported parameters include fillfactor, s2_max_level, s2_level_mod, s2_max_cells, geometry_min_x, geometry_max_x, geometry_min_y, and geometry_max_y. The fillfactor parameter is a no-op, allowed for PostgreSQL-compatibility.

For details, see Spatial index tuning parameters. For an example, see Create a spatial index that uses all of the tuning parameters.
ON COMMIT PRESERVE ROWS This clause is a no-op, allowed by the parser for PostgresSQL compatibility. CockroachDB only supports session-scoped temporary tables, and does not support the clauses ON COMMIT DELETE ROWS and ON COMMIT DROP, which are used to define transaction-scoped temporary tables in PostgreSQL.

Deprecation

Interleaving tables and indexes was deprecated in CockroachDB v20.2 for the following reasons:

  • Scans over tables or indexes with interleaved, child objects (i.e., interleaved tables or indexes) are much slower than scans over tables and indexes with no child objects, as the scans must traverse the parent object and all of its child objects.
  • Database schema changes are slower for interleaved objects and their parents than they are for non-interleaved objects and objects with no interleaved children. For example, if you add or remove a column to a parent or child table, CockroachDB must rewrite the entire interleaved hierarchy for that table and its parents/children.
  • Internal benchmarks have shown the performance benefits of interleaving tables and indexes are limited to a small number of use cases.

Interleaving is disabled with the sql.defaults.interleaved_tables.enabled cluster setting set to false by default. Interleaving will be permanently disabled in a future release.

For more details, see the GitHub tracking issue.

After upgrading to v21.2, we recommend that you do the following:

Tip:

Test your schema changes in a non-production environment before implementing them in production.

Convert interleaved tables

To convert an interleaved table to a non-interleaved table, issue an ALTER PRIMARY KEY statement on the table, specifying the existing primary key column(s) for the table, and no INTERLEAVE IN PARENT clause.

When converting interleaved tables with ALTER PRIMARY KEY, note the following:

  • CockroachDB executes ALTER PRIMARY KEY statements as online schema changes. This means that you can convert your interleaved tables to non-interleaved tables without experiencing any downtime.
  • ALTER PRIMARY KEY statements can only convert a child table if that table is not a parent. If your cluster has child tables that are also parents, you must start from the bottom of the interleaving hierarchy and work your way up (i.e., start with child tables that are not parents).

For example, suppose you created an interleaved hierarchy between the customers, orders, and packages tables, using the following CREATE TABLE statements:

icon/buttons/copy
> CREATE TABLE customers (
   id INT PRIMARY KEY,
   name STRING(50)
 );
icon/buttons/copy
> CREATE TABLE orders (
   customer INT,
   id INT,
   total DECIMAL(20, 5),
   PRIMARY KEY (customer, id),
   CONSTRAINT fk_customer FOREIGN KEY (customer) REFERENCES customers
 ) INTERLEAVE IN PARENT customers (customer);
icon/buttons/copy
> CREATE TABLE packages (
   customer INT,
   "order" INT,
   id INT,
   address STRING(50),
   delivered BOOL,
   delivery_date DATE,
   PRIMARY KEY (customer, "order", id),
   CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES orders
 ) INTERLEAVE IN PARENT orders (customer, "order");

The INTERLEAVE IN PARENT clauses will appear in SHOW CREATE statements for the packages and orders tables:

icon/buttons/copy
> SHOW CREATE TABLE orders;
 table_name |                                  create_statement
-------------+-------------------------------------------------------------------------------------
 orders     | CREATE TABLE public.orders (
            |     customer INT8 NOT NULL,
            |     id INT8 NOT NULL,
            |     total DECIMAL(20,5) NULL,
            |     CONSTRAINT "primary" PRIMARY KEY (customer ASC, id ASC),
            |     CONSTRAINT fk_customer FOREIGN KEY (customer) REFERENCES public.customers(id),
            |     FAMILY "primary" (customer, id, total)
            | ) INTERLEAVE IN PARENT public.customers (customer)
(1 row)
icon/buttons/copy
> SHOW CREATE TABLE packages;
 table_name |                                        create_statement
-------------+--------------------------------------------------------------------------------------------------
 packages   | CREATE TABLE public.packages (
            |     customer INT8 NOT NULL,
            |     "order" INT8 NOT NULL,
            |     id INT8 NOT NULL,
            |     address STRING(50) NULL,
            |     delivered BOOL NULL,
            |     delivery_date DATE NULL,
            |     CONSTRAINT "primary" PRIMARY KEY (customer ASC, "order" ASC, id ASC),
            |     CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES public.orders(customer, id),
            |     FAMILY "primary" (customer, "order", id, address, delivered, delivery_date)
            | ) INTERLEAVE IN PARENT public.orders (customer, "order")
(1 row)

To convert these tables to non-interleaved tables, use ALTER PRIMARY KEY statements, starting at the bottom of the hierarchy (i.e., with packages):

icon/buttons/copy
> ALTER TABLE packages ALTER PRIMARY KEY USING COLUMNS (customer, "order", id);
icon/buttons/copy
> ALTER TABLE orders ALTER PRIMARY KEY USING COLUMNS (customer, id);
icon/buttons/copy
> SHOW CREATE TABLE orders;
 table_name |                                  create_statement
-------------+-------------------------------------------------------------------------------------
 orders     | CREATE TABLE public.orders (
            |     customer INT8 NOT NULL,
            |     id INT8 NOT NULL,
            |     total DECIMAL(20,5) NULL,
            |     CONSTRAINT "primary" PRIMARY KEY (customer ASC, id ASC),
            |     CONSTRAINT fk_customer FOREIGN KEY (customer) REFERENCES public.customers(id),
            |     UNIQUE INDEX orders_customer_id_key (customer ASC, id ASC),
            |     FAMILY "primary" (customer, id, total)
            | )
(1 row)
icon/buttons/copy
> SHOW CREATE TABLE packages;
 table_name |                                        create_statement
-------------+--------------------------------------------------------------------------------------------------
 packages   | CREATE TABLE public.packages (
            |     customer INT8 NOT NULL,
            |     "order" INT8 NOT NULL,
            |     id INT8 NOT NULL,
            |     address STRING(50) NULL,
            |     delivered BOOL NULL,
            |     delivery_date DATE NULL,
            |     CONSTRAINT "primary" PRIMARY KEY (customer ASC, "order" ASC, id ASC),
            |     CONSTRAINT fk_order FOREIGN KEY (customer, "order") REFERENCES public.orders(customer, id),
            |     UNIQUE INDEX packages_customer_order_id_key (customer ASC, "order" ASC, id ASC),
            |     FAMILY "primary" (customer, "order", id, address, delivered, delivery_date)
            | )
(1 row)

Replace interleaved indexes

Interleaved secondary indexes cannot be converted to non-interleaved indexes. You must drop the existing index, and then create a new index without an INTERLEAVE IN PARENT clause.

See also

YesYes NoNo