Subject: Data Type Size Calculation

From: PG Doc comments form <[email-masked]>
Date: Fri, 11 Feb 2022 20:12:08 +0000

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.
:(

Troy.
#

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 14 Feb 2022 13:51:05 -0500

> 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  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

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

---

From: Troy Frericks <[email-masked]>
Date: Tue, 15 Feb 2022 12:50:05 -0600

For now, yes... I'm suggesting that the documentation be completed by
adding a few sentences few extra sentences.
Troy.
#

---

From: Jian He <[email-masked]>
Date: Wed, 16 Feb 2022 09:54:12 +0530

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 │ Name  │ Type  │ Owner  │ Persistence │ Access method │ Size
│ Description
────────┼───────┼───────┼────────┼─────────────┼───────────────┼───────┼─────────────
 public │ testb │ table │ depesz │ permanent   │ heap          │ 35 MB │

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 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, that is part of
> 8 byte block, you have to add operation to zero the other 32 bits.
>