Float Types in ClickHouse: Float32 vs Float64 Explained with Use Cases

  

When working with analytical databases like ClickHouse, handling floating-point numbers accurately and efficiently is crucial. ClickHouse provides two primary types for representing non-integer values: Float32 and Float64. Each has trade-offs in terms of precision and performance. This post explores the differences between these types, when to use each, and common pitfalls to avoid when working with floating-point data in ClickHouse.

 

Float32

·      4-byte single-precision floating-point number.

·      Approx. 6–7 decimal digits of precision.

·      Smaller memory footprint, faster processing.

·      Good for sensor data, percentages, or approximate metrics.

 

Float64

·      8-byte double-precision floating-point number.

·      Approx. 15–17 decimal digits of precision.

·      More accurate but consumes more space.

·      Ideal for financial data, scientific computations, or when high precision is required.

 

Example

CREATE DATABASE IF NOT EXISTS demo_db;

-- Creating a table with float columns
CREATE TABLE demo_db.sensor_data (
    sensor_id UInt32,
    temperature Float32,
    pressure Float64,
    timestamp DateTime
) ENGINE = MergeTree
ORDER BY timestamp;

-- Inserting sample data
INSERT INTO demo_db.sensor_data VALUES
(1, 22.45678, 1013.256789123456, now());

-- Querying the data
SELECT
    sensor_id,
    round(temperature, 3) AS temp_rounded,
    round(pressure, 6) AS pressure_rounded
FROM demo_db.sensor_data;

krishna :) SELECT
    sensor_id,
    round(temperature, 3) AS temp_rounded,
    round(pressure, 6) AS pressure_rounded
FROM demo_db.sensor_data;

SELECT
    sensor_id,
    round(temperature, 3) AS temp_rounded,
    round(pressure, 6) AS pressure_rounded
FROM demo_db.sensor_data

Query id: 8f4f09b5-ab18-43d6-8a31-29aa95ba289f

   ┌─sensor_id─┬─temp_rounded─┬─pressure_rounded─┐
1. │         1 │       22.457 │      1013.256789 │
   └───────────┴──────────────┴──────────────────┘

1 row in set. Elapsed: 0.004 sec.

   

Key Points to Consider When Working with Float Types in ClickHouse:

Precision Loss: Float32 can lose precision for numbers beyond 6–7 digits. Avoid using Float32 for money, timestamps, or values that require high accuracy.

 

Storage vs Accuracy Tradeoff: Use Float32 for high-volume, less sensitive data to save space. Use Float64 for calculations, joins, or aggregations involving precise values.

 

Aggregation Sensitivity: Floating-point precision can affect results of SUM(), AVG(), etc. Consider rounding or using round(x, n) during display or reporting.

 

Performance: Float32 has better performance on CPU and I/O due to smaller size. Benchmark both types if you're unsure.

 

Comparison Caution: Avoid direct equality comparisons between floats. 

 

-- Risky
WHERE temperature = 22.45678

-- Better
WHERE abs(temperature - 22.45678) < 0.0001

 

   

Let’s demo it with an example.

CREATE TABLE demo_db.float_test (
    id UInt32,
    temperature Float32
) ENGINE = MergeTree
ORDER BY id;

INSERT INTO demo_db.float_test VALUES
(1, 22.45678),
(2, 22.4567),
(3, 22.4568),
(4, 22.456781);

   

Let’s get the records where temperature is 22.45678.

 

SELECT * FROM demo_db.float_test
WHERE temperature = 22.45678;

krishna :) SELECT * FROM demo_db.float_test
WHERE temperature = 22.45678;

SELECT *
FROM demo_db.float_test
WHERE temperature = 22.45678

Query id: bb2caa39-052c-4a06-a33b-479bf1ae2e1f

Ok.

0 rows in set. Elapsed: 0.002 sec.

   

Oops, above query return zero  rows, even though we inserted a value like 22.45678. It is due to floating-point precision limitations, the value 22.45678 might not be stored exactly as it is written. Internally, Float32 can slightly round or represent the value inaccurately due to binary limitations.

 

Let’s address this problem with the help of safe comparison using approximate match.

 

SELECT * FROM demo_db.float_test
WHERE abs(temperature - 22.45678) < 0.0001;

   

Instead of checking for exact equality, this query checks whether the value is “close enough” to 22.45678 within a small tolerance (also called epsilon), here 0.0001.

 

krishna :) SELECT * FROM demo_db.float_test
WHERE abs(temperature - 22.45678) < 0.0001;

SELECT *
FROM demo_db.float_test
WHERE abs(temperature - 22.45678) < 0.0001

Query id: 8a91ac98-0214-407a-ab49-17b8858716db

   ┌─id─┬─temperature─┐
1. │  1 │    22.45678 │
2. │  2 │     22.4567 │
3. │  3 │     22.4568 │
4. │  4 │   22.456781 │
   └────┴─────────────┘

4 rows in set. Elapsed: 0.006 sec.

 

  

Previous                                                    Next                                                    Home