@Rocky Since both

DECIMAL(18,5)

and

DECIMAL(5,2)

fall under Decimal64 (8-byte int64), is there any practical difference between them regarding disk storage, memory pre-allocation, scan efficiency, or vectorized compute cost? Does reducing the precision actually save any resources here?

Since both

DECIMAL(18,5)

and

DECIMAL(5,2)

have a precision $P \le 18$, they are both mapped to the Decimal64 logical type and stored internally as 8-byte integers (

int64

). While they share the same underlying machine type, there are practical differences in how they affect your system resources: 1. Disk Storage (Significant Difference) Even though both are "8-byte integers," StarRocks uses columnar encoding techniques like Bit-packing and Frame of Reference (FOR). * DECIMAL(5,2): The maximum internal integer value is $99,999$, which fits into approximately 17 bits. StarRocks can pack these values into a much smaller space on disk before applying compression (LZ4/ZSTD). * DECIMAL(18,5): The values can reach $10^{18}-1$, which requires nearly the full 64 bits. * Result: Reducing precision from 18 to 5 will significantly reduce the physical footprint on disk and improve compression ratios. 2. Scan Efficiency (Direct Benefit) Because the data is smaller on disk (due to the encoding mentioned above), the storage engine reads fewer bytes from the I/O layer. * Smaller precision leads to less I/O bandwidth usage. * Decompression is faster because there is less data to process. * Result: Scans for

DECIMAL(5,2)

are generally faster than

DECIMAL(18,5)

. 3. Memory Pre-allocation (No Difference) StarRocks' vectorized execution engine processes data in chunks. For a

Decimal64

column, the engine allocates a contiguous memory buffer where each row occupies exactly 8 bytes (a 64-bit lane). * Whether your precision is 1 or 18, the

Column

object in memory will still reserve 8 bytes per row during query execution. * Result: There is no memory saving in the execution engine's shuffle or join buffers by reducing precision within the same storage class. 4. Vectorized Compute Cost (Negligible Difference) Both types use the same 64-bit CPU instructions for arithmetic (e.g.,

ADD

,

MUL

). * The only overhead is alignment: If you perform operations between decimals of different scales (e.g.,

DECIMAL(18,5) + DECIMAL(5,2)

), the system must perform a shift/multiply to align the decimal points. * Result: If your operations use consistent scales, the CPU cost is identical. Summary Feature | Difference? | Detail Disk Storage | Yes | Smaller precision = better bit-packing and compression. Scan Efficiency | Yes | Reduced I/O due to smaller disk footprint. Memory (RAM) | No | Both allocate a fixed 8 bytes per row in vectorized blocks. Compute (CPU) | No | Both use the same 64-bit integer arithmetic instructions. Recommendation: You should use the smallest precision that safely covers your data range. While it won't save RAM during a join, it will notably reduce your storage costs and improve scan-I/O-heavy query latency. References • sql-reference/data-types/numeric/DECIMAL.md • release_notes/release-3.2.md • sql-reference/System_limit.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md

@Rocky If I use FLOAT or DOUBLE for range filtering (e.g.,

>= 0.3

or

<= 0.9

), can floating-point precision errors cause the query to miss expected rows or include extra rows at the boundaries? To guarantee exact accuracy for range queries, should I strictly define the column as DECIMAL?

Yes, precision errors in FLOAT and DOUBLE can cause queries to miss expected rows or include extra rows at the boundaries. To guarantee exact accuracy for range queries (and especially equality queries), you should use the DECIMAL type. 1. Why boundaries fail with FLOAT/DOUBLE Floating-point numbers (IEEE 754) use a binary representation that cannot exactly represent many decimal fractions (like

0.1

,

0.3

, or

0.9

). * The Literal vs. Storage Problem: When you write

WHERE col >= 0.3

, the literal

0.3

is converted to the nearest representable binary fraction. If the value in your column was calculated (e.g.,

0.1 + 0.2

), the resulting bits might be slightly smaller or larger than the bits for the literal

0.3

. * Example:

0.1 + 0.2

in double precision is actually

0.30000000000000004

. A filter

col <= 0.3

would miss this row. * The Representation Error: A value you think is exactly

0.9

might be stored as

0.8999999999999999

. A filter

col >= 0.9

would miss this row. 2. When to use DECIMAL The StarRocks documentation explicitly recommends DECIMAL for scenarios requiring high precision: * Financial/Accounting data: Where every cent must be accounted for. * Exact Range Filtering: When business logic depends on specific thresholds (like "exactly 0.3"). * Equality Checks: The StarRocks FAQ warns that using

=

on floating-point numbers is unstable and recommends range checks or DECIMAL. 3. Practical Trade-offs While DECIMAL guarantees accuracy, it comes with specific costs: * Performance: Calculations on

DECIMAL

types can be 2-3 times slower than `FLOAT`/`DOUBLE` because they cannot always use native CPU floating-point instructions and may require more complex integer arithmetic. * Storage: As discussed previously,

DECIMAL

types (especially

Decimal128

or

Decimal256

) can consume more space and memory than a 4-byte

FLOAT

or 8-byte

DOUBLE

. Summary Recommendation * Use FLOAT/DOUBLE for scientific data, sensor readings, or metrics where a tiny error (e.g., $10^{-15}$) is irrelevant and performance is the priority. * Use DECIMAL for any case where a value like

0.3

must behave exactly like the decimal

0.3

. This is the only way to ensure that boundary values are never "lost" due to binary rounding. References • faq/Sql_faq.md • knowledge/usage/best-practice.md • sql-reference/data-types/numeric/DECIMAL.md References • faq/Sql_faq.md • using_starrocks/caching/query_cache.md