Choosing the Right String Column Data Type: CHAR vs VARCHAR vs TEXT

Relational databases typically offer three primary data types for storing text data: CHAR, VARCHAR, and TEXT. While they all store strings, engineers often misunderstand how they function under the hood, how database engines persist them to disk, and the trade-offs regarding performance and storage.

This guide breaks down how CHAR, VARCHAR, and TEXT work, demystifies inline versus off-page storage mechanics, and provides a clear decision framework for production schema design.

---

1. Overview of String Data Types

CHAR (Fixed-Length Character)

CHAR(n) defines a column that holds a fixed length of up to n characters (note: characters, not bytes, which is critical when dealing with multi-byte encodings like UTF-8).

CREATE TABLE users (
    country_code CHAR(2)
);

• Padding Behavior: If you insert a string shorter than n characters, the database pads the remaining space with blank spaces to the right to reach width n. For example, storing 'om' in a CHAR(5) column results in 'om '.
• Trailing Space Handling: If you insert a string longer than n characters where the excess characters are spaces, the database truncates the trailing spaces to fit n.
• Length Violation: If you attempt to store non-space characters exceeding n (e.g., storing a 6-character string in CHAR(5)), the engine throws an error.
• The Trade-Off: Because of the right-padding behavior, CHAR wastes disk storage and memory buffers on padding when strings are shorter than n. In modern database engines, CHAR offers no tangible performance advantage over VARCHAR.

VARCHAR (Variable-Length Character)

VARCHAR(n) stores variable-length strings up to a defined maximum of n characters.

CREATE TABLE users (
    email VARCHAR(255)
);

• No Blank Padding: If you insert 'om' into a VARCHAR(5) column, it persists exactly 'om' without space padding.
• Length Prefix: Under the hood, the engine stores the actual string along with a small prefix (typically 1 to 2 bytes) indicating the length of the string.
• Length Violation: Trying to insert a string longer than n characters raises an error.
• Upper Limits: In PostgreSQL, a VARCHAR without an explicit limit can hold up to 1 GB (the same upper limit as TEXT), while in MySQL, the maximum row size limit restricts VARCHAR to roughly 65,535 bytes across the entire row.

TEXT (Variable Unlimited Character)

TEXT allows storing variable-length strings intended for large textual content.

CREATE TABLE articles (
    content TEXT
);

• No Max Length Argument: Unlike VARCHAR(n), TEXT does not require a length boundary at definition time (though maximum engine limits still apply, e.g., 1 GB in PostgreSQL).
• Constraint Limitations: Because TEXT columns can store massive payloads, database engines often restrict them from being used directly in foreign key constraints or full b-tree index keys without specifying prefix lengths (engine-dependent).

---

2. Common Storage Misconceptions

Two pervasive misconceptions exist in database schema design:

1. “TEXT is always stored out-of-line on separate disk pages, while VARCHAR is always stored inline.”
2. “CHAR is substantially faster than VARCHAR because it has fixed length.”

Both assumptions are false in modern relational engines.

Inline vs. Off-Page (Overflow) Storage

Relational databases store records inside fixed-size pages (e.g., 8 KB in PostgreSQL, 16 KB in MySQL InnoDB). To maximize query throughput and buffer cache hit rates, engines attempt to keep row lengths as short as possible.

