Skip to content
BytePatterns

CHAR vs VARCHAR vs TEXT: Padding, Length Limits and Speed

6 min readBytePatterns

CHAR vs VARCHAR vs TEXT: fixed length padded with spaces, variable with a limit, or no limit at all. Trailing spaces, MySQL index limits, SQLite affinity.

CHAR vs VARCHAR vs TEXT in one line: CHAR(n) stores exactly n characters, padding shorter values with spaces; VARCHAR(n) stores what you give it, up to a limit of n; TEXT stores strings of any length with no declared limit. The differences that matter in practice are trailing spaces, length errors and index rules, not speed: PostgreSQL's manual says there is no performance difference among the three apart from padding and length checks, and recommends text or varchar. MySQL stores and indexes them differently, and SQLite ignores the declared length entirely. Vendor behaviour is quoted from the manuals, as of October 2026; the SQLite behaviour is run live in Python.

TypeStoresTrailing spaces
CHAR(n)exactly n characters, space-paddedpadding ignored in comparisons
VARCHAR(n)up to n characters, as givenkept and significant (PostgreSQL)
TEXTany length, as givenkept and significant (PostgreSQL)

The problem it solves

Every string column forces a choice, and the old advice, "CHAR for fixed-width codes because it is faster", is now mostly folklore. The real consequences show up later: a WHERE that misses rows because of a trailing space, an insert that fails because a name is one character over the limit, or a MySQL index that cannot be created on a TEXT column. Knowing what each type actually does to the value decides which of those bugs you get.

The intuition

Think about what the engine stores and what it compares.

  • CHAR(n) pads. Store 'W1' in a CHAR(5) and PostgreSQL keeps 'W1 '. Because the padding is not data, the manual says trailing spaces are semantically insignificant and disregarded when two character values are compared, and they are removed when the value is converted to another string type. CHAR with no length means CHAR(1).
  • VARCHAR(n) checks a limit. It stores the value as given, and trailing spaces count in comparisons and in LIKE. A value longer than n is an error, unless the excess is all spaces, which are cut off silently. An explicit cast to varchar(n) truncates instead of failing. VARCHAR with no length accepts any length.
  • TEXT has no limit. In PostgreSQL it behaves like an unlimited varchar, and the manual suggests it over an arbitrary length limit.

MySQL differs in storage and rules. CHAR is 0 to 255 characters, padded on write and stripped of trailing spaces on read. VARCHAR stores a 1-byte or 2-byte length prefix plus the data, keeps trailing spaces, and shares a 65,535-byte maximum row size with the other columns. In strict mode an over-long value is an error. Comparisons ignore trailing spaces only under a PAD SPACE collation; Unicode collations based on UCA 9.0.0 and later, including the default for utf8mb4, are NO PAD, so 'a' and 'a ' differ. TEXT columns need a prefix length to be indexed, and accept a default only as an expression in parentheses, such as DEFAULT ('none').

SQLite takes a different path: type affinity. Any declared type containing CHAR, CLOB or TEXT gets TEXT affinity, and the number in parentheses is ignored.

When to use which

  • PostgreSQL: text by default; varchar(n) when the length limit is a real business rule, such as a two-letter country code.
  • MySQL: VARCHAR(n) for anything you filter, sort or index; CHAR(n) for genuinely fixed-width values such as hashes; TEXT for long bodies you rarely search by prefix.
  • SQLite: TEXT, with a CHECK (length(col) <= n) if you need a limit, or a STRICT table for type checks.
  • Avoid CHAR for values whose length varies, unless you want the padding rules.

Watch it run

The animation is the lesson's apiary. WHERE mites > 40 AND queen_seen = 0 is tested on each hive in turn: Willow fails the first half, Chalk fails the AND, Beacon is nowhere near, and only Long Mead passes both. Then the NULL case: mites = NULL matches nothing, and IS NULL is the question you meant. Every test there is a comparison, and for string columns the type decides what equality means. Under CHAR rules, name = 'Willow' also matches 'Willow '; in a PostgreSQL varchar or text column, or a MySQL NO PAD collation, it does not.

WHERE and Filtering

Step 1 of 10

WHERE runs a yes-or-no test on every candidate row and keeps only the passes.

The same interactive animation as the lesson — step through it with the controls.

The code

SQLite first. The lesson's hives, with a CHAR(3) code, a VARCHAR(5) name and a TEXT note. The declared lengths are stored but not enforced:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE hive (code CHAR(3), name VARCHAR(5), notes TEXT);
  INSERT INTO hive VALUES ('W1', 'Long Mead', 'swarmed in May');
