Skip to content

Machine-Native Data Types & Relational Constraints: PostgreSQL & MariaDB Design Patterns

Created: 2026-11-08  ·  Last updated: 2026-11-08  ·  Nejat Hakan

Executive Summary: Choosing suboptimal column data types inflates disk consumption, exhausts CPU cache locality, causes silent floating-point calculation errors, and introduces catastrophic index fragmentation. A database schema is not merely a passive storage definition—it is the ultimate mathematical enforcement layer for application business logic. This guide covers how to select machine-native data types (such as BIGINT IDENTITY, time-ordered UUIDv7, fixed-point NUMERIC, UTC TIMESTAMPTZ, and network INET) and how to declare robust integrity constraints (CHECK, FOREIGN KEY, and GiST EXCLUDE) to protect data validity directly at the storage engine level.

  • Identity & Sequential IDs

    Replace legacy SERIAL pseudo-types with standard BIGINT GENERATED ALWAYS AS IDENTITY and time-ordered UUIDv7 to eliminate 32-bit overflow and B-Tree bloat.

  • Financial Math: No Floats!

    Eliminate binary floating-point rounding errors: store atomic integer units (cents/satoshis) in BIGINT or use arbitrary-precision NUMERIC.

  • Strict UTC Timestamps

    Prevent Daylight Saving Time (DST) corruption: enforce TIMESTAMPTZ to normalize all temporal timestamps to UTC at the physical storage layer.

  • Declarative Constraints

    Enforce business invariants at the disk level: configure CHECK constraints, deferred foreign keys, and GiST-backed EXCLUDE range constraints.

1. Data Type Selection Matrix & Memory Layout

Selecting appropriate data types directly influences how many rows fit inside an 8 KiB PostgreSQL page or a 16 KiB MariaDB InnoDB page. More rows per page means fewer physical I/O read operations from NVMe drives and higher cache hit ratios in RAM.

Data Domain ❌ Anti-Pattern Type ✅ Production PostgreSQL Type ✅ Production MariaDB Type Architectural Reason
Primary Keys SERIAL (32-bit int) or random VARCHAR(36) UUIDv4 BIGINT GENERATED ALWAYS AS IDENTITY or UUID (UUIDv7) BIGINT UNSIGNED AUTO_INCREMENT 32-bit integers overflow at 2.14 × 109 rows; random UUIDv4 causes severe B-Tree leaf page fragmentation.
Currency & Finance FLOAT / REAL / DOUBLE PRECISION BIGINT (atomic cents) or NUMERIC(14, 2) BIGINT (cents) or DECIMAL(14, 2) IEEE-754 binary floating-point cannot accurately represent decimal fractions like 0.10 + 0.20.
Timestamps TIMESTAMP (without timezone) or VARCHAR TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) DATETIME(6) or TIMESTAMP(6) (stored in UTC) Naive timestamps cause silent calculation errors across Daylight Saving Time (DST) transitions.
Strings & Text CHAR(255) (pads spaces) TEXT or VARCHAR(n) (identical storage in PG) VARCHAR(n) CHAR(n) wastes disk space and pads trailing whitespace bytes.
IP Addresses VARCHAR(45) INET / CIDR (7 or 19 bytes) INET6 (native 16-byte binary type) Validates IPv4/IPv6 syntax at parse time and supports binary subnet containment math (<<, >>).
Semi-Structured TEXT containing serialized JSON strings JSONB (decomposed binary format) JSON (stored with binary validation) JSONB enables partial updates, fast key existence checks, and GIN index searching. 
%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    ROW["Logical Row Tuple<br/>(Incoming INSERT Payload)"]
    PK_TYPE["Primary Key: BIGINT Identity<br/>(8 Bytes, Sequential Order)"]
    MONEY_TYPE["Currency: BIGINT Cents<br/>(8 Bytes, Exact Integer Math)"]
    TIME_TYPE["Timestamp: TIMESTAMPTZ<br/>(8 Bytes, Stored as UTC Epoch)"]
    IP_TYPE["Network: INET<br/>(7 Bytes IPv4 / 19 Bytes IPv6)"]
    PAGE["Target 8 KiB Buffer Page<br/>(Compact, High Density, Fast Scan)"]

    ROW --> PK_TYPE
    ROW --> MONEY_TYPE
    ROW --> TIME_TYPE
    ROW --> IP_TYPE
    PK_TYPE --> PAGE
    MONEY_TYPE --> PAGE
    TIME_TYPE --> PAGE
    IP_TYPE --> PAGE

2. Deep Dive: Critical Anti-Patterns

Anti-Pattern 1: The Floating-Point Financial Disaster

Floating-point data types (FLOAT, DOUBLE PRECISION, REAL) adhere to the IEEE-754 standard, which uses binary fractions. In binary, numbers such as 0.1 or 0.2 cannot be represented with finite precision (analogous to 1/3 in base-10).

-- Demonstrate the IEEE-754 rounding bug in PostgreSQL
SELECT (0.1::float8 + 0.2::float8) = 0.3::float8 AS is_equal;
-- Returns: FALSE (0.1 + 0.2 produces 0.30000000000000004)

