PostgreSQL Data Types: Choose the Right Type and Save Half the Trouble

I once inherited a legacy project where the users table used VARCHAR(255) for phone, INTEGER for age, DOUBLE PRECISION for balance, TIMESTAMP without a time zone for created_at, and VARCHAR(20) for status, storing the strings 'active' and 'inactive'. Could it run? Yes. Was it correct? Almost none of it was.

A data type is not just something that can hold a value. Choosing the wrong type has cascading costs: precision disappears without warning, indexes become larger and slower than necessary, storage usage can triple, and every query needs CAST after CAST. Worse, types form the foundation of a schema. Once the system is running in production, changing a type becomes an expensive, risky migration. Choosing the right types is half the battle in schema design.

This article explains PostgreSQL's most commonly used type categories. The focus is not on what types exist, but on how to choose and why.

1. Numbers: Choose Integers by Range; Money Must Use numeric

Numeric types follow two tracks: integers and numbers with fractional parts.

The integer types are smallint (2 bytes, -32768 to 32767), integer (4 bytes, roughly 2.1 billion), and bigint (8 bytes, roughly 9.22 quintillion). The rule is simple: choose by range; do not reach for bigint automatically. integer is more than enough for status codes, ages, and sort-order fields. Using bigint everywhere adds 4 bytes per row, while indexes, foreign keys, and WAL all grow with it. On a large table, that can waste several gigabytes. smallint is suitable for enum-like numeric values, such as priorities from 1 to 5 or days of the week, and uses half the space.

For auto-incrementing primary keys, use serial or bigserial. On PG 10+, GENERATED ALWAYS AS IDENTITY is recommended because its syntax is more standard and its behavior is cleaner; serial is a historical artifact. integer is enough for small and medium-sized tables, but if a table might ever grow to 2.1 billion rows, such as logs, events, or tracking data, use bigint immediately and eliminate the risk. When an integer finally overflows, the migration is hell.

Numbers with fractional parts are where the real trouble begins. real (4 bytes) and double precision (8 bytes) are floating-point types. Binary floating point cannot represent decimal fractions exactly, so 0.1 + 0.2 does not equal 0.3. Floating point is fine for scientific calculations and sensor readings, but never use it for money:

-- Bad: storing money as floating point will eventually cause trouble
ALTER TABLE accounts ADD COLUMN balance DOUBLE PRECISION;
-- 0.1 + 0.2 = 0.30000000000000004; reconciliation will make you cry

-- Good: numeric stores money without losing precision
ALTER TABLE accounts ADD COLUMN balance NUMERIC(12, 2);
-- 12 total digits, 2 decimal places, maximum 9999999999.99, exactly precise

numeric(p, s) and decimal(p, s) are exact decimal types. p is the total number of digits, and s is the number of decimal places. The tradeoff is slower computation than floating point and variable storage size, but for money, interest rates, and tax rates, correctness >> performance. There is no real alternative. For calculations such as exchange rates that require both high precision and a large range, use numeric without specifying precision. This variable-precision form allows thousands of significant digits.

In short: choose integers by range, then decide whether a fractional value represents money. Use numeric for money and double for scientific data; keep the boundary clear.

2. Text: Stop Using VARCHAR(255) for Everything

The most common myth about text types is that VARCHAR(255) works for everything. That is a relic of the MySQL era, when older InnoDB versions had a 255-byte index-prefix limit. In PostgreSQL, VARCHAR(n), TEXT, and VARCHAR without a length have exactly the same storage representation. The only difference is whether a length constraint exists.

-- Bad: using VARCHAR(255) without thinking
name        VARCHAR(255)   -- Email addresses, usernames, and article titles all get 255 for no reason
bio         VARCHAR(255)   -- 255 characters is nowhere near enough for a biography

-- Good: follow business rules, or simply use TEXT
email       VARCHAR(320)   -- The RFC maximum email length is 320
username    VARCHAR(32)    -- The business rule sets a limit of 32
bio         TEXT           -- No length limit; enforce constraints in the application

