14 ms·

I wish I could do this, but I already have more than four billion As, so my A IDs need 64 bits. I use composite keys for my Bs (A ID + sequence number per A), w

by derefr 6y ago

I wish I could do this, but I already have more than four billion As, so my A IDs need 64 bits. I use composite keys for my Bs (A ID + sequence number per A), which allows me to search on the pkey index using just the A column; but by the time I get to C, that gets unwieldy. So my Cs have UUIDs, my Bs also have UUIDs (secondary unique ID, not the pkey) and my Cs have a foreign key pointing to their B. While also having an indexed A ID field. I'd love to just just have "A-B-C" as my Cs' IDs... but it'd only work for my use-case (i.e. be performant) if it was running on a computer with 256-bit registers.

skyde 6y ago
Curious why your primary key are GUID? Also if the primary key of table C is made of 3 column (A GUID, B GUID, C GUID) the index in PostgreSQL will not be that big with compression enabled because all the common prefix will not be stored redundantly. Also having a single compound index on table C covering column (A ForeingKey, B ForeingKey, C GUID) is much better than having multiple index on table C.
- Ombudsman 6y ago
  GUIDs are extremely useful if you want to move and merge data around without having to worry about primary key collisions.
- tehlike 6y ago
  Having the primary key generatable at application level has performance benefits especially on inserts. You can write all related entities in one go vs waiting for db to generate the the pk, and passing that to dependent entities.
  - skyde 6y ago
    you can have "id" Generatable at application level without using GUID. This is what twitter is doing "snowflake". It generate tens of thousands of ids per second. Those id fit in 64 bits. And those id are sortable, meaning that if tweets A and B are posted around the same time, they should have ids in close proximity to one another. See: https://blog.twitter.com/engineering/en_us/a/2010/announcing-snowflake.html https://blog.twitter.com/engineering/en_us/a/2010/announcing...
  - skyde 6y ago
    Also the problem with clustered indexes in a GUID field are that the GUIDs are random, so when a new record is inserted, a significant portion of the data on disk has to be moved to insert the records into the middle of the table.

by derefr 6y ago

skyde 6y ago

Curious why your primary key are GUID? Also if the primary key of table C is made of 3 column (A GUID, B GUID, C GUID) the index in PostgreSQL will not be that big with compression enabled because all the common prefix will not be stored redundantly. Also having a single compound index on table C covering column (A ForeingKey, B ForeingKey, C GUID) is much better than having multiple index on table C.

Ombudsman 6y ago
GUIDs are extremely useful if you want to move and merge data around without having to worry about primary key collisions.
tehlike 6y ago
Having the primary key generatable at application level has performance benefits especially on inserts. You can write all related entities in one go vs waiting for db to generate the the pk, and passing that to dependent entities.
- skyde 6y ago
  you can have "id" Generatable at application level without using GUID. This is what twitter is doing "snowflake". It generate tens of thousands of ids per second. Those id fit in 64 bits. And those id are sortable, meaning that if tweets A and B are posted around the same time, they should have ids in close proximity to one another. See: https://blog.twitter.com/engineering/en_us/a/2010/announcing-snowflake.html https://blog.twitter.com/engineering/en_us/a/2010/announcing...
- skyde 6y ago
  Also the problem with clustered indexes in a GUID field are that the GUIDs are random, so when a new record is inserted, a significant portion of the data on disk has to be moved to insert the records into the middle of the table.