Re: How to do faster DML - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: How to do faster DML
Date
Msg-id 20240216214533.m74rqmybygu4hjzm@hjp.at
Whole thread Raw
In response to Re: How to do faster DML  (veem v <veema0000@gmail.com>)
List pgsql-general
On 2024-02-16 12:10:20 +0530, veem v wrote:
>
> On Fri, 16 Feb 2024 at 06:04, Peter J. Holzer <hjp-pgsql@hjp.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@hjp.at> wrote:
>     >     On 2024-02-14 22:55:01 -0700, David G. Johnston wrote:
>     >     > On Tuesday, February 13, 2024, veem v <veema0000@gmail.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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

pgsql-general by date:

Previous
From: Greg Sabino Mullane
Date:
Subject: Re: Encryption Options
Next
From: Karsten Hilbert
Date:
Subject: "reverse" (?) UPSERT -- how to ?