Data Types

Choosing the right data type is the most fundamental decision in database design. A poor choice can lead to data loss (truncation), massive storage waste, or extremely slow queries.

Definition: Data Types

A data type is an attribute that specifies the type of data that an object can hold: integer data, character data, monetary data, date and time data, and so on.

Context: The Storage Trade-off

Every byte counts. In a table with 1 billion rows, adding a single unnecessary byte to a column increases the table size by 1 Gigabyte. This affects disk space, but more importantly, it affects how many rows can fit into the server’s memory (RAM) at once.

Detailed Explanation: The Primary Categories

1. Numeric Types

INT / INTEGER: Whole numbers. (Usually 4 bytes).
BIGINT: Very large whole numbers (e.g., Global transaction IDs).
DECIMAL(p, s) / NUMERIC: Exact fixed-point numbers. p is total digits, s is digits after the decimal. Always use this for money.
FLOAT / REAL: Approximate floating-point numbers. Fast for scientific math but can have rounding errors.

2. Character (String) Types

CHAR(n): Fixed length. If you store ‘AB’ in a CHAR(10), it stores 8 spaces.
VARCHAR(n): Variable length. Only stores what is needed.
TEXT: For very long strings (descriptions, logs). No fixed limit.

3. Temporal Types

DATE: YYYY-MM-DD.
TIMESTAMP: Date and Time.
TIMESTAMPTZ: Date and Time with a Time Zone offset. (Best practice).

4. Boolean

BOOLEAN: Stores TRUE, FALSE, or NULL.

Example: Designing a User Table

Imagine we are creating a table for a social media app.

CREATE TABLE Users (
  UserID      BIGINT,         -- Expecting billions of users
  Username    VARCHAR(50),    -- Variable length up to 50 chars
  Bio         TEXT,           -- Long text, unknown length
  IsActive    BOOLEAN,        -- Simple flag
  AccountBal  DECIMAL(19,4),  -- Exact currency
  CreatedAt   TIMESTAMPTZ     -- Time with zone awareness
);

Under the Hood: Memory Alignment and Padding

The database engine doesn’t just pack data randomly. It often aligns data to “word” boundaries (e.g., 8-byte chunks).

The “Null Bitmap”

Every row has a hidden header that includes a Null Bitmap. This is a series of bits (1s and 0s) that tell the engine which columns in that specific row are NULL. Because of this, a NULL value usually takes up almost zero space in the actual data storage area, but the column definition still “reserves” its place in the table schema.

Precision vs. Scale

In DECIMAL(10, 2):

Precision (10): The total number of digits.
Scale (2): The number of digits to the right of the decimal.
This column can store numbers up to 99,999,999.99.