-- Safe engineering solution: Exact arbitrary-precision NUMERIC
SELECT (0.1::numeric + 0.2::numeric) = 0.3::numeric AS is_equal;
-- Returns: TRUE

Store Currency in Cents

For high-throughput payment systems, store currency as an integer count of the smallest atomic denomination (e.g., Euro cents, US cents, or Bitcoin satoshis) in a BIGINT. This avoids decimal conversion overhead entirely and enables fast native CPU integer arithmetic.

Anti-Pattern 2: Random UUIDv4 vs. Time-Ordered UUIDv7

Generating random UUIDv4 identifiers in distributed microservices avoids central coordination. However, inserting random 128-bit hashes into a standard B-Tree index causes massive index page fragmentation: every new row lands on an arbitrary physical leaf page on disk, causing continuous page splits and cache evictions.

UUIDv7 solves this by embedding a 48-bit millisecond UNIX timestamp in the most significant bits: - Sequential Ingestion: New keys append linearly to the right-hand edge of the B-Tree leaf pages. - Cache Locality: Recent rows reside in the same physical memory buffer. - Global Uniqueness: Retains 74 bits of cryptographic randomness.

3. Relational Integrity Constraints in Practice

A production schema uses declarative constraints to ensure bad data never reaches storage.

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    OP["Write Operation (INSERT / UPDATE)"]
    C_NULL{"NOT NULL Check"}
    C_DOM{"Domain & Type Check"}
    C_CHK{"CHECK Expressions"}
    C_FK{"Foreign Key Lookup"}
    C_EXCL{"Unique & EXCLUDE Test"}
    WRITE_OK["Commit to Heap Page"]
    WRITE_ERR["Abort with Error Code"]

    OP --> C_NULL
    C_NULL -->|Pass| C_DOM
    C_NULL -->|Fail| WRITE_ERR
    C_DOM -->|Pass| C_CHK
    C_DOM -->|Fail| WRITE_ERR
    C_CHK -->|Pass| C_FK
    C_CHK -->|Fail| WRITE_ERR
    C_FK -->|Pass| C_EXCL
    C_FK -->|Fail| WRITE_ERR
    C_EXCL -->|Pass| WRITE_OK
    C_EXCL -->|Fail| WRITE_ERR

Constraint Types & Best Practices

  1. NOT NULL: Eliminates SQL three-valued logic (TRUE, FALSE, UNKNOWN). Make columns NOT NULL by default unless absence of value has a distinct business meaning.
  2. CHECK Constraints: Enforce domain rules (e.g., CHECK (stock_qty >= 0) or CHECK (status IN ('PENDING', 'PAID', 'SHIPPED'))).
  3. FOREIGN KEY Actions:
    • ON DELETE RESTRICT (Default): Prevents deleting a parent record if children exist.
    • ON DELETE CASCADE: Automatically deletes child records when the parent is deleted (use with caution).
    • DEFERRABLE INITIALLY DEFERRED: Delays foreign key evaluation until COMMIT, enabling circular dependencies within a single transaction.
  4. EXCLUDE Constraints (PostgreSQL): Uses GiST indexes to prevent overlapping ranges (e.g., booking intervals or room reservations) without application-level race conditions.

4. Production DDL Workshop

Execute this script in PostgreSQL 17 to provision a hardened schema featuring identity keys, check constraints, network types, and exclusion rules:

-- File: /opt/db-ops/hardened_schema_workshop.sql
BEGIN;

-- Enable btree_gist extension for range exclusion constraints
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 1. Customers Relation
CREATE TABLE customers (
    customer_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE 
                    CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
    company_name    VARCHAR(128) NOT NULL,
    ipv4_registered INET NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- 2. Products Relation
CREATE TABLE products (
    product_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku             VARCHAR(32) NOT NULL UNIQUE,
    title           TEXT NOT NULL,
    price_cents     BIGINT NOT NULL CHECK (price_cents >= 0),
    stock_qty       INT NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
    metadata        JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- 3. Orders Relation
CREATE TABLE orders (
    order_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id     BIGINT NOT NULL REFERENCES customers(customer_id) ON DELETE RESTRICT,
    status          VARCHAR(16) NOT NULL DEFAULT 'PENDING'
                    CHECK (status IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED')),
    ordered_at      TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- 4. Order Line Items (Composite Primary Key)
CREATE TABLE order_items (
    order_id        BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id      BIGINT NOT NULL REFERENCES products(product_id) ON DELETE RESTRICT,
    quantity        INT NOT NULL CHECK (quantity > 0),
    unit_cents      BIGINT NOT NULL CHECK (unit_cents >= 0),
    PRIMARY KEY (order_id, product_id)
);

-- 5. Resource Reservation with GiST Exclusion Constraint (Prevents Overbooking)
CREATE TABLE server_reservations (
    reservation_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    server_node     TEXT NOT NULL,
    reserved_during TSRANGE NOT NULL,
    EXCLUDE USING gist (server_node WITH =, reserved_during WITH &&)
);

COMMIT;