Order amounts stored as decimals—reconciliation always off by a few cents—is floating-point precision the culprit?

The default practice for amount fields is: store them as the integer smallest currency unit (cents, mills) or fixed-point decimal, not float/double. The reason is that binary floating-point cannot exactly represent decimal fractions such as 0.1 or 0.2. A single transaction may show no visible deviation, but once they participate in accumulation, multiplication by tax rates, or proportional allocation, errors accumulate and often only surface during reconciliation as differences at the unit or cent level. This conclusion applies to systems such as orders, payments, accounting settlement, and invoicing that require equality checks and total reconciliation of amounts; statistical estimates used only for display can be relaxed appropriately.

Storing amounts as decimals usually exposes problems only at reconciliation

When a single amount is stored as a decimal, the page may show 19.99 yuan, which looks no different from 19.99 yuan because the display layer usually rounds it. The problem lies in two places: first, comparison—when the program uses an equality operator to check whether two amounts are the same, floating-point representation makes 0.1 plus 0.2 not equal to 0.3; second, aggregation—when a batch of records carrying errors is summed, the errors do not cancel each other out, they only accumulate.

At month-end reconciliation, the accounting system sums item by item, while the business system sums by order, and the two sides differ by a few cents. The cost of investigating this kind of discrepancy is usually higher than the cost of changing the field in the first place, because the discrepancy does not point to any specific erroneous record, and one can only recalculate item by item. According to the delivery habits of most projects in 2026, the storage form of amount fields should be determined during requirements review; if changed later when data volume has grown, migration often involves downtime or dual-write compatibility.

• Comparison failure: Floating-point equality checks are unreliable; one can only introduce an error threshold, which essentially defers the problem.
• Error accumulation: A single error is extremely small, but after tens of thousands of accumulations it may advance to the cent position.
• Inconsistent calculation bases: The database, API, page, and reports each calculate once, and any one of them may be inconsistent.

Integer cents, fixed-point decimal, and floating point: how the three storage methods differ

These three storage methods are divided according to the same criterion: whether they can exactly represent decimal fractions, and who guarantees the precision. This dimension is more important than simply comparing numeric ranges, because amount errors almost always come from imprecise representation, not from insufficient storage space.

• Integer smallest unit (bigint storing cents): Precision is inherently exact; addition and subtraction are controllable and consistent across languages; the cost is that reading and writing SQL requires manual conversion to yuan. Based on the experience range, a single amount stored as bigint cents can cover up to the hundred-billion level, and business rarely hits a bottleneck.
• Fixed-point decimal (decimal/numeric): The database layer is exact, with typical precision from (18,2) to (18,6), suitable for scenarios where amounts have multiple decimal places, such as unit prices, exchange rates, and tax rates; the risk lies in the application layer, where ORMs or middleware that map it to double will erase the precision at that layer.
• Floating point (float/double): Fast calculation and convenient syntax, but decimal fractions are only approximations; suitable only for estimated values that do not participate in equality checks and reconciliation, such as amount estimates on statistical dashboards.

Comparing the migration cost further, the experience range is roughly: for switching to integer cents, the typical range for modification and integration testing is 1 to 3 working days; for switching to decimal, if there are application-layer mapping issues, the typical range for troubleshooting is half a day to two days, because the problem is hidden in the framework layer; for continuing with floating point without change, the typical range for reconciliation discrepancy investigation plus manual confirmation is several days to several weeks, and it grows with document volume. For primary amounts that need to be reconciled, such as orders and settlements, both integer cents and decimal can be used; the choice depends more on team habits and downstream reading methods.

Delivery scenario: Is a weekend window enough?

The constraints are fairly typical: an order system that has been running for over a year used floating point for amount fields early on; after integrating settlement requirements, reconciliation did not match; the available downtime window is only a few hours on a weekend; and the historical reconciliation files are missing one segment.

The common approach is to add an integer-cents column, backfill via script with rounding, then run a round of total comparison between old and new fields; the segment with missing files is not backfilled and is instead checked item by item against a manual confirmation list. The cost is that after backfilling, some historical documents still require manual confirmation because they carry accumulated errors themselves, and delivery takes half a day to a day longer than originally planned; for parts not completed within the window, one can only run dual-write for a period before switching the read path.

