On 2024-02-11 22:23:58 +0530, veem v wrote:
> On Sun, 11 Feb 2024 at 19:02, Peter J. Holzer <hjp-pgsql@xxxxxx> wrote:
>
> > Similarly for Number/Numeric data type.
>
> Number in Oracle and numeric in PostgreSQL are variable length types.
> But in PostgreSQL you also have a lot of fixed length numeric types
> (from boolean to bigint as well as float4 and float8) and you would
> normally prefer those over numeric (unless you really need a decimal or
> very long type). So padding is something you would encounter in a
> typical PostgreSQL database while it just wouldn't happen in a typical
> Oracle database.
>
>
>
> When you said "you would normally prefer those over numeric " I was thinking
> the opposite. As you mentioned integer is a fixed length data type and will
> occupy 4 bytes whether you store 15 or 99999999.But in case of variable
> length type like Number or numeric , it will resize itself based on the actual
> data, So is there any downside of going with the variable length data type like
> Numeric, Varchar type always for defining the data elements?
The fixed width types are those that the CPU can directly process:
Integers with 16, 32 and 64 bits, floating point numbers with 32 and 64
bits. The CPU can read and write them with a single memory access, it
can do arithmetic with a single instruction, etc.
Number/Numeric are not native types on any CPU. To read them the CPU
needs several memory accesses (probably one per byte unless you get
really clever) and then it can't do any calculations with them
directly, instead it has run a subroutine which does operations on
little chunks and then puts those chunks together again - basically the
same as you do when you're doing long addition or multiplication on
paper. So that's not very efficient.
Also the space savings aren't that great and probably even negative: In
my example the length of a numeric type with at most 10 digits varied
between 3 and 7 bytes, Only for values between -99 and +99 is this (3
bytes) actually shorter, for other values it's the same length or
longer. So you would only save space if most of your values are in that
±99 range. But not if all of them are, because then you could simply use
a smallint (Range -32768..32767) in PostgreSQL and save another byte.
Finally - and I'm probably biased in this as I learned programming 40
years ago - to me the native types feel more natural than product
specific variable-length decimal types.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature
