PostgreSQL gives you a huge toolbox of data types, which is great — until you accidentally pick the wrong one and end up fighting performance issues, storage problems, or subtle bugs that show up months later. In this article, we’ll go over the four data type pairs developers misuse the most, and I’ll show you the simple rules that will keep your schemas clean and your data correct.

1. text vs varchar — Stop Pretending They’re Different

In PostgreSQL, text and varchar (without a length) are the same thing. They perform the same, store the same, and behave the same internally. The only time varchar matters is when you specify a length, like varchar(2) or varchar(50). But most people add lengths for no real reason.

When people misuse it:

• Using varchar(255) because that's what MySQL tutorials did in 2010.
• Adding arbitrary length limits that don’t reflect real business rules.
• Assuming varchar is faster or more optimized than text.

The truth is simple: use text unless you have a real reason to enforce a max length. If the business actually needs a limit (state codes, short identifiers, etc.), then varchar(n) makes sense. Otherwise, text keeps everything simpler.

CREATE TABLE customer (
  email TEXT NOT NULL,
  state_code VARCHAR(2)  -- this is a real constraint
);

2. json vs jsonb — You Probably Want jsonb

PostgreSQL supports both json and jsonb, and this confuses a lot of people. json stores the raw text exactly as you send it. jsonb parses and stores it in a binary structure that’s faster to query and index.

Use jsonb when:

• You want to query inside JSON fields.
• You want to index JSON paths.
• You want duplicates removed and keys normalized.

Use json when:

• You need to preserve whitespace, formatting, or key order.
• You store JSON but never query it.

For 95% of use cases, jsonb is the correct default. It’s the workhorse type and integrates beautifully with PostgreSQL indexing.

CREATE TABLE event_log (
  event_id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

-- Example: index a field inside JSON
CREATE INDEX idx_event_payload_user_id
  ON event_log ((payload->>'user_id'));

3. numeric vs float — Don’t Let Rounding Bugs Ruin Your Day

If you’re storing anything related to money, pricing, totals, percentages, or precision, do not use a floating-point type. Float types introduce tiny rounding errors that eventually lead to incorrect invoices, mismatched reports, or subtle bugs that waste hours of debugging.

Use numeric when:

• You need exact precision (money, totals, interest calculations, taxes).

Use float when:

• You’re doing scientific computing or processing sensor data.
• Precision doesn’t matter but performance and range do.

CREATE TABLE invoice (
  invoice_id SERIAL PRIMARY KEY,
  total NUMERIC(12,2) NOT NULL
);

If you want to scare yourself, try adding 0.1 + 0.2 using float. That alone will make you appreciate numeric.

4. timestamp vs timestamptz — The Most Misunderstood Types

This is the one almost everyone gets wrong. A timestamp without time zone is just a wall-clock time — there's no timezone awareness at all. A timestamptz, despite its name, doesn’t store a timezone; it stores the exact moment in time (UTC) and converts it when you read it.

Use timestamptz for almost everything:

• user activity
• audit logs
• events
• created_at / updated_at timestamps

Use timestamp only when:

• You’re storing something like business hours that don’t map to a specific timezone.
• You truly don’t care about timezone behavior.

The rule is simple: If a human will interpret the time, use timestamptz.

CREATE TABLE login_event (
  user_id INTEGER NOT NULL,
  logged_in_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Conclusion

Choosing the right data type is one of the easiest ways to keep your schemas simple, predictable, and easy to work with. PostgreSQL gives you powerful options, but that also means it’s easy to accidentally pick a type that will bite you later. Stick to these guidelines and you’ll avoid the most common pitfalls developers make when designing schemas!