Manage tables and indexes

Add and remove tables and indexes in universes with xCluster Replication

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

When making DDL changes to databases in xCluster Replication (such as creating, altering, or dropping tables or partitions), you must do the following:

  • Make the change at the SQL level on both the source and target.
  • Update the xCluster Replication configuration in YugabyteDB Anywhere.

The order in which you do this varies depending on the operation.

Order of operations

Perform DDL operations in the order as shown in the following table. The order varies depending on the xCluster configuration.

DDL Step 1 Step 2 Step 3
CREATE TABLE Execute on Source Execute on Target Add table to replication
DROP TABLE Execute on Target Execute on Source No changes needed
CREATE TABLE foo
PARTITION OF bar		 Execute on Source Execute on Target Add table to replication
CREATE TABLE
Colocated		 Execute on Target Execute on Source using same colocation ID No changes needed
CREATE INDEX Execute on Source
wait for backfill		 Execute on Target
wait for backfill		 No changes needed
DROP INDEX Execute on Target Execute on Source No changes needed
ALTER TABLE or INDEX Execute on Target Execute on Source No changes needed
ALTER TABLE
ADD CONSTRAINT UNIQUE		 Execute on Source
wait for backfill		 Execute on Target
wait for backfill		 No changes needed
ALTER TABLE
DROP CONSTRAINT
(unique constraints only)		 Execute on Target Execute on Source No changes needed
DDL Step 1 Step 2 Step 3
CREATE TABLE Execute on Source Execute on Target Add table to replication
DROP TABLE Execute on Target Execute on Source No changes needed
CREATE TABLE foo
PARTITION OF bar		 Execute on Source Execute on Target Add table to replication
CREATE TABLE
Colocated		 Execute on Target Execute on Source using same colocation ID No changes needed
CREATE INDEX Execute on Source
wait for backfill		 Execute on Target
wait for backfill		 Add index table to replication
DROP INDEX Execute on Target Execute on Source No changes needed
ALTER TABLE or INDEX Execute on Target Execute on Source No changes needed
ALTER TABLE
ADD CONSTRAINT UNIQUE		 Execute on Source
wait for backfill		 Execute on Target
wait for backfill		 Add the index table corresponding to the constraint to replication
ALTER TABLE
DROP CONSTRAINT
(unique constraints only)		 Execute on Target Execute on Source No changes needed
DDL Step 1 Step 2 Step 3
CREATE TABLE Execute on Source Execute on Target Add table to replication
DROP TABLE Remove table from replication Execute on Target Execute on Source
CREATE INDEX Execute on Source Execute on Target Add table to replication
DROP INDEX Remove index table from replication Execute on Target Execute on Source
ALTER TABLE or INDEX Execute on Target Execute on Source No changes needed

In addition, keep in mind the following:

  • If you are using Colocated tables, you CREATE TABLE on target, then CREATE TABLE on source, making sure that you force the Colocation ID to be identical to that on target.
  • If you try to make a DDL change on source and it fails, you must also make the same attempt on target and get the same failure.
  • TRUNCATE TABLE is not supported. To truncate a table, pause replication, truncate the table on both source and target, and resume replication.

Use the following guidance when managing tables and indexes in universes with replication configured.

Tables

Note: If you are performing application upgrades involving both adding and dropping tables, perform the upgrade in two parts: first add tables, then drop tables.

Add a table to replication

To avoid a full copy, add the table to replication when it is newly created (that is, empty) on both source and target.

Before adding a table to replication in YugabyteDB Anywhere, refer to Order of operations for your setup.

Add tables to replication as follows:

  1. In YugabyteDB Anywhere, navigate to your source, select xCluster Replication, and select the replication configuration.

  2. Click Actions and choose Select Databases and Tables.

  3. Select the tables and click Validate Selection.

    For YSQL, select the database(s) with the tables you want in replication. Colocated tables require additional conditions. For more information, see YSQL tables.

  4. If data needs to be copied, click Next: Confirm Full Copy.

  5. Click Apply Changes.

This operation also automatically adds any associated index tables of this table to the replication configuration.

Remove a table from replication

Before dropping a table in replication in YugabyteDB Anywhere, refer to Order of operations for your setup.

When dropping a table, you can drop the tables and YugabyteDB Anywhere automatically updates the xCluster configuration. You can also manually remove the tables from the configuration in YugabyteDB Anywhere, in which case you would still need to drop the tables.

Indexes

Add an index to replication

To add an index, refer to Order of operations for your setup.

For non-transactional YSQL xCluster configurations, adding an index table to a main table that already has data will trigger a full copy of the entire database. To avoid the full copy, you can add the index table manually (see Adding indexes), and then reconcile the configuration.

For YCQL xCluster configurations, if adding an index table triggers a full copy, the main table and all associated index tables are copied.

Remove an index from replication

To remove an index, refer to Order of operations for your setup.

Reconcile configuration

For some operations, to ensure changes made outside of YugabyteDB Anywhere are reflected in YugabyteDB Anywhere, you need to reconcile the configuration as follows:

  1. In YugabyteDB Anywhere, navigate to your source, select xCluster Replication, and select the replication configuration.
  2. Click Actions > Advanced and choose Reconcile Config with Database.