4 ms·

I've literally been waffling back and forth between UUID and BigInt for the last week. The reason for this is I need to handle eventually distributed systems. D

I've literally been waffling back and forth between UUID and BigInt for the last week. The reason for this is I need to handle eventually distributed systems. Do I need UUIDs? Maybe not, but after much debt, I've decided that the storage requirements of such an index in memory is worth the ability to move from Postgres to Postgresql-xl.

qaq 10y ago
The problem is not with memory problem is that after checkpoint postgres will do full page writes for serial you will be touching way fewer pages compared to UUID so write amplification for UUID vs bigint can be 60X+
- virmundi 10y ago
  Can you go into a bit more detail? I've seen amplification referenced before. My estimation is that the database will be pretty much read heavy. I will need to split a given database across multiple instances.
  - qaq 10y ago
    https://blog.2ndquadrant.com/on-the-impact-of-full-page-writes/ https://blog.2ndquadrant.com/on-the-impact-of-full-page-writ... this is fairly detailed analysis of this issue
    - virmundi 10y ago
      Thank you. I'm reading over it now and for the next few days (to really grok it). I do wonder how bad it will be for low write situations. At the same time I'm open to switching to BigInt and using Postgresql-xl.
    - Dylan16807 10y ago
      How about simply using non-random UUIDs?
      - qaq 10y ago
        That would def. help
graphememes 10y ago
luid, check it out
ProblemFactory 10y ago
> Do I need UUIDs? Maybe not, but after much debt, I've decided that the storage requirements of such an index in memory is worth the ability to move from Postgres to Postgresql-xl. There is a major benefit to UUIDs besides server-side scaling concerns: you can generate them offline in mobile apps and other client-side applications. With serial primary keys, you are often stuck between waiting for a server round trip before saving data locally, or inventing complicated solutions for referring to not-yet-uploaded data locally.

qaq 10y ago

The problem is not with memory problem is that after checkpoint postgres will do full page writes for serial you will be touching way fewer pages compared to UUID so write amplification for UUID vs bigint can be 60X+

virmundi 10y ago
Can you go into a bit more detail? I've seen amplification referenced before. My estimation is that the database will be pretty much read heavy. I will need to split a given database across multiple instances.
- qaq 10y ago
  https://blog.2ndquadrant.com/on-the-impact-of-full-page-writes/ https://blog.2ndquadrant.com/on-the-impact-of-full-page-writ... this is fairly detailed analysis of this issue
  - virmundi 10y ago
    Thank you. I'm reading over it now and for the next few days (to really grok it). I do wonder how bad it will be for low write situations. At the same time I'm open to switching to BigInt and using Postgresql-xl.
  - Dylan16807 10y ago
    How about simply using non-random UUIDs?
    - qaq 10y ago
      That would def. help

graphememes 10y ago

luid, check it out

ProblemFactory 10y ago

> Do I need UUIDs? Maybe not, but after much debt, I've decided that the storage requirements of such an index in memory is worth the ability to move from Postgres to Postgresql-xl. There is a major benefit to UUIDs besides server-side scaling concerns: you can generate them offline in mobile apps and other client-side applications. With serial primary keys, you are often stuck between waiting for a server round trip before saving data locally, or inventing complicated solutions for referring to not-yet-uploaded data locally.