Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nKBrl-0002P4-7c for pgsql-docs@arkaria.postgresql.org; Wed, 16 Feb 2022 04:24:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nKBrj-0007bx-V1 for pgsql-docs@arkaria.postgresql.org; Wed, 16 Feb 2022 04:24:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nKBrj-0007bo-Jj for pgsql-docs@lists.postgresql.org; Wed, 16 Feb 2022 04:24:31 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nKBre-0003K5-Vh for pgsql-docs@lists.postgresql.org; Wed, 16 Feb 2022 04:24:29 +0000 Received: by mail-yb1-xb35.google.com with SMTP id p19so2366261ybc.6 for ; Tue, 15 Feb 2022 20:24:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=FHD5whDVSRQUcWjNrcO0J0eeCumiKJuGNaC41XTNPSI=; b=kEV81e+/GEoG2/9koeIDnYIOKZlv5ZApK9czZDtnq+etVzlRl/owDuYmb3v/8FcPRd tktrC32wb1wKutAJJJvXdLQpAVoHG433dXHbo0QpNMH+V9tP1cJtugVSXY4ZRHVzEAnH lV0sp/bxpWWiDdpCaLo+kfdrZi9EaNQ4J616guCXHsW7wgn3DnPi9Fi99p5E89D/FKwf KNm63zY/CwR6I3hMGeEgC4DvOwNzylS6Lbc1GDs2plVfAL4ee/WfiFvTAJKPI0vNjvPW 4SkFnuimfgW+vUtWA9au/t5mGkXFV3YmAgOyeH/X11TzsTMJAEQUAh+jCS1v2RqOdsrz jK+w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=FHD5whDVSRQUcWjNrcO0J0eeCumiKJuGNaC41XTNPSI=; b=bHN8Z9RyvnhlAoois1ITzUzcShstPHJtF3G1JhVMMn2MYD791IKuH03c8jMyz9rN74 s2BbERkEtH7k82HxeP3mXnk+B4Iz1O/rQZdNXKWzYjiqf960NrFi8YP9N6+LBb9aMFSJ A5qzKVC4ntNJckcZUehFdPolUdjrMDbVUJFidjfBbs5uCePpE7AdIo9K8a2zUxbrgeyy ecV+nIlqbfvLSFVOEUVxFgPPery7NKwqGdgHR2GT86CKVu5dRX4kvSlpuRXhz6oyd6zW OH/ftwbZm15+ek1/VMUCW8M1Rod4AEbBZXY161PizC7Rv/hyqK+39oMEqUELeJqyKDuL 30vQ== X-Gm-Message-State: AOAM530PJYTF3rKsKUDcVumKRkD2lSmwTLdbmgjhGPw7UH7OHbotVsDO VQItG99AOyu2T4auU8u1vU6HjpCyrvgXezRjXJc= X-Google-Smtp-Source: ABdhPJx5Xww8ZcwhH2kX+43q2Wup2pz1S8jLU255z4ZC9ZRI+76NWTt2AauyuxWcY/s8LAO2lbzbYvgwsbB8Eywfuk4= X-Received: by 2002:a81:3d89:0:b0:2d0:76d9:c410 with SMTP id k131-20020a813d89000000b002d076d9c410mr867222ywa.291.1644985464317; Tue, 15 Feb 2022 20:24:24 -0800 (PST) MIME-Version: 1.0 References: <164461032874.9678.9708057254834166884@wrigleys.postgresql.org> In-Reply-To: From: Jian He Date: Wed, 16 Feb 2022 09:54:12 +0530 Message-ID: Subject: Re: Data Type Size Calculation To: Troy Frericks Cc: Bruce Momjian , troy@frericks.us, pgsql-docs@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000042e9ad05d81b06ae" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000042e9ad05d81b06ae Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable https://www.depesz.com/2022/02/13/how-much-disk-space-you-can-save-by-using= -int4-int-instead-of-int8-bigint/ Hope this link is useful. create table testb as select 'true'::bool as b from generate_series(1,1000000) i;SELECT 1000000 $ \dt+ testb List of relations Schema =E2=94=82 Name =E2=94=82 Type =E2=94=82 Owner =E2=94=82 Persiste= nce =E2=94=82 Access method =E2=94=82 Size =E2=94=82 Description =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80 public =E2=94=82 testb =E2=94=82 table =E2=94=82 depesz =E2=94=82 permanen= t =E2=94=82 heap =E2=94=82 35 MB =E2=94=82 Why is that, though? From what I gather the answer is: performance. I don't > know low-level details, but based on what I understand, processors proces= s > data in arch-dependent block sizes. 64bit processor works on 64 bits. And > this means that if you want to do something on int4 value, that is part o= f > 8 byte block, you have to add operation to zero the other 32 bits. > On Wed, Feb 16, 2022 at 12:26 AM Troy Frericks wrote: > For now, yes... I'm suggesting that the documentation be completed by > adding a few sentences few extra sentences. > Troy. > # > > > On Mon, Feb 14, 2022, 12:51 Bruce Momjian wrote: > >> On Fri, Feb 11, 2022 at 08:12:08PM +0000, PG Doc comments form wrote: >> > The following documentation comment has been logged on the website: >> > >> > Page: https://www.postgresql.org/docs/13/datatype-numeric.html >> > Description: >> > >> > > The actual storage requirement is two bytes for each group of four >> decimal >> > digits, plus three to eight bytes overhead. >> > >> > Please describe what 'overhead' means. >> > >> > I'd like to be able to calculate the data size of NUMBER(19,4). I can >> > calculate 2 bytes per 4 digits... with 19 digits, I have 5 groups of 4 >> > digits, >> > >> > so the data length I seek is 5 bytes + overhead... then I'm left >> hanging. >> > :( >> >> Well, you can create it and then call pg_column_size(): >> >> CREATE TABLE test (x NUMERIC(19,4)); >> >> SELECT pg_column_size('test.x'); >> pg_column_size >> ---------------- >> 7 >> >> If you want more details, you will need to look at the source code. >> >> -- >> Bruce Momjian https://momjian.us >> EDB https://enterprisedb.com >> >> If only the physical world exists, free will is an illusion. >> >> --00000000000042e9ad05d81b06ae Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 
https://www.depesz.com/2022/02/1=
3/how-much-disk-space-you-can-save-by-using-int4-int-instead-of-int8-bigint=
/
Hope this link is useful. 

 create table =
