Application‑level retry with ON CONFLICT DO NOTHING versus DuckDB’s automatic SERIALIZABLE retry for exactly‑once writes

Goal: achieve exactly‑once semantics for write operations in DuckDB when transient serialization failures may occur, without creating duplicate rows.

Two documented approaches exist: (1) wrap the INSERT in an application‑level retry loop and use ON CONFLICT DO NOTHING (or a unique key) so that retries are idempotent; (2) rely on DuckDB’s default SERIALIZABLE isolation, which automatically retries a transaction after a serialization failure, requiring the application only to re‑execute the unchanged SQL after catching error 40001.

• The application‑level approach needs explicit back‑off logic and a suitable unique constraint, but gives full control over retry policy.
• The engine‑level approach hides the retry policy, which is not configurable and may evolve between releases, and requires the table to have a primary key or unique index to avoid duplicate inserts when a retry occurs.

Given these trade‑offs, which approach provides more predictable behavior under varying DuckDB versions, and how does the requirement for a unique constraint influence schema design?