Character Types

Introduction

Overhead

When you store a text string on disk, PostgreSQL cannot simply write those raw letters. If it only did that, upon reading the data back, Postgres would not know:

How many bytes long is this string?
Where does it end so it can read the next data column?
Is this a short string, a long string, or compressed?

Therefore, Postgres must spend a few extra bytes right at the beginning of the string to record this management information. This additional consumed capacity is called Overhead, which is the accompanying management cost.

Header Structure

PostgreSQL uses a common format named varlena (Variable-length array) to store all string types (TEXT, VARCHAR, BYTEA, JSONB...). This Overhead portion varies depending on the length of the data string:

Very short strings (Under 127 bytes)
- Overhead: 1 byte.
- Mechanism: Postgres uses this first 1 byte (8 bits) to store 7 bits for recording the actual length of the string, and 1 bit as a flag signaling that this is a short string.
- Example: You save the word Hi (2 bytes), Postgres spends 1 + 2 = 3 bytes.
Medium / long strings (From 127 bytes to a few Kbytes)
- Overhead: 4 bytes.
- Mechanism: When the string exceeds 127 bytes, 1 byte is no longer enough to record the length. Postgres switches to using a 4 bytes header to store the string length (supporting lengths up to 1 GB).
- Example: You save an article of 500 bytes, Postgres spends 4 + 500 = 504 bytes.
Very large strings (Compressed or moved to the TOAST table)
Overhead: 18 bytes containing Pointer Header.
Mechanism: If the string is thousands of bytes long (for example 100 KB), Postgres will compress that string and move it to be stored in an auxiliary table called the TOAST table. In the main table, that data cell only stores a Pointer containing the actual data location information. This pointer takes about 18 bytes.

Character Types

Next, we will learn about Character Types as follows:

VARCHAR(N): Varying Character
- Stores text strings with variable lengths, but limited by a parameter N.
- If you store a 5-character string in a VARCHAR(100) column, Postgres only stores those exact 5 characters along with a bit of overhead to record the length, wasting no storage capacity at all.
- Storage size: Actual string size + (1 to 4 bytes overhead).
CHAR(n): character
- Always forces the string to have exactly N characters.
- If you store a string shorter than N, Postgres will automatically pad spaces at the end so the string reaches N characters.
- When doing SELECT data, Postgres will automatically trim these trailing spaces.
- Very wasteful of capacity if the actual data length is shorter than N.
- Storage size is fixed to the exact capacity for n characters + overhead.
TEXT
- Stores text strings of any length without needing to declare a limit beforehand.
- In PostgreSQL, TEXT is the standard and most optimized string data type.
- Unlike MySQL or SQL Server (where TEXT has worse performance than VARCHAR), in Postgres, TEXT and VARCHAR have identical storage structures (TOAST) and performance.
- Storage size: Actual string size + overhead.

Characteristics

Whether using CHAR or VARCHAR, the maximum declarable value N is 10,485,760 characters.
The maximum storage size for all 3 is 1GB for the entire data cell.
Absolutely restrict the use of CHAR(N) because this is a legacy holdover from the SQL standard. Its automatic padding of extra spaces often causes issues when comparing strings (for example: 'admin'::char(10) will not equal admin).
When designing a Database, you should use TEXT as the default for most text columns (name, address, description, email...).
- If you want to limit the length (for example: maximum length of 200 characters), the best way in Postgres is to use TEXT combined with a CHECK constraint.
- With this approach, it supports Constraint very flexibly, and in the future, if there is a need to change, you only need to delete the old constraint and create a new one, rather than changing the data type of the entire column as with VARCHAR.

Detail

Let us create a table as follows:

CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    col_char CHAR(10),
    col_varchar VARCHAR(10),
    col_text TEXT,
    col_text_length TEXT CHECK (char_length(col_text_length) <= 10)
);

Use the following statements to verify:

-- Statement 1
INSERT INTO test(col_char) VALUES ('1');
INSERT INTO test(col_char) VALUES ('0123456789');
INSERT INTO test(col_char) VALUES ('01234567891');

-- Statement 2
INSERT INTO test(col_varchar) VALUES ('1');
INSERT INTO test(col_varchar) VALUES ('0123456789');
INSERT INTO test(col_varchar) VALUES ('01234567891');

-- Statement 3
INSERT INTO test(col_text) VALUES ('1');
INSERT INTO test(col_text) VALUES ('01234567891');

-- Statement 4
INSERT INTO test(col_text_length) VALUES ('1');
INSERT INTO test(col_text_length) VALUES ('0123456789');
INSERT INTO test(col_text_length) VALUES ('01234567891');

-- Query 5
SELECT 
  col_text,
  length(col_text) AS char_count,
  pg_column_size(col_text) AS size_in_bytes,
  octet_length(col_text) AS string_bytes
FROM test;

Statement 1: Using CHAR

Statement 2: Using VARCHAR

Statement 3: Using TEXT

Statement 4: Using TEXT with CHECK is better than using CHAR/VARCHAR because when length changes are needed later, you only need to update the constraint without changing the data type

Query 5: Checking the size of each value

To check actual storage capacity, use the pg_column_size function, which returns the actual number of bytes that the column value consumes on disk (including PostgreSQL overhead).
To check string character length, use the length function, which returns the number of characters in the column data (applies to CHAR, VARCHAR, TEXT types).
To check string byte count, use the octet_length function, which returns the byte length of the character string (differing from pg_column_size as it only counts string bytes according to encoding like UTF-8 without counting system overhead).

When using CHAR, regardless of how many characters are saved, the size remains the same