Prompt and context
The system must find a username or email from user input using Unicode Default Caseless Matching. Explain how you would evaluate PostgreSQL 18 casefold(), choose a collation, build indexes, and migrate existing data. Do not stop at one function call.
What the interviewer evaluates
- Knowing that case folding differs from simple lowercasing and can expand one character into several.
- Checking UTF-8, the collation provider, and deployment-specific constraints.
- Explaining expression indexes, uniqueness, normalization, and migration order.
- Designing conflict detection, rollback, performance validation, and user-facing rules.
Clarifying questions to ask
- Is the requirement Unicode default caseless matching or a locale-specific sort rule?
- Is this for search, login matching, or global uniqueness after normalization?
- Can existing data contain ß, Greek letters, or combining characters? May the display value change?
- What are the database encoding, collation provider, version, and online index-change window?
30-second answer framework
I would confirm the matching and uniqueness semantics, then verify UTF-8 and a collation that supports case folding. casefold() should generate a comparison key, not replace the display value; some characters expand, and a libc provider may behave like lower(). I would generate keys offline and find conflicts before creating an expression index or stored-key unique constraint, then migrate reads and writes in stages while watching query plans. Representative multilingual samples, index usage, length changes, and rollback must be tested before launch.
Step-by-step deep dive
1. Define the matching and display boundary
Store the original value separately from the comparison key. Decide whether Unicode normalization, whitespace removal, or email-specific rules are also required; casefold only handles case folding.
2. Verify encoding and collation
The documentation requires UTF-8 server encoding. Case folding depends on the collation: a Unicode collation may fold ß to ss, while a libc provider without case-folding support makes casefold equivalent to lower. Run representative samples in the target environment before deployment.
3. Design indexes and uniqueness
Search can use an expression index on casefold(column). For uniqueness, decide whether the comparison key is persisted and how historical conflicts are resolved. Do not assume the result length is unchanged or let the display column enforce identity.
4. Migrate and validate safely
Scan existing rows for equal folded keys, define merge or manual-resolution rules, then backfill and add constraints in stages. Validate with EXPLAIN, multilingual production-like samples, concurrent writes, and retries; pause the cutover and keep a rollback switch when conflicts appear.
Model answer
I would treat casefold() as a comparison-key rule, not a display transformation. I would confirm Unicode Default Caseless Matching, UTF-8 encoding, and the target collation's behavior for characters such as ß, then check the provider because unsupported libc folding falls back to lower(). I would scan existing rows for folded-key conflicts, choose a stored key or expression index, and create the matching uniqueness constraint. The migration would backfill and dual-write in stages, with query-plan, concurrency, multilingual, and rollback tests. Users would see the original value while the matching rule remains explicit.
Common mistakes
- Treating
casefold()as a simple alias forlower(). - Ignoring UTF-8, collation, and provider differences.
- Assuming folded output keeps the same length and truncating it.
- Adding a unique constraint before scanning historical conflicts.
- Overwriting the display value with the comparison key.
- Testing only functionality and not expression-index plans on multilingual data.
Follow-up questions and responses
Can casefold replace normalization?
No. It handles case folding; combining characters, compatibility characters, and business cleanup need separate normalization rules whose order must be tested.
Why is ß a useful test case?
With collations such as PG_UNICODE_FAST, ß may fold to ss. The length change exposes matching, field-size, and uniqueness design assumptions at once.
What risk does the libc provider introduce?
The documentation says that without case-folding support, libc makes casefold equivalent to lower. Pin the collation/provider and run migration and regression tests in the production-like environment.