Re: How to do faster DML

Thank you so much for the clarification.

On Mon, 12 Feb 2024 at 03:40, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:

> 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.
>
> So it looks like the fixed length data type(like integer, float) should be
the first choice while choosing the data type of the attributes
wherever possible, as these are native types. (Like choosing
"Integer/float" over "Numeric", "Char" over "Varchar" etc).
However I do see even in Oracle databases, we have Integer type too, but
it's suggesting(For e.g. in below blog) to rather go with Number types over
Integer and Varchar2 over Char, which is opposite of what we are discussing
here. Is the fixed length data type behaves differently in postgres vs
oracle and thus should be treated differently?

https://www.databasestar.com/oracle-data-types/

From above blog:-

*When to use CHAR: There should be no reason to use the CHAR data type, as
it is similar to a VARCHAR2 and it’s better to be consistent.*
*When to use INTEGER: You should use the NUMBER data type instead.*

Regards
Veem