Float vs Decimal: Choosing Numeric Types for Precision and Performance

Choosing between floating-point types (Float32, Float64) and fixed-point Decimal types is one of the most consequential schema decisions in ClickHouse, because it directly determines whether your aggregations are exact or merely approximate. Floats are fast and compact and cover an enormous range, but they cannot represent most decimal fractions exactly. Decimals trade some speed and range for guaranteed precision, which is non-negotiable for money.

This guide explains how each family works in ClickHouse, the precision and performance tradeoffs between them, and concrete recommendations by domain.

The Core Difference

Float32 and Float64 are IEEE 754 binary floating-point numbers (C float and double). They store values as a sign, mantissa, and exponent, which lets them cover a huge dynamic range — roughly ±3.4×10³⁸ for Float32 and far more for Float64 — but most decimal fractions have no exact binary representation. The classic symptom:

SELECT 0.1 + 0.2 = 0.3;        -- 0 (false)
SELECT 1 - 0.9;                 -- 0.09999999999999998

Decimal(P, S) is a fixed-point type backed by an integer. P is precision (total significant digits) and S is scale (digits after the decimal point). The value is stored as an integer scaled by 10^S, so Decimal(10, 2) storing 9.99 is really the integer 999. Because it is an exact integer underneath, decimal arithmetic does not accumulate the binary rounding error that floats do.

ClickHouse Float Types

A few ClickHouse-specific notes:

• A precision argument like FLOAT(12) is accepted but ignored — you always get Float32 or Float64.
• Floats support the special IEEE 754 values Inf, -Inf, and NaN. For example, 0.5 / 0 yields Inf and 0 / 0 yields NaN. Decimal cannot represent any of these.
• NaN = NaN returns false, but in GROUP BY and DISTINCT different NaN bit patterns are treated as distinct values, which can be counterintuitive when grouping.
• BFloat16 is a 16-bit type (8-bit exponent, 7-bit mantissa) aimed at machine-learning workloads. Most operations are unsupported; convert with toFloat32() / toBFloat16().

ClickHouse Decimal Types

Decimal(P, S) maps to one of four underlying integer widths depending on the precision you request:

You can write either the generic Decimal(P, S) form (ClickHouse picks the width from P) or name the width directly, e.g. Decimal64(4). Choosing the narrowest type that fits your precision keeps both storage and compute cheaper.

CREATE TABLE payments
(
    payment_id  UInt64,
    amount      Decimal(18, 2),   -- backed by Decimal64
    fx_rate     Decimal(18, 8),
    created_at  DateTime
)
ENGINE = MergeTree
ORDER BY (created_at, payment_id);

See the CREATE TABLE reference for full column-definition syntax.

Precision and Range Tradeoff

The decision is essentially exactness versus range and speed:

A key constraint: operations between Decimal and Float32/Float64 are not defined. You must explicitly convert one side (for example toFloat64(amount) or toDecimal64(rate, 8)) before combining them, which prevents silent precision loss.

Decimal Arithmetic and Scale Rules

Decimal operations follow fixed rules for the scale of the result, and these rules are where most surprises come from:

• Addition / subtraction: result scale = max(S1, S2)
• Multiplication: result scale = S1 + S2
• Division: result scale = S1, and least-significant digits are discarded, not rounded

Because multiplication adds scales and the intermediate is computed at the wider scale, an operation whose final result would fit can still overflow mid-computation:

-- Can overflow on the intermediate even though 66.6667 fits:
SELECT CAST(200, 'Decimal32(4)') / CAST(3, 'Decimal32(4)');

Two reliable workarounds:

-- Control precision explicitly with the dedicated functions:
SELECT divideDecimal(toDecimal64(200, 4), toDecimal64(3, 4), 4);
SELECT multiplyDecimal(toDecimal64(2, 4), toDecimal64(3, 4), 4);

multiplyDecimal() and divideDecimal() let you pin the result scale and avoid intermediate overflow. They are slower than the plain operators but predictable.

Overflow Behavior (Read This Before Production)

Overflow handling differs by width and is the single biggest footgun:

• Excess fractional digits are silently discarded.
• Excess integer digits raise an exception for Decimal32 and Decimal64 (overflow checks are on).
• Overflow checks are not implemented for Decimal128 and Decimal256. These can silently produce incorrect results instead of erroring.

The practical lesson: prefer Decimal32/Decimal64 when their precision is enough, and be deliberate about scale headroom when you reach for Decimal128/Decimal256. For the related table-key restriction, see Data type cannot be used in key.

Literal Parsing Gotcha

An unquoted decimal literal is parsed as a Float64 first and only then converted, which can inject float rounding error into a Decimal. Pass the literal as a quoted string to parse it exactly:

-- Imprecise: 9.2 becomes a Float64 first
SELECT CAST(9.2, 'Decimal64(2)');

-- Exact: parsed directly as Decimal
SELECT CAST('9.2', 'Decimal64(2)');
SELECT toDecimal64('9.2', 2);

Storage and Compression

On disk, the raw widths are simple: Float32 and Decimal32 are 4 bytes, Float64 and Decimal64 are 8 bytes. Compression, however, differs by access pattern and codec:

• Floats compress best with codecs designed for them. Gorilla (XOR against the previous value) and FPC shine on slowly changing time-series; combine DoubleDelta + Gorilla for sensor/metric streams.
• Decimals are integers underneath, so integer-oriented codecs help: T64 crops unused high bits and Delta works well on monotonic or slowly changing sequences.

CREATE TABLE sensor_readings
(
    ts          DateTime CODEC(DoubleDelta, LZ4),
    temperature Float32  CODEC(Gorilla, LZ4),
    -- exact monetary column, integer-style codec
    meter_cost  Decimal(18, 4) CODEC(GCD, LZ4)
)
ENGINE = MergeTree
ORDER BY ts;

For how disk layout differs from in-memory representation and why a column's RAM footprint can dwarf its on-disk size, see Data Types: Storage on Disk vs Memory.

Recommendations by Domain

• Money, billing, invoicing, ledgers, tax: use Decimal. A typical currency amount is Decimal(18, 2) (or more scale for crypto/FX, e.g. Decimal(38, 8)). Floats will eventually lose a cent.
• FX rates and unit prices: Decimal with enough scale (e.g. Decimal(18, 8)); be careful with multiplication scale growth.
• Scientific, physics, audio, graphics: Float64 — you need range and the inputs are already approximate.
• Metrics, telemetry, ML features, embeddings: Float32 (or BFloat16 for ML) — exactness rarely matters and storage/speed do.
• Counts, IDs, quantities (no fractions): neither — use integer types (UInt*/Int*).

Best Practices

1. Use Decimal for anything audited. If a human or regulator could dispute a sum, store it as Decimal, not Float.
2. Pick the narrowest Decimal width. Prefer Decimal32/Decimal64 over Decimal128/Decimal256 for speed and working overflow checks.
3. Quote decimal literals. Use '9.2' or toDecimal64('9.2', 2), never bare 9.2, to avoid a Float64 round-trip.
4. Plan scale for multiplication. Multiplication adds scales; use multiplyDecimal()/divideDecimal() to pin the result and avoid intermediate overflow.
5. Never mix Float and Decimal implicitly. Convert explicitly so precision loss is intentional and visible.
6. Match the codec to the type. Gorilla/FPC for floats, GCD/Delta for decimals and integers.

Common Issues

• Aggregations are slightly off. SUM() over a Float64 money column accumulates rounding error across millions of rows. Switch the column to Decimal.
• Illegal types ... of arguments on arithmetic. You combined Decimal and Float directly. Cast one side first.
• Division dropped digits. Decimal division truncates rather than rounds; use divideDecimal() with an explicit scale, or round intentionally.
• Silent wrong results on big Decimals. Decimal128/Decimal256 skip overflow checks — validate ranges in your application or stay within Decimal64.

Changing the Type Later

Migrating an existing column from Float to Decimal (or widening a Decimal) is a real schema change with rewrite cost and rounding implications. Plan it as a migration rather than an in-place edit — see ClickHouse schema migration tools for safe approaches to altering column types on populated tables.

How Pulse Helps

Picking the wrong numeric type is the kind of decision that is invisible until a finance team notices the totals do not reconcile, or until Decimal128 arithmetic silently overflows in production. Pulse reviews ClickHouse schemas for exactly these risks — flagging monetary columns stored as floats, Decimal precision/scale that is too tight for the data, and codec choices that leave compression on the table. When you do need to migrate a column type on a large, live table, Pulse helps plan and validate the change so aggregations stay correct and the rewrite does not destabilize your cluster.

Frequently Asked Questions

Q: Should I always use Decimal for currency?

Yes. Store monetary values as Decimal (commonly Decimal(18, 2)). Float accumulates binary rounding error, so large SUM() results can be off by fractions of a cent — unacceptable for billing or accounting.

Q: Why does 0.1 + 0.2 = 0.3 return false?

Because Float is binary IEEE 754 and 0.1, 0.2, and 0.3 have no exact binary representation. The stored approximations do not add up to exactly the stored 0.3. Decimal does not have this problem.

Q: Can I do math directly between a Decimal and a Float column?

No. Operations between Decimal and Float32/Float64 are undefined in ClickHouse. Convert one side explicitly with toFloat64() or toDecimal64() first.

Q: Are Decimal128 and Decimal256 slower than Decimal64?

Yes. Decimal32 and Decimal64 map to native CPU integers, while Decimal128 and Decimal256 are emulated in software and run significantly slower. They also skip arithmetic overflow checks, so stay within Decimal64 when you can.

Q: How do I store NaN or Infinity?

Only Float32/Float64 support NaN, Inf, and -Inf. Decimal cannot represent them. If your data needs these special values, you must use a float type.

Q: My Decimal division is dropping digits — is that a bug?

No. Decimal division discards least-significant digits rather than rounding, and the result keeps the left operand's scale. Use divideDecimal(a, b, scale) to control the output scale explicitly.