A Guide to PostgreSQL Data Types

PostgreSQL’s type system is one of the richest available in a relational database, and PostgreSQL data types carry over fully into YSQL, YugabyteDB’s PostgreSQL-compatible SQL layer. 

What changes in a distributed SQL environment is the weight of certain decisions: primary key type, timestamp handling, and JSON storage strategy all interact with how YugabyteDB distributes and queries data across nodes.

What Are the Main Categories of PostgreSQL Data Types?

YSQL supports the full PostgreSQL type taxonomy across seven main categories:

1. Numeric — integers, exact decimals, and floating-point values.
2. Character — fixed-length, variable-length, and unlimited text strings.
3. Temporal — dates, times, timestamps, and intervals.
4. Boolean — true/false values.
5. JSON and JSONB — structured document storage within a relational table.
6. UUID — 128-bit globally unique identifiers, particularly important for primary key design in distributed databases.
7. Composite and user-defined — arrays, enums, and custom types for application-specific structures.

The full reference is in the YSQL data types docs.

Numeric Types: Which One Should You Use?

Numeric type selection is one of the most common schema mistakes. The right answer depends on the value range, precision requirements, and write patterns.

Integer types (SMALLINT, INT, BIGINT) differ in storage size and value range. INT covers up to about 2.1 billion and is the common default. Use BIGINT when IDs, counters, or timestamps might exceed that ceiling.

Exact decimals (NUMERIC, DECIMAL) are the right choice for financial data where rounding errors are unacceptable. They’re slower than floating-point due to arbitrary-precision arithmetic.

Floating-point (REAL, DOUBLE PRECISION) is fast but inexact. Don’t use it for monetary values.

Serial types (SERIAL, BIGSERIAL) generate auto-incrementing integers, convenient in single-node PostgreSQL. YugabyteDB hash-shards primary keys by default, so a plain SERIAL or BIGSERIAL key is already distributed across tablets by its hash value rather than concentrated on one. The hot-spot risk shows up in a more specific case: if you explicitly declare range sharding (ASC/DESC) on a monotonically increasing column, such as a timestamp-first key in a time-series table, new writes will land on whichever tablet holds the current end of the range. For most schemas, the bigger practical consideration with SERIAL at high write throughput is sequence-generation overhead rather than tablet placement. See the database schema guide for more on primary key design and sharding behavior.

Character Types: TEXT, VARCHAR, or CHAR?

YSQL supports CHAR(n), VARCHAR(n), and TEXT. In practice, VARCHAR and TEXT perform identically in PostgreSQL and YSQL. CHAR(n) pads values with spaces to the declared length, which is almost never the right behavior.

Use TEXT for unconstrained strings. Use VARCHAR(n) only when a length limit is a genuine business constraint, not for performance reasons. There’s no storage or speed advantage either way.

Temporal Types: Dates, Times, and Timestamps

YSQL supports five temporal types: DATE, TIME, TIMESTAMP, TIMESTAMPTZ, and INTERVAL.

The most consequential choice is between TIMESTAMP and TIMESTAMPTZ. Plain TIMESTAMP stores a date and time with no timezone context. TIMESTAMPTZ stores the moment in UTC and converts to the session timezone on retrieval. 

In a distributed, multi-region deployment where nodes operate across time zones, plain TIMESTAMP creates data integrity risks that are difficult to debug after the fact. Always prefer TIMESTAMPTZ for storing moments in time. 

JSON and JSONB: When To Use Each

Both types store JSON data, but they work differently under the hood.

JSON preserves the raw input text and re-parses it on every operation that processes the value. It maintains original whitespace and key ordering. 

JSONB stores JSON in a decomposed binary format. It’s faster to query, supports GIN indexing, and enables containment operators for efficient document lookups. Whitespace and key order are not preserved, which almost never matters in practice.

For the vast majority of use cases, JSONB is the right choice. JSON is only preferable when exact input preservation is a hard requirement.

In YugabyteDB, JSONB is particularly valuable for applications migrating document-model workloads to a distributed SQL environment. It allows flexible column structures while the surrounding table still benefits from ACID transactions and horizontal scalability.

UUID: A Reasonable Default, Not Always a Necessary One

UUID is a 128-bit identifier that is globally unique without a central sequence generator. In standard PostgreSQL, it’s a convenient alternative to serial integers. In YugabyteDB, it remains a solid choice for many schemas, but not because it uniquely avoids sharding hot spots. Since YugabyteDB hash-shards primary keys by default, both UUID and integer-based keys are distributed across tablets out of the box.

Where UUID genuinely helps: it avoids any coordination overhead from a shared sequence generator under very high write concurrency, and it’s useful when IDs need to be generated client-side or merged across systems without collision risk. Where it’s not strictly necessary: hot-spot avoidance on its own, since that’s already handled by the default hash-sharding behavior. The schema decision that actually matters for hot spots is whether you (or a query pattern) push a table toward range sharding on a monotonically increasing column, not whether the key itself is a UUID or an integer.

What About Arrays, Enums, and Composite Types?

Arrays: YSQL supports multidimensional arrays of any built-in or user-defined type. Useful for storing sets of values without a separate join table. Keep individual array columns to a reasonable size, as very large arrays can affect row storage and query performance.

Enums: CREATE TYPE … AS ENUM defines a static, ordered set of values and is suitable for columns with a fixed, known value set (status fields, categories). Adding values to an enum later requires a schema migration.

Composite types: Defined with CREATE TYPE, composite types group multiple fields into a single column type. Useful for function return types, ROW constructors, and structured column values in normalized schemas.”

Data type selection is foundational to schema design, and in YugabyteDB, choices around primary key type, timestamp handling, and JSONB have direct implications for sharding efficiency and query performance at scale.

Try YugabyteDB Aeon free, or book a demo to walk through schema design for your workload.