4 ms·

Craig, attended your talk at ATO earlier this week--good talk. I was the guy who asked about performance considerations of joins in Postgres using UUIDs. This

by aejnsn 9y ago

Craig, attended your talk at ATO earlier this week--good talk. I was the guy who asked about performance considerations of joins in Postgres using UUIDs. This post reads like it's vaguely implementing what should be in a queue backend. There's a locked_at field in the schema, furthermore should this not be performed via ...FOR UPDATE?

skrebbel 9y ago
Care to share the answer to your question about uuid join performance? I've had a hard time finding much about that on the internet.
- craigkerstiens 9y ago
  Sure, there is definitely a little extra overhead on the join performance, though my experience I've seen so many other issues become the biggest bottleneck before joins of UUIDs. We regularly used UUID as identifies at Heroku Postgres and use them at Citus as well and they work extremely well for us. It is of note that we're actually using the UUID datatype though and not just generating a UUID and throwing it into a text field.
  - kbenson 9y ago
    > It is of note that we're actually using the UUID datatype though and not just generating a UUID and throwing it into a text field. I was thinking that a UUID datatype implemented as a series of ints could have fairly good join performance, since you can effectively treat it as a series of separate smaller int indices that you join across, and I imagine that's a well understood and optimized problem for years now. A text UUID field though, ugh, that just seems so wasteful even before you get to optimization techniques.
    - netcraft 9y ago
      We use a combination of both - the app is old enough that uuid's weren't an option when it was started - and like the gp said, even in the case of text fields theres a bunch of other things that degrade perf before those joins become an issue. that being said, we are hopeful that the new pg10 hash indexes will be a good fit for these (we already cant do range lookups on a uuid) and are hopeful that we will get a speed improvement with them when we get to that point.
      - kbenson 9y ago
        I'm curious, did you ever do testing on whether converting a UUID to a a pair of 64-bit ints and using a composite primary key worked well? I imagine it's annoying to deal with at both the schema and application level, but it is space efficient. Depending on how optimized numeric/decimal types are, those might by a good fit as well. You said other things degrade perf before that matters, so it may be moot, but wasting space for what's going to be in an index seems likely to have performance problems that affect other things subtly (such as ballooning your index memory usage). If we're storing UUID's in canonical form that's 36 bytes of information (32+4 for separators, and if you're stripping separators why not convert to a numeric form anyway...). Even if we assume we're only storing 32 characters, that's 256 bits, so we're doubling the storage requirements and memory usage for an index. Assuming you're using UUID's because you have a lot of records and want to keep them unique, that space presumably matters (especially if you're using it multiple places because there are relations). If you don't have a huge amount of records, a sufficiently large auto-incrementing int field that increments by a random amount between 1 and 255, and a randomized bit shuffle codified into a reversible function (extend int to 128 bits, use previously decided bit shuffle to rearrange bits. Fully reversible) would solve that need and make everyone who has to deal with the DB's life easier. Just convert the ints to and from UUID representations between your view and controller. I've never had to actually deal with UUID's at the DB level, so there could be some major downsides to these that I'm not thinking of. At this point, I'm just not sure what storing a UUID in a database gets you.
        netcraft 9y ago
        The two biggest things it gets us is an identifier that is unique across the database and also something that the application layer can generate and then provide to the database - instead of inserting a record and finding out what the id is after the fact. Secondarily it means that someone cannot guess what the next record is by trying to increment or decrement an integer key but like you say there are ways around that. We do plan to eventually convert the text versions into real uuids but our performance/size isnt an issue at the moment and its a lot of risky engineering work to do.
    - grzm 9y ago
      Reading your comment, it's not clear to me whether you're aware that the UUID datatype in PostgreSQL is a 128-bit value as opposed to a text field; or if you're just speaking of applications that use a database where native UUID is not available. If the latter, feel free to ignore the rest of this :) For comparison in storage: select pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7B172'::uuid) AS uuid_bytes, pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7B172'::text) AS text_bytes; uuid_bytes | text_bytes ------------+------------ 16 | 40 (1 row) As 'craigkerstiens points out, the index performance of the native UUID datatype is very good: it's not a string comparison. If you're using the UUID type to encode information that you may want to access subfields of, you could create function indexes to do so. Otherwise, I think indexing on the native UUID type is going to be better than a collection of narrower fields and indexes. Edit: Spell 'craigkerstiens correctly.
      - kbenson 9y ago
        > Reading your comment, it's not clear to me whether you're aware that the UUID datatype in PostgreSQL is a 128bit value as opposed to a text field I was speaking towards the idea that some people throw UUIDs into text fields, as implied by the parent;s clarification that they were not doing that. For example, in years past when that type may not have existed yet. People have been talking about "just use a uuid for the primary key" for at least around a decade now. > As 'craigkierstens points out, the index performance of the native UUID datatype is very good: it's not a string comparison. I would assume so! Any database that implemented an actual 128 bit numeric type (no matter if it's converted to hexidecimal notation for display, ints aren't stored in decimal either) as a text field would deserve quite a bit of ridicule, in my opinion. > If you're using the UUID type to encode information that you may want to access subfields of And that answers a question I raised somewhat implicitly in my other comment, which is why use a UUID in the first place. Because you can encode specific sub type information into the parts. Thanks! Edit: Forgot some words in my paragraph that mentioned ridicule, so I added them to make sure it was obvious what and why I thought they would deserve some ridicule (which is actually a bit stronger than how I generally like to come across).
  - Dowwie 9y ago
    Snowflake id is a BIGINT. How does it contrast from UUID, other than joining faster?
    - grzm 9y ago
      Trivially, a UUID is wider (128 bits as opposed to 64). You can choose to generate your UUIDs using a Instragram[0]-like strategy so you can, for example, benefit from temporal/spatial locality in the indexes. There may be performance consequences of using the wider datatype, but I suspect this could be overwhelmed by other application concerns if you're dealing with data that benefits from the wider type. [0] https://engineering.instagram.com/sharding-ids-at-instagram-1cf5a71e5a5c https://engineering.instagram.com/sharding-ids-at-instagram-... Edit: Update to correct snowflake to Instagram's strategy. I make this mistake often enough that it's now self-reinforcing :/
      - Dowwie 9y ago
        So you are presumably agreeing that snowflake makes sense?
        grzm 9y ago
        Oh, of course! Depending on your application needs, it certainly can work. The benefit of PostgreSQL UUID is that you effectively have twice the bits, so you have more room to encode data and satisfy your uniqueness guarantees.
craigkerstiens 9y ago
Hi online :) I'll wait a bit to see if Brandur (author chimes in first), otherwise happy to pile in some thoughts.
- brandur 9y ago
  > This post reads like it's vaguely implementing what should be in a queue backend. There's a locked_at field in the schema, furthermore should this not be performed via ...FOR UPDATE? Yeah, I'm relying on transactional semantics to guarantee no contention during the lock, but the part that I'd intended as important here is really just the idea of locking the key — not the particular implementation. I think alternatively using a `FOR UPDATE` is totally plausible and likely even to be more performant than what I've written here. Try to imagine this as more of a "ideas guide" rather than a dictation on precise implementation.

