Data interview: How would you use NULLS NOT DISTINCT for optional keys in PostgreSQL 18?

Prompt and context

An orders table allows one optional external reference per tenant: at most one row per tenant may have NULL. How would you enforce this in PostgreSQL 18 while handling historical duplicates, concurrent writes, and rollback?

PostgreSQL’s default unique index treats NULL values as distinct, so multiple NULLs do not conflict. PostgreSQL 18 adds NULLS NOT DISTINCT, making NULL values participate in uniqueness. The question tests constraint design, migration safety, and concurrency semantics rather than moving a race-prone business check into application code.

What the interviewer is testing

• Whether you explain default NULL uniqueness versus NULLS NOT DISTINCT precisely.
• Whether you know the option applies to unique B-tree indexes or unique constraints, not ordinary comparisons.
• Whether you find and resolve historical duplicate NULL and non-NULL combinations first.
• Whether you design an online migration, lock plan, concurrent-write behavior, and rollback.
• Whether ORM, replication, partitioning, and downstream contracts remain compatible.

Questions to clarify first

• Is the scope the whole table, or one rule per tenant, region, or active state?
• Does NULL mean unassigned, unknown, or an intentional shared reference?
• Do historical rows contain multiple NULLs, empty strings, case variants, or soft-deleted keys?
• What are the write rate, lock budget, and migration window?
• Do the application, ORM, CDC, and reports assume that NULL may repeat?

Thirty-second answer

“I would clarify the meaning of NULL and the scope, then audit historical duplicates. For a tenant-scoped rule, I would put tenant and reference in a composite unique key with NULLS NOT DISTINCT; the option makes NULL participate in that key’s uniqueness but does not change SQL’s three-valued comparison or make the column NOT NULL. I would clean or decide historical conflicts, deploy conflict handling before the index, build it with a lock and latency plan, and test ORM and CDC behavior. The database becomes the concurrent final authority; if the business semantics are wrong, I roll back the constraint and application policy rather than rely on a racy pre-check.”

Step-by-step deep dive

Step 1: Confirm NULL meaning and scope

Distinguish an unassigned value from an unknown value. If NULL means unassigned, one NULL may be the intended rule; if it means unknown and repeats are valid, uniqueness is wrong. Decide whether the constraint is global or grouped by tenant, region, and active state, then choose composite-key order and whether a partial index is needed.

Step 2: Choose the database expression

PostgreSQL 18 supports NULLS NOT DISTINCT on unique indexes. The default treats NULLs as unequal and permits multiple NULLs. A unique constraint may make the model clearer, while a unique B-tree index can fit an online migration. The option changes uniqueness comparison only: WHERE value = NULL still follows three-valued logic, and the column can remain nullable.

Step 3: Audit and clean historical data

Group by the proposed key and count multiple NULLs, empty strings, case variants, and soft-deleted rows that still occupy a key. Decide per conflict whether to merge orders, fill the reference, retain one row and migrate the others, or document an exception. Make cleanup replayable and auditable, and validate it in a shadow environment before index creation exposes unresolved conflicts.

Step 4: Design the online migration

Release compatible application handling for unique conflicts first, then create the index or constraint during a controlled window. For a large table, evaluate concurrent creation, lock level, disk space, and write latency; watch conflicts and long transactions throughout. If tenants must be phased, create the rule in batches and record a completion watermark. Keep the old pre-check until the database constraint and error mapping are ready.

Step 5: Handle concurrency and downstream contracts

The unique index is the final arbiter for concurrent inserts and updates. An application “check then insert” can improve the message but cannot replace the constraint. Map a unique violation to a retryable or user-visible business error without infinite retries. Check CDC, replication, ORM schema, reports, and caches for assumptions that NULL can repeat, then update contracts and alerts.

Step 6: Verify, monitor, and roll back

In staging and a canary tenant, test one NULL, a second NULL, equal non-NULL values, different non-NULL values, updates, delete-and-recreate, and concurrent writes. Monitor index build, lock waits, conflict rate, application errors, and downstream latency. If the semantics or conflict rate are unacceptable, stop the new write path, remove the constraint, restore compatible error handling, and preserve the audit trail for analysis.

High-quality sample answer

I would first confirm the meaning of NULL and the scope. If each tenant may have one optional reference, I would include tenant and reference in a composite unique key and use NULLS NOT DISTINCT in PostgreSQL 18. It makes NULL participate in uniqueness for that key, while ordinary SQL three-valued logic and the nullable column remain unchanged.

Before rollout, I would audit multiple NULLs, empty strings, case variants, and soft-deleted rows, decide how each conflict is merged or filled, and record the decision. I would deploy unique-conflict handling before building the index, then monitor locks, space, long transactions, and conflicts. Tests cover one and two NULLs, equal and different non-NULLs, updates, delete-and-recreate, and concurrency. The database is the final authority, and ORM, CDC, reports, and caches must adopt the same contract; if the business meaning proves wrong, I remove the constraint and roll back the application policy.

Common mistakes

• Assuming UNIQUE allows only one NULL by default → PostgreSQL treats NULLs as distinct → use NULLS NOT DISTINCT explicitly.
• Treating it as NOT NULL → The option still allows one NULL → separate missing-value meaning from uniqueness.
• Using only an application pre-check → Concurrent requests still race → let the database unique index decide.
• Ignoring empty strings and case variants → Business duplicates may not be NULL conflicts → define normalization and cleanup first.
• Building online without historical cleanup → Existing duplicates can fail or block the migration → audit, decide, and monitor long transactions.
• Changing only the database → ORM, CDC, and reports may still assume repeatable NULLs → update the data contract and error mapping.

Follow-up questions

Does NULLS NOT DISTINCT change ordinary NULL comparisons?

No. It changes whether NULLs collide in a unique index. WHERE value = NULL still follows SQL’s three-valued logic and should use IS NULL. Query semantics, index semantics, and column nullability must be explained separately.

How can you migrate without downtime when two NULLs already exist?

Choose the retained row per tenant and business state, merge or fill the others, and record the decision in an audit table. Deploy conflict handling, create the index in batches while monitoring locks and long transactions, and defer tenants whose conflicts cannot be resolved in the window.

Should a multi-tenant rule use a composite key or a partial index?

If every state must be unique, use tenant plus reference in a composite unique key. If only active rows are constrained, a partial index limited to active state may fit. The choice depends on delete, restore, and state-transition contracts and must be proved with concurrent tests.