Understanding Postgres String Sizes

Question

I got a large (> 100M rows) Postgres table with the structure {integer, integer, integer, timestamp without time zone}. I expected the line size to be 3 * integer + 1 * timestamp = 3 * 4 + 1 * 8 = 20 bytes.

In fact, the string size is pg_relation_size(tbl) / count(*) = 52 bytes. Why?

(No table deletions: pg_relation_size(tbl, 'fsm') ~ = 0)

Answer 1

Calculating the row size is much more complicated.

Storage is usually paginated. . There is a small fixed invoice on the page, the possible balances are not large enough to fit the other tuples, and, more importantly, the dead lines or percentage originally reserved with the FILLFACTOR parameter.

More importantly, there is overhead per row (tuple). 23 bytes HeapTupleHeader and alignment padding . The beginning of the tuple header as well as the beginning of the tuple data are aligned with the short value MAXALIGN , which is 8 bytes on a typical 64-bit machine. Some data types require matching with the next multiple of 2, 4, or 8 bytes.

Note the manual in the pg_tpye system table:

typalign is the alignment required when storing a value of this type. It is used for storage on disk, as well as for most representations of the value inside PostgreSQL. When storing several values ​​sequentially, for example, in representing a complete line to disk, a gasket is inserted in front of a database of this type so that it starts from the specified boundary. The alignment reference is the start of the first reference point in the sequence.

Possible values:

• c = char alignment i.e. no alignment required.

• s = short alignment (2 bytes on most machines).

• i = int alignment (4 bytes on most machines).

• d = double alignment (8 bytes on many machines, but not all).

Check out the basic information in the manual here .

Your example

This results in 4 padding bytes after the 3 integer columns, because the timestamp column requires double alignment and needs to start at the next multiple of 8 bytes.

So one line takes up:

  23 -- heaptupleheader + 1 -- padding or NULL bitmap + 12 -- 3 * integer (no alignment padding here) + 4 -- padding after 3rd integer + 8 -- timestamp + 0 -- no padding since tuple ends at multiple of MAXALIGN 

Finally, in the page header there is an ItemData pointer (a pointer to an element) for each tuple (as noted by @AH in the comment ), which takes 4 bytes:

  + 4 -- item pointer in page header ------ = 52 bytes 

So, we come to the observed 52 bytes .

The calculation of pg_relation_size(tbl) / count(*) is a pessimistic estimate. pg_relation_size(tbl) includes bloat (dead lines) and FILLFACTOR reserved space, as well as the overhead for each data page and table. (And we did not even mention compression for long varlena data in TOAST tables , since this is not applicable here.)

You can install the pgstattuple add-on module and call SELECT * FROM pgstattuple('tbl_name'); for more information on table size and tuple.

Related answer:

• Page Layout Size

Answer 2

Each row has metadata associated with it. The correct formula (subject to equalization of naivety):

 3 * 4 + 1 * 8 == your data 24 bytes == row overhead total size per row: 23 + 20 

Or about 53 bytes. I actually wrote postgresql-varint to help with this problem in this particular use case. You may want to view a similar post for more information re: overhead.