SERIAL and UUID keys

Surrogate primary keys identify rows independently of business data. Postgres traditionally used SERIAL; modern apps often prefer UUID for distributed ID generation.

SERIAL and IDENTITY

CREATE TABLE orders_serial (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL
);

CREATE TABLE orders_identity (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id INT NOT NULL
);

SERIAL is shorthand creating a sequence; IDENTITY is SQL-standard and preferred in new schemas.

Practice: Run in psql or DB Fiddle (PostgreSQL). Use your practice database from the workflow lesson.

UUID keys

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE orders_uuid (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id INT NOT NULL
);

UUIDs avoid coordination across services; index size is larger than integers—acceptable for many OLTP tables.

Choosing a strategy

Use bigint identity for single-database monoliths; UUID when exposing IDs publicly or sharding writes. Natural keys (email) still get UNIQUE constraints.

Important interview questions and answers

1. Q: SERIAL implementation?
   A: Creates an integer column plus a sequence default—not a true type.
2. Q: UUID index size?
   A: 16-byte keys vs 8-byte bigint—more index pages but better for distributed systems.

Self-check

1. What does SERIAL create behind the scenes?
2. When might UUID beat SERIAL?

Tip: Prefer GENERATED AS IDENTITY over SERIAL in new schemas.