by aejnsn 9y ago

skrebbel 9y ago

Care to share the answer to your question about uuid join performance? I've had a hard time finding much about that on the internet.

craigkerstiens 9y ago
Sure, there is definitely a little extra overhead on the join performance, though my experience I've seen so many other issues become the biggest bottleneck before joins of UUIDs. We regularly used UUID as identifies at Heroku Postgres and use them at Citus as well and they work extremely well for us. It is of note that we're actually using the UUID datatype though and not just generating a UUID and throwing it into a text field.
- kbenson 9y ago
  > It is of note that we're actually using the UUID datatype though and not just generating a UUID and throwing it into a text field. I was thinking that a UUID datatype implemented as a series of ints could have fairly good join performance, since you can effectively treat it as a series of separate smaller int indices that you join across, and I imagine that's a well understood and optimized problem for years now. A text UUID field though, ugh, that just seems so wasteful even before you get to optimization techniques.
  - netcraft 9y ago
    We use a combination of both - the app is old enough that uuid's weren't an option when it was started - and like the gp said, even in the case of text fields theres a bunch of other things that degrade perf before those joins become an issue. that being said, we are hopeful that the new pg10 hash indexes will be a good fit for these (we already cant do range lookups on a uuid) and are hopeful that we will get a speed improvement with them when we get to that point.
    - kbenson 9y ago
      I'm curious, did you ever do testing on whether converting a UUID to a a pair of 64-bit ints and using a composite primary key worked well? I imagine it's annoying to deal with at both the schema and application level, but it is space efficient. Depending on how optimized numeric/decimal types are, those might by a good fit as well. You said other things degrade perf before that matters, so it may be moot, but wasting space for what's going to be in an index seems likely to have performance problems that affect other things subtly (such as ballooning your index memory usage). If we're storing UUID's in canonical form that's 36 bytes of information (32+4 for separators, and if you're stripping separators why not convert to a numeric form anyway...). Even if we assume we're only storing 32 characters, that's 256 bits, so we're doubling the storage requirements and memory usage for an index. Assuming you're using UUID's because you have a lot of records and want to keep them unique, that space presumably matters (especially if you're using it multiple places because there are relations). If you don't have a huge amount of records, a sufficiently large auto-incrementing int field that increments by a random amount between 1 and 255, and a randomized bit shuffle codified into a reversible function (extend int to 128 bits, use previously decided bit shuffle to rearrange bits. Fully reversible) would solve that need and make everyone who has to deal with the DB's life easier. Just convert the ints to and from UUID representations between your view and controller. I've never had to actually deal with UUID's at the DB level, so there could be some major downsides to these that I'm not thinking of. At this point, I'm just not sure what storing a UUID in a database gets you.
      - netcraft 9y ago
        The two biggest things it gets us is an identifier that is unique across the database and also something that the application layer can generate and then provide to the database - instead of inserting a record and finding out what the id is after the fact. Secondarily it means that someone cannot guess what the next record is by trying to increment or decrement an integer key but like you say there are ways around that. We do plan to eventually convert the text versions into real uuids but our performance/size isnt an issue at the moment and its a lot of risky engineering work to do.
  - grzm 9y ago
    Reading your comment, it's not clear to me whether you're aware that the UUID datatype in PostgreSQL is a 128-bit value as opposed to a text field; or if you're just speaking of applications that use a database where native UUID is not available. If the latter, feel free to ignore the rest of this :) For comparison in storage: select pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7B172'::uuid) AS uuid_bytes, pg_column_size('DDB5E9ED-60B6-4405-A6A8-18E339C7B172'::text) AS text_bytes; uuid_bytes | text_bytes ------------+------------ 16 | 40 (1 row) As 'craigkerstiens points out, the index performance of the native UUID datatype is very good: it's not a string comparison. If you're using the UUID type to encode information that you may want to access subfields of, you could create function indexes to do so. Otherwise, I think indexing on the native UUID type is going to be better than a collection of narrower fields and indexes. Edit: Spell 'craigkerstiens correctly.
    - kbenson 9y ago
      > Reading your comment, it's not clear to me whether you're aware that the UUID datatype in PostgreSQL is a 128bit value as opposed to a text field I was speaking towards the idea that some people throw UUIDs into text fields, as implied by the parent;s clarification that they were not doing that. For example, in years past when that type may not have existed yet. People have been talking about "just use a uuid for the primary key" for at least around a decade now. > As 'craigkierstens points out, the index performance of the native UUID datatype is very good: it's not a string comparison. I would assume so! Any database that implemented an actual 128 bit numeric type (no matter if it's converted to hexidecimal notation for display, ints aren't stored in decimal either) as a text field would deserve quite a bit of ridicule, in my opinion. > If you're using the UUID type to encode information that you may want to access subfields of And that answers a question I raised somewhat implicitly in my other comment, which is why use a UUID in the first place. Because you can encode specific sub type information into the parts. Thanks! Edit: Forgot some words in my paragraph that mentioned ridicule, so I added them to make sure it was obvious what and why I thought they would deserve some ridicule (which is actually a bit stronger than how I generally like to come across).
- Dowwie 9y ago
  Snowflake id is a BIGINT. How does it contrast from UUID, other than joining faster?
  - grzm 9y ago
    Trivially, a UUID is wider (128 bits as opposed to 64). You can choose to generate your UUIDs using a Instragram[0]-like strategy so you can, for example, benefit from temporal/spatial locality in the indexes. There may be performance consequences of using the wider datatype, but I suspect this could be overwhelmed by other application concerns if you're dealing with data that benefits from the wider type. [0] https://engineering.instagram.com/sharding-ids-at-instagram-1cf5a71e5a5c https://engineering.instagram.com/sharding-ids-at-instagram-... Edit: Update to correct snowflake to Instagram's strategy. I make this mistake often enough that it's now self-reinforcing :/
    - Dowwie 9y ago
      So you are presumably agreeing that snowflake makes sense?
      - grzm 9y ago
        Oh, of course! Depending on your application needs, it certainly can work. The benefit of PostgreSQL UUID is that you effectively have twice the bits, so you have more room to encode data and satisfy your uniqueness guarantees.

craigkerstiens 9y ago

Hi online :) I'll wait a bit to see if Brandur (author chimes in first), otherwise happy to pile in some thoughts.

brandur 9y ago
> This post reads like it's vaguely implementing what should be in a queue backend. There's a locked_at field in the schema, furthermore should this not be performed via ...FOR UPDATE? Yeah, I'm relying on transactional semantics to guarantee no contention during the lock, but the part that I'd intended as important here is really just the idea of locking the key — not the particular implementation. I think alternatively using a `FOR UPDATE` is totally plausible and likely even to be more performant than what I've written here. Try to imagine this as more of a "ideas guide" rather than a dictation on precise implementation.