0

When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?

1

It's not that simple.

The n in varchar(n) is just the upper limit of allowed characters (not bytes!). Only the actual string is stored, not padded to the maximum allowed size. That's opposed to the largely outdated, blank-padded data type char(n), which always stores the maximum length.

Each character can occupy one or more bytes, depending on the character and the encoding. In common UTF-8 encoding it's 1-4 bytes, and only basic ASCII characters occupy a single byte. You speak of "printable characters", but that's insignificant. Printable characters can still occupy 1-4 bytes.

a_horse's quote from the manual refers to storage of varlena types (including varchar) "on disk" (Hardly any disks in use any more. And the same applies to temporary tables in RAM. So "in tabular storage", really.). For up to 126 bytes (not characters!), the overhead is reduced to a single byte. The source code speaks of "packed" format. And there is no additional alignment padding in this format: nominal "int" alignment for varlena types is violated.

But you speak of "in memory". In RAM, once the value has been extracted from the stored row, the overhead is always 4 bytes - no "packed" format. And all varlena types require "int" alignment, which may add 1 to 3 bytes of padding.

And NULL storage has its own rules. See:

So, the basic storage requirement for these strings in varchar(5) is:

"on disk"

NULL ... 1 bit (typically)
'' ... 1 byte
'foo' ... 4 bytes
'Motor' ... 6 bytes
'Motör' ... 7 bytes
'Motörhead' ... still 7 bytes, as an explicit cast to varchar(5) truncates to 'Motör'

"in memory"

NULL ... ?
'' ... 4 bytes
'foo' ... 7 bytes
'Motor' ... 9 bytes
'Motör' ... 10 bytes
'Motörhead' ... still 10 bytes, as an explicit cast to varchar(5) truncates to 'Motör'

Plus, possibly, alignment padding.

See:

4
  • I always thought, the data was represented in-memory essentially in the same way as on disk. Postgres simply reads the block from disk and keeps it (more or less) "as-is" in memory. – a_horse_with_no_name Jul 28 '20 at 6:54
  • Is the same logic applied when I use for example bytea(38) to store a binary number ? And how does it affect the query performance – d4bbi Jul 28 '20 at 8:28
  • @d4bbi: Basically yes, except that there is not bytea(n) type. Just bytea. Performance .. depends on many things, starting with performance of what exactly. That's no matter for comments. Ask a new question with specifics in case. – Erwin Brandstetter Jul 29 '20 at 2:18
  • @a_horse_with_no_name: It's much like "toasting", on a smaller scale. I added another link. – Erwin Brandstetter Jul 29 '20 at 2:21
1

Quote from the manual

If the string to be stored is shorter than the declared length [...] values of type character varying will simply store the shorter string.
...
The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string

(character varying is the same as varchar)

So, it will take as much space as needed.

2
  • 1
    So a 5 printable characters will take exactly 6 bytes in memory – d4bbi Jul 27 '20 at 14:37
  • 1
    @d4bbi: Well, mostly no. I added an answer. – Erwin Brandstetter Jul 28 '20 at 1:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.