Why Using FLOAT for Financial Pipelines is a Silent $100k Trap (and How PostgreSQL NUMERIC Saves Your Ledger)

Here is a simple SQL query that should return 0.3: SELECT 0 . 1 :: FLOAT4 + 0 . 2 :: FLOAT4 ; In PostgreSQL, MySQL, and most relational SQL engines, the result is: 0.30000001192092896 If you calculate sales tax, loan interest, or wallet balances across 10,000,000 transactions a day , those tiny fractional drifts accumulate into real cash discrepancies during month-end ledger reconciliation. 🔍 Why Does Binary Floating-Point Drift Happen? Hardware Implementation: Modern computer CPUs represent FLOAT and DOUBLE PRECISION using binary floating-point numbers (IEEE 754 standard). Base-2 vs. Base-10 Math: In base-10, fractions like 0.1 (1/10) and 0.2 (2/10) look clean and simple. But in base-2 binary, 0.1 is an infinite recurring fraction : 0.000110011001100110011... (binary) Because hardware registers have finite bits (32-bit for FLOAT4 , 64-bit for FLOAT8 ), the value is truncated, introducing a tiny approximation error on every calculation. ⚙️ How PostgreSQL NUMERIC Works Under the Hood Unlike FLOAT , PostgreSQL's NUMERIC (or DECIMAL ) data type does NOT use IEEE 754 binary floating-point hardware representation. ┌────────────────────────────────────────────────────────────────────────┐ │ PostgreSQL NUMERIC Internal Memory Representation │ │ 1. Header (4 Bytes): Sign, weight, display scale, digit count │ │ 2. Digits Array: Stores exact base-10000 integer chunks (0000 to 9999) │ │ ➔ 100% Exact Arbitrary-Precision Base-10 Arithmetic │ └────────────────────────────────────────────────────────────────────────┘ It stores exact decimal digits in memory using base-10000 arithmetic . There is ZERO floating-point drift. 10.50 + 20.25 is always 100% exactly 30.75 . 💡 The Senior Data Engineer Production Standard When designing production DDL schemas for transactional, warehousing, or financial pipelines: Never use FLOAT , REAL , or DOUBLE PRECISION for: Product pricing ( unit_price ) Account balances ( wallet_balance , available_funds ) Tax & GST calculations ( tax_amount , discou