0

When inserting a BYTEA string in PostgreSQL the documentation says that such a string will occupy 1 or 4 bytes plus byte string length.

The question that I can't seem to find the answer to is what determines whether 1 or 4 bytes are added and is this effect consistent or will it vary depending on some factor or other?

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
Alex
  • 355
  • 1
  • 3
  • 5

1 Answers1

1

I believe the answer is in this header comment of heaptuple.c:

* Before Postgres 8.3 varlenas always had a 4-byte length header, and
* therefore always needed 4-byte alignment (at least).  This wasted space
* for short varlenas, for example CHAR(1) took 5 bytes and could need up to
* 3 additional padding bytes for alignment.
*
* Now, a short varlena (up to 126 data bytes) is reduced to a 1-byte header
* and we don't align it.  To hide this from datatype-specific functions that
* don't want to deal with it, such a datum is considered "toasted" and will
* be expanded back to the normal 4-byte-header format by pg_detoast_datum.
Josh Kupershmidt
  • 2,436
  • 14
  • 18
  • So am I correct in the understanding that libpq will always return the byte string as the length + 4 bytes but internally it may either be stored either as 1 or 4 extra bytes? – Alex Nov 21 '14 at 09:33
  • The documentation page you linked to is talking about the on-disk "storage size" of the bytea data type. Toasting and tuple header overhead should be invisible at the libpq level. The data representation that you get back from e.g. PQexec is generally well-described by the libpq documentation (and pay attention to your setting of bytea_output, of course). – Josh Kupershmidt Nov 21 '14 at 14:28