3 ms·

I hate UUIDs with passion currently - not postgres but recently spent so much extra time on relatively small table (35 mil records in few columns) and doing som

I hate UUIDs with passion currently - not postgres but recently spent so much extra time on relatively small table (35 mil records in few columns) and doing some queries and updating subset of it. UUIDs there is stored in Oracle 'raw' datatype which to me is the worst combination possible, basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and readability, atrocious performance on stored procedures. Absolutely worst DB design I've seen in past 20 years, and we talk about expensive core anonymization service of top big banking package.

marcosdumay 4y ago
AFAIK, the Oracle way is to store them as Number. The type is made just large enough for them. But if you want to talk about Oracle's usability, there are much larger fish to fry. I wouldn't recommend anybody to use that database.
- saiya-jin 4y ago
  more often than not one doesn't have any choice in DB, especially when it comes bundled in product like in my case
dspillett 4y ago
> basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and readability, atrocious performance on stored procedures But this give a significant performance benefit for storage (storing in the display format gives 4 bits per byte, or less if you include decorations like the '-' characters, rather than 8) and more importantly when joining (it doesn't need to convert for this, so on a 64-bit architecture each comparison is a pair of 64-bit compares in the CPU) rather than a more complex string comparison. If you are converting between string and binary representations more often than on input to your stored procedures or for output, then something is very wrong (the query planner are likely not able to use indexes that it could too, so scanning instead of seeking).

marcosdumay 4y ago

AFAIK, the Oracle way is to store them as Number. The type is made just large enough for them. But if you want to talk about Oracle's usability, there are much larger fish to fry. I wouldn't recommend anybody to use that database.

saiya-jin 4y ago
more often than not one doesn't have any choice in DB, especially when it comes bundled in product like in my case

dspillett 4y ago

> basically string stored in small binary blob, due to binary nature all needs to be converted to hex all the time for matching and readability, atrocious performance on stored procedures But this give a significant performance benefit for storage (storing in the display format gives 4 bits per byte, or less if you include decorations like the '-' characters, rather than 8) and more importantly when joining (it doesn't need to convert for this, so on a 64-bit architecture each comparison is a pair of 64-bit compares in the CPU) rather than a more complex string comparison. If you are converting between string and binary representations more often than on input to your stored procedures or for output, then something is very wrong (the query planner are likely not able to use indexes that it could too, so scanning instead of seeking).