Selection rule: use VARCHAR(n) for fields that genuinely have a business-defined upper limit, such as email addresses, usernames, or fixed-length ISO country codes. If the limit is uncertain or the value may grow, as with biographies, notes, or article bodies, use TEXT and enforce length constraints in the application. CHAR(n) is fixed-length and pads short values with spaces. It is almost never useful, and PG still stores it internally as variable-length data. Consider it only for compatibility with legacy systems or fixed-code fields such as a CHAR(2) country code.

Do not store UUID strings in VARCHAR for unique identifiers:

-- Bad: storing a UUID as a string
id  VARCHAR(36) PRIMARY KEY
-- 36 bytes, slower string comparisons, larger indexes

-- Good: use the native uuid type
id  UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- 16 bytes, binary storage, smaller and faster indexes

The uuid type requires only 16 bytes, less than half the size of its string representation. Comparisons are bytewise, require no collation, and make indexes and JOINs faster. PG 13+ includes gen_random_uuid() as a built-in function, so it can be used directly as the default.

3. Time: Default to timestamptz

This is the most important section in the article. Use TIMESTAMPTZ with a time zone by default for time fields; do not use TIMESTAMP without one.

-- Bad: no time zone, creating a future problem
created_at  TIMESTAMP

-- Good: includes a time zone
created_at  TIMESTAMPTZ DEFAULT now()

Many people think they do not need time zones because their business is not international. That is wrong. A TIMESTAMP without a time zone is a bare time whose meaning is ambiguous. The same 2026-07-17 10:00:00 means entirely different things when a service runs in UTC versus UTC+8. If the server time zone changes, a container defaults to UTC, or the business expands internationally, every historical timestamp shifts, with no way to repair it because the information identifying the original time zone has already been lost.

The truth about timestamptz is simple: it is always stored in UTC and merely displayed in the client session's time zone. It consumes no extra space because both timestamp and timestamptz use 8 bytes, yet it preserves one critical fact: the instant is unique worldwide. If you write 2026-07-17 10:00:00+08, PG stores the corresponding UTC value. Reading it in a UTC session produces 02:00:00+00, while a UTC+8 session produces 10:00:00+08. Calculations, comparisons, and cross-time-zone display all remain correct.

Related types include DATE for a date without a time, TIME for a time without a date, which is rarely needed, and INTERVAL for a duration such as INTERVAL '30 days', which is excellent for expiration periods and polling intervals. Use now() or CURRENT_TIMESTAMP for the current instant, and AT TIME ZONE 'Asia/Shanghai' for time-zone conversion.

Do not store time as an integer Unix timestamp. It can be calculated, but you lose type safety, readability, and PostgreSQL's rich set of time functions. It only creates extra work.

4. Booleans: Use boolean, Not 0/1

-- Bad
is_active  SMALLINT DEFAULT 1   -- 0/1 must still be translated in the application

-- Good
is_active  BOOLEAN DEFAULT TRUE

boolean occupies 1 byte, has clear semantics, and is recognized natively by PostgreSQL's query optimizer, drivers, and ORMs. Storing 0/1 in SMALLINT is an old MySQL habit, although it also appears elsewhere. It only adds cognitive overhead for you and the application. Every query must remember that 1 means true, and it is easy to write SQL such as WHERE is_active, whose behavior is not intuitive for an integer.

5. Enums: enum, Strings, or a Lookup Table?

A status field such as status = pending/paid/cancelled can be represented in three ways, each suited to different scenarios.

Strings (bad): with status VARCHAR(20), any value can be inserted. Without constraints, someone will eventually write 'Paid', 'PAID', or 'padi', and WHERE status = 'paid' will miss half the records. Eliminate this option immediately.

The enum type (moderate use cases, most common):

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

CREATE TABLE orders (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status  order_status NOT NULL DEFAULT 'pending'
);

