DEV Community

Arpit Bangre
Arpit Bangre

Posted on

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

#sql #database #datascience #dataengineering

Here is a simple SQL query that should return 0.3:

SELECT 0.1::FLOAT4 + 0.2::FLOAT4;
Enter fullscreen mode Exit fullscreen mode

In PostgreSQL, MySQL, and most relational SQL engines, the result is:

0.30000001192092896
Enter fullscreen mode Exit fullscreen mode

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?

  1. Hardware Implementation: Modern computer CPUs represent FLOAT and DOUBLE PRECISION using binary floating-point numbers (IEEE 754 standard).
  2. 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)
Enter fullscreen mode Exit fullscreen mode

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                    │
└────────────────────────────────────────────────────────────────────────┘
Enter fullscreen mode Exit fullscreen mode
  • 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:

  1. Never use FLOAT, REAL, or DOUBLE PRECISION for:
    • Product pricing (unit_price)
    • Account balances (wallet_balance, available_funds)
    • Tax & GST calculations (tax_amount, discount_rate)
  2. Always enforce NUMERIC(precision, scale): 
   CREATE TABLE customer_orders (
       order_id        BIGINT PRIMARY KEY,
       customer_id     INT NOT NULL,
       order_amount    NUMERIC(12, 2) NOT NULL CHECK (order_amount >= 0.00),
       created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
   );
Enter fullscreen mode Exit fullscreen mode
  • precision = 12: Total digits allowed (supports up to ₹9,999,999,999.99 / ~999 Crore).
  • scale = 2: Exactly 2 digits reserved after the decimal point (paise/cents).

🏆 Key Takeaway for System Design & Interviews

Feature FLOAT / REAL NUMERIC / DECIMAL
Storage Engine Hardware IEEE 754 Binary Software Base-10000 Digit Array
Calculation Speed Extremely fast (native CPU ALU) Slightly slower (software math)
Precision Approximate (drifts on fractions) 100% Exact (penny-perfect)
Best Use Case Machine learning embeddings, GPS coordinates, physics simulations Financial ledgers, billing, invoices, banking, e-commerce

💡 What is your team's strict rule for financial columns in production DDL? Drop your thoughts below!

💼 Let's connect: linkedin.com/in/arpitmbangre

Top comments (0)