The numeric type has existed in Postgres for more than 25 years. Over that time, it has undergone many optimisations and refinements. And yet operations on this type - aggregation in particular - still look rather inefficient.Since the PostgreSQL community always fixes problems with more or less simple and obvious solutions, let's find out whether numeric has structural limitations that prevent the DBMS operators for this type from working faster.To make the investigation more visual, let's also trace how DuckDB solves the same tasks - luckily, AI agents have made source-code analysis and testing much easier.One thing to keep in mind here: DuckDB is built exclusively for OLAP queries. That means, as I noted earlier, it places weaker demands on the exactness of operations - and, as you will see below, the DuckDB developer team actively uses this to make query execution more efficient.
Contents
The Teaser
To avoid making empty claims, let me draw your attention to the two EXPLAINs below. The first one groups the rows of a table by a set of 12 numeric columns.The second is exactly the same query, but the columns have type double precision - for the sake of the test, we have neglected exactness.Almost a threefold speedup! Note that the double-precision variant had to scan more disk pages (125 thousand versus 111 thousand). However, aggregation over numeric data requires 9 times as much memory. What is the reason for such a negative effect on performance after 25+ years of optimising this type? Can it be reduced somehow, or removed altogether? Let's dig into the fundamentals and try to understand what is going on.It would seem the question is about decimal arithmetic. However, three independent attempts to make a fast decimal type as an extension - pgDecimal by Pavel Stehule, pgdecimal2 by Feng Tian, and fixeddecimal by 2ndQuadrant - have stalled, although each of them did speed up the arithmetic. So it is not only about arithmetic, and the situation is a bit more complicated.Four Decisions Postgres Pays For
Before diving into a complex construction, it is useful to see the problem as a whole.Every well-known DBMS has an exact decimal type, and those that have a fast one do so in a very similar way: the value is stored as an ordinary integer, and the decimal point lives separately in the column description. This is (probably) how DECIMAL works in SQL Server, DuckDB and ClickHouse, and how decimal works in Arrow and Parquet. PostgreSQL went another way, deliberately taking the following key decisions:- The representation does not depend on the declaration.
- Everyone else picks the width of the value from the declared precision: a narrow number takes two to four bytes, a wide one eight or sixteen. In PostgreSQL, width is a property of the type, not of the column, so numeric must be variable-length.
- The scale lives in the value, not in the column description.
- That is why 1.5 and 1.50 differ even in a numeric column for which no scale is specified.
- The scale of a result is computed from the data, not in advance.
- How many digits a division produces depends on the numbers themselves, not only on their types.
- A flexible upper bound on precision.
- Formally, numeric does have a limit - 131072 digits before the decimal point and 16383 after - but no practical value comes anywhere near it: room for the result is allocated by what came out, not by the declaration, and getting an overflow error at runtime is practically impossible.
How an Exact Decimal Type is Built Inside
The numeric implementation in PostgreSQL
General-purpose processors count in binary, and 0.1 in binary is an infinite repeating fraction - exactly like 1/3 in decimal. So double precision stores not 0.1 but the nearest representable binary number, and the famous 0.1 + 0.2 gives 0.30000000000000004.However, money (meters of cable in a warehouse, utility bills) is counted in decimal form: cents, interest rates, and rounding rules are written down in decimal digits. This is exactly what an exact decimal type is for - so that 0.1 is exactly 0.1, and rounding happens at the digit the regulation talks about.Hence the first decision: the digits stored are decimal. By itself it dictates neither the width of the value nor the position of the decimal point - DECIMAL in DuckDB is decimal too, and fixed-width at the same time - but this is where the design of numeric starts.Digits, but not one at a time. It would be naive to spend a byte per digit (although an artifact of such a representation can be found in the PostgreSQL code): a byte can represent a value up to 255, and we would write 0–9 in it. PostgreSQL takes two bytes per group and stores a number from 0 to 9999 in it. The result is positional notation in base 10000 - the same as the familiar base-10 notation, only there are ten thousand "digits" instead of ten.Why exactly 10000? It comes from the long-multiplication algorithm: the product of two "digits" has to fit into an int, and purely arithmetically any even base below sqrt(INT_MAX) ≈ 46341 would do. Among those, a power of ten is chosen - with it, printing and rounding to a decimal digit stay trivial - and the largest such power is exactly 10000.Finding where the fraction starts. Storing the position of the decimal point as "so many digits from the start" is inconvenient: very large and very small numbers would accumulate long chains of zeros. Instead, a weight is stored - the position of the very first "digit", counted in powers of 10000. The value of the number is restored by the following formula:For example, 123456.00 looks like this:The benefit shows on small numbers: 0.0000000012 is one "digit" 1200 with weight -3, not a pile of zeros.How to print it: dscale. Here things get unfamiliar. The numbers 1.5 and 1.50 are equal but print differently, while their "digits" are the same. So the difference has to be stored separately - that is what dscale is for, meaning "how many digits after the decimal point to show":Byte for byte, these are different values, yet comparison is obliged to treat them as equal. Various difficulties stem from this. For example, comparison and hashing must ignore dscale, while printing must remember it.Note that the above holds for a value declared as plain numeric. In a numeric(15,2) column, the cast on write sets dscale = 2 for both values, and they become identical byte for byte.Sign and special values. There is no separate place for the sign - it hides in the two high bits of the header, together with the format flag:Special values have no digits at all - only the two-byte header remains (three bytes in a tuple, six as a standalone value). And as a bonus: zeros at both ends of the number are dropped, so 1.0000 is stored as a single "digit" 1 with dscale = 4.Packing. There is a so-called packed storage format for a numeric value. If the number of digits is small (no more than ~62), a one-byte length header is used, and the value is stored without alignment. Longer numbers use the standard four-byte header. Each particular value can be in either of these two formats. The operators working on numeric expect the standard header, so every function must be ready at any moment to "unpack" an incoming number with a one-byte header into the standard representation.Clearly, in most practical applications the numbers are short. So the unpacking is usually performed on every number - and that means, among other things, a dynamic allocation of extra memory plus a copy.The positive side effect is this: small numeric values are sometimes cheaper to store than even bigint - their "packed" representation can be noticeably shorter than eight bytes, as pg_column_size shows:As a result, a numeric value is a small self-sufficient structure: a format flag, a sign, a weight, a scale and a chain of base-10000 "digits". It can tell everything about itself - both what it equals and how to print it - and therefore it can sit in a column about which nothing is declared except the word numeric.On the one hand, this speaks of reliability and the ability to control data integrity; on the other, of extra costs for storage and processing. To understand how it could have been done differently, let's look at the approach of DuckDB, which, because of its different application area, can afford to be more aggressive in implementing an exact decimal type.The DECIMAL Implementation in DuckDB
Starting from the same point as PostgreSQL - exact decimal values are needed - DuckDB went down the road of storing decimal numbers as integers. DECIMAL(15,2) with the value 123456.00 is stored as the integer 12345600. And that's it. No base-10000 digits, no weight, no dscale:The decimal point exists only at the moment of printing. Here is the wording from the MonetDB documentation, which is where this construction came to DuckDB from:The scale thus becomes a property of the column type. Let's check on a live DuckDB:The distinction is that PostgreSQL keeps the scale value in the “dscale” inside the value itself, and DuckDB keeps it in the type name. Different spellings of the same number are different types, not different bytes. The consequence shows up immediately once you put them into one column:So the column has one type, hence one scale and one printed form. The same three values in a numeric column would keep dscale 1, 2 and 3 and print differently.This way, the optimal representation format for a number can be chosen in advance - and DuckDB makes active use of this, implementing a ladder of carriers: INT16, INT32, INT64, INT128 for precision up to 4, 9, 18 and 38 digits. The ladder is described in the documentation and defined in the code by a single set of specialisations, decimal.hpp: The choice is made once, at query planning time; after that, a monomorphic function over a concrete integer runs in the loop. The width is visible from the outside as well: when exporting to Parquet, DECIMAL(15,2) is written with the physical type INT64, whereas DECIMAL(20,2) is already a byte array. The 18 and 38 boundaries in Parquet and DuckDB coincide not by accident: both systems run into the same machine integers.As a result, the number is stored in an ordinary byte representation. And although such a number may take more space than a packed numeric, the basic arithmetic (addition and multiplication) for it is much simpler.However, a fixed format means a hard ceiling on the maximum value. And, apparently for this reason - to reach the industry-standard 38 digits - DuckDB has no special values NaN, +Infinity, −Infinity. Going over the 38-digit ceiling means a runtime error.Pass by Value or by Reference
Inside PostgreSQL, every value travels as a Datum - a machine word, eight bytes on a 64-bit platform. If a type fits into these eight bytes, it is passed by value: a bigint lives right inside the Datum, that is, in fact in a processor register. If it does not fit, a pointer is passed. The type becomes pass-by-reference.A numeric value is always passed by reference. So the result of every arithmetic operation on numeric values has to be stored somewhere, which implies allocating memory per operation. Let's compare what this means for addition:Moreover, even if we constrained the numeric implementation but kept the very idea of the approach - a large scale and a flexible bound on the intermediate result - the value would still not fit into eight bytes and would not start being passed by value. Thomas Munro noticed exactly this obstacle in 2017, discussing DECFLOAT in the thread "Decimal64 and Decimal128": DuckDB chose a different path. The physical carrier is selected by the declared width of the column.At precision up to 18 digits, the value lands in a machine integer: no Datum, no pointer, no palloc. How cheap this is is evident from a measurement on 20 million rows (M4 Pro, single thread): SUM over DECIMAL(18,2) - 8.7 ms; over BIGINT - 8.5 ms. The ratio is 1.02: there is no surcharge for being decimal at all: the scale lives in the catalogue, and at runtime this is an ordinary int64.However, already on DECIMAL(19,2), on exactly the same data, the benchmark shows ~880 ms. A 100-fold jump at the 18/19-digit boundary. So the payment is not for decimal logic but for the value's width. In this case, DuckDB is fast mostly because it uses int64 under the hood.In PostgreSQL, the length of a type and the way it is passed (by reference or by value) are characteristics of the type, written in the system catalogue, not properties of the column. So a storage ladder inside numeric cannot be built in the current architecture at all.Tuple Deforming
Before comparing anything, the values have to be taken out of the row. This operation is called deform, and for numeric it is more expensive too.A row in PostgreSQL consists of a header, a NULL bitmap, and the column values one after another, with no separators. To reach the twentieth column you need to know its offset from the start. If all columns are fixed-width, the offset is computed arithmetically and cached in the table descriptor - once, for its entire lifetime:If the width is variable, this doesn't work. The rule is written right in the code that builds the table descriptor: cache offsets only up to the first column of non-fixed length. The comment there says exactly that: "don't cache offsets beyond fixed-width attributes". And this is precisely the numeric case. The offset cache breaks off at the first such column, and every offset after it has to be recomputed, reading the header of every preceding value:This, by the way, is the aspect that is being actively worked on in core right now, although from the other side. David Rowley spent two development cycles on deform: commit d28dff3f (PostgreSQL 18) replaced the 104-byte FormData_pg_attribute in the table descriptor with a 16-byte CompactAttribute and gave "~10% TPS on OLAP aggregation over a 16-column table, up to ~25%", because fewer cache lines are touched during deforming. The follow-up - "More speedups for tuple deformation" - was committed to PostgreSQL 19: 21% on average, up to 44%. Tellingly, half of the test cases in that benchmark differ in exactly one thing: whether the first column is INT or TEXT. So the effect of a variable-length column on performance is noticed in the community.But this does not remove the cause itself. Variable length is a direct consequence of the decision, back in 1998, that numeric must hold numbers of arbitrary precision. In other words, by making numeric a fixed-length type, we could make tuple traversal a little cheaper.DuckDB has no notion of tuple deforming at all, since storage is columnar. A value is addressed by an index into an array, and no offsets need to be computed. However, they have some analogue of this problem too. With compression enabled (and it is enabled by default), DuckDB shows a large difference between scanning DECIMAL(18,2) and DECIMAL(19,2) columns. On the query:the average execution time is 5–7 ms for the "narrow" and ~380 ms for the "wide" storage variant. If compression is switched off (SET force_compression='uncompressed'), this gap disappears. Since there is practically no arithmetic here, the only thing that can play a role is unpacking int128, which turns out to be a fairly expensive operation.So in both engines, delivering the value to the operation costs more than the operation itself. For Postgres it is deform, for DuckDB it is decompression; what they share is that the payment is for width and storage format, not for arithmetic.On the other hand, the integer representation opens access to fast operations, and the result comes out counter-intuitive - worth keeping in mind every time someone advises "take float, it's faster". On top of that, decimal columns in DuckDB get BitPacking, while DOUBLE gets the more expensive ALP.So "exactness costs resources" is rather a property of a particular implementation than a property of exact decimal arithmetic.The Scale of an Intermediate Result
We have covered the rules of packing, representation and storage. However, in a query plan, the original values are used only as input to operations whose precision and scale can differ significantly from the input. This, in turn, determines the computational cost and the amount of extra memory needed to store intermediate results. How do our two subjects handle this question?Managing the precision of intermediate results in numeric
With bigint everything is simple: the result of an operation on two bigints is either a bigint or an overflow error. There is no third option, so there is exactly one check - the processor's overflow flag.With numeric this won't do:- multiplying two numbers of 32 significant digits each gives up to 64 digits;
- division may not terminate at all - 1/3 in decimal notation is infinite.
