📐CREATE TABLE

Declare the relation: columns, keys, rules

CREATE TABLE fixes the heading of a relation: names, types, nullability, defaults, primary and alternate keys, foreign keys, and check constraints. These choices are your data API contract, ORMs sit on top, but the database still enforces what can exist. Prefer exact numeric types for money, time zones for global timestamps, and explicit NOT NULL where the domain is mandatory.

SQL

Table with surrogate PK, natural unique key, and CHECK

CREATE TABLE core.patients (
  patient_id      BIGSERIAL PRIMARY KEY,
  mrn             VARCHAR(32) NOT NULL UNIQUE,
  birth_date      DATE NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT mrn_format_chk CHECK (mrn ~ '^MRN-[0-9]{7}#x27;)
);

✓Illustrative result set

status
CREATE TABLE

SQL

CTAS / UNLOGGED staging (PostgreSQL-flavored)

CREATE UNLOGGED TABLE staging.hot_reads AS
SELECT *
FROM sequencing_reads
WHERE run_id = 'R-TEST'
LIMIT 0;  -- structure-only pattern; confirm semantics for your workflow

✓Illustrative result set

status
CREATE TABLE AS

FLOAT for currency or concentrations

Binary floating point accumulates rounding error across aggregates and regulatory submissions.

NUMERIC / DECIMAL with explicit scale

Model money and published scientific values as NUMERIC(p,s) (or engine equivalent) and align with API JSON decimals.

🧱Partitioning and storage

Declare it up front when tables will exceed single-disk friendliness.

Range or list partitioning by run_date, tenant_id, or region keeps maintenance (detach old partitions, vacuum, index rebuild) bounded. Adding partitioning later often means rewriting the table, design for growth when ingestion volume is known.

Audit columns

Add created_at, updated_at, and optional updated_by early, retrofits on hot tables are painful, and incident response without timestamps is guesswork.

UNLOGGED / TEMP

Staging tables that skip WAL trade durability for speed, perfect for ETL scratch space, dangerous if someone points a billing job at them by mistake.

⚡ Advanced / dialect note

Generated columns, compression, and tablespaces differ widely, capture the reviewed full CREATE TABLE in migrations (Flyway, Liquibase, sqitch) instead of relying on GUI “sync schema” diffs alone.

📏Physical Layout and Alignment

Columns aren't just names; they are offsets in a byte array.

The Padding Tax

CPUs read data efficiently when it is aligned to word boundaries (usually 8 bytes). If you place a 1-byte BOOLEAN between two 8-byte BIGINTs, the database may insert 7 bytes of "hidden" padding to align the second bigint. On a billion-row table, this is 7GB of wasted disk and memory.

Fixed-width first

Group INT, BIGINT, and TIMESTAMP columns at the start of the CREATE TABLE statement. These have predictable offsets that the engine can jump to without parsing.

The NULL Bitmap

Every row begins with a header and a bitmask indicating which columns are NULL. This header itself must be aligned, further influencing the starting offset of your first column.

⚖️Alignment Strategy

Ordering columns for byte-efficiency.

Ordering	Impact
Fixed-width (8, 4, 2, 1) → Variable	Optimal. Minimizes internal padding and simplifies offset math.
Random Interleaving	Sub-optimal. Can bloat row size by 10-30% depending on the schema.

Primary key strategy

Beyond logical identity, the PK determines the **Clustered Index** order in many engines, this is the physical sequence on disk. Use monotonic keys (sequences) to avoid random I/O and page fragmentation on insert.

TL;DR

CREATE TABLE defines a new relation: column names, data types, NULL/NOT NULL, defaults, keys, and indexes. The physical layout (heap vs clustered index) depends on the engine, choices made here affect every future query and migration. Prefer explicit constraints and one clear primary key grain before loading data at scale.