My notes on Gitlab's Postgres schema design (2022)

Original Article ↗ Hacker News Discussion ↗

GitLab’s PostgreSQL schema choices spark wide-ranging critique of how modern apps model data at scale. Commenters examine trade-offs between 32‑bit and 64‑bit primary keys, UUIDs versus sequential IDs, and strategies for online schema migration, highlighting how index locality, foreign keys, and ORM abstractions can make or break performance on billion-row tables. The thread also questions common “best practices” such as hiding primary keys from users, arguing these often reflect business and competitive concerns more than security, and emphasizing that careful, database-first schema design remains crucial despite tooling advances.

Primary key size, migrations, and tooling

  • Many comments debate int vs bigint PKs. Some argue hitting 32‑bit limits is a real risk for large services and “too close for comfort,” others note real data (e.g. GitHub repos/issues) is still under 2^31.
  • Migrating int→bigint is described as feasible but non‑trivial at scale: table rewrites, index rebuilds, foreign keys, and single‑threaded index creation can take hours on multi‑TB tables.
  • Zero‑/low‑downtime approaches mentioned: logical replication with switchover, online schema change tools (pg-osc, gh-ost), decoupling schema migrations from app deploys, and DBA‑run migration systems.
  • Some note secondary pain: JavaScript bigint deserialization, downstream consumers assuming 32‑bit ints.

UUIDs vs sequential integers

  • UUIDv4 as PK is criticized mainly for performance, not size. Extra 8 bytes per FK/index entry compound across many tables and make it harder for indexes to fit in RAM.
  • Random UUIDs destroy index locality: btree inserts become scattered, cause page bloat, and lead to severe performance cliffs once indexes exceed memory, not just a steady ~25% hit.
  • Time‑ordered IDs (UUIDv6/v7, Snowflake‑style, encrypted or permuted sequences) are discussed as better compromises, but have trade‑offs (time leakage, key rotation, complexity).

Schema design, ORMs, and migrations

  • Several argue schema design is “stone age” and migration tools (EF, Rails, Django, Prisma, etc.) either generate dangerous migrations (locks, rewrites) or obscure what actually runs.
  • Strong current in favor of schema‑first thinking and plain SQL (often with stored procedures) over heavy ORMs, which are seen as leaky, complex, and hard to operate at scale.
  • Others defend ORMs as useful for dynamic queries and complex object graphs, if used with understanding.

GitLab vs GitHub architecture and performance

  • Some perceive GitLab pages as noticeably slower than GitHub. Explanations offered include culture and prioritization of performance, but details are anecdotal and incomplete.
  • There is disagreement/uncertainty over whether GitLab.com is a single multi‑tenant DB vs DB‑per‑customer; one comment asserts it’s essentially a multitenant instance of their self‑hosted product.

Exposing primary keys and “external IDs”

  • One camp sees hiding sequential PKs as mostly “security theater”; if authz is broken, guessed IDs shouldn’t matter.
  • Others emphasize defense in depth and competitive intelligence: sequential IDs reveal counts/growth (orders, users, issues) and make mass enumeration and exploitation easier.
  • Examples include e‑commerce order volumes, user enumeration, and scraping; counter‑arguments claim motivated attackers can often infer similar data anyway.
  • GitLab‑style internal id plus per‑project iid is seen as user‑friendly and decouples URLs from internal PK changes, at the cost of extra joins and indexes.

Postgres specifics: text vs varchar, FKs

  • Discussion clarifies that in Postgres, text vs varchar(n) has no runtime performance difference; the real issue is migration cost when changing lengths on varchar(n) vs adjusting a CHECK on text.
  • One commenter pushes back on the idea that foreign keys are “expensive,” arguing that integrity must be enforced somewhere and DB‑level FKs usually win if used correctly.