Re: How to do faster DML

On 2024-02-16 12:10:20 +0530, veem v wrote:
>
> On Fri, 16 Feb 2024 at 06:04, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
>
> On 2024-02-15 16:51:56 -0700, David G. Johnston wrote:
> > On Thu, Feb 15, 2024 at 4:31 PM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
> >     On 2024-02-14 22:55:01 -0700, David G. Johnston wrote:
> >     > On Tuesday, February 13, 2024, veem v <veema0000(at)gmail(dot)com> wrote:
> >     >
> >     >     float data types rather than numeric. This will give better
> >     >     performance.
> >     >
> >     >
> >     > Only use an inexact floating-point data type if you truly
> >     > understand what you are getting yourself into.  Quickly getting
> >     > the wrong answer isn’t tolerable solution.
> >
> >     Do NOT assume that a decimal type (even if it can grow to ridiculuous
> >     lengths like PostgreSQL's numeric) is exact in the mathematical
> sense.
> >     It isn't. It cannot represent almost all real numbers
> >
> >
>
>
> Thank You.
>
> So it looks like the use cases where we don't need precision or decimal point
> values to be stored in postgres , integer data type is the way to go without a
> doubt.
>
> However in cases of precision is required, as you all mentioned there are
> certain issues(rounding error etc) with "Float" data type and considering a
> normal developers usage point of view, it should be the Numeric type which we
> should use.

You misunderstood. My point was that these rounding errors also happen
with numeric, and if you want to avoid or minimize them you have to
understand what you are doing. For a hilarious example of what happens
if you don't understand that, see
https://en.wikipedia.org/wiki/Vancouver_Stock_Exchange#Rounding_errors_on_its_Index_price

I basically see two reasons to use numeric:

* Your numbers are amounts of money. Accountants are neither mathematicians
nor engineers, and numeric mimics the way they think. So the results
will be wrong in the correct way ;-)
* You need lots (more than 15 or 18) digits.

For anything else there is a good chance that float8 or int8 is a better
choice, because those types behave much more consistently.

Of course there are valid reasons to use other types (including numeric)
but the point is that each type has real pros and cons and false
arguments like "numeric is an exact type and float isn't" is not
helpful. That said, "I don't understand binary numbers" might be a valid
reason.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"