+-------------------------------------------------------------------------+
|                           DATABASE PAGE                                 |
|                                                                         |
|  +-------------------------------------------------------------------+  |
|  | Row: [ ID | Name (inline) | Large Col Pointer ------------------+ |  |
|  +-----------------------------------------------------------------|-+  |
+--------------------------------------------------------------------|----+
                                                                     |
                                   +---------------------------------+ 
                                   v
                    +-----------------------------+
                    | OVERFLOW DISK PAGE(S)       |
                    | (TOAST / LOB / Extent Page) |
                    | [ Full Text Payload Data ]  |
                    +-----------------------------+

Here is how engines handle data placement:

• Small Strings in TEXT Columns: If a TEXT column contains a short string (e.g., a 15-character description), the database stores it inline inside the primary row tuple on the main page. It does not force an off-page pointer overhead.
• Large Strings in VARCHAR Columns: If you define a VARCHAR(65535) or a PostgreSQL VARCHAR with thousands of characters, the database will not force it to remain inline if it exceeds page limits. Instead, the engine automatically pushes it to secondary overflow storage (such as PostgreSQL’s TOAST mechanism or InnoDB’s off-page overflow).

The Cost of Overflow Pages

When a column value is pushed to an overflow page:

• The row tuple stores a compressed pointer referencing the overflow chunk.
• Reading that column requires the engine to load secondary disk pages into the buffer pool, increasing read I/O and query latency.

Therefore, off-page behavior is dictated by data size, not by whether you labeled the column VARCHAR or TEXT.

---

3. Performance Reality in Modern Databases

In modern versions of PostgreSQL and MySQL:

• Execution Speed: There is virtually no performance difference between CHAR, VARCHAR, and TEXT for retrieval and query execution.
• Storage Overhead: CHAR can consume significantly more disk space and buffer memory due to blank-padding.
• CPU Cycles: VARCHAR and TEXT require negligible CPU overhead to read the length-byte prefix and unpack data, which is virtually unnoticeable compared to disk I/O and memory cache lookups.

---

4. Decision Matrix: When to Choose What

Given that TEXT is flexible and does not suffer arbitrary performance penalties, why not use TEXT everywhere?

Criteria	CHAR	VARCHAR(n)	TEXT
Length Behavior	Fixed (space-padded)	Variable up to $n$	Variable up to engine max
Inline Storage	Yes	Yes (if fits in page)	Yes (if fits in page)
Out-of-Line Storage	No	Yes (if exceeds threshold)	Yes (if exceeds threshold)
Schema Constraints	Enforces exact width	Enforces maximum limit $n$	Unbounded
Foreign Key Support	Supported	Supported	Disallowed or heavily limited
Exact-Match B-Tree Indexing	Supported	Fully supported	Prefix indexing often required

When to Use VARCHAR(n)

1. Enforcing Schema-Level Boundaries: If business rules dictate that a field must never exceed a certain length (e.g., usernames $\le 30$, postal codes $\le 10$, bio summaries $\le 160$), VARCHAR(n) rejects over-length entries at the database tier.
2. Indexed Columns and Foreign Keys: Relational databases optimize B-Tree indexes for bounded key sizes. Columns participating in foreign keys or frequent exact-match index scans (WHERE email = ?) should use VARCHAR.
3. Predictable Row Size Allocations: For query planners that allocate fixed sorting memory based on column data definitions, explicit bounds help optimize memory usage.

When to Use TEXT

1. Unbounded or Variable Payloads: Articles, blog posts, markdown content, raw JSON blobs, and audit logs where string length cannot be predicted.
2. Non-Indexed Attributes: Columns that are rarely filtered by exact equality and do not participate in foreign keys.

When to Use CHAR(n)

• Virtually never, except for strictly standardized, non-variable codes where every row has the identical character count (e.g., 2-character ISO country codes US, IN, or fixed-size SHA-256 hash strings) and where space padding will never trigger.

---

5. Summary and Architectural Takeaways

1. CHAR is rarely optimal: It wastes space through blank-padding and offers no performance benefit over VARCHAR.
2. Inline vs. External storage depends on payload size: Both VARCHAR and TEXT store small payloads inline and large payloads in overflow pages (e.g., PostgreSQL TOAST).
3. Choose VARCHAR(n) for bounded, indexed attributes: Use it when you need schema-level size enforcement, foreign key constraints, or primary search indices.
4. Choose TEXT for freeform data: Use it for large descriptions, content bodies, and arbitrary strings where strict length caps are unnecessary.