Your repository tests run on H2 — what bugs slip through that a PostgreSQLContainer would catch?

## The premise to reject "H2 in PostgreSQL mode is close enough" is the assumption under test here. H2 is an independent database that *accepts* a portion of PostgreSQL's syntax. It does not embed PostgreSQL's parser, planner, type system, MVCC implementation or error codes. Compatibility mode narrows the syntax gap; it cannot narrow the behaviour gap, because there is no shared code. Everything below follows from that one fact. ## Class 1: SQL and type coverage The features teams reach for in PostgreSQL are exactly the ones least likely to be emulated: `jsonb` and its containment and path operators, native arrays, `ON CONFLICT … DO UPDATE` upserts, `LATERAL` joins, range types, full-text search with `tsvector`, and anything provided by an extension. Type semantics differ too — timestamp-with-time-zone handling, numeric precision behaviour, text versus varchar. The practical consequence is a chilling effect: developers avoid the database features that would make the code simpler and faster, because "the tests won't run". Testing against the real engine is as much about what it *permits* you to write as about what it catches. ## Class 2: migrations Migration scripts are among the highest-risk SQL in any codebase — they run once, in production, usually under a deploy window. If your tests apply them to H2, then either the scripts are written to a lowest common denominator (and are not the scripts production runs), or they are PostgreSQL scripts that H2 partially accepts (and the test proves nothing about them). Running migrations against a real container at the start of the suite converts the riskiest artefact you own into the most-exercised one. Many teams find their first container-backed run fails immediately on a migration — that failure is the value being delivered, not a setback. ## Class 3: semantics and error handling Unquoted identifiers are folded differently: PostgreSQL lower-cases, H2 historically upper-cases, and a quoted identifier is case-sensitive in both. Mixed-case names and quoting therefore behave differently between the two. Error codes matter more than people expect. Code that catches a constraint violation and maps it to a domain error — "email already registered" — depends on the SQLState and the constraint name the engine reports. If that mapping is only ever exercised against H2, the branch you most want to trust is the one you never truly ran. Also in this class: default values and generated keys, sequence versus identity behaviour, `NULL` ordering, collation-dependent sorting, and the ORM dialect chosen at runtime — you are testing a different dialect implementation from the one production uses. ## Class 4: concurrency The most expensive production incidents live here. Row-level locking, `SELECT … FOR UPDATE` and its `SKIP LOCKED` variants, deadlock detection and which transaction gets aborted, lock-wait timeouts, and the precise behaviour of isolation levels are all engine-specific. A queue-worker pattern or an inventory decrement that works under one engine's concurrency model can corrupt data under another. No amount of H2 testing tells you anything here. ## What H2 is still good for Speed, and only speed. If a test genuinely exercises Java logic — mapping, validation, a service orchestrating collaborators — and merely needs *a* database to exist, an in-memory one is legitimate. The mistake is letting that convenience decide the fidelity of your *repository* and *migration* tests, which exist precisely to test the database interaction. ## Paying the cost honestly A container adds startup time to the suite. The mitigation is architectural — share one database container across the suite rather than starting one per test class — and it works well enough that container-backed persistence testing is now the default in most JVM shops. Docker availability on developer machines and build agents becomes a hard requirement, which is a real operational commitment worth stating. ## Interview framing Structure the answer by class of defect rather than reciting a feature list, name migrations explicitly (it is the point most candidates miss), and acknowledge the cost with a concrete mitigation. Ending with "and it lets us actually use the database we chose" shows you understand the upside as well as the risk.