Table Schema and Design Analysis

Analyze table structure, partitioning, ORDER BY, materialized views, and identify design anti-patterns.

Diagnostics

Run all queries from the file checks.sql and analyze the results.

Deep Dive Queries (Placeholder-Based)

Partition Distribution for Specific Table

select
    database,
    table,
    count() as partitions,
    sum(rows) as total_rows,
    formatReadableSize(sum(bytes_on_disk)) as total_size,
    formatReadableSize(median(bytes_on_disk)) as median_partition_size,
    min(partition) as oldest_partition,
    max(partition) as newest_partition
from system.parts
where active and database = '{database}' and table = '{table}'
group by database, table, partition
order by partition desc
limit 100

Column Compression Analysis for Specific Table

select
    name,
    type,
    formatReadableSize(data_compressed_bytes) as compressed,
    formatReadableSize(data_uncompressed_bytes) as uncompressed,
    round(data_uncompressed_bytes / nullIf(data_compressed_bytes, 0), 2) as ratio,
    compression_codec
from system.columns
where database = '{database}' and table = '{table}'
order by data_compressed_bytes desc
limit 50

Look for:

• Columns with ratio < 2 → consider better codec or data transformation
• Large columns without codec → add CODEC(ZSTD) or LZ4HC
• String columns with low cardinality → consider LowCardinality(String)

Index Usage Analysis for Specific Database

select
    database,
    table,
    name as index_name,
    type,
    expr,
    granularity
from system.data_skipping_indices
where database = '{database}'
order by database, table

Schema Design Recommendations

Partition Key Guidelines

ORDER BY Guidelines

1. First column: Low cardinality, frequently filtered (e.g., tenant_id, region)
2. Second column: Time-based if range queries common
3. Subsequent: Other filter columns by selectivity (most selective last)

Anti-patterns:

• UUID/hash as first column
• High-cardinality ID without tenant prefix
• DateTime64 with microseconds as first column

Compression Codec Recommendations

Cross-Module Triggers

Settings Reference