testb as select<=
/span> 'true'::bool as b from generate_seri=
es(1,1000000) i;
SELECT 1000000
=C2=A0
$ \dt+ testb
                                  List of =
relations
 Schema =E2=94=82 Name  =E2=94=82 Type  =
=E2=94=82 Owner  =E2=94=82 Persistence =E2=94=82 Access method =E2=94=82 Size  =E2=94=82 Description=20
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80
 public =E2=94=82 testb =E2=94=82 table =
=E2=94=82 depesz =E2=94=82 permanent   =E2=94=82 heap          =E2=94=82 35 MB =E2=94=82

Why is that, though? From what I gather the answer is: performance. I=20 don't know low-level details, but based on what I understand, processor= s process data in arch-dependent block sizes. 64bit processor works on 64 bits. And this means that if you want to do something on int4 value,=20 that is part of 8 byte block, you have to add operation to zero the=20 other 32 bits.

On Wed, Feb 16, 2022 at 12:26 AM Troy Frericks <troy.frericks@gmail.com> wrote= :
For now, yes... I'm suggesting that the docum= entation be completed by adding a few sentences few extra sentences.
Troy.
#


On Mon, Feb 14, 2022, 12:51 Br= uce Momjian <bruce= @momjian.us> wrote:
On Fri, Feb 11, 2022 at 08:12:08PM +0000, PG Doc comments form w= rote:
> The following documentation comment has been logged on the website: >
> Page: https://www.postgresql= .org/docs/13/datatype-numeric.html
> Description:
>
> > The actual storage requirement is two bytes for each group of fou= r decimal
> digits, plus three to eight bytes overhead.
>
> Please describe what 'overhead' means.
>
> I'd like to be able to calculate the data size of NUMBER(19,4). I = can
> calculate 2 bytes per 4 digits... with 19 digits, I have 5 groups of 4=
> digits,
>
> so the data length I seek is 5 bytes + overhead... then I'm left h= anging.
> :(

Well, you can create it and then call pg_column_size():

=C2=A0 =C2=A0 =C2=A0 =C2=A0 CREATE TABLE test (x NUMERIC(19,4));

=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT pg_column_size('test.x');
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0pg_column_size
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----------------
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 7

If you want more details, you will need to look at the source code.

--
=C2=A0 Bruce Momjian=C2=A0 <bruce@momjian.us>=C2=A0 =C2=A0 =C2=A0 = =C2=A0 https://momjian.us
=C2=A0 EDB=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 https://enterprisedb.com

=C2=A0 If only the physical world exists, free will is an illusion.

--00000000000042e9ad05d81b06ae--