5 ms·

uuidv7 will be supported in PostgreSQL 17, at which point generation should be as fast as uuidv4, or if you implement it in pgsql now it will be as fast as the

uuidv7 will be supported in PostgreSQL 17, at which point generation should be as fast as uuidv4, or if you implement it in pgsql now it will be as fast as the proposed ULID algorithm. Insert performance could be even better, iirc for BTree Indexes monotonic increasing values are better than random ones, but feel free to correct me on that ;)

lfittl 2y ago
Commenting on this a bit late, but in case anyone reads this later too: UUIDv7 support unfortunately didn't make it to Postgres 17, since the RFC wasn't completely finalized yet by the time of feature freeze (April 8), see discussion on pgsql-hackers: https://www.postgresql.org/message-id/flat/ZhzFQxU0t0xk9mA_%40paquier.xyz#bbf76f7ce0122496b747baf9d657f5c8 https://www.postgresql.org/message-id/flat/ZhzFQxU0t0xk9mA_%... So I guess we'll unfortunately have to rely on extensions or client-side generation for the time being, until Postgres 18.
hobs 2y ago
As with databases it always depends, for maximum insert performance you'd actually often go with random uuids so you dont get a hot page.
- HumanOstrich 2y ago
  Hot page? Using a monotonically increasing PK would cause pages in the index to be allocated and filled sequentially, increasing throughput. Using random UUIDs would lead to page-splitting and partially-filled pages everywhere, negatively impacting performance and size-on-disk.
  - rileymat2 2y ago
    Not always, this article describes the problem: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/resolve-pagelatch-ex-contention https://learn.microsoft.com/en-us/troubleshoot/sql/database-...
    - hobs 2y ago
      Exactly, with one big insert its better to have a sequential value, for many small ones its often better to not. As with all databases, measure before you cut.
  - erik_seaberg 2y ago
    The way to take advantage of the bandwidth of multiple storage devices is to distribute concurrent writes across them, rather than forcing everything to commit sequentially using contended locks or rollbacks.
    - sgarland 2y ago
      The DB (or any application) should not have any need to know what devices are underneath its mount point. If you’re striping across disks, that’s a device (or filesystem, for ZFS) level implementation.
SahAssar 2y ago
In that case I don't understand why the author didn't go for uuidv7? It seems like existing tooling (both in database and outside) deals with it better, it seems like there are no downsides unless you expect your identifiers to be generated past 4147 but don't care if they are generated past 10889 (I'd love to hear that use-case, seems like it must be interesting).

lfittl 2y ago

Commenting on this a bit late, but in case anyone reads this later too: UUIDv7 support unfortunately didn't make it to Postgres 17, since the RFC wasn't completely finalized yet by the time of feature freeze (April 8), see discussion on pgsql-hackers: https://www.postgresql.org/message-id/flat/ZhzFQxU0t0xk9mA_%40paquier.xyz#bbf76f7ce0122496b747baf9d657f5c8 https://www.postgresql.org/message-id/flat/ZhzFQxU0t0xk9mA_%... So I guess we'll unfortunately have to rely on extensions or client-side generation for the time being, until Postgres 18.

hobs 2y ago

As with databases it always depends, for maximum insert performance you'd actually often go with random uuids so you dont get a hot page.

HumanOstrich 2y ago
Hot page? Using a monotonically increasing PK would cause pages in the index to be allocated and filled sequentially, increasing throughput. Using random UUIDs would lead to page-splitting and partially-filled pages everywhere, negatively impacting performance and size-on-disk.
- rileymat2 2y ago
  Not always, this article describes the problem: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/resolve-pagelatch-ex-contention https://learn.microsoft.com/en-us/troubleshoot/sql/database-...
  - hobs 2y ago
    Exactly, with one big insert its better to have a sequential value, for many small ones its often better to not. As with all databases, measure before you cut.
- erik_seaberg 2y ago
  The way to take advantage of the bandwidth of multiple storage devices is to distribute concurrent writes across them, rather than forcing everything to commit sequentially using contended locks or rollbacks.
  - sgarland 2y ago
    The DB (or any application) should not have any need to know what devices are underneath its mount point. If you’re striping across disks, that’s a device (or filesystem, for ZFS) level implementation.

SahAssar 2y ago

In that case I don't understand why the author didn't go for uuidv7? It seems like existing tooling (both in database and outside) deals with it better, it seems like there are no downsides unless you expect your identifiers to be generated past 4147 but don't care if they are generated past 10889 (I'd love to hear that use-case, seems like it must be interesting).