Fairplay Interview Prep
Module 06

PostgreSQL, SQL and Flyway

PostgreSQL is Arena's source of truth. This module teaches relational design, constraints, indexes (and why one rank query took 18 seconds), transactions and isolation levels, row locks and deadlocks, triggers, percentiles, keyset pagination, connection pools and schema migrations, using your schema and queries throughout.

P1· about 2.5 hours

By the end you can answer

  • "How does a B-tree index work? When is an index not used?"
  • "Why was the database rank lookup so slow, and how did Redis fix it?"
  • "What does ACID mean? Explain isolation levels and the anomalies they prevent."
  • "What is a lost update and how did you prevent it?"
  • "How do deadlocks happen and how did you avoid them?"
  • "How do you make a table append-only? Is your trigger enough?"
  • "How do schema migrations work, and why ddl-auto: validate?"

1. The relational model, using Arena's schema

Data lives in tables (relations) of rows with typed columns. Each row has a primary key; a foreign key references a row in another table. Arena's schema (V1__init.sql):

 players ───────────────┬──────────────< refresh_tokens   (player_id FK; token_hash UNIQUE)
  id (PK, UUID)         │
  username, email       ├──────────────< matches.recorded_by
  role, status, rating  │                 matches: id PK, external_id UNIQUE
  running totals        │                    │
  version (optimistic)  ├──< match_participants >──┘  PK (match_id, player_id), ON DELETE CASCADE
                        │
                        ├──────────────< moderation_actions  (player_id, actor_id FKs; append-only)
                        │                       │
                        └──────────────< appeals ── ban_action_id UNIQUE FK ──┘

Column types worth explaining

TypeWhereWhy
UUIDAll idsGenerated in Java before insert; 16 bytes; no guessable sequence
TIMESTAMPTZAll timesStores an absolute instant (normalised to UTC). Plain TIMESTAMP has no zone and is ambiguous. Hibernate is told hibernate.jdbc.time_zone: UTC
BIGINTLifetime totalsCan't overflow like a 32-bit INTEGER
NUMERIC(4,3)confidenceExact decimal (0.000 to 9.999, constrained to 0..1); maps to BigDecimal
JSONBevidenceBinary JSON: a flexible list of strings without a separate table; indexable with GIN if ever queried
VARCHAR(16) + CHECK (status IN (...))EnumsReadable values that match Java enum names; the CHECK rejects typos (PostgreSQL also has native ENUM types, which are harder to change)

2. Constraints: the database as the last line of defence

V1__init.sql (excerpts)
rating        INTEGER NOT NULL CHECK (rating >= 0),
headshots     INTEGER NOT NULL CHECK (headshots >= 0 AND headshots <= kills),
external_id   VARCHAR(64) NOT NULL UNIQUE,
CHECK (ended_at > started_at),
match_id      UUID NOT NULL REFERENCES matches(id) ON DELETE CASCADE,
confidence    NUMERIC(4,3) CHECK (confidence BETWEEN 0 AND 1),

Application validation can be bypassed: by a bug, by a new code path, by someone running SQL by hand, or by two concurrent requests that each pass a check. Constraints are enforced atomically by the database for every write. Two of Arena's correctness guarantees rest entirely on them: no duplicate externalId under a race, and no duplicate username under a race.

Normalisation, and one deliberate denormalisation

Normalisation means storing each fact once (no repeating groups, no column depending on only part of a key or on another non-key column), so updates can't create contradictions. Arena is normalised, with one exception: players stores running totals (total_kills, total_shots_fired, sum_reaction_ms, ...) that could be computed by summing match_participants. That is a deliberate trade-off: every profile read and every percentile query would otherwise aggregate a player's whole match history. The price is that totals must be updated in the same transaction as the match insert, under a row lock, which is exactly what MatchService does.

3. Indexes

