Last updated: 2026-08-02

VARCHAR Max Length by Database - and How to Choose the Right Length

The maximum VARCHAR length depends on the database system: MySQL allows up to 65,535 bytes (shared by all columns in a row), PostgreSQL about 1 GB, SQL Server 8,000 bytes, and Oracle 4,000 bytes in standard mode (32,767 in extended mode), while SQLite imposes no limit at all. The quick-reference table below gives the direct answer; the rest of this article explains what those numbers mean in practice.

Choosing the right VARCHAR length for database columns is a fundamental design decision that affects storage efficiency, query performance, and data integrity, much like API response length design impacts the overall system quality. This article covers practical guidelines for common field types across major database systems.

Why VARCHAR(255)? - The Myth Behind the Default

The ubiquitous VARCHAR(255) default traces back to an old MySQL limitation. Before MySQL 5.0, the VARCHAR length prefix was stored in a single byte, capping the maximum at 255. MySQL 5.0 switched to a 2-byte length prefix, allowing up to 65,535 bytes - but the "255" convention persisted as a habit long after the technical constraint disappeared.

There is another reason this convention stuck. In MySQL's InnoDB, VARCHAR columns with a declared length of 255 or less use a 1-byte length prefix, while those with 256 or more use a 2-byte prefix. This means there is a 1-byte overhead difference per row between VARCHAR(255) and VARCHAR(256). For a table with 1 million rows, this amounts to only 1 MB - yet the perception that "255 is efficient" became widespread.

VARCHAR Internal Implementation Across RDBMS

Even with the same VARCHAR(100) declaration, the internal storage format and memory allocation behavior differ significantly across database systems. Failing to understand these differences can lead to unexpected performance and storage issues.

MySQL and SQL Server deserve special attention. In these systems, even if a VARCHAR(255) column stores only 10 characters, temporary tables and sort operations allocate 255 × 4 = 1,020 bytes of memory. For tables with many columns, this excessive memory allocation can significantly degrade query performance.

UTF-8 Variable-Length Encoding Impact on VARCHAR(255)

Even in RDBMS that specify VARCHAR length in characters, internal byte limits still apply. Understanding the difference between characters and bytes is essential for proper schema design. UTF-8 is a variable-length encoding where different character types consume different numbers of bytes.

With MySQL's utf8mb4, a VARCHAR(255) column can consume up to 1,020 bytes in the worst case. InnoDB's row size limit is approximately 8,126 bytes (half of a 16 KB page minus headers), so just 8 VARCHAR(255) columns can exceed the row size limit. Use Character Counter to verify byte counts during schema design and prevent unexpected data truncation.

How Many Japanese Characters Fit in VARCHAR(255)?

For CJK text the character-vs-byte question stops being theoretical. The same VARCHAR(255) declaration stores anywhere from 85 to 255 Japanese characters depending on the RDBMS and its length semantics:

SQL Server is the system that most often surprises teams migrating Japanese applications: per Microsoft's official documentation, the n in varchar(n) defines the string size in bytes, and "never defines numbers of characters." A Japanese product-name column planned as "255 characters" but declared VARCHAR(255) under a Japanese collation truncates at roughly half that. The conventional fix is NVARCHAR; since SQL Server 2019, UTF-8 enabled collations (code page 65001) are an alternative that lets VARCHAR hold the full Unicode range at UTF-8 byte costs. Oracle's BYTE default has the same failure mode, which is why explicit CHAR semantics are the standard recommendation for CJK deployments.

DDL Examples: Declaring Columns for Japanese Text

The following CREATE TABLE statements show the safe declaration for a 100-character Japanese product name in each system, followed by the queries that verify how your data is actually being counted.

-- MySQL 8.0: utf8mb4 gives character semantics + full emoji support
CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100)
) CHARACTER SET utf8mb4;

-- PostgreSQL: VARCHAR(n) is always n characters
CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100)
);

-- SQL Server: NVARCHAR for Unicode (or VARCHAR + a UTF-8 collation on 2019+)
CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name NVARCHAR(100)
);

-- Oracle: explicit CHAR semantics so 100 means 100 characters
CREATE TABLE products (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100 CHAR)
);

To confirm whether an existing column is counting characters or bytes, compare the character-length and byte-length functions on real data:

-- MySQL: characters vs. bytes ("こんにちは世界" → 7 vs. 21)
SELECT CHAR_LENGTH(name), LENGTH(name) FROM products;

-- PostgreSQL
SELECT char_length(name), octet_length(name) FROM products;

-- SQL Server (LEN ignores trailing spaces; DATALENGTH returns bytes)
SELECT LEN(name), DATALENGTH(name) FROM products;

-- Oracle
SELECT LENGTH(name), LENGTHB(name) FROM products;

If the two numbers match on Japanese test data, the column is byte-limited with single-byte data only so far - a latent truncation bug waiting for the first real Japanese input. Testing with actual CJK strings (and emoji) before launch catches this class of problem cheaply.

VARCHAR vs TEXT - Performance and Indexing Reality

