# PostgreSQL varchar vs text: When a Length Limit Is Worth Declaring

> PostgreSQL stores text and varchar(n) the same way and documents no performance difference between them, so the only thing a length limit buys is an error when a value is too long. Use text, or varchar with no limit, unless that error is a business rule you mean. Changing a limit later is cheap in one direction and a table rewrite in the other, and on MySQL the boundary is a byte count, which Schemity's impact analysis helps you see before the migration runs.

Source: https://schemity.com/blog/postgres-varchar-vs-text/

On PostgreSQL, use `text`, or `varchar` with no length, unless a maximum length is a rule your data genuinely has. The two types store strings the same way, so a limit is not a storage or speed optimization: it is a constraint that turns a too-long value into an error, and it is worth declaring only when that error is what you want.

Most string columns never get that decision. The length comes from a habit or a generator, and `varchar(255)` is the most common number in any schema for reasons that have nothing to do with the data. It costs nothing until the first real value is longer than the guess, and then someone has to change a column type on a live table, where the cost depends on which direction the limit moves and which engine you run.

## Varchar vs text Postgres: which should you choose?

Choose `text` by default. The PostgreSQL manual's [character types page](https://www.postgresql.org/docs/current/datatype-character.html) is unusually direct about it: "There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column." It closes the tip with "In most situations `text` or `character varying` should be used instead."