Without an index, finding rows means a sequential scan: read every row. An index is a separate data structure that maps column values to row locations. PostgreSQL's default is the B-tree:

                    [ 1200 | 1800 ]                 ← root
              ┌────────┼──────────┐
       [ 700 | 950 ]  [1400|1600]  [2100|2300]       ← internal pages
        ┌──┼──┐        ...            ...
     leaves: sorted (rating, id) entries → row pointers, linked left↔right

Arena's indexes and the query each serves

IndexServes
ux_players_username_lower ON players (lower(username)) uniqueCase-insensitive uniqueness ("Alice" vs "alice") and login lookup. An expression index: the query must use the same expression, lower(p.username) = lower(:username), to use it (and PlayerRepository does)
ix_players_rating ON players (rating DESC, id)Ordering players by rating with a deterministic tie-break, as the leaderboard and database rank need
ix_players_status ON players (status, status_changed_at)The moderation queue: flagged players, oldest first
ix_moderation_player_time ON moderation_actions (player_id, created_at DESC)A player's history newest-first, and "latest action per player" in the queue query
ix_participants_player ON match_participants (player_id)A player's matches. The primary key (match_id, player_id) can't serve this because it starts with match_id
ix_matches_ended_at, ix_appeals_status_time, ix_refresh_tokens_playerRecent matches; pending appeals oldest-first; revoking all of a player's tokens
The leftmost-prefix rule

A composite index on (a, b) works for conditions on a, or on a and b, but generally not for b alone, just as a phone book sorted by (surname, first name) can't find everyone named "Priya". That's why match_participants needs a separate index on player_id.

When an index is not used

Use EXPLAIN ANALYZE <query> to see the actual plan: Seq Scan vs Index Scan / Index Only Scan, estimated vs actual rows, and time.

4. Case study: why the database rank lookup was slow

arena/.../leaderboard/LeaderboardService.java (lookupFromDb)
SELECT COUNT(*) + 1
FROM players p
WHERE p.role = 'PLAYER'
  AND p.status IN ('ACTIVE', 'FLAGGED', 'REINSTATED')
  AND (p.rating > :rating
       OR (p.rating = :rating AND (p.id::text COLLATE "C") > :idText))

"My rank = 1 + how many ranked players are ahead of me." It is correct, but to count the players ahead, PostgreSQL must visit every one of them. For a mid-table player in a million, that's about half a million rows per request: O(N). Measured on 1,000,000 players:

Sourcep50p95Throughput
PostgreSQL COUNT(*) + 115.62 s18.01 s12.6 req/s
Redis ZREVRANK33.07 ms63.53 ms5,764 req/s

The server-side timer showed about 1.5 s per database query; k6 saw about 15 s. The difference is queueing for a connection: 200 virtual users competed for 20 pooled connections (section 9), and the timer only starts once a connection is held. Being able to explain the gap between those two numbers is a strong signal.

The tie-break detail: COLLATE "C"

About 500 players share each rating, so ties must be ordered identically in PostgreSQL and in Redis, or the two sources would disagree on rank. Redis orders members with equal scores by comparing their bytes, and ZREVRANK reverses that, so the larger UUID string comes first. The SQL compares p.id::text COLLATE "C": the "C" collation compares raw bytes, like Redis, instead of using language-aware rules that could order some characters differently.

Could SQL do better? A RANK() OVER (ORDER BY rating DESC, id) window function is still O(N). Materialising ranks in a column means rewriting many rows on every rating change. A purpose-built structure, a sorted set with O(log N) rank, is the right tool. That's module 08.

5. Transactions and ACID

A transaction groups statements into one unit of work:

6. Isolation levels and anomalies

PostgreSQL uses MVCC (multi-version concurrency control): an update writes a new version of the row instead of overwriting it, and each transaction reads from a snapshot. Readers never block writers and writers never block readers; only two writers to the same row wait for each other.

