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 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 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, 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 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 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: 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 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 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.

“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: 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 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 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 reads the same planner that writes the 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. 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, 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, 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 and a string length picked by habit are the same kind of choice, and the model is where both become visible.