PostgreSQL and UUID as Primary Key

Re:PostgreSQL and UUID as Primary Key by aabhay on Friday July 05, 2024 @04:51PM (#40885889)

I think insert performance is a bad way to evaluate performance here, no? While B-Tree performance for time sorted keys is better on insert, what about during large transactions?

In SQLite, my assumption was that the consensus was towards UUID4 rather than 7 because it meant less likelihood for page cache contention during transaction locks? Would that not also roughly map onto a Postgres-flavored system? Or dues Postgres only have row-level locking?

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by samokhvalov on Friday July 05, 2024 @05:02PM (#40885979)

there is also a problem of data locality and blocks present in caches (page cache, buffer pool) at any given time, in general -- UUIDv4 is losing to bigint and UUIDv7 in this area

[ Parent | on HN ]

Re:PostgreSQL and UUID as Primary Key by shi on Saturday July 06, 2024 @08:02AM (#40889894)

This is a valid point but it highly depends on the use case and larger context on whether this will be relevant or not. If you have a table where you fetch multiple rows close to each other in paginated manner this would be relevant for the performance but if you only fetch individual records by uuid, data locality wouldn’t increase the performance.

[ Parent | on HN ]