""")
print(db.execute("SELECT code, length(code), name, length(name) FROM hive").fetchall())
# [('W1', 2, 'Long Mead', 9)]      <- no padding, and 9 characters in a VARCHAR(5)
print([(c[1], c[2]) for c in db.execute("PRAGMA table_info(hive)")])
# [('code', 'CHAR(3)'), ('name', 'VARCHAR(5)'), ('notes', 'TEXT')]
db.execute("INSERT INTO hive VALUES (12, 345, 6.5)")            # numbers into text columns
print(db.execute("SELECT typeof(code), typeof(name), typeof(notes) FROM hive "
                 "WHERE rowid = 2").fetchall())
# [('text', 'text', 'text')]       <- TEXT affinity converted them

print(db.execute("SELECT 'W1' = 'W1 ', 'W1' = 'W1 ' COLLATE RTRIM").fetchall())
# [(0, 1)]                         <- BINARY counts the space, RTRIM ignores it

try:
    db.execute("CREATE TABLE hive2 (name VARCHAR(5)) STRICT")
except sqlite3.OperationalError as e:
    print(e)                       # unknown datatype for hive2.name: "VARCHAR(5)"
db.execute("CREATE TABLE hive3 (name TEXT CHECK (length(name) <= 5))")
try:
    db.execute("INSERT INTO hive3 VALUES ('Long Mead')")
except sqlite3.IntegrityError as e:
    print(e)                       # CHECK constraint failed: length(name) <= 5

'Long Mead' went into a VARCHAR(5) whole, 'W1' went into a CHAR(3) unpadded, and numbers were stored as text, all because of TEXT affinity. SQLite's default comparison counts trailing spaces; its RTRIM collation ignores them, which is the CHAR rule. A STRICT table rejects VARCHAR(5) outright, since it only accepts a few type names, and a CHECK is how you get a real limit.

Now a small model of PostgreSQL's storage rules, and a seeded check that the CHAR comparison rule matches SQLite's RTRIM collation:

import random

def store(value, kind, n=None):
    """Toy model of PostgreSQL's rules for storing into char(n), varchar(n) and text."""
    if kind == "text" or n is None:
        return value
    if len(value) > n:
        if value[n:].strip(" "):
            raise ValueError(f"value too long for type {kind}({n})")
        value = value[:n]                     # excess characters were all spaces
    return value.ljust(n) if kind == "char" else value

def char_eq(a, b):                            # char(n) = char(n): trailing spaces ignored
    return a.rstrip(" ") == b.rstrip(" ")

print(repr(store("W1", "char", 5)), repr(store("W1", "varchar", 5)))   # 'W1   ' 'W1'
print(repr(store("good      ", "varchar", 5)))                          # 'good '
try:
    store("Long Mead", "varchar", 5)
except ValueError as e:
    print(e)                                  # value too long for type varchar(5)
print(char_eq(store("W1", "char", 5), "W1"), store("W1", "char", 5) == "W1")   # True False

# Cross-check the comparison rule against SQLite's RTRIM collation
random.seed(11)
ok = True
for _ in range(3000):
    a, b = ("".join(random.choice("ab ") for _ in range(random.randint(0, 5))) for _ in "ab")
    got = db.execute("SELECT ? = ? COLLATE RTRIM", (a, b)).fetchone()[0]
    ok = ok and bool(got) == char_eq(a, b)
print(ok)                                     # True

The padded 'W1 ' equals 'W1' under the CHAR rule and not as a plain string, which is exactly the trap when a CHAR value leaves the database and is compared in application code. 'good ' fitted into varchar(5) because only spaces were cut, the same example PostgreSQL's manual gives. Over 3,000 random strings of letters and spaces, the rule agreed with SQLite's RTRIM collation every time.

The complexity

  • Length check: O(length) per write into a VARCHAR(n) or CHAR(n) column, the "few extra CPU cycles" PostgreSQL mentions.
  • Storage: CHAR(n) uses n characters per row however short the value; VARCHAR and TEXT use the value's length plus a small length header.
  • Indexing: the same B-tree cost for all three in PostgreSQL; in MySQL a TEXT index covers only its declared prefix.

Where it goes wrong

  • CHAR values compared outside the database. The padding comes back with the value in PostgreSQL, and 'W1 ' is not 'W1' in Python.
  • Duplicate-key errors on trailing spaces in MySQL. Where comparisons ignore padding, a unique index treats 'a' and 'a ' as the same value.
  • Over-long values in MySQL without strict mode. They are truncated to fit with only a warning, so data is lost quietly instead of failing the insert.
  • Trusting VARCHAR(255) in SQLite. Nothing enforces it; add a CHECK.
  • Picking CHAR for speed in PostgreSQL. The manual says it is usually the slowest of the three, because of its extra storage.

When it shows up in interviews

Usually as "what is the difference between CHAR and VARCHAR?", with "and TEXT?" as the follow-up, or inside schema design when you pick column types. Comparison surprises tie in with SQL NULL in WHERE, and key columns, where trailing spaces can break uniqueness, are in primary key vs unique key vs foreign key. Filtering on string columns is in the WHERE and Filtering lesson.

How to say it in an interview

"CHAR(n) is fixed length: shorter values are padded with spaces, and the padding is ignored in comparisons. VARCHAR(n) stores the value as given with a maximum length, and TEXT has no declared limit. In PostgreSQL there is no performance difference apart from padding and the length check, so I use text, or varchar(n) when the limit is a real rule. In MySQL I prefer VARCHAR for anything indexed, because TEXT needs a prefix length to be indexed and accepts only expression defaults. And in SQLite the length is ignored, so I enforce it with a CHECK."

Frequently asked questions

What is the difference between CHAR and VARCHAR?

CHAR(n) always stores n characters, padding shorter values with spaces, and ignores that padding in comparisons. VARCHAR(n) stores the value as given, up to n characters, and keeps trailing spaces. Both reject longer values in PostgreSQL and in MySQL strict mode.

Is VARCHAR faster than TEXT?

In PostgreSQL, no: the manual says there is no performance difference beyond the length check, and recommends text or varchar. In MySQL, VARCHAR is usually the better choice for indexed or frequently selected columns, because a TEXT index must declare a prefix length and a TEXT default must be an expression.

Does SQLite enforce VARCHAR length?

No. SQLite ignores the number in VARCHAR(255) and gives the column TEXT affinity, so longer strings are stored whole. Enforce a limit with CHECK (length(col) <= 255); a STRICT table checks the type but rejects VARCHAR as a type name.

Sources