PostgreSQL Data Types In Depth
PostgreSQL's type system is not just about validating input, it also determines how a value is stored on disk, which operators and index types can accelerate queries against it, and how much implicit conversion happens before a comparison ever runs.
This page is the conceptual map for the rest of the data-types-jsonb section, covering how PostgreSQL categorizes types, how storage and indexing follow from that choice, and when to reach for jsonb, arrays, enums, or domains instead of a strictly normalized column.
Summary
- Core Idea: A column's type in PostgreSQL determines its storage format, its valid operators, and which index access methods can accelerate it, not just what values it accepts.
- Why It Matters: Picking the wrong type category can silently disable indexing, waste storage, or make constraints impossible to enforce.
- Key Concepts: type family, TOAST, operator class, GIN/GiST/B-tree access methods, json vs jsonb.
- When to Use: Reach for this model when designing a new table's columns, or when a query that "should" use an index is doing a sequential scan instead.
- Limitations / Trade-offs: Flexible types like jsonb trade constraint strength and index precision for schema flexibility.
- Related Topics: numeric and text types, arrays and composite types, UUID/enum/domain types, date and timestamp types.
Foundations
Every column in PostgreSQL has exactly one declared type, and that type constrains which values are valid, how they compare, and how they sort.
PostgreSQL groups its built-in types into families: numeric, character, boolean, date/time, UUID, binary, geometric, network, and the semi-structured json and jsonb types.
Beyond built-in scalar types, PostgreSQL also supports composite types built from other types, array types that hold multiples of any type, range types that represent a span of values, and enum types for closed sets of labels.
A domain is a user-defined constraint layered on top of an existing base type, letting you name and reuse a validation rule like "a valid email address" across many columns.
Choosing bigint over int, or numeric over float, is not a stylistic preference, it changes overflow behavior, arithmetic precision, and index width.
The general rule of thumb is to choose the smallest and most specific type that correctly represents the data, since narrower types produce smaller indexes and catch invalid data earlier.
text and varchar(n) behave identically in PostgreSQL apart from the length check, unlike some other databases where varchar carries a real storage or performance difference.
jsonb stores JSON in a decomposed binary format rather than as text, which is what separates it from the plain json type.
Mechanics & Interactions
PostgreSQL stores most row data in fixed-size or variable-length fields packed into 8-kilobyte pages, and any single value larger than about 2 kilobytes gets moved out of line into a side table called TOAST.
TOAST is why a large jsonb document or long text value does not bloat every sequential scan of the main table, since the oversized value is only fetched when a query actually needs it.
Every type belongs to one or more operator classes, and an index can only accelerate operators that its access method supports for that type, which is why not every type works with every index type.
B-tree indexes support equality and ordering comparisons and work with most scalar types, while GIN indexes support containment and existence operators and are what make jsonb and array queries fast.
GiST and SP-GiST indexes support geometric and range-overlap style operators, which is why PostGIS and range types lean on them instead of B-tree.
Implicit and explicit casts change a value's type before comparison, and a cast applied to an indexed column in a WHERE clause can silently prevent the planner from using that index.
-- Index exists on created_at (timestamptz)
CREATE INDEX events_created_at_idx ON app.events (created_at);
-- Sargable: planner can use the index
SELECT id FROM app.events WHERE created_at > now() - interval '1 day';
-- Not sargable: cast wraps the column, index unusable
SELECT id FROM app.events WHERE created_at::date = current_date;The second query looks equivalent to a person, but the cast on created_at forces PostgreSQL to evaluate an expression for every row instead of seeking through the index.
jsonb also drops key ordering, whitespace, and duplicate keys during its decomposition, which makes it faster to query but means it cannot reproduce the exact original JSON text the way json can.
Enum types store their values as small integers internally while displaying the label you defined, giving compact storage with readable output.
Advanced Considerations & Applications
Deciding between jsonb and normalized columns is really a decision about how stable the schema is and how the data will be queried.
Fields you filter on, join on, or need constraints against belong in real typed columns, while genuinely variable or sparse attributes are a reasonable fit for jsonb.
A GIN index on a jsonb column accelerates containment queries with the @> operator, but it does not replace a B-tree index for equality lookups on a single well-known key, which usually wants an expression index instead.
Range types let you model a span, like a reservation's time window, as one value with built-in overlap operators, which is more correct and often faster than comparing a pair of start/end columns manually.
Enum types are fast and self-documenting, but adding or removing a value requires a schema migration, which makes a lookup table a better fit when the set of values changes frequently.
Domains centralize a validation rule in one place, but errors reference the domain name rather than the column, which some teams find less immediately readable in application error handling.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
| Normalized columns | Strong constraints, best index and join support | Rigid, needs a migration for every new field | Stable, well-known attributes queried directly |
| jsonb column | Flexible schema, groups sparse or variable attributes | Weaker constraints, GIN index only covers containment-style queries | Semi-structured or rarely-queried attribute bags |
| Enum type | Compact storage, self-documenting values | Adding a value requires a migration | Small, rarely-changing closed sets |
| Lookup table (foreign key) | Values changeable without a migration, supports metadata per value | An extra join for every read | Sets that change often or need extra attributes per value |
None of these are mutually exclusive within one schema, and most real designs mix normalized columns, a jsonb catch-all, and a couple of enum or lookup-table sets depending on the field.
Common Misconceptions
- jsonb is schemaless so it has no cost - jsonb still carries storage and indexing costs, and skipping typed columns for everything makes constraints and joins harder to enforce.
- varchar(n) is faster than text - in PostgreSQL they use the same underlying storage, and
varchar(n)only adds a length check on write. - Floating point is fine for money as long as you round carefully - floating point cannot represent most decimal fractions exactly, so
numericis the correct type for currency regardless of rounding discipline. - Any index can accelerate any query on a column - an index only speeds up operators its access method actually supports for that type, which is why jsonb containment needs GIN, not B-tree.
- json and jsonb are basically the same type with a different name -
jsonstores the exact original text whilejsonbstores a decomposed binary form, and onlyjsonbsupports indexing operators like containment.
FAQs
When should I use jsonb instead of separate typed columns?
Reach for jsonb when the attribute set is genuinely variable or sparse and you rarely need to filter, join, or constrain individual fields, while stable, frequently-queried fields belong in typed columns instead.
What's the real difference between json and jsonb?
json stores the exact input text, preserving key order and whitespace.
jsonb decomposes the value into a binary format, which drops that formatting but enables indexing and faster containment queries.
Does varchar(n) perform better than text in PostgreSQL?
No, since both use the same storage representation and varchar(n) only adds an extra length check when a value is written.
Why doesn't my index speed up a query that filters a jsonb field?
- A B-tree index on the whole jsonb column does not accelerate key-level lookups.
- Containment queries with
@>need a GIN index instead. - Filtering one well-known key usually wants an expression index on that key, not a whole-column index.
What is TOAST and when does it kick in?
TOAST moves values larger than roughly 2 kilobytes out of the main table row into a side table, so wide jsonb or text values do not bloat every sequential scan of the table.
Why is numeric preferred over float for money?
numeric stores exact decimal digits, while floating point types approximate values in binary and cannot represent most decimal fractions exactly, which introduces rounding error in sums and comparisons.
What's the difference between an enum type and a lookup table?
An enum is compact and self-documenting but requires a migration to add a value, while a lookup table with a foreign key can gain new values without a migration and can carry extra metadata per value.
How do range types help compared to separate start/end columns?
A range type represents a span as a single value with built-in overlap and containment operators, which is both more correct and often faster than comparing two separate columns by hand.
Can a cast on a column break index usage?
Yes, since wrapping an indexed column in a cast or function, such as created_at::date, forces PostgreSQL to evaluate that expression per row instead of seeking through the index on the raw column.
What is a domain, and how is it different from a CHECK constraint on a column?
A domain wraps a base type with a reusable constraint you define once and apply to many columns, so the rule and its error message live in one place instead of being repeated on every column.
Do arrays replace the need for a join table?
Not usually, since arrays work well for small, denormalized lists like tags, but once array elements need their own attributes or relationships, a proper join table is the better fit.
Why do some types need GIN or GiST indexes instead of B-tree?
Because B-tree only supports equality and ordering operators, while containment (jsonb, arrays) needs GIN and geometric or range-overlap operators need GiST or SP-GiST, since those are not orderable the way B-tree requires.
Related
- Data Types Basics - hands-on tour of the core types
- JSONB Operators and Indexing - containment queries and GIN indexes in practice
- Numeric and Text Types - precision, scale, and string type choices
- Arrays and Composite Types - collection and structured column types
- UUID, Enum and Domain Types - identifiers, closed sets, and reusable constraints
- Date, Time and Timestamptz - temporal type choices
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, and PostGIS 3.5+.