AnomalyWhat happens
Dirty readYou read another transaction's uncommitted change, which then rolls back
Non-repeatable readYou read a row twice and get different values, because someone committed in between
Phantom readYou run the same query twice and get extra or missing rows
Lost updateTwo transactions read the same value, both modify it, and the second write erases the first
Write skewTwo transactions each check a condition over several rows, then write different rows; together they break the rule
Level (PostgreSQL)PreventsNotes
Read UncommittedBehaves like Read Committed in PostgreSQLPostgreSQL never shows dirty reads
Read Committed (default)Dirty readsEach statement sees a fresh snapshot. Lost updates are possible with read-then-write code
Repeatable Read+ non-repeatable reads, phantomsOne snapshot for the whole transaction ("snapshot isolation"). A conflicting update fails with a serialization error that you must retry
SerializableEverything, including write skewSSI detects dangerous patterns and aborts one transaction; the app must retry

The lost-update problem in Arena

Two matches finish at the same time, and Alice is in both. Under Read Committed, without locks:

 Tx A (match 1: Alice +30)            Tx B (match 2: Alice −10)
 SELECT rating → 1000
                                      SELECT rating → 1000
 UPDATE rating = 1030
 COMMIT
                                      UPDATE rating = 990     ← A's +30 is lost
                                      COMMIT

Three standard fixes:

  1. Atomic SQL: UPDATE players SET rating = rating + 30. Simple, but the code here needs the old value to compute the delta and record rating_before, and has to update many columns.
  2. Optimistic locking: a version column; the update says WHERE version = :read, and if 0 rows changed someone else won, so retry. Arena's Player and Appeal do have @Version (module 07).
  3. Pessimistic locking: SELECT ... FOR UPDATE locks the rows when read; the second transaction waits until the first commits, then reads 1030. This is what POST /matches uses, because collisions are expected and retrying a whole match is wasteful.

7. Row locks and deadlocks

arena/.../player/PlayerRepository.java
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("select p from Player p where p.id in :ids order by p.id")
List<Player> findAllByIdInForUpdate(@Param("ids") Collection<UUID> ids);
// generates roughly: SELECT ... FROM players WHERE id IN (...) ORDER BY id FOR UPDATE

A deadlock is a cycle of waits:

 Tx A (match: Alice, Bob)                 Tx B (match: Bob, Alice)
 lock Alice ✔                             lock Bob ✔
 lock Bob … waits for B                   lock Alice … waits for A      ← cycle: deadlock

PostgreSQL detects the cycle (after deadlock_timeout, 1 second by default) and aborts one transaction with an error. The cure is a global lock order: if every transaction locks rows in the same order, no cycle can form. MatchService sorts the participant UUIDs in Java and the query locks with ORDER BY p.id, so both transactions above would lock Alice first. One waits briefly instead of deadlocking.

How to say it

"Concurrent matches often share players, so I lock all participants with SELECT ... FOR UPDATE to prevent lost rating updates. To make deadlocks impossible rather than just rare, every transaction acquires those locks in the same order, sorted by UUID. Overlapping matches then queue up instead of forming a cycle."

Lock variants: FOR UPDATE NOWAIT (fail at once instead of waiting), FOR UPDATE SKIP LOCKED (skip rows others hold, which is ideal for job queues where many workers each take different rows), FOR SHARE (block writers but allow other readers).

8. Triggers: an append-only audit log

V2__audit_log_append_only.sql
CREATE FUNCTION forbid_audit_change() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'moderation_actions is append-only';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_moderation_actions_append_only
    BEFORE UPDATE OR DELETE ON moderation_actions
    FOR EACH ROW EXECUTE FUNCTION forbid_audit_change();

A trigger runs a function automatically on a table event. This one fires before any row update or delete and raises an exception, which aborts the statement and its transaction. Even a buggy repository method or a mistaken save() on a loaded entity cannot rewrite history; corrections must be new rows. AuditLogAppendOnlyTest proves it with raw UPDATE and DELETE statements against a real PostgreSQL in Testcontainers.

Curveball: is the trigger enough?

