Table Cloning: Create Instant Snapshots Without Data Duplication

What is Table Cloning?

Table Cloning is a database operation that makes a copy of an X100 table without the performance penalty of copying the underlying data. If you arrived here looking for the SQL syntax to clone a table in Actian Vector, it works like this:

CREATE TABLE newtable CLONE existingtable
[, newtable2 CLONE existingtable2, ...]
            [ WITH <option, option, ...> ];

The WITH options are briefly listed here. We’ll explain them in more detail later on.

NODATA

GRANTS

REFERENCES=     
     NONE
   | RESTRICTED
   | EXTENDED

The new table – the “clone” – has the same contents the existing table did at the point of cloning. The main thing to remember is that the clone you’ve created is just a table. No more, no less. It looks exactly like a copy. The new table may subsequently be inserted into, updated, deleted from, and even dropped, without affecting the original table, and vice versa.

In developing this feature, it became common to field questions like “Can you create a view on a clone?” or “Can you update a clone?” and “Can you grant privileges on a clone?” The answer, in all cases, is yes. It’s a table. If it helps, after you clone a table, you can simply forget that the table was created with the CLONE syntax. That’s what Vector does.

What Isn’t Table Cloning?

It’s just as important to recognize what Table Cloning is not. You can only clone an X100 table, all its contents or none of it, within the same database. You can’t clone only part of a table, or clone a table between two databases.

What’s it For?

With Table Cloning, you can make inexpensive copies of an existing X100 table. This can be useful to create and persist daily snapshots of a table that changes gradually over time, for example. These snapshots can be queried like any other table.

Users can also make experimental copies of sets of tables and try out changes on them, before applying those changes to the original tables. This makes it faster for users to experiment with tables safely.

How Table Cloning Works

In X100’s storage model, when a block of table data is written to storage, that block is never modified, except to be deleted when no longer required. If the table’s contents are modified, a new block is written with the new data, and the table’s list of storage blocks is updated to include the new block and exclude the old one.

X100 catalog and storage for a one-column table MYTABLE, with two storage blocks.

There’s nothing to stop X100 creating a table that references another table’s storage blocks, as long as we know which storage blocks are still referenced by at least one table. So that’s what we do to clone a table. This allows X100 to create what looks like a copy of the table, without having to copy the underlying data.

In the image below, mytableclone references the same storage blocks as mytable does.

X100 catalog and storage after MYTABLECLONE is created as a clone of MYTABLE.

Note that every table column, including the column in the new table, “owns” a storage file, which is the destination file for any new storage blocks for that column. So if new rows are added to mytableclone in the diagram above, the new block will be added to its own storage file:

X100 catalog and storage after another storage block is added to MYTABLECLONE.

X100 tables can also have in-memory updates, which are applied on top of the storage blocks when the table is scanned. These in-memory updates are not cloned, but copied. This means a table which has recently had a large number of updates might not clone instantly.

My First Clone: A Simple Example

Create a table (note that on Actian Ingres, WITH STRUCTURE=X100 is needed to ensure you get an X100 table):

CREATE TABLE mytable (c1 INT, c2 VARCHAR(10)) WITH STRUCTURE=X100;

Insert some rows into it:

