NUMERIC vs DOUBLE PRECISION in Postgres: The Difference That Hides in Money

The bug was 12 cents. Not every row was wrong. Not every invoice was wrong. A reconciliation job was off by 12 cents across thousands of transactions, and the team spent two days blaming payment provider webhooks before anyone looked at the column type.

The amounts were stored as double precision. Most values looked fine in the UI. The math was not fine. Floating point numbers are approximate. Money is not supposed to be approximate.

The framework: exact contract or measured signal?

I choose the type by asking what the number means:

• If the value is a contract, balance, price, quantity owed, tax amount, or ledger entry, use numeric or integer minor units.
• If the value is a sensor reading, model score, latitude, percentile, ratio, or scientific measurement, double precision is often the right tool.

This is not a style preference. It is a correctness boundary. numeric stores exact decimal values with user-defined precision and scale. double precision stores an inexact floating point approximation.

The money rule

For money, I prefer one of two designs:

amount_cents bigint NOT NULL

or:

amount numeric(12, 2) NOT NULL

Integer minor units are simple and fast when every value has the same currency scale. numeric(12, 2) is clearer when decimal scale belongs in the schema or when the application works directly in decimal units. What I avoid is double precision for anything that must reconcile exactly.

Show the failure mode

A demo like this is enough to make the point:

SELECT
  (0.1::double precision + 0.2::double precision) AS float_sum,
  (0.1::numeric + 0.2::numeric) AS numeric_sum;

The floating point result represents the nearest binary approximation. PostgreSQL is doing what the type promises. The problem is choosing that promise for a domain that needs decimal identity.

Where double precision is the better choice

I do not default every number to numeric. That creates unnecessary CPU cost and sometimes false precision. Telemetry, ML scores, distance estimates, ratios, and physical measurements often arrive with uncertainty already baked in. Storing them as exact decimals can make the data look more precise than it is.

CREATE TABLE query_samples (
  captured_at timestamptz NOT NULL,
  query_id bigint NOT NULL,
  duration_ms double precision NOT NULL,
  cpu_pct double precision NOT NULL
);

For this kind of table, speed and compactness usually matter more than exact decimal arithmetic. You aggregate, graph, bucket, and compare trends. You are not balancing a ledger.

Performance is real, but it is not the first question

PostgreSQL documentation notes that numeric arithmetic is slower than integer and floating point arithmetic. That matters on high-volume analytics paths. It does not justify putting account balances in floating point columns.

The production decision is not "fast or correct." It is "what does correctness mean here?" If correctness means exact decimal results, use exact storage and optimize around that. If correctness means approximate measurement within a known tolerance, use floating point and document the tolerance.

Rounding belongs at boundaries

A common smell is rounding at every step because the type cannot be trusted:

round(price * tax_rate * discount, 2)

For financial workflows, keep the internal rule explicit. Store exact values, decide where rounding legally or commercially occurs, and test that boundary. Rounding scattered through application code is how two reports disagree while both look reasonable.

How I audit an existing schema

SELECT
  table_schema,
  table_name,
  column_name,
  data_type
FROM information_schema.columns
WHERE data_type IN ('double precision', 'real', 'numeric')
ORDER BY table_schema, table_name, column_name;

Then I classify columns by domain. A double precision column named amount, balance, price, tax, or quantity deserves review. A double precision column named duration_ms or score may be exactly right.

Migrating away from floating point money

The migration has to be conservative because old values may already contain approximation artifacts:

1. Add a new numeric or bigint column.
2. Backfill with the business rounding rule, not an ad hoc cast.
3. Compare old and new totals by account, invoice, day, and currency.
4. Dual-write for one release if the application path is risky.
5. Cut reads over only after reconciliation is clean.

SELECT
  currency,
  sum(old_amount::numeric) AS old_total,
  sum(new_amount) AS new_total,
  sum(new_amount) - sum(old_amount::numeric) AS delta
FROM payments
GROUP BY currency;

If the delta is not zero, that is not a database problem. That is the migration surfacing a product rule that was implicit before.

The default I use

Use bigint minor units or numeric for money and contractual quantities. Use double precision for measurements, estimates, scores, and scientific values where approximation is acceptable and expected.

The mistake is not choosing the slower type or the faster type. The mistake is storing a product promise in a type that makes a different promise.