Making sense of Postgres row sizes

17,924

Solution 1

Calculation of row size is much more complex than that.

Storage is typically partitioned in 8 kB data pages. There is a small fixed overhead per page, possible remainders not big enough to fit another tuple, and more importantly dead rows or a percentage initially reserved with the FILLFACTOR setting.

And there is even more overhead per row (tuple): an item identifier of 4 bytes at the start of the page, the HeapTupleHeader of 23 bytes and alignment padding. The start of the tuple header as well as the start of tuple data are aligned at a multiple of MAXALIGN, which is 8 bytes on a typical 64-bit machine. Some data types require alignment to the next multiple of 2, 4 or 8 bytes.

Quoting the manual on the system table pg_tpye:

typalign is the alignment required when storing a value of this type. It applies to storage on disk as well as most representations of the value inside PostgreSQL. When multiple values are stored consecutively, such as in the representation of a complete row on disk, padding is inserted before a datum of this type so that it begins on the specified boundary. The alignment reference is the beginning of the first datum in the sequence.

Possible values are:

  • c = char alignment, i.e., no alignment needed.

  • 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 by no means all).

Read about the basics in the manual here.

Your example

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

So, one row occupies:

   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

Plus item identifier per tuple in the page header (as pointed out by @A.H. in the comment):

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

So we arrive at the observed 52 bytes.

The calculation pg_relation_size(tbl) / count(*) is a pessimistic estimation. pg_relation_size(tbl) includes bloat (dead rows) and space reserved by fillfactor, as well as overhead per data page and per table. (And we didn't even mention compression for long varlena data in TOAST tables, since it doesn't apply here.)

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

Related:

Solution 2

Each row has metadata associated with it. The correct formula is (assuming naïve alignment):

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

Or roughly 53 bytes. I actually wrote postgresql-varint specifically to help with this problem with this exact use case. You may want to look at a similar post for additional details re: tuple overhead.

Share:
17,924

Related videos on Youtube

Arman
Author by

Arman

Updated on September 14, 2022

Comments

  • Arman
    Arman over 1 year

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

    In reality the row size is pg_relation_size(tbl) / count(*) = 52 bytes. Why?

    (No deletes are done against the table: pg_relation_size(tbl, 'fsm') ~= 0)

  • Arman
    Arman over 11 years
    So then Postgres is not so good for huge tables with very short rows, eg. couple of ints. The 28 bytes overhead will always bloat it. Do you know if Postgres compresses these tables when holding them in cache?
  • A.H.
    A.H. over 11 years
    Isn't there also an addition per-row overhead in each block: The ItemData pointer (4 byte) to the actual tuple header?
  • Erwin Brandstetter
    Erwin Brandstetter over 11 years
    @A.H.: Good point. Not part of the tuple itself, but the ItemData pointer is allocated in the page header per tuple and should explain the difference between 48 bytes in my calculation and the observed 52 bytes of disk space. I added a note to my answer.
  • Erwin Brandstetter
    Erwin Brandstetter over 11 years
    @Arman: Representation of data in RAM needs even a bit more space. So no, no compression there. If you have long character strings they are compressed and possibly "toasted". More about TOAST in the manual here. So, there is a considerable overhead for very small tuples. Still, operations on tables are usually very fast, so don't fall for the temptation to prematurely denormalize your tables. If in doubt, run performance tests.
  • Arman
    Arman over 11 years
    @ErwinBrandstetter: Hm, how do we make sense of this then: A 400M RAM Postgres server, with a 4G database (1 table), serving 30K cpm load with ease. About 50% reads/50% inserts into that 1 table. How can it possibly handle this much load with disk reads/writes?
  • Erwin Brandstetter
    Erwin Brandstetter over 11 years
    The table doesn't need to be cached as a whole. Also, there is a ton of other factors here ..