In PG, an enum is a real type. It uses 4 bytes internally, provides type safety by rejecting invalid values, remains readable, and saves space. It fits stable value sets, such as order states, user roles, and priorities. The catch is that changing values is cumbersome. Adding one requires ALTER TYPE ... ADD VALUE. PG 12+ supports doing so inside a transaction, but with limitations. Removing or renaming a value requires a migration and may even require rebuilding the table. Use enums for value sets that almost never change; be cautious when the business is still evolving.

Lookup tables (larger use cases):

CREATE TABLE order_statuses (code TEXT PRIMARY KEY, label TEXT, ...);
CREATE TABLE orders (..., status TEXT REFERENCES order_statuses(code));

Use a separate dictionary table with a foreign key when the value set is large, requires extra attributes such as a Chinese label, description, or sort order for each state, or changes frequently. This is flexible but costs more because it requires a JOIN.

In practice: use enum for core, stable enumerations; use a lookup table when the value set will grow or needs metadata; use plain strings only temporarily during prototyping.

6. Arrays and JSONB: PostgreSQL's Killer Features

These are two of PostgreSQL's signature capabilities that distinguish it from MySQL.

Arrays such as int[] and text[] are suitable for simple multivalue scenarios, such as article tags or role lists:

CREATE TABLE articles (
    id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tags   TEXT[] NOT NULL DEFAULT '{}'
);
-- Insert tags ARRAY['postgres', 'db', 'backend']
-- Query tags && ARRAY['postgres']  (overlap test)
-- Supports GIN indexes: CREATE INDEX ON articles USING GIN (tags);

Arrays are lightweight and atomic. You do not need a separate table for tags or a JOIN, and a GIN index makes queries for records containing a given tag fast. Do not overuse them, though. Once those values need their own attributes, must be referenced from multiple places, or require statistical aggregation, create a proper association table. Arrays are ideal for simple, tag-like multivalue fields.

JSONB is the right solution for semi-structured data: fields with variable structures, such as user-defined properties or unmodified responses from third-party APIs, or tables that need to hold several entity types. JSONB is binary JSON. It supports GIN indexes and rich query operators, including ->> for extracting text, @> for containment tests, and ? for checking whether a key exists. Distinguish JSON from JSONB: the former preserves the original text, including whitespace and order, while the latter parses the data for storage, supports fast queries, and can be indexed. Use JSONB in 99% of cases.

JSONB's pitfalls and best practices, including when to use it, when to extract relational columns, how to build indexes, and how to handle schema evolution, are a large subject of their own. They deserve a separate article, so they are not covered here. For now, follow one rule: extract structurally stable fields into native columns so they can be indexed, constrained, and queried efficiently; reserve JSONB for genuinely dynamic, schema-less data. Put the entire business model in one enormous JSONB document, and you will soon miss the relational model.

Summary

• Choosing the wrong type has cascading costs: lost precision, bloated indexes, wasted storage, queries that need CASTs, and expensive, risky migrations. Types are the foundation of a schema, and choosing them correctly is a fundamental design skill.
• Choose numeric types by purpose: select integers by range, preferring integer unless overflow is a real risk, in which case use bigint immediately. Always use numeric for money, interest rates, and tax rates; reserve double for scientific or sensor data. Never store money in floating point.
• Stop using VARCHAR(255) for everything: use VARCHAR(n) when a real business limit exists and TEXT when values may grow. Use the native 16-byte uuid type instead of a 36-byte string for UUIDs.
• Default to timestamptz for time: a timestamp without a time zone is a time bomb. A server time-zone change or international expansion can invalidate everything. The default combination should be now() plus timestamptz.
• Use native types instead of turning everything into strings: use boolean for booleans, enum for fixed and stable enumerations, arrays for simple multivalue fields, and JSONB for the genuinely dynamic parts of semi-structured data, not the entire table.

One final sentence: PostgreSQL gives you a type system precise enough to express exactly what a value is. Do not waste it by reducing every field to VARCHAR. Choosing the right type means performance, correctness, and maintainability. The decisions in a single CREATE TABLE statement determine the table's fate for the next three to five years.