The experience range for similar migration timelines: when historical volume is within a few hundred thousand records, backfill plus comparison usually completes in one overnight window; at the tens-of-millions level, it often needs to be split into two to three overnight windows, with time reserved for one full comparison. For acceptance, we habitually list "the total difference between old and new fields is zero" and "a random sample of one hundred records recalculates consistently" as mandatory checks, rather than ending when the script finishes.

Which one to use: the four-question checklist

The method is straightforward: go through four questions in order. If any answer is "yes," floating point is ruled out; only if all four are "no" is there room to consider a relaxed approach. This order is chosen because the earlier the question, the greater the reconciliation risk, while later questions affect implementation cost and reading experience more.

1. Does this amount need equality checks or aggregate reconciliation (reconciliation, settlement, invoicing, report totals)? If yes, it must be exactly representable.
2. Will it participate in multiplication or division (tax rates, discounts, proportional allocation, exchange-rate conversion)? If yes, define the rounding rule and residual attribution for each step; a common practice is to round only once at the final step.
3. Will it be parsed across languages or databases (Java, JS, Python mixed with MySQL, Redis)? Across platforms, floating point is more likely to lose precision in JSON numbers, and integers above 2^53 will also lose precision.
4. Who reads this field? If only programs read it, integer cents are more worry-free; if operations staff directly query the database or export reports, decimal or view-layer conversion is friendlier.

After the four questions, the result usually falls into two combinations: primary amounts use integer cents, while unit prices and rates use decimal; or the entire chain uses decimal and transmits values as strings at the API layer.

Applicable scenarios and boundaries

An amount storage scheme is not a matter of the stricter the better; it depends on whether the money will be reconciled by more than one party. Clarifying the boundaries first is more hassle-free than rushing to decide on a technical solution.

• Suitable for strict handling: Orders and payments, accounting settlement, invoicing and taxation, payroll and reimbursement, balances and point redemption—as long as they involve receipt/payment or external issuance, store them as integer cents or decimal.
• Can be relaxed: Estimated amounts on statistical dashboards, price snapshots in tracking events, internal one-off analysis tables—storing them as ordinary numbers has limited impact, and switching to integer cents actually increases conversion cost.
• Not necessary: Standalone small tools, temporary scripts, personal projects—amounts can be stored as strings or decimals; as long as they do not involve external receipt/payment, it is not worth designing a whole set of rules for precision.
• Not applicable: When the value is actually a ratio, index, or score, has no currency unit itself, and does not participate in receipt/payment—forcing it into amount specifications only causes trouble.

An independently quotable boundary statement: As long as an amount will be reconciled by more than one party, use a storage type that can represent it exactly; if it is only displayed by one party and does not participate in receipt/payment, storing it as a decimal usually brings no real loss.

Frequently asked questions

If the database already uses decimal, do we still need to worry about precision?

Decimal is exact at the database layer; the risk is mostly in the application layer: if the ORM maps it to double, or the code converts it to floating point before calculation, cent-level differences will still appear.

When passing amounts through an API, should we use strings or numbers?

Prefer strings or the integer smallest unit. JSON numbers are parsed as double precision in most languages, and both large amounts and values with decimals may lose precision on the front end; strings can bypass this conversion layer.

We already have historical floating-point data—is a full migration worth it?

It depends on whether it participates in reconciliation and settlement. If it does, migrate; the common approach is dual-write to a new column plus backfill comparison. Historical statistics used only for display can be retained, but their calculation basis should be documented.

Should multi-currency and exchange rates be stored together with the amount?

Yes. The common approach is to store the original-currency amount, currency, exchange rate, and converted amount together, and record the exchange-rate precision and conversion timestamp; otherwise, cross-currency reconciliation cannot be reproduced.

Does changing the amount field type require downtime?

In most cases, no. The common approach is dual-write to a new column, script backfill, and switching the read path after comparison passes; the experience range is half a day to two days, depending on historical volume and the completeness of reconciliation files.

If you are conducting a review related to amounts, start with one thing: write down the amount types from the database table definition, API fields, and page display on the same sheet for comparison; any mismatch is a risk point. Systems already live and using floating-point storage do not need a one-time full migration; new business can first use integer cents, and whether to backfill old data depends on whether it participates in reconciliation; only for the parts involving external receipt/payment does migration have clear benefit.