Data Representation and Storage Concepts

CPUs operate on registers of fixed width. Disks and networks move untyped octets. The encoding is the agreement that those octets mean a particular value. System design interviews often jump to sharding; production outages equally often come from a Java int, a Postgres INTEGER, a protobuf int32, and a JSON number all silently disagreeing about range.

After the scalar encoding, the engine packs fields into a tuple: null bitmap, fixed columns, then a variable-length region (text, JSON, TOAST pointers). That tuple lands in a page. Durability usually writes a log record first, then eventually the page or an immutable file. Representation choices (width, compression, column vs row) decide how many media bytes that one logical field becomes.

Unsigned 32-bit holds 0…4,294,967,295. Signed 32-bit two’s complement holds −2,147,483,648…2,147,483,647. A user id that “fits in an int” in the first million rows can overflow a signed 32-bit primary key years later. Auto-increment that started at 1 still hits the wall; migrating BIGINT under load is a storage project, not an ALTER that is free.

Value 0x01020304 (32-bit)

Big-endian bytes:    01 02 03 04     // network order, Java DataOutput
Little-endian bytes: 04 03 02 01     // x86/x64 memory, most WAL dumps

Signed 8-bit two's complement:
  0000 0111 = 7
  1111 1001 = -7     // invert 0000 0111 → 1111 1000, add 1

Varints (protobuf / SQLite):
  small magnitudes use 1 byte; large use more
  great for sparse small ids; worst-case wider than fixed 32-bit

On-disk engines pick one endianness and document it. Never memcpy a C struct to the network. Decode through an explicit codec. When you copy pages between architectures, either the format is endian-neutral (byte-oriented varints, UTF-8) or you byte-swap at the boundary.

UTF-8 is the default on the web and in most databases. A VARCHAR(10) in one engine means 10 characters; in another it means 10 bytes. Emoji and combining marks make “character” itself slippery (code point vs grapheme cluster). Binary columns (BYTEA, blobs) must never pass through a text codec.

"é"  U+00E9            UTF-8: C3 A9           (2 bytes)
"é"  e + combining acute  UTF-8: 65 CC 81      (3 bytes)  // different bytes, may look equal
"😀" U+1F600           UTF-8: F0 9F 98 80     (4 bytes)

Index keys include the encoded bytes unless you normalize:
  NFC vs NFD, case-fold, trim
  Expression indexes on LOWER(email) exist because the stored encoding is exact bytes

Truncation:
  cut at 255 bytes mid-code-point → invalid UTF-8
  always truncate on code-point or grapheme boundaries

Collation is a separate contract on top of encoding: how two strings compare. A unique index on names without a documented collation will surprise you the first time two visually equal strings collide or fail to. For keys (emails, slugs), store a normalized form and compare as bytes.

Typical heap tuple (simplified):
  [header: xmin/xmax or version | infomask | null bitmap]
  [fixed fields in declared order, with alignment padding]
  [offsets into variable region]
  [varlena: text / jsonb / arrays]

NULL is not a sentinel value in the column bytes; it is a bit.
Missing vs empty string vs JSON null are three different encodings.

TOAST / overflow:
  huge JSON or text stored out-of-line
  main tuple keeps a pointer; extra I/O on SELECT of that column
  columnar files instead cut the column into compressed chunks

alignment inside the tuple is why swapping column order in a struct can shrink rows. On-disk, many engines pack more densely than C structs. In memory (buffer pool), they may decompress into padded form. Count both sizes when you size cache: the page on disk is not always the bytes in RAM.

WAL records, Kafka payloads, and RPC bodies all serialize. Self-describing JSON is debuggable and expensive. Protobuf tags fields by number so you can add fields without breaking old readers if you follow compatibility rules. Avro often stores a schema id and dense binary values — excellent for data lakes, painful if you lose the schema registry.

Checksums belong in the representation: CRC32C per WAL record or Parquet page so a torn write is detected rather than decoded as a plausible integer. Representation without integrity is how silent corruption hides for months.

An 8 KiB page holding 40 padded 200-byte rows is a different I/O economy than the same page holding 80 packed rows. Column chunks of 1 MiB change the unit again: great sequential scan, painful if you needed one row. SSD erase blocks and object-store request pricing add yet another grain.

Worked density (OLTP orders row ≈ 200 B logical):
  C-struct with padding: ~256 B  → ~32 rows / 8 KiB page
  Packed tuple:          ~210 B  → ~38 rows / page
  2x dictionary-compress status+country in columnar chunk:
    amt column 8 B × 100k rows = 800 KB uncompressed
    bit-pack + delta on sorted ts: often 2–4 B equivalent

Cache: buffer pool caches pages, not fields.
A wide JSON column on the same row-page pollutes cache for id lookups.
Split hot narrow row vs cold blob (TOAST or side table).

Width vs future range. int32 is smaller and hotter in cache; BIGINT avoids a painful migration. Pick from growth math, not from “int feels enough.”

Exact vs approximate numeric. IEEE 754 is fast and compact; cents or DECIMAL are correct for money. Mixing them in one total is a representation bug.

Row vs column. Point-get latency versus scan bandwidth and compression. HTAP systems pay for both layouts or accept staleness on one path.

Self-describing vs schemaful serialization. JSON eases evolution and debugging; binary schemas cut CPU and bytes but need a compatibility process.

Compression vs update grain. Dense encodings assume similarity and larger blocks. Fine for immutable files; costly if every click updates one cell in a huge compressed chunk.