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.
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 ──┘
- One-to-many: a player has many refresh tokens and many moderation actions.
- Many-to-many: players and matches, through the join table
match_participants, whose composite primary key(match_id, player_id)makes "a player appears at most once per match" a database fact. It also carries data about the relationship (placement, kills, rating before and after). - One-to-one-ish:
appeals.ban_action_idisUNIQUE, so a ban can be appealed at most once.
Column types worth explaining
| Type | Where | Why |
|---|---|---|
UUID | All ids | Generated in Java before insert; 16 bytes; no guessable sequence |
TIMESTAMPTZ | All times | Stores an absolute instant (normalised to UTC). Plain TIMESTAMP has no zone and is ambiguous. Hibernate is told hibernate.jdbc.time_zone: UTC |
BIGINT | Lifetime totals | Can't overflow like a 32-bit INTEGER |
NUMERIC(4,3) | confidence | Exact decimal (0.000 to 9.999, constrained to 0..1); maps to BigDecimal |
JSONB | evidence | Binary JSON: a flexible list of strings without a separate table; indexable with GIN if ever queried |
VARCHAR(16) + CHECK (status IN (...)) | Enums | Readable 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
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
- Balanced and shallow: a lookup is O(log n) page reads. A million rows is only about 3–4 levels.
- Sorted, so it also serves range queries (
rating > 1500) andORDER BYwithout sorting, by walking the linked leaves. - Cost: every insert or update must also update each index, and indexes take disk and memory. Index what you query.
Arena's indexes and the query each serves
| Index | Serves |
|---|---|
ux_players_username_lower ON players (lower(username)) unique | Case-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_player | Recent matches; pending appeals oldest-first; revoking all of a player's tokens |
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
- The column is wrapped in a function that the index doesn't match (
WHERE upper(username) = ...against alower()index). - The condition matches a large fraction of the table; then a sequential scan is cheaper, and the planner knows it from statistics. That's why the seed script ends with
ANALYZE players;, which refreshes those statistics. - Leading wildcard:
LIKE '%abc'. (Arena's search uses a prefix,lower(username) LIKE 'abc%', which can use a B-tree under the right collation or operator class.) - Type mismatches that force a cast on the indexed column.
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
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:
| Source | p50 | p95 | Throughput |
|---|---|---|---|
PostgreSQL COUNT(*) + 1 | 15.62 s | 18.01 s | 12.6 req/s |
Redis ZREVRANK | 33.07 ms | 63.53 ms | 5,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.
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:
- Atomicity: all or nothing. Recording a match inserts a match, inserts N participants and updates N players. If anything fails, none of it happened.
- Consistency: every transaction takes the database from one valid state (all constraints satisfied) to another.
- Isolation: concurrent transactions don't see each other's half-done work (how strictly depends on the isolation level).
- Durability: once committed, the change survives a crash. PostgreSQL writes changes to the write-ahead log (WAL) and flushes it to disk before confirming the commit.
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.
| Anomaly | What happens |
|---|---|
| Dirty read | You read another transaction's uncommitted change, which then rolls back |
| Non-repeatable read | You read a row twice and get different values, because someone committed in between |
| Phantom read | You run the same query twice and get extra or missing rows |
| Lost update | Two transactions read the same value, both modify it, and the second write erases the first |
| Write skew | Two transactions each check a condition over several rows, then write different rows; together they break the rule |
| Level (PostgreSQL) | Prevents | Notes |
|---|---|---|
| Read Uncommitted | Behaves like Read Committed in PostgreSQL | PostgreSQL never shows dirty reads |
| Read Committed (default) | Dirty reads | Each statement sees a fresh snapshot. Lost updates are possible with read-then-write code |
| Repeatable Read | + non-repeatable reads, phantoms | One snapshot for the whole transaction ("snapshot isolation"). A conflicting update fails with a serialization error that you must retry |
| Serializable | Everything, including write skew | SSI 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:
- Atomic SQL:
UPDATE players SET rating = rating + 30. Simple, but the code here needs the old value to compute the delta and recordrating_before, and has to update many columns. - Optimistic locking: a
versioncolumn; the update saysWHERE version = :read, and if 0 rows changed someone else won, so retry. Arena'sPlayerandAppealdo have@Version(module 07). - Pessimistic locking:
SELECT ... FOR UPDATElocks the rows when read; the second transaction waits until the first commits, then reads 1030. This is whatPOST /matchesuses, because collisions are expected and retrying a whole match is wasteful.
7. Row locks and deadlocks
@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.
"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
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.
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
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
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.
- When all 20 are busy, the next request waits for a connection (up to a timeout). That's the queueing that inflated the benchmark's database latency from 1.5 s to 15 s.
- Bigger is not better. Each PostgreSQL connection is a process using memory, and the database only has so many cores and disks. Beyond a modest size, more connections add contention and make everything slower. A common starting point is around (cores × 2) + effective disks, then measure.
- With virtual threads you can have thousands of concurrent requests, but still only 20 connections. The pool is the real concurrency limit for database work, so keep transactions short.
spring.jpa.open-in-view: falsematters here too: with open-in-view on, a connection can stay attached through view rendering and JSON serialisation, so it is held longer than needed.
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
- Flyway records each applied script in a table called
flyway_schema_history, with a checksum. On startup it applies only the scripts that are new. - Never edit an applied migration. The checksum would no longer match and Flyway refuses to start. To change something, add
V3__.... Migrations are forward-only. - Hibernate is set to
ddl-auto: validate: at startup it checks that the entities match the tables and fails if they don't, but it never changes the schema.updateorcreate-droplets Hibernate guess DDL, which is fine for prototypes and dangerous in production (it can't rename a column safely, and it never drops anything). - Integration tests run the same migrations against a real PostgreSQL 17 container, so the schema used in tests is exactly production's.
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;ANALYZErefreshes statistics. - DB rank = COUNT of players ahead = O(N): 1.5 s server, 15 s client (pool queueing). Redis
ZREVRANKO(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, pessimisticFOR 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; keysetWHERE 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.