No, and saying so is impressive. Row-level triggers do not fire on TRUNCATE. Your own integration tests rely on that: AbstractIntegrationTest runs TRUNCATE ... moderation_actions ... before each test. A database owner can also DROP TRIGGER. Real tamper-resistance adds: a separate application database role with UPDATE, DELETE and TRUNCATE revoked on that table; the migration role kept separate from the runtime role; and shipping audit events to an external, write-once store. The trigger protects against application bugs, which was its purpose. Permissions protect against people.

9. SQL techniques Arena uses

Percentiles with an ordered-set aggregate

arena/.../player/StatDistributionService.java
SELECT percentile_cont(:points) WITHIN GROUP (ORDER BY <metric expression>)
FROM players
WHERE role = 'PLAYER' AND status NOT IN ('BANNED', 'APPEALED') AND matches_played >= :minMatches

percentile_cont computes continuous (interpolated) percentiles over the sorted values. Passing an array of 101 points (0.00, 0.01, ..., 1.00) returns all 101 cut points in one scan. Arena runs this for headshot rate, accuracy and reaction time every 60 seconds, caches the arrays, and finds any player's percentile with a binary search in Java (O(log 101)) instead of querying per request. When no player qualifies, the aggregate returns NULL, which crashed startup on an empty database until the extractor returned Optional.

Also note CASE WHEN total_kills = 0 THEN 0.0 ELSE total_headshots::double precision / total_kills END: it avoids division by zero, and the cast avoids integer division (in SQL, as in Java, 3 / 4 on integers is 0).

"Latest row per group" with JOIN LATERAL

arena/.../moderation/ModerationQueueQuery.java
SELECT p.id, p.username, ..., a.action, a.reason, a.requires_human_review
FROM players p
JOIN LATERAL (
    SELECT * FROM moderation_actions m
    WHERE m.player_id = p.id
    ORDER BY m.created_at DESC
    LIMIT 1                                  -- the latest action for THIS player
) a ON TRUE
WHERE p.status = 'FLAGGED'
  AND (:humanReviewOnly = FALSE OR a.requires_human_review)
ORDER BY p.status_changed_at ASC, p.id ASC
LIMIT :size OFFSET :offset

A lateral subquery can reference columns of the preceding table (p.id), so it runs once per player. With the index (player_id, created_at DESC), each run is one index probe. Alternatives you should know: DISTINCT ON (player_id) ... ORDER BY player_id, created_at DESC (PostgreSQL-specific), or ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY created_at DESC) = 1 (portable window function).

Keyset pagination for bulk reads

SELECT id, rating FROM players
WHERE role = 'PLAYER' AND status IN ('ACTIVE','FLAGGED','REINSTATED')
  AND id > :lastId              -- continue after the last row of the previous batch
ORDER BY id ASC
LIMIT :limit                    -- 10,000

LeaderboardRebuilder streams a million players into Redis this way: each batch starts with an index seek on the primary key, so batch 100 costs the same as batch 1. Redis was rebuilt from a million rows in 22.6 s.

Generating test data

The benchmark seed inserts a million rows in one statement with generate_series(1, 1000000), gen_random_uuid() and 500 + floor(random() * 2000) ratings, then ANALYZE. Set-based SQL is orders of magnitude faster than a million single inserts from Java.

10. Connection pooling with HikariCP

Opening a PostgreSQL connection is expensive (a TCP handshake, authentication, and a new backend process on the server). A pool keeps open connections and lends them out. Spring Boot uses HikariCP; Arena sets maximum-pool-size: 20.

11. Schema migrations with Flyway

The schema must evolve with the code, reproducibly, on every environment. Flyway runs versioned SQL scripts in order at application startup:

src/main/resources/db/migration/
  V1__init.sql                     -- tables, indexes
  V2__audit_log_append_only.sql    -- the trigger
Zero-downtime migrations (a senior-level follow-up)

To rename a column while old and new app versions run side by side, use expand and contract: (1) add the new column, (2) deploy code that writes both and reads the new one, (3) backfill, (4) deploy code that uses only the new one, (5) drop the old column in a later migration. Also, on big tables, CREATE INDEX CONCURRENTLY avoids locking writes while the index builds.

