Use of ULID as Postgres (psycopg2) "UUID" primary key

[glentner]

I'm looking into performance enhancements to a few apps I have going by switching from standard uuid.uuid4 generated primary keys to sneaking in ULID values, thinking the lexicographical sortable ULID will increase efficiency of transactions.

In the app we use str(uuid.uuid4()) on the client side to build the Postgres inserts/updates. My understanding (from the docs) is that ULID().to_uuid4() would be destructive to the underlying byte-order, and so would in-effect just be a UUIDv4. But that using ULID().to_uuid() would look like a valid UUID as far as Postgres is concerned, but the underlying bytes would be friendlier to the database's b-tree Index and page-cache.

I have a well resourced Postgres instance though that I've been doing performance testing on the last two days and I cannot detect any significant different in throughput of inserts or updates. I feel like I'm missing something though. Should I not be able to insert with str(ULID.to_uuid()) or have I missed something?

[johnpaulett]

PostgreSQL (or the driver) natively converts from the text UUID to the actual uuid datatype that is stored.

I cannot detect any significant different in throughput of inserts or updates

I'm guessing the conversion from string to uuid is fast

Effectively your str(ULID.to_uuid())) is running INSERT INTO mytable SET id='00000000-0000-0000-0000-000000000000'; and the DB casts that text to uuid, equivalent to INSERT INTO mytable SET id='00000000-0000-0000-0000-000000000000'::uuid

ULID().to_uuid4() would be destructive to the underlying byte-order, and so would in-effect just be a UUIDv4

to_uuid() not to_uuid4(). Sure, it's stored as a uuid, but you can recover this into a ULID, via ULID.from_uuid(db_value)

If using SQLAlchemy, you can do this automatically with a new SA type:

class ULIDType(types.TypeDecorator[ULID]):
    impl = UUID

    cache_ok = True

    def process_bind_param(
        self,
        value: ULID | str | None,
        dialect: Dialect,
    ) -> Optional[_UUID]:
        if value is None:
            return value

        if isinstance(value, str):
            return ULID.from_str(value).to_uuid()

        return value.to_uuid()

    def process_result_value(
        self,
        value: Optional[_UUID],
        dialect: Dialect,
    ) -> Optional[ULID]:
        if value is None:
            return value
        return ULID.from_uuid(value)

[glentner]

Ah, you are right. Looking at the transaction logs from SQLAlchemy, it has something like this

00-00:00:00.433 [hostname]     INFO [engine.Engine] INSERT INTO test.task (id, ...) VALUES (%(id)s::UUID, ...

So then, you're saying Postgres is interpreting '...'::UUID and doing something to re-arrange the bytes from the ULID effectively eliminating the benefits? I had thought we could store other variants within a UUID field in Postgres provided it conserved the same 128-bit size? Or I'm rearranging the bytes by using ULID().to_uuid() on the client side in Python?

Disregarding particular libraries/frameworks for a minute. Do you have any thoughts on how to actually use one of these generated ULID() values, if I were constructing my insert statement by hand, what would conserve the benefits of ULIDs?

[glentner]

Actually, I think something is correct here. Looking at the actually stored "UUID"s in the DB they look sequential (different from the completely random bytes with uuid.uuid4)

019057aa-a64d-1163-bb8a-b0f665f986f6
019057aa-a64f-03c7-b44b-33098b5b6b85
019057aa-a64f-cd1e-2a42-7bdfcc0ba50f
019057aa-a64f-d0bc-c79e-5266256fe18d
019057aa-a64f-4ac6-c95c-e6c2e7f13cdd
...

I'm not sure why I'm not seeing measurable improvement in insert performance.