---
title: "Compression & Storage Quick Start Guide"
---

# Compression & Storage Quick Start Guide

This guide explains HeliosDB-Lite's compression architecture and per-column storage optimization features.

---

## Compression Architecture

HeliosDB-Lite combines two kinds of compression:

1. **Column codecs**
   - ALP for numeric columns and FSST for string columns, enabled in the
     `[compression]` section of `config.toml` (`enable_alp`, `enable_fsst`)
   - Per-column override: `ALTER TABLE t ALTER COLUMN c SET COMPRESSION {zstd|lz4|none}`
   - See the [Compression Codecs User Guide](/docs/lite/guides/compression_codecs_user_guide/)

2. **Per-Column Storage Modes** (optional, per-column)
   - Dictionary encoding for low-cardinality strings
   - Content-addressed deduplication for large values
   - Columnar batching for analytics workloads

SQL is unchanged: compression and decompression happen inside the engine.

```sql
CREATE TABLE sensors (id INT, reading FLOAT8, location TEXT);
INSERT INTO sensors VALUES (1, 23.5, 'Building A, Floor 3, Room 301');
SELECT * FROM sensors;
```

### Compression Ratios (10GB Benchmarks)

| Data Pattern | Compression Ratio | Example |
|--------------|-------------------|---------|
| Email addresses | 6.95x | 10.5 GB → 1.5 GB |
| Sorted text (regions) | 6.26x | 10.7 GB → 1.7 GB |
| Sequential integers | 3.96x | 10.7 GB → 2.5 GB |
| Low-cardinality strings | 3.96x | 10.7 GB → 2.7 GB |
| Floating-point prices | 3.70x | 18.3 GB → 2.9 GB |

Figures from internal tests (HeliosDB Lite, 10 GB data set per pattern, block-level LZ4 compression); ratios depend on your data.

---

## Per-Column Storage Modes

For additional optimization, you can specify storage modes per column:

### 1. Dictionary Encoding

Best for columns with few unique values (< 64K distinct):

```sql
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status TEXT STORAGE DICTIONARY,     -- 6 values: pending, shipped, etc.
    country TEXT STORAGE DICTIONARY     -- ~200 country codes
);
```

**Benefits:**
- 50-95% storage reduction for low-cardinality strings
- 4-byte IDs instead of full strings in row tuples
- Faster equality comparisons

### 2. Content-Addressed Storage

Best for large values (>1KB) with duplicates:

```sql
CREATE TABLE documents (
    id INT PRIMARY KEY,
    content TEXT STORAGE CONTENT_ADDRESSED,  -- Many docs share templates
    thumbnail BYTEA STORAGE CONTENT_ADDRESSED -- Default images
);
```

**Benefits:**
- Near-100% deduplication for duplicate large values
- Blake3 hashing for fast lookups
- Only stores each unique value once

### 3. Columnar Storage

Best for analytics and aggregations:

```sql
CREATE TABLE metrics (
    id INT PRIMARY KEY,
    timestamp INT8 STORAGE COLUMNAR,     -- Time-series data
    value FLOAT8 STORAGE COLUMNAR        -- Aggregation targets
);
```

**Benefits:**
- 20-50% better compression for numeric sequences
- 2-4x faster aggregations (SUM, AVG, COUNT)
- Efficient column-only scans

---

## Changing Storage Modes (Online Migration)

You can change storage modes on existing tables:

```sql
-- Migrate existing data to dictionary encoding
ALTER TABLE orders ALTER COLUMN status SET STORAGE DICTIONARY;

-- Migrate to content-addressed storage
ALTER TABLE documents ALTER COLUMN content SET STORAGE CONTENT_ADDRESSED;

-- Migrate to columnar storage
ALTER TABLE metrics ALTER COLUMN value SET STORAGE COLUMNAR;

-- Revert to default
ALTER TABLE orders ALTER COLUMN status SET STORAGE DEFAULT;
```

**Note:** Migrations are performed online and restructure all existing data.

---

## When to Use Each Mode

| Scenario | Recommended | Reason |
|----------|-------------|--------|
| Response Codes, enums | DICTIONARY | Few unique values |
| Country/region codes | DICTIONARY | ~200 unique values |
| Email templates | CONTENT_ADDRESSED | Many duplicates |
| Default profile images | CONTENT_ADDRESSED | High duplication |
| Time-series timestamps | COLUMNAR | Sequential, aggregations |
| Sensor readings | COLUMNAR | Numeric, analytics |
| Unique IDs (UUIDs) | DEFAULT | High cardinality |
| Primary keys | DEFAULT | Point lookups |

---

## Fast Bulk Loading

For faster ingestion, insert many rows per statement and group batches in one
transaction:

```sql
BEGIN;
INSERT INTO sensors VALUES
    (1, 23.5, 'Building A'),
    (2, 24.1, 'Building A'),
    (3, 22.8, 'Building B');
COMMIT;
```

### Bulk Loading Performance

| Method | Throughput | Use Case |
|--------|------------|----------|
| Standard INSERT | 30K-50K rows/sec | OLTP operations |

Figures from internal tests (HeliosDB Lite, standard INSERT statements); reproduce on your own hardware.

---

## Monitoring Compression

### Check Storage Usage

```sql
-- Per-table size statistics
SELECT * FROM helios_table_memory_stats;
```

### Estimate Compression Ratio

Compare logical vs physical size:
- Logical: Row count × average row size
- Physical: Database file size

---

## Performance Tips

### 1. Use Appropriate Storage Modes

```sql
-- Good: Dictionary for enums
status TEXT STORAGE DICTIONARY  -- 6 unique values

-- Bad: Dictionary for UUIDs
user_id TEXT STORAGE DICTIONARY  -- Millions of unique values
```

### 2. Batch Inserts for Better Compression

Compression works better with larger batches of similar data:

```sql
BEGIN;
-- many multi-row INSERT statements
INSERT INTO metrics VALUES (1, 1705700000, 1.5), (2, 1705700001, 1.6);
COMMIT;
```

### 3. Consider Data Order

Sorted data compresses better:

```sql
-- Better compression if data is pre-sorted
CREATE TABLE events (
    timestamp INT8 STORAGE COLUMNAR,  -- Sorted timestamps compress well
    event_type TEXT STORAGE DICTIONARY
);
```

---

## Troubleshooting

### Low Compression Ratio

**Check data patterns:**
- Random data (UUIDs, hashes) compresses poorly
- Already-compressed data (images, videos) won't compress further
- Encrypted data has no compressible patterns

**Solution:** Use DEFAULT storage for incompressible data.

### Dictionary Too Large

**Symptom:** Dictionary encoding not helping

**Check:**
```sql
SELECT COUNT(DISTINCT column_name) FROM table_name;
```

**Solution:** If >64K unique values, switch to DEFAULT.

### Slow Aggregations

**Symptom:** SUM/AVG queries slow on large tables

**Solution:** Use COLUMNAR storage for aggregation targets:
```sql
ALTER TABLE metrics ALTER COLUMN value SET STORAGE COLUMNAR;
```

---

## Further Reading

- [Per-Column Storage Modes Guide](https://www.heliosdb.com/docs/lite/guides/user/storage_modes_guide/) - Detailed storage mode documentation

---