12. Interview questions

How does a B-tree index speed up queries, and what does it cost? Basic

It keeps keys sorted in a balanced tree of pages, so finding a key takes O(log n) page reads instead of scanning every row, and sorted leaves support range queries and ORDER BY without a sort. The cost is extra work on every insert or update to keep each index current, plus storage. So I index the columns my queries filter, join and sort on. For example, (player_id, created_at DESC) serves "a player's history, newest first".

Why was the database rank lookup 280× slower than Redis at p95? Intermediate

Rank is computed as one plus the count of players ahead, so the database has to visit all of them, which is O(N) per request: about 1.5 s server-side for a mid-table player among a million. Under 200 concurrent users with a 20-connection pool, requests also queued for connections, so clients saw about 15 s. Redis keeps members in a skip list ordered by score, which answers ZREVRANK in O(log N), around 3 ms server-side. Different data structures, different complexity.

Explain ACID. Basic

Atomicity: all of a transaction or none (a match, its participants and the rating updates). Consistency: constraints hold before and after. Isolation: concurrent transactions don't see each other's partial work, to the degree set by the isolation level. Durability: committed data survives crashes, via PostgreSQL's write-ahead log being flushed before the commit returns.

What's PostgreSQL's default isolation level, and what can still go wrong at it? Intermediate

Read Committed: each statement sees data committed before it began, so there are no dirty reads. Non-repeatable reads, phantoms and, crucially, lost updates in read-modify-write code are still possible. Two concurrent matches for the same player would both read 1000 and one rating change would be lost. I prevent that with SELECT ... FOR UPDATE on the participants. Repeatable Read and Serializable prevent more, but abort conflicting transactions, so the application must be ready to retry.

What is MVCC? Intermediate

Multi-version concurrency control: updates create new row versions rather than overwriting, and each transaction reads a consistent snapshot of the versions visible to it. Readers don't block writers and writers don't block readers; only concurrent writes to the same row conflict. Old versions are cleaned up later by VACUUM.

How did you prevent deadlocks when locking players? Deep

A deadlock needs a cycle: A holds Alice and waits for Bob while B holds Bob and waits for Alice. If every transaction takes locks in one global order, no cycle can exist. MatchService sorts the participant UUIDs and locks them with SELECT ... WHERE id IN (...) ORDER BY id FOR UPDATE, so overlapping matches serialise on their first shared player instead of deadlocking. PostgreSQL would detect and abort one side of a deadlock anyway, but that turns into an error and a retry; ordering avoids it completely.

Pessimistic or optimistic locking: when would you pick each? Deep

Optimistic (a version column; detect the conflict at write time and retry) is best when conflicts are rare and the work is cheap to redo, because it holds no locks while thinking. Pessimistic (FOR UPDATE) is best when conflicts are likely or retries are expensive, because it makes the second writer wait instead of fail. Match recording touches up to 100 players that may be in concurrent matches, so I lock pessimistically in a fixed order. Entities still carry @Version, so other update paths (profile edits, appeals) get optimistic protection, which surfaces as 409 CONCURRENT_MODIFICATION.

How do you make a table append-only, and is a trigger enough? Deep

Arena uses a BEFORE UPDATE OR DELETE ... FOR EACH ROW trigger that raises an exception, tested against real PostgreSQL. That stops application bugs. It doesn't stop TRUNCATE (row triggers don't fire on it, which is how my tests reset the table), or someone who can drop the trigger. For tamper-resistance against people, I'd add a runtime database role without UPDATE, DELETE or TRUNCATE on that table, keep migrations under a different role, and stream audit events to write-once external storage.

Why is ddl-auto set to validate? Basic

The schema is owned by Flyway's versioned, reviewed migrations. Hibernate only verifies that entity mappings match the tables, failing fast at startup on drift, and never alters the schema itself. Auto-update can't do safe renames or data migrations, and might do surprising things in production.

