Right, let’s talk about text. This is the one you’ll use 95% of the time you need to store anything longer than a simple word or two. It’s PostgreSQL’s workhorse for storing strings of practically any length you can throw at it. Forget about pre-defining a maximum length; that’s the whole point. It’s a variable-length type, meaning it only takes up the space it needs (plus a tiny bit of overhead), which is exactly what you want most of the time.

Think of it like a dynamically allocated buffer. You’re not reserving a fixed block of memory for the worst-case scenario, like you do with varchar(n). You’re just saying, “Here, hold this for me. It’s… well, it’s this big.” This is incredibly efficient and flexible.

Why You Almost Always Want text

I’ll say this outright: for any free-form text field—names, descriptions, comments, JSON blobs, the contents of your unfinished novel—you should default to text. Don’t get cute and try to guess a maximum length for a varchar field. Why? Because you will almost certainly be wrong someday.

Let’s say you define a varchar(100) for a “product name.” Seems reasonable, right? Then marketing launches the “Super Deluxe Mega Ultimate Edition 3000 with Extra Sparkles and a Side of Fries,” and your application starts throwing errors because it’s 72 characters too long. You now have a production issue that requires a database migration to fix. If you’d just used text, it would have just worked. The storage engine would have handled it without a second thought. Using an arbitrary length limit on varchar is often premature optimization, and it creates a hard, artificial ceiling for no good reason.

Performance-wise, for most operations, there’s virtually no difference between text and varchar(n). The same functions (length(), upper(), substring()) work on both. They’re stored in exactly the same way behind the scenes.

-- Just use text. Seriously.
CREATE TABLE book_reviews (
    id serial PRIMARY KEY,
    book_title varchar(200), -- Okay, maybe a title has a sane max length.
    review_content text,     -- This is where the essay goes. No limits.
    author_rant text         -- Could be one word, could be ten thousand.
);

INSERT INTO book_reviews (book_title, review_content, author_rant)
VALUES (
    'A Very Long Title Indeed: The Extended Director''s Cut',
    'A masterpiece...', -- understatement
    'This book changed my life! ' || repeat('Very ', 1000) || 'inspiring!'
);

-- This works perfectly, storing all 4000+ characters of that "rant"
SELECT id, length(author_rant) AS rant_length FROM book_reviews;

The One “Gotcha”: Size Limits (Or Lack Thereof)

Okay, “unlimited” is a bit of a marketing term. The theoretical maximum size for a single text value is 1 GB. But let’s be honest: if you’re trying to store a single string that’s a gigabyte in size in a database field, you’re almost certainly doing something wrong. That’s what the filesystem or object storage is for. The database should store a reference to it, not the thing itself. In practical, everyday terms, for storing human-readable text, it’s unlimited.

Indexing and Performance

You can index text columns just like any other. The most common type is a B-tree index, but if you’re doing full-text search, you’ll dive into the world of GIN indexes and the tsvector type, which is built on top of text.

A crucial point here is that if you want to create a uniqueness constraint or index on a text column, you need to be aware of the potential size. While it’s technically possible, creating a unique index on a column that could potentially contain megabytes of text in each row is a… questionable choice. The index would be enormous. This is one of the few times where slapping a reasonable maximum length on the application side (before it hits the DB) makes sense, not for storage efficiency, but for operational sanity.

Best Practices and When to Use varchar(n)

So when would you use varchar(n) over text? It boils down to two main scenarios:

  1. Enforcing Domain Logic at the Database Level: When a maximum length is a genuine, hard business rule. For example, a two-letter country code must be exactly 2 characters. A varchar(2) column makes this rule explicit in the schema and will enforce it relentlessly. This is a good thing.
  2. Slight Storage Optimization for Very Short Strings: For very short, fixed-length codes (like a state abbreviation), using char(2) can be microscopically more efficient than text or varchar(2) because of the lack of a size overhead. But we’re talking bytes. Don’t do this unless you’ve proven you need to.

The takeaway? Start with text. Only use varchar(n) when you have a specific, well-justified reason to impose a hard limit. Your future self, who doesn’t have to run a migration at 3 AM to increase a column size, will thank you.