Making sense of Postgres row sizes

by

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:

More Related Contents:

  1. Calculating and saving space in PostgreSQL
  2. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  3. Convert hex in text representation to decimal number
  4. Store common query as column?
  5. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  6. How to convert primary key from integer to serial?
  7. Do nullable columns occupy additional space in PostgreSQL?
  8. How to set value of composite variable field using dynamic SQL
  9. CONSTRAINT to check values from a remotely related table (via join etc.)
  10. Is there a way to disable function overloading in Postgres
  11. Generate series of dates – using date type as input
  12. Grant all on a specific schema in the db to a group role in PostgreSQL
  13. Does a Postgres UNIQUE constraint imply an index?
  14. Difference between timestamps with/without time zone in PostgreSQL
  15. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  16. PostgreSQL – set a default cell value according to another cell value
  17. How to change PostgreSQL user password?
  18. How to get the count of current month Sunday’s in psql?
  19. Custom SERIAL / autoincrement per group of values
  20. What’s the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?
  21. Can I automatically create a table in PostgreSQL from a csv file with headers?
  22. PostgreSQL query very slow with limit 1
  23. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  24. psql: FATAL: database “” does not exist
  25. Omitting the double quote to do query on PostgreSQL
  26. Storing Images in PostgreSQL
  27. Psql could not connect to server: No such file or directory, 5432 error?
  28. No function matches the given name and argument types
  29. Maximum characters in labels (table names, columns etc)
  30. Docker – Postgres and pgAdmin 4 : Connection refused

Leave a Comment