INSERT INTO mytable VALUES (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five');

Create a clone of this table called myclone:

CREATE TABLE myclone CLONE mytable;

The tables now have the same contents:

SELECT * FROM mytable;

SELECT * FROM myclone;

Note that there is no further relationship between the table and its clone. The two tables can be modified independently, as if you’d created the new table with CREATE TABLE … AS SELECT …

UPDATE mytable SET c2 = 'trois' WHERE c1 = 3;
INSERT INTO mytable VALUES (6, 'six');
DELETE FROM myclone WHERE c1 = 1;

SELECT * FROM mytable;

SELECT * FROM myclone;

You can even drop the original table, and the clone is unaffected:

DROP TABLE mytable;

SELECT * FROM myclone;

Security and Permissions

You can clone any table you have the privilege to SELECT from, even if you don’t own it.

When you create a table, whether by cloning or otherwise, you own it. That means you have all privileges on it, including the privilege to drop it.

By default, the privileges other people have on your newly-created clone are the same as if you created a table the normal way. If you want all the privileges other users were GRANTed on the existing table to be granted to the clone, use WITH GRANTS.

Metadata-Only Clone

The option WITH NODATA will create an empty copy of the existing table(s), but not the contents. If you do this, you’re not doing anything you couldn’t do with existing SQL, of course, but it may be easier to use the CLONE syntax to make a metadata copy of a group of tables with complicated referential relationships between them.

The WITH NODATA option is also useful on Actian Ingres 12.0. The clone functionality only works with X100 tables, but Actian Ingres 12.0 allows you to create metadata-only clones of non-X100 Ingres tables, such as heap tables.

Cloning Multiple Tables at Once

If you have a set of tables connected by foreign key relationships, you can clone them to create a set of tables connected by the same relationships, as long as you clone them all in the same statement.

For example, suppose we have the SUPPLIER, PART and PART_SUPP, defined like this:

CREATE TABLE supplier (
supplier_id INT PRIMARY KEY,
supplier_name VARCHAR(40),
supplier_address VARCHAR(200)
);

CREATE TABLE part (
part_id INT PRIMARY KEY,
part_name VARCHAR(40)
);

CREATE TABLE part_supp (
supplier_id INT REFERENCES supplier(supplier_id),
part_id INT REFERENCES part(part_id),
cost DECIMAL(6, 2)
);

If we want to clone these three tables at once, we can supply multiple pairs of tables to the clone statement:

CREATE TABLE
supplier_clone CLONE supplier,
part_clone CLONE part,
part_supp_clone CLONE part_supp;

We now have clones of the three tables. PART_SUPP_CLONE references the new tables SUPPLIER_CLONE and PART_CLONE – it does not reference the old tables PART and SUPPLIER.

Without Table Cloning, we’d have to create the new tables ourselves with the same definitions as the existing tables, then copy the data into the new tables, which would be further slowed by the necessary referential integrity checks. With Table Cloning, the database management system doesn’t have to perform an expensive referential integrity check on the new tables because their contents are the same as the existing tables, which have the same constraints.

WITH REFERENCES=NONE

Don’t want your clones to have references to each other? Then use WITH REFERENCES=NONE:

CREATE TABLE
supplier_clone CLONE supplier,
part_clone CLONE part,
part_supp_clone CLONE part_supp
WITH REFERENCES=NONE;

WITH REFERENCES=EXTENDED

Normally, the CLONE statement will only create references between the newly-created clones.

For example, if you only cloned PART and PART_SUPP:

CREATE TABLE
part_clone CLONE part,
part_supp_clone CLONE part_supp;

PART_SUPP_CLONE would have a foreign key reference to PART_CLONE, but not to SUPPLIER.

But what if you want all the clones you create in a statement to retain their foreign keys, even if that means referencing the original tables? You can do that if you want, using WITH REFERENCES=EXTENDED:

CREATE TABLE
part_clone CLONE part,
part_supp_clone CLONE part_supp
WITH REFERENCES=EXTENDED;

After the above SQL, PART_SUPP_CLONE would reference PART_CLONE and SUPPLIER.

Table Cloning Use Case and Real-World Benefits

The ability to clone tables opens up new use cases. For example, a large eCommerce company can use table cloning to replicate its production order database. This allows easier reporting and analytics without impacting the performance of the live system. Benefits include:

• Reduced reporting latency. Previously, reports were generated overnight using batch ETL processes. Table cloning can create reports in near real-time, enabling faster decision-making. It can also be used to create a low-cost daily or weekly snapshot of a table which receives gradual changes.
• Improved analyst productivity. Analysts no longer have to make a full copy of a table in order to try out modifications. They can clone the table and work on the clone instead, without having to wait for a large table copy or modifying the original.
• Cost savings. A clone takes up no additional storage initially, because it only refers to the original table’s storage blocks. New storage blocks are written only as needed when the table is modified. Table cloning would therefore reduce storage costs compared to maintaining a separate data warehouse for reporting.

This hypothetical example illustrates the potential benefits of table cloning in a real-world scenario. By implementing table cloning effectively, you can achieve significant improvements in development speed, performance, cost savings, and operational efficiency.

Create Snapshot Copies of X100 Tables

Table Cloning allows the inexpensive creation of snapshot copies of existing X100 tables. These new tables are tables in their own right, which may be modified independently of the originals.

Actian Vector 7.0, available this fall, will offer Table Cloning. You’ll be able to easily create snapshots of table data at any moment, while having the ability to revert to previous states without duplicating storage. With this Table Cloning capability, you’ll be able to quickly test scenarios, restore data to a prior state, and reduce storage costs. Find out more.

About Actian Corporation

Actian makes data easy. We deliver cloud, hybrid, and on-premises data solutions that simplify how people connect, manage, and analyze data. We transform business by enabling customers to make confident, data-driven decisions that accelerate their organization’s growth. Our data platform integrates seamlessly, performs reliably, and delivers at industry-leading speeds.