Keys, IDs & Constraints

The most trivial-looking column is the highest-leverage one

An ID looks like the most trivial column in the table. It is actually one of the highest-leverage decisions you make, because the primary key drives physical storage layout, index locality, and how the data shards, and all three are painful to change once the table is large.

The monotonic-key hotspot

Auto-increment integer keys are compact, sort naturally, and give great index locality: new rows cluster at the right edge of the B-tree. That same property is a curse at write scale. In an InnoDB-style clustered index, rows are physically stored in primary-key order, so if the key is monotonic, every insert lands on the same rightmost page and, worse, the same shard. You get a write hotspot: one page or one node absorbs all the insert traffic while the rest sit idle. Auto-increment also leaks information (competitors count your order volume) and does not work cleanly across multiple write nodes that would collide on the next value.

The random-UUID cure that causes a new disease

The obvious fix is a random UUIDv4: 128 bits of randomness, generated anywhere with no coordination, no information leak, no collision. But UUIDv4 destroys index locality. Because values are random, every insert lands on a random B-tree page, so the working set of pages you must keep in memory balloons, pages split constantly, and the index fragments. On a large table this can multiply write cost and index size several-fold. Using a random UUIDv4 as a clustered primary key is one of the most common and expensive modeling mistakes.

Time-ordered IDs, the actual answer

You want the coordination-free, information-hiding property of a UUID with the locality of a sequential key. That is exactly what ULID and UUIDv7 provide: a high-order timestamp prefix (millisecond) followed by random bits. Because the prefix increases with time, new IDs are roughly ordered, so they cluster like an auto-increment for locality, while the random suffix keeps them collision-free and generatable anywhere. Snowflake IDs (Twitter's scheme: timestamp + machine id

• per-ms sequence, packed into 64 bits) give the same time-ordering plus an embedded shard/worker id, at the cost of needing worker-id coordination. Rule of thumb: default to ULID/UUIDv7 for distributed primary keys; use Snowflake when you want a compact 64-bit id and already have worker-id assignment.

Interview nuance: if you propose random UUIDs, expect "what does that do to your clustered index," and if you propose auto-increment, expect "how does that shard." The answer that ends the line of questioning is "ULID/UUIDv7: time-ordered for locality, random-tailed for distribution, coordination-free."

Keys, constraints, and types

Natural vs surrogate keys. A natural key is a real-world attribute (email, ISBN, SKU). A surrogate key is a synthetic id with no business meaning. Prefer surrogate keys for entity primary keys, because natural attributes change (people change emails) and a primary key should be immutable and stable as a foreign-key target. Keep the natural attribute as a UNIQUE constraint, not the PK. Composite keys are right when the identity truly is the combination, for example a junction table keyed by (order_id, product_id).

Constraints are guardrails, not decoration. They enforce invariants at the one place nothing can bypass: the database. NOT NULL stops missing data, UNIQUE stops duplicate emails, a FOREIGN KEY stops orphaned rows, and a CHECK (quantity > 0, status IN (...)) stops invalid values regardless of which service wrote them. Application-level validation is not a substitute, because a second service or a manual fix can write around it.

Data types encode correctness. Store money as decimal/integer cents, never float, because binary floating point cannot represent 0.10 exactly and will drift by cents over millions of rows. Use timezone-aware timestamps (timestamptz, stored UTC) so events order correctly across regions. Size integers to the domain. Finally, decide soft vs hard delete: a deleted_at timestamp preserves history and audit trails and lets you undo, at the cost of every query filtering WHERE deleted_at IS NULL; a hard delete reclaims space and simplifies queries but loses the record. Pick soft delete when history or recovery matters, hard delete for high-churn or privacy-mandated erasure.

Recap: avoid monotonic keys for hotspots and random UUIDv4 for fragmentation, default to ULID/UUIDv7 (or Snowflake) for time-ordered distributed IDs, use surrogate keys with natural attributes as unique constraints, enforce invariants with DB constraints, and pick types that encode correctness.