The PostgreSQL wiki's [Don't Do This](https://wiki.postgresql.org/wiki/Don't_Do_This) page lists "Don't use varchar(n) by default" alongside its other common mistakes, and its answer to *when should you?* is the honest one: "If what you want is a text field that will throw an error if you insert too long a string into it, and you don't want to use an explicit check constraint then varchar(n) is a perfectly good type."

So the real question is not which type is better. It is whether this column has a maximum length that belongs to the domain:

- **A limit is a real rule.** A country code is two characters, a phone number in E.164 has at most 15 digits, an external system rejects more than 64. Declare it, as `varchar(n)` or as a check constraint, because the database is the only place every write goes through.
- **A limit is a guess.** A name, an address line, a title, a URL. There is no number that is correct, only one that has not failed yet. Use `text`.

One detail makes the error less absolute than it looks. The same manual page notes that a value longer than the limit raises an error "unless the excess characters are all spaces, in which case the string will be truncated", and that an explicit cast to `character varying(n)` truncates without raising an error. A migration that converts data with a cast can shorten values silently.

## Where does varchar(255) come from?

Much of it traces back to MySQL, where the type choice has real consequences. MySQL's `TEXT` is a different kind of column from `VARCHAR`: according to the [BLOB and TEXT documentation](https://dev.mysql.com/doc/refman/8.4/en/blob.html), an index on it must specify a prefix length, it "cannot have `DEFAULT` values", and a query result containing it that needs an internal temporary table uses one on disk rather than in memory. On MySQL, reaching for `VARCHAR` over `TEXT` is a reasonable default.

The number then comes from byte limits. A `VARCHAR` of up to 255 bytes needs one length byte and anything longer needs two, and InnoDB's [index key prefix limit](https://dev.mysql.com/doc/refman/8.4/en/innodb-limits.html) is 767 bytes for the older `REDUNDANT` and `COMPACT` row formats (3072 for `DYNAMIC` and `COMPRESSED`). With the 3-byte `utf8` character set, 255 characters is the widest column whose full value still fits that 767-byte index limit; with `utf8mb4` at 4 bytes per character the same arithmetic gives 191, which is why that odd number appears in so many schemas too.

Frameworks carried the habit across engines. Django's [CharField](https://docs.djangoproject.com/en/5.2/ref/models/fields/) requires a `max_length` on "all database backends included with Django except PostgreSQL and SQLite", so any model written to run on more than one backend picks a number, and on PostgreSQL that number becomes a constraint nobody chose. It is the same shape as [the ORM quietly choosing timestamp over timestamptz](https://schemity.com/blog/postgres-timestamp-vs-timestamptz/): the physical type is decided by a default, and the schema expresses an opinion no one formed.

## What does changing a length limit cost later?

It depends on the direction, and on MySQL, on bytes rather than characters.

PostgreSQL 9.2's [release notes](https://www.postgresql.org/docs/release/9.2.0/) made the common case cheap: "Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite." Tightening is not covered by that change. Shrinking a limit, or adding one to a `text` column, has to check every existing row, and a single value that is too long fails the statement.

MySQL draws its line at the length byte. The [online DDL documentation](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html) says in-place `ALTER TABLE` "only supports increasing `VARCHAR` column size from 0 to 255 bytes, or from 256 bytes to a greater size", and that "decreasing `VARCHAR` size requires a table copy". Its own example is `VARCHAR(255)` to `VARCHAR(256)` in a single-byte character set, which fails with `ALGORITHM=INPLACE is not supported`. Under `utf8mb4` the boundary arrives much earlier, because `VARCHAR(64)` can already need 256 bytes: widening `VARCHAR(50)` to `VARCHAR(100)` crosses it and copies the table.

| Change | PostgreSQL | MySQL (InnoDB) |
| --- | --- | --- |
| Widen a limit | No table rewrite (since 9.2) | In place, if the length-byte count stays the same; table copy if it crosses 255 bytes |
| Remove a limit (`varchar(n)` to `text` or `varchar`) | No table rewrite (since 9.2) | Changing to `TEXT` is a different column type, not a widening |
| Shrink a limit | Checks every row; fails on any longer value | Table copy (`ALGORITHM=COPY`) |
| Add a limit to an unlimited column | Checks every row; fails on any longer value | Not applicable: `VARCHAR` always has a limit |

"No rewrite" is also not the same as "instant". On 17 February 2020 Jeremy Finzel asked pgsql-general about [a varchar widening that still took a very long time](https://www.postgresql.org/message-id/CAMa1XUiqdpJZHq_bDAsvbEZw%3DRjtOuH3z1xz-Xd27a6b1Pf9xA%40mail.gmail.com): `varchar(20)` to `varchar(100)` on a 100-million-row table on PostgreSQL 9.6. Tom Lane's reply was that the rewrite itself is supposed to be avoided, but he wondered whether "something else that's not being avoided, such as an index rebuild or foreign-key verification" was doing the work. A column that is indexed, or referenced by a foreign key, is exactly the kind whose length gets widened.

## How to decide once, and check the change before it runs

The decision above only helps for tables that do not exist yet. For those, the cheapest fix is to stop retyping it. An [entity template](https://schemity.com/doc/templates/) defines the fields every new entity starts with, so the string convention your team settled on arrives with the table instead of depending on who wrote the migration.

For the schema you already have, the first job is finding the habit. [Search on the canvas](https://schemity.com/doc/erd-canvas/) matches fields on their type as well as their name, using the text the canvas shows, and the `[f` prefix scopes a query to fields. `[f varchar(255)` lists every column carrying the default number, and `[f varchar(191)` finds the ones sized for an old MySQL index limit, which is usually a better starting list than any grep through migration history.

When a limit does need to change, read the cost before running it. Change the type on a connected diagram and press F7: [impact analysis](https://schemity.com/doc/impact-analysis/) reads the same planner that writes the [migration SQL diff](https://schemity.com/doc/migration-sql-diff/), so on PostgreSQL a shrinking `varchar` shows up under "Loses data" and "Rewrites the table", with a row estimate from the catalog, while a widening one raises neither. Either one is refused outright while a view reads the column, which has [its own fix](https://schemity.com/blog/postgres-alter-column-type-used-by-view/). If the change arrives as a migration instead, hand-written SQL or the output of Django's `sqlmigrate`, **Analyse a migration file** reports the same thing for its `ALTER COLUMN ... TYPE` against the connected database without running any of it.

The value of seeing it first is not that the rules are obscure; the table above fits on a screen. It is that the rule depends on facts that live in the database rather than in the migration: how many rows the table holds, whether the column is indexed, which character set it uses. A [migration linter reads the statement](https://schemity.com/blog/schema-linting-vs-migration-linting/), and the statement looks the same on an empty table and a 100-million-row one.

## A length limit is a constraint, so treat it like one

The mistake is not choosing `varchar(n)`. It is choosing it without meaning it, and then paying for the guess on the day the data disagrees. A declared limit belongs with the other rules a schema enforces, next to [the check constraints and lookup tables that decide what a status column may hold](https://schemity.com/blog/postgres-enum-vs-check-constraint-vs-lookup-table/), and it deserves the same scrutiny: write down why the number is what it is, or remove it.

The same instinct applies to every type that gets copied without a decision. A key type [picked by a generator and repeated into every foreign key](https://schemity.com/blog/postgres-uuid-vs-bigint-primary-key/) and a string length picked by habit are the same kind of choice, and the model is where both become visible.

## Frequently asked questions

### Is varchar(255) faster than text in PostgreSQL?

No. The PostgreSQL documentation states there is no performance difference among character(n), varchar(n) and text, apart from a few extra CPU cycles to check the length when storing into a length-constrained column. A 20-character string takes the same space in a text column as in a varchar(255) one.

### Does increasing a varchar length lock the table in PostgreSQL?

Since PostgreSQL 9.2 it no longer requires a table rewrite, and neither does removing the limit. It still takes a lock to change the column definition, and indexes or other objects that depend on the column can make the statement take longer than expected on a large table, so run it with a lock timeout rather than assuming it is instant.

### What happens if I insert a string longer than varchar(n) in PostgreSQL?

The insert fails with an error, unless every excess character is a space, in which case the value is silently truncated to n characters as the SQL standard requires. An explicit cast to varchar(n) also truncates instead of raising an error, which is worth knowing when a migration converts data with a cast.