Subchapter 4.32

Schema Types Avoid Nullable

rules/schema-types-avoid-nullable.mdMarkdown2 KBView on GitHub

Avoid Nullable Unless Semantically Required

Impact: HIGH

Nullable columns maintain a separate UInt8 column for tracking null values, increasing storage and degrading performance. Use DEFAULT values instead when feasible.

Schema Types Avoid Nullable · Clickhouse Best Practices · ClickHouse/agent-skills · Skills Docs

CREATE TABLE users (
    id Nullable(UInt64),              -- IDs should never be null
    name Nullable(String),            -- Empty string is fine
    age Nullable(UInt8),              -- 0 is a valid default
    login_count Nullable(UInt32)      -- 0 is a valid default
)

CREATE TABLE users (
    id UInt64,                                    -- Never null
    name String DEFAULT '',                       -- Empty = unknown
    age UInt8 DEFAULT 0,                          -- 0 = unknown
    login_count UInt32 DEFAULT 0,                 -- 0 = never logged in
    deleted_at Nullable(DateTime),                -- NULL = not deleted (semantic!)
    parent_id Nullable(UInt64)                    -- NULL = no parent (semantic!)
)

Use Case	Why
`deleted_at`	NULL = “not deleted”, timestamp = “deleted at X”
`parent_id`	NULL = “no parent”, value = “has parent”
`discount_percent`	NULL = “no discount”, 0 = “0% discount”

Type	Default
String	`''` (empty string)
UInt/Int	`0`
DateTime	`now()` or `toDateTime(0)`
UUID	`generateUUIDv4()`