Is it faster to try and avoid UNIQUE violations when inserting into a PostgreSQL database with efcore, or to catch the resulting exception?

Question

Assuming I have a UNIQUE constraint on a column in my postgresql database, is it more performant to query the database before attempting to insert a new value or is it acceptable to catch the thrown exception when the database rejects the query?

My assumption was that postgres will check for these violations faster than it takes for EF core to construct and execute a query + then run the insertion. Is this the case?

try
{
    // _dctx is my DbContext
    _dctx.SomeTable.Add(newEntity);
    await _dctx.SaveChangesAsync();
}
catch (DbUpdateException ex)
{
    if (ex.InnerException is PostgresException npgex && npgex.SqlState == PostgresErrorCodes.UniqueViolation)
    {
        // Reached only when the UNIQUE constraint was violated
    }
    throw; // How other exceptions are handled isn't relevant to the question
}

vs

try
{
    if (await _dctx.SomeTable.AnyAsync(x => x.UniqueProperty == hopefullyUniquePropertyValue))
    {
        // Handle duplicate insertion here
    }
    _dctx.SomeTable.Add(newEntity);
    await _dctx.SaveChangesAsync();
}
catch (DbUpdateException ex) {}

Since this will be done concurrently / over several instances of this service I'll still be expecting to get these exceptions occasionally, but the question is whether the overhead of the AnyAsync() call is going to be substantially greater than that of allowing the database + ORM to take care of it?

Answer 1

Checking whether a row exists before inserting is definitely the worse option, for two reasons:

• You are executing two SQL commands: one query to check if the row exists, and another to insert a new one. This means two network roundtrips, two commands executed in the database... It's quite a lot heavier than a single insert.
• The database may have changed between your check and your insert, so you may get unique constraint violations in any case (which you're not checking in your sample above).

However, rather than discussing performance in theory, it's always far better to simply benchmark the two options (with something like BenchmarkDotNet).