The common advice "use TEXT for long strings" oversimplifies the situation. The trade-offs between VARCHAR and TEXT vary significantly by RDBMS.

In PostgreSQL, there is virtually no difference between VARCHAR(n) and TEXT - the official documentation even recommends "using TEXT or unconstrained VARCHAR unless you have a specific reason." In MySQL, however, TEXT columns cannot have full indexes, so VARCHAR should be chosen for columns that will be searched.

Emoji (4-Byte UTF-8) Pitfalls with VARCHAR

Modern applications must be designed with the assumption that user input will contain emoji. Emoji consume 4 bytes in UTF-8, but the problems go beyond just byte count.

• MySQL's utf8 vs utf8mb4 trap: MySQL's utf8 (officially utf8mb3) supports only up to 3 bytes per character and cannot store 4-byte emoji. Attempting to INSERT emoji results in an Incorrect string value error. Migration to utf8mb4 is essential, but changing the character set of existing tables requires index rebuilding, which can cause downtime for large tables.
• Compound emoji counting issues: The family emoji "👨‍👩‍👧‍👦" appears as a single character but internally consists of 7 Unicode code points (4 person emoji + 3 ZWJ connectors), consuming 25 bytes in UTF-8. Whether it fits in VARCHAR(10) depends on how the RDBMS counts "characters." MySQL's CHAR_LENGTH() counts this as 7, so it fits in VARCHAR(10), but without understanding the difference between characters and bytes, unexpected truncation can occur.
• Oracle's byte semantics: Oracle's default setting (NLS_LENGTH_SEMANTICS=BYTE) means VARCHAR2(100) allows "100 bytes." A single emoji consumes 4 bytes, so emoji-containing text can store far fewer characters than expected. Use VARCHAR2(100 CHAR) to explicitly specify character semantics, or set NLS_LENGTH_SEMANTICS=CHAR at the session level.

VARCHAR vs. CHAR

Before choosing a string type, understand the fundamental difference between VARCHAR and CHAR.

VARCHAR is the right choice for the vast majority of use cases. Reserve CHAR for truly fixed-length data like ISO country codes (CHAR(2)) or currency codes (CHAR(3)). Note that in MySQL's InnoDB, CHAR columns are also stored as variable-length (trailing spaces are removed), so the storage difference is minimal.

Common VARCHAR Design Mistakes and Correction Costs

• Defaulting every column to VARCHAR(255): This lazy default weakens application-level validation and risks storing unexpectedly long data. In MySQL's InnoDB, the row size limit is approximately 8,126 bytes, so just 8 utf8mb4 VARCHAR(255) columns can exceed this limit. Fixing this requires ALTER TABLE on every column - for a 100 GB table, this can take several hours of lock time.
• Confusing characters with bytes: VARCHAR(100) means "100 characters" in MySQL, but in Oracle's default configuration (NLS_LENGTH_SEMANTICS=BYTE) it means "100 bytes." Since a single CJK character consumes 3 bytes in UTF-8, Oracle's VARCHAR2(100) can only store about 33 CJK characters.
• Setting VARCHAR too short: Designing a name column as VARCHAR(20) with the assumption "20 characters is enough" frequently fails when encountering long foreign names or names with middle names. Extending VARCHAR length later via ALTER TABLE can trigger an internal table copy in MySQL's online DDL, causing hours of downtime for large tables.
• API validation mismatch: If the API allows 500 characters but the DB column is VARCHAR(200), INSERT operations will either silently truncate data or throw errors (in MySQL's strict mode). Always align API response length design with database column lengths.

VARCHAR Length Changes During Migration - Risks and Safe Procedures

ALTER TABLE operations to change VARCHAR length in production behave very differently across RDBMS. Understanding the internal mechanics is essential for safe execution.

Safe procedure for changing VARCHAR length in MySQL:

1. Check existing data maximum length: SELECT MAX(CHAR_LENGTH(column_name)) FROM table_name;
2. Determine if the change crosses the 255-byte boundary (crossing triggers a table rebuild).
3. For large tables (1M+ rows), use pt-online-schema-change or gh-ost for zero-downtime changes.
4. After the change, run ANALYZE TABLE to update optimizer statistics.

Recommended Lengths by Field Type

1. When a specification or standard (RFC, ISO, etc.) defines a maximum, match it.
2. When no specification exists, add 20–50% margin to the maximum observed data length.
3. Plan for future growth while avoiding excessively large values. In MySQL, be mindful of the 255-byte boundary.
4. Implement the same character limit validation on the application side to prevent DB mismatches.
5. For internationalized columns, design based on the longest language/culture, not just your primary locale.

Conclusion

VARCHAR length design requires a holistic consideration of data characteristics, encoding, and RDBMS internal implementation. Instead of defaulting to 255, set lengths with clear rationale to improve storage efficiency, query performance, and data quality. In MySQL, pay special attention to the 255-byte prefix boundary, temporary table memory allocation, and index size impact. Use Character Counter to measure real-world data lengths when designing your schema.