Switch to UUIDv7 for client-side GUID generation

[tparvi]

In the past there have been few issues related to guid generation:

The issue #105 says We're dropping sequential Guid generation because PostgreSQL doesn't do clustered indices

The issue #2414 is closed because there really wasn't any proof/documentation provided that using sequential guids is beneficial

I ran into the following article that compared random uuids vs sequential and found an issue with random uuids:
https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-keys-in-postgresql/

I guess my question has multiple parts

a) Does that article have any merit? I am not postgres expert so I have no way of knowing is this is a real issue or can I just keep using Guid.NewGuid()
b) We are using Guid as primary key and generating it with Guid.NewGuid. There is also this GuidValueGenerator but documentation says it can only be used to generate non key values: https://www.npgsql.org/efcore/modeling/generated-properties.html?tabs=13%2Cefcore5#guiduuid-generation Is there a way to use that value generator directly like var id = GuidValueGenerator().Generate(). We need to generate the id client side.
c) If sequential uuids would be beneficial are there any recommendations on how to generate them? There are of course loads of libraries that can generate e.g. v7 uuid like https://github.com/mareek/UUIDNext

[roji]

@tparvi thanks for posting this; this question actually came up several times in some other contexts as well, and I've been meaning to look into it.

I do think there's probably something here, and am reopening to consider re-introducing some form of client-side sequential GUID generation for better perf. I've done a very quick test using the built-in EF SequentialGuidValueGenerator, following the steps in the above blog post; it did not improve performance in any way (i.e. the number of buffers was the same). I don't remember the details right now, but SequentialGuidValueGenerator was specifically crafted to match SQL Server sequential GUID generation, and so probably doesn't make sense for PG. But we should try to use a .NET UUIDv7 generator instead (e.g. based on the above-suggested UUIDNext), and at that point we should be able to get the same performance benefits as in the post.

If anyone wants to take a stab at this, please do!

In the meantime, remember that you can simply do server-side UUID generation; this should work just as well, except for scenarios where you're inserting both principal and dependent in the same SaveChanges, at which point an extra roundtrip is needed to fetch the principal's UUID (in order to send it as the foreign key of the dependent).

IMPORTANT: dotnet/efcore#30753 seems to indicate that for SQL Server, client-generation of GUIDs may not be ideal for perf in any case. I'm not sure if that would apply to PG as well (would need to investigate), but users always have the option of installing the pg_uuidv7 extension and setting up uuid_generate_v7() as the column default value (for server-side UUIDv7 generation).

PG test program with SequentialGuidValueGenerator

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

const int totalRows = 10_000_000;
const int batchSize = 1000;

for (var i = 0; i < totalRows / batchSize; i++)
{
    Console.WriteLine($"Batch {i} of {totalRows / batchSize}");
    for (var j = 0; j < batchSize; j++)
        ctx.Blogs.Add(new Blog());

    await ctx.SaveChangesAsync();
    ctx.ChangeTracker.Clear();
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql("Host=localhost;Username=test;Password=test");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>().Property(b => b.SequentialGuid)
            .HasValueGenerator(typeof(SequentialGuidValueGenerator));
    }
}

public class Blog
{
    public int Id { get; set; }
    public Guid RegularGuid { get; set; }
    public Guid SequentialGuid { get; set; }
}

[ProTip]

There has been some buzz around this in recent months.

Benchmarks: https://ardentperf.com/2024/02/03/uuid-benchmark-war/

Lingering v7 support patch: https://commitfest.postgresql.org/47/4388/

[roji]

Thanks @ProTip, that's useful!

We could take a dependency on https://github.com/stevesimmons/uuid7-csharp (thanks @Brar), or https://github.com/mareek/UUIDNext. dotnet/runtime#88290 tracks adding UUIDv7 generation in .NET itself.

One possible thing to keep in mind is that UUIDv7 isn't yet a finalized standard (still in draft). But if the generation functions make it into PG itself, that should be good enough for us too.

In the meantime, anybody who wants UUIDv7 can easily wrap one of the above .NET libraries with an EF value generator.

[cremor]

dotnet/runtime#88290 tracks adding UUIDv7 generation in .NET itself.

This has been superseeded by dotnet/runtime#103658 which was completed recently. .NET 9 will contain Guid.CreateVersion7().

[roji]

Thanks @cremor - that's indeed perfect for our needs. We should switch to this for 9.

[cremor]

Will Npgsql.EntityFrameworkCore.PostgreSQL v9 actually target/require .NET 9?
It looks like EF Core 9 will only target .NET 8 (just like EF Core 7 only targeted .NET 6).

[roji]

EF 9 (and therefore EFCore.PG 9 as well) will indeed target net8.0, not net9.0; this means we can't yet use the new BCL API for generating UUIDv7. So we can either:

1. Temporarily copy UUIDv7 generation code into the provider (I don't want us to add a full nuget dependency for this), and then remove it in 10. The implementation would need to be exactly the same as the net9.0 runtime one, since we don't want changes between 9 and 10; essentially I think we'd copy in the runtime implementation, assuming that's feasible.
2. Defer this issue for 10 altogether.

Is someone interesting in tacking (1), possibly @Timovzl?