What happens if you edit V1__init.sql after it's been applied? Intermediate

Flyway compares checksums with flyway_schema_history and fails validation at startup: the app won't start. Applied migrations are immutable history; changes go in a new version (V3__...). Only in local development might you reset the database or run flyway repair.

Your rank SQL has COLLATE "C". Why? Curveball

With about 500 players per rating, the tie order must match Redis exactly, or the two rank sources would disagree. Redis orders equal-score members by raw byte comparison (reversed for ZREVRANK). PostgreSQL's default collation may follow language rules. COLLATE "C" forces byte order, so the database ordering is identical to Redis's.

Why only 20 database connections when you have virtual threads? Curveball

Each PostgreSQL connection is a server process with its own memory, and throughput is bounded by the database's CPU and I/O, not by client threads. Past a modest pool size, more connections add context switching and lock contention and lower throughput. Virtual threads make waiting cheap on the application side; the pool is the deliberate limit, and the fix for latency under load is faster queries (or Redis), not a bigger pool.

How would you get the latest moderation action per player? Intermediate

Arena uses JOIN LATERAL (SELECT ... WHERE m.player_id = p.id ORDER BY created_at DESC LIMIT 1), backed by an index on (player_id, created_at DESC), so it's one index probe per flagged player. Alternatives: PostgreSQL's DISTINCT ON (player_id), or a window function ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY created_at DESC) = 1.

13. Quick quiz

1. An index on (player_id, created_at) helps which query most?

The leftmost-prefix rule: filter on the first column, ordered by the second.

2. Two transactions lock rows in opposite orders. This risks:

The fix is a consistent lock order (sorted UUIDs).

3. Which statement bypasses a BEFORE UPDATE OR DELETE ... FOR EACH ROW trigger?

Row-level triggers don't fire on TRUNCATE.

4. PostgreSQL's default isolation level is:

Each statement sees a fresh snapshot of committed data.

5. You edit an already-applied Flyway migration. On next start:

Applied migrations are immutable; add a new version.

6. Why did k6 measure ~15 s while the server timer said ~1.5 s for the database rank?

The server timer starts after a connection is acquired.

14. Cheat sheet

  • Schema: UUID ids; TIMESTAMPTZ; BIGINT totals; NUMERIC confidence; JSONB evidence; CHECK enums; a composite PK on the join table; UNIQUE external_id, token_hash, ban_action_id.
  • Constraints = the last line of defence and the only race-proof check.
  • Denormalised totals on players, updated under a row lock in the same transaction.
  • B-tree: O(log n), sorted, ranges and ORDER BY; leftmost prefix; expression index lower(username); EXPLAIN ANALYZE; ANALYZE refreshes statistics.
  • DB rank = COUNT of players ahead = O(N): 1.5 s server, 15 s client (pool queueing). Redis ZREVRANK O(log N). COLLATE "C" matches Redis tie order.
  • ACID; the WAL gives durability; MVCC: readers and writers don't block each other.
  • Isolation: Read Committed (default) → lost updates possible; Repeatable Read = snapshot; Serializable = SSI with retries.
  • Lost update fixes: atomic UPDATE, optimistic @Version, pessimistic FOR UPDATE (used for matches).
  • Deadlocks: a cycle of waits; prevent with a global lock order (sorted UUIDs + ORDER BY id FOR UPDATE).
  • Trigger blocks UPDATE/DELETE, not TRUNCATE or DROP; add role permissions for real tamper-resistance.
  • SQL: percentile_cont(array) WITHIN GROUP, cached 101 cut points; JOIN LATERAL ... LIMIT 1; keyset WHERE id > :last; generate_series.
  • Hikari: 20 connections; a small pool is deliberate; keep transactions short; open-in-view off.
  • Flyway: versioned and immutable, a checksum in flyway_schema_history; ddl-auto: validate; expand and contract for zero downtime.
Saved in this browser.