Skip to main content


This page compiles crucial insights into table operations, serving as a comprehensive guide for you navigating the intricacies of working with tables in Databend. It strings together essential table-related commands to provide a cohesive understanding of key considerations in table management.

Table Creation Essentials

It is a good idea to to familiarize yourself with the following topics before proceeding to create a table.

1. Understanding Table Types

Databend supports two types of tables based on their Time Travel support:

  • General Tables (Default): These tables inherently support Time Travel, allowing you to trace and retrieve historical data. This feature is valuable for data analysis and auditing.

  • Transient Tables: In contrast, transient tables do not support Time Travel. They are designed for scenarios where historical data tracking is not necessary. To create a transient table, you must explicitly specify the keyword TRANSIENT in the CREATE TABLE command. For more information, see CREATE TRANSIENT TABLE.

2. Selecting Table Storage

Databend defaults to storing table data in the location configured in the databend-query.toml configuration file. Additionally, it provides the flexibility to store table data in a different bucket, deviating from the default setting. For more information, see CREATE TABLE ... EXTERNAL_LOCATION.

3. Defining Table Structure

The primary method to define columns in a table is through the CREATE TABLE command, where you list your columns one by one. Please note that Computed Columns are supported as an Enterprise Edition feature in Databend. For more information, see Computed Columns.

Databend also offers convenient methods for creating tables by copying column structures and even data from existing tables:

4. Setting Cluster Key for Big Tables

Cluster Key is designed to enhance query performance by physically organizing data in proximity. Databend recommends configuring cluster keys, especially for large tables encountering sluggish query performance. For the syntax to set a cluster key during table creation, see SET CLUSTER KEY.

Routine Table Maintenance

Once your table is created, you gain the foundation for organizing and managing your data effectively. With this structure in place, you can seamlessly execute various commands to enhance, modify, or extract information from your table. Whether it's adjusting column properties, fine-tuning configurations, or querying data, Databend provides a versatile set of tools to meet your evolving needs.

Table Deletion & Recovery Strategies

Databend provides a variety of commands for deleting a table or vacuuming the table data. The table below compares these commands, which may initially seem complex, outlining any associated recovery options for each operation.

CommandEnterprise Edition?DescriptionRecovery
TRUNCATE TABLENoRemoves all data from a table while preserving the table's schema.FLASHBACK TABLE
VACUUM TABLEYesPermanently removes historical data files of a table.Not applicable.
VACUUM DROP TABLEYesPermanently removes data files of dropped tables.Not applicable.

Advanced Table Optimization Techniques

Tables in Databend might need optimizations over time to ensure efficient performance and storage utilization. In this case, the following commands can help you out:


Table Optimization is an advanced-level operation. Databend recommends carefully reading the links below and understanding the optimization process before proceeding to avoid potential data loss.

  • ANALYZE TABLE: Calculates table statistics.
  • OPTIMIZE TABLE: Involves compacting or purging historical data to save storage space and enhance query performance.
Did this page help you?