PostgreSQL's numeric is slow because of design decisions from 1998

The test that exposes the problem

On October 8, Andrei Lepikhov published a study on Planet PostgreSQL comparing two nearly identical EXPLAIN plans. The first groups a table by 12 numeric columns; the second performs the same GROUP BY, but with the columns converted to double precision.

The result: the numeric version took 4512 ms versus 1620 ms for the double precision version. Almost three times slower. Worse: the numeric aggregation consumed nine times more memory (1835 MB versus 196 MB), even though it read fewer disk pages (111,632 versus 125,000 buffers).

In short: the type that exists to guarantee accuracy is the same one that most penalizes those who do math at volume. And this isn't a recent bug, it's the consequence of choices made in 1998, when numeric was designed to support arbitrary precision.

Four decisions Postgres is still paying for today

Lepikhov isolates four architectural decisions that, taken together, form the basis of the problem:

• Representation is independent of the declaration: unlike SQL Server, DuckDB, ClickHouse, Arrow, and Parquet (which derive the value's width from the precision declared on the column), Postgres treats width as a property of the type, not the column. This forces numeric to be variable-length.
• Scale lives in the value, not the column: that's why 1.5 and 1.50 are stored as different bytes even in a numeric column without a fixed scale.
• The result's scale is calculated at runtime: the number of digits in a division depends on the operands, not just the types.
• A flexible upper limit: formally there's a cap of 131,072 digits before the decimal point and 16,383 after it, but in practice space is allocated based on the result, not the declaration, making overflow errors almost impossible.

Each one makes sense in isolation, usually to meet accuracy requirements typical of regulated industries. The problem is the sum of them all.

Inside numeric: digits in base 10000

Processors count in binary, and 0.1 in binary is an infinite repeating fraction, which is why double precision stores the closest representable number (hence 0.1 + 0.2 resulting in 0.30000000000000004). Money, interest, and rounding rules, however, are thought of in base ten, and that's exactly what an exact decimal type is for.

Postgres's numeric stores digits in two-byte groups, each group holding a value from 0 to 9999, that is, positional notation in base 10000. The position of the first group is stored as weight, a power of 10000, preventing very small or very large numbers from accumulating zeros.

The less intuitive part is dscale: it stores how many decimal digits to print, separate from the digits themselves. That's why 1.5 and 1.50 have the same internal digits but different dscale, and comparison/hashing must ignore that field while printing must remember it. In a column declared as numeric(15,2), the cast on write fixes dscale = 2 for both cases, making them byte-for-byte identical.

Small values use a packed format with a 1-byte header, but internal operators expect the standard 4-byte header, so almost every value gets unpacked before any operation. This has a curious effect: sometimes numeric takes up less space than bigint. In Lepikhov's example, pg_column_size returns 7 bytes for a numeric(15,2) stored in a table, but 10 bytes for the same value in a standalone cast (123456.00::numeric).

DuckDB's counterpoint: decimal as pure integer

DuckDB, focused on OLAP, took the opposite path on all four points. DECIMAL(15,2) with value 123456.00 is stored as the integer 12345600; the decimal point only exists at print time. Scale becomes a property of the column's type, not the value, which allows choosing in advance the smallest integer that fits the declared precision:

The gain shows up in concrete numbers cited by Lepikhov: in a benchmark on an M4 Pro, single thread, SUM over 20 million rows of DECIMAL(18,2) took 8.7 ms versus 8.5 ms for an equivalent BIGINT, a ratio of 1.02, that is, almost no extra cost for being decimal. DECIMAL(19,2), same data, jumped to about 880 ms, a 100x jump just from crossing the boundary from 18 to 19 digits, when the carrier changes from INT64 to INT128. The cost isn't from decimal logic, it's from the value's width.

MonetDB's documentation, where this idea came from for DuckDB, sums up the principle well:

The decimal types are represented as fixed-length integers, whose decimal point is produced during result rendering.

MonetDB documentation

Two hidden costs: pass-by-reference and tuple deforming

In Postgres, every value travels as a Datum, an 8-byte machine word. Types that fit in it are passed by value (a bigint lives inside the processor's own register); those that don't fit require a pointer and become pass-by-reference. numeric is always pass-by-reference, which implies palloc (memory allocation) on every arithmetic operation, even a simple sum.

Thomas Munro had already identified this structural limitation in 2017, in the mailing list discussion about DECFLOAT:

DECFLOAT(9) [= 32 bit] and DECFLOAT(17) [= 64 bit] could in theory be passed by value. Of course we don't have a way to make those pass-by-value and yet pass DECFLOAT(34) [= 128 bit] by reference! That is where I got stuck last time I was interested in this subject, because that seems like the place where we would stand to gain a bunch of performance, and yet the limited technical factors seems to be very well baked into Postgres.

Thomas Munro, in the thread Decimal64 and Decimal128 (2017)

The other cost is tuple deforming (deform). Fixed-width columns have their offsets calculated once and cached in the table descriptor; variable-width columns, like numeric, break this cache starting from their first occurrence, and every access to a later column requires re-reading the headers of the previous ones, row after row.

This is precisely the point David Rowley has been tackling in Postgres core: commit d28dff3f in PostgreSQL 18 replaced the 104-byte-per-attribute structure with a 16-byte one, gaining up to 25% in OLAP aggregation; the follow-up work, already in PostgreSQL 19, reached 44% in some cases. But that reduces the symptom, it doesn't eliminate the cause: numeric's variable width is still there.

What changes in your schema design

For those modeling databases in Brazil, the practical lesson isn't to abandon numeric, it's to use it where accuracy is worth the cost. Monetary fields, interest rates, and any column subject to regulatory rounding rules still require the exact decimal type: a one-cent difference across millions of transactions is unacceptable, and double precision simply doesn't guarantee that.

The point of attention is bulk aggregation over these columns. If the management report, the analytics dashboard, or the BI pipeline doesn't depend on absolute accuracy down to the last decimal place, it's worth testing double precision on the hot path and reserving numeric for where the value is actually persisted and reconciled. Another design alternative, more work but fully within the SQL standard, is storing monetary values as bigint representing cents, avoiding both binary imprecision and the variable-width cost, as long as the whole application handles the conversion consistently.

It's also worth noting that attempts to speed up exact decimal within Postgres itself, such as the pgDecimal, pgdecimal2, and fixeddecimal extensions, advanced the arithmetic but didn't solve the underlying problem, because the limitation lies in the variable-width representation and pass-by-reference, not just the math. Before changing the type of a production column, the correct path is still to measure the actual execution plan with EXPLAIN (ANALYZE, BUFFERS) on your own data volume: the 25x to 100x gain that shows up in microbenchmarks only matters if it repeats in your workload.