SQL, indexing and PostgreSQL interview questions

B-tree and other index types, reading EXPLAIN, N+1 queries, upserts, pagination, NULL rules, pooling and migrations that don't lock production.

5 quick facts

– interview-ready · 14 questions in this topic

– of 14 tried

Saved on this device. Interview-ready means you got it right on two different days. Guesses and hints count for less.

Questions

Practise this topic

Flashcards · 19

Flip, then say if you knew it. Needs JavaScript; every answer is also on its question page.

Quick quiz · 14 questions

One exercise per question, checked as you go. Needs JavaScript.

Mock interview · 2 sets

Timed, out loud, then tick what you covered. Needs JavaScript.

Fix this PR · 1 review

Review a diff like a senior: flag the lines you'd comment on. Needs JavaScript.

Quick facts

Recall the answer first, then tap Show to check.

char(n) vs varchar vs text
In PostgreSQL there's no speed difference between them; char(n) pads with spaces and is usually the slowest. Prefer text with a CHECK if you need a limit.
integer range
PostgreSQL integer is signed 32-bit: up to 2,147,483,647. Use bigint (or bigint identity) for ids that can grow.
Identity columns
id bigint GENERATED ALWAYS AS IDENTITY is the standard replacement for serial.
LIMIT without ORDER BY
Row order is unspecified without ORDER BY; the same query can return different rows run to run.
UUIDv7
Time-ordered UUIDs keep B-tree inserts local, unlike random v4. PostgreSQL 18 adds uuidv7().
PostgreSQL 18+

Printable cheat sheet · Review your weak spots in this topic

Sources: Topic outline informed by System Design Primer by Donne Martin and the Zalando RESTful API Guidelines, both licensed under CC BY 4.0. Every question and answer is written by Best Latest Apps. Credits and license