Last updated: 2026-09-04

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, a column whose stored values can never exceed 255 bytes uses a 1-byte length prefix, while a column that can exceed that spends 2 bytes. The detail almost everyone misses is that this boundary is measured in bytes, not characters. With a single-byte character set, VARCHAR(255) and VARCHAR(256) really do differ by one byte of overhead per row. With utf8mb4, a single character can take up to 4 bytes, so VARCHAR(64) already reaches 256 bytes at its maximum and already pays the 2-byte prefix. For a schema that stores Japanese text or emoji, the premise that "255 keeps you on the 1-byte side" simply does not hold. The belief that "255 is efficient" is a leftover from the days when character sets were single-byte.

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.

What matters here is whether the declared length turns into a runtime cost. SQL Server plans a query around the declared length, so a VARCHAR(255) column that only ever holds 10 characters can still produce an oversized memory grant for a sort or a hash join, and the query runs less efficiently than the data warrants. MySQL behaved the same way through the 5.x era, when internal temporary tables were built on the MEMORY engine and padded to a fixed width. Since 8.0 the default TempTable engine keeps VARCHAR variable-length, so that specific penalty is largely gone. From 8.0 onward, the reason to keep declared lengths tight is less about saving memory and more about refusing data that should never have been stored in the first place.

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 roughly 8,000 bytes on the default 16 KB page (a little under half the page), and that limit applies to the in-row portion, excluding variable-length columns that have been pushed off-page. So eight VARCHAR(255) columns side by side still give you a table you can create; what happens instead is that as values grow they are moved to overflow pages, and each row whose values no longer fit in-row costs an extra page access on every read. The practical question is not "will it fit" but "will it fit in-row". Use Character Counter to check the byte count of real data during schema design and prevent unexpected 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.

The PostgreSQL documentation is explicit that there is no performance difference among character varying, text and character(n), and that character(n) is in fact the slowest of the three because its blank padding costs extra storage; for most situations it advises using text or character varying. Read that carefully: a length limit is not a performance device, it is a constraint that asserts what a valid value looks like. In MySQL, by contrast, a TEXT column cannot be indexed without a prefix length, so VARCHAR is the right choice for columns you intend to search or sort on.

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. A utf8mb4 VARCHAR(255) occupies up to 1,020 bytes at its maximum, so a table lined with such columns accumulates values that no longer fit in-row, and read costs degrade little by little. Tightening every column with ALTER TABLE after the fact grows heavier in proportion to the data volume, and on a large table that leaves only a narrow window in which the work can be run.
• 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 an extended outage on a large table.
• 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, the point at which long values spill to off-page storage, and the impact on index size. Use Character Counter to measure real-world data lengths when designing your schema.