> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.firebolt.io/feedback

```json
{
  "path": "/overview/indexes/table-and-column-compression",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

> Learn about custom compression for managed tables

# Table and Column Compression

## Overview

Firebolt allows users to specify a custom compression algorithm at both the table level and the column level when creating a table. Compression helps you reduce storage costs and improve query performance by decreasing disk usage and I/O.

## Supported compression algorithms

Firebolt supports the following compression algorithms:

<Note>
  Disabling compression with `NONE` is not supported. Firebolt always compresses data using one of the supported algorithms.
</Note>

### LZ4

Fast lossless compression algorithm optimized for speed over compression ratio. This is the default compression codec in Firebolt.

**Performance characteristics:**

* Compression speed: Very fast
* Decompression speed: Very fast
* Typical compression ratio: Good
* CPU usage: Very low

**Best for:**

* High-throughput data ingestion
* Frequently accessed data requiring fast queries
* Real-time analytics workloads
* When CPU resources are limited

**Data type compatibility:** All types

### ZSTD (Zstandard)

Modern compression algorithm providing excellent balance between compression ratio and speed with configurable compression levels.

**Parameters:**

* `COMPRESSION_LEVEL`: Compression level (1-22, default: 1)
  * Levels 1-3: Fast compression, good for online workloads
  * Levels 4-10: Balanced performance
  * Levels 11-22: High compression, slower writes

**Performance characteristics by level:**

| Level | Compression Speed | Compression Ratio | Use Case               |
| ----- | ----------------- | ----------------- | ---------------------- |
| 1     | Fast              | Good              | Real-time data         |
| 3     | Fast              | Better            | General workloads      |
| 7     | Moderate          | Better            | Balanced storage/speed |
| 15    | Slow              | Excellent         | Archival data          |

<Note>
  ZSTD compression levels higher than 3 typically reach the point of diminishing returns for most workloads.
</Note>

**Best for:**

* General analytics workloads (levels 1-3)
* Archival data storage (levels 10+)
* Data with good compressibility (JSON, text, logs)

**Data type compatibility:** All types

## Specify compression settings

You can specify compression settings for a table, columns, or a combination of both when creating tables:

* [Table-level compression](#table-level-compression) sets a default compression method for all columns.
* [Column-level compression](#column-level-compression) overrides the table-level default for individual columns.

### Table-level compression

You specify table-level compression using the `WITH` clause in the `CREATE TABLE` statement. This compression applies to all columns unless explicitly overridden at the column level.

#### Syntax:

Use the following syntax to set table-level and/or column-level compression.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE table_name (
    column1 data_type COMPRESSION algorithm COMPRESSION_LEVEL level,
    column2 data_type COMPRESSION algorithm,
    column3 data_type
)
WITH (COMPRESSION = algorithm, COMPRESSION_LEVEL = level);
```

#### Compression level

* The optional `COMPRESSION_LEVEL` parameter lets you fine-tune the balance between compression ratio and CPU usage.
* Higher compression levels provide greater compression but increase CPU overhead.
* Firebolt applies algorithm-specific default levels if no level is specified.

#### Default behavior

* If you don't specify column-level compression, Firebolt applies the table-level setting.
* If no table-level setting is provided, Firebolt defaults to `lz4`.

#### Example

The following code creates a table with ZTSD compression:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE sales (a INT) WITH (COMPRESSION=zstd, COMPRESSION_LEVEL=1);
```

### Column-level compression

Column-level compression allows you to override table-level compression settings for specific columns.
This approach is useful for columns that have different compression requirements, such as large text fields or numerical columns.

#### Syntax:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE table_name (
    column1 data_type COMPRESSION algorithm COMPRESSION_LEVEL level,
    column2 data_type COMPRESSION algorithm,
    column3 data_type
);
```

##### Compression level

* The `compression_level` parameter fine-tunes compression efficiency versus CPU usage.
* Higher compresson levels reduce storage but consume more CPU resources during compression and decompression.
* If not specified, Firebolt applies a default compression level appropriate for the selected algorithm.

##### Default behavior

* If you do not specify compression explicitly, Firebolt applies the default `lz4` compression algorithm.
* When you define table-level compression, all columns inherit this setting unless overridden with column-level compression.

## Step-by-step example

The following example shows how to use table-level and column-level compression in Firebolt.

**Step 1: Create and populate table with default compression**
Create a base table without custom compression and insert 10000 identical rows into it.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE table_default_compression (a int, b int, c string, d string);
-- insert 10000 rows
INSERT INTO table_default_compression SELECT 1, 2, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 'In dapibus metus quis velit mattis dignissim.' FROM GENERATE_SERIES(1,10000) ;
```

**Step 2: Apply table-level compression**

Create a table that applies ZSTD compression at level 1 to all columns via table-level setting.

<Note>
  This example uses ZSTD with compression level 1. You can use a higher compression level to achieve better compression at the cost of increased CPU usage.
  However, ZSTD compression levels higher than 3 typically reach the point of diminishing returns.
</Note>

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE table_zstd1(a int, b int, c string, d string) WITH (COMPRESSION = zstd, COMPRESSION_LEVEL = 1);
```

**Step 3: Apply column-level compression**
Create tables that apply ZSTD compression at level 1 to different sets of columns:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE table_c_column_zstd1(a int, b int, c string COMPRESSION zstd COMPRESSION_LEVEL 1, d string);
CREATE TABLE table_c_d_column_zstd1(a int, b int, c string COMPRESSION zstd COMPRESSION_LEVEL 1, d string COMPRESSION zstd COMPRESSION_LEVEL 1);
```

**Step 4: Insert the data**

Insert data into these tables from the `table_default_compression` table:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
INSERT INTO table_zstd1 SELECT * FROM table_default_compression;
INSERT INTO table_c_column_zstd1 SELECT * FROM table_default_compression;
INSERT INTO table_c_d_column_zstd1 SELECT * FROM table_default_compression;
```

**Step 5: Compare compression results**
Check the compression ratios of the tables:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT
	table_name, ROUND(uncompressed_bytes::DOUBLE / compressed_bytes, 1) as compression_ratio, compressed_bytes
FROM information_schema.tables
WHERE
	table_name IN ('table_default_compression', 'table_zstd1', 'table_c_column_zstd1', 'table_c_d_column_zstd1')
ORDER BY table_name;
```

| table\_name                 | compression\_ratio | compressed\_bytes |
| --------------------------- | ------------------ | ----------------- |
| table\_c\_column\_zstd1     | 449.4              | 2559              |
| table\_c\_d\_column\_zstd1  | 1505.2             | 764               |
| table\_default\_compression | 241                | 4772              |
| table\_zstd1                | 2948.7             | 780               |

As demonstrated in these examples, column-level compression significantly reduces storage for specific columns. ZSTD typically offers higher compression efficiency compared to the default LZ4.