4 ms·
You need it to make database indices perform better. If you don't need that, but just need a random UUID, UUIDv4 is better.
by madsbuch 3y ago
You need it to make database indices perform better.
If you don't need that, but just need a random UUID, UUIDv4 is better.
- mcv 3y agoBut shouldn't that be a separate field then?
- mattashii 3y agoThe logistics of combining fields in indexes and identifiers is relatively complex, while the logistics of indexing a single field is comparatively trivial. This is also why you don't ship timestamps using separate fields for second/minute/hour/day/month/year, but a single ISO-string or UNIX timestamp as representation: it makes querying and interpreting the value more consistent.
- frankreyes 3y agoCreate a new column with an MD5 hash of the other columns. Easy. /s
- btilly 3y agoDisagree on "relatively complex". create index on $table ($field1, $field2); Seems pretty simple to me.
- mattashii 3y agoIt is noy just the DDL of the primary table that needs to care about it, but also all foreign keys, DML, queries, APIs accessing the data, etc. Storing that UUIDv7 is likely going to be cheaper than pushing the cost of keeping a composite identity onto other components and systems that work with that data.
- btilly 3y agoWell, that depends on how you manage it. An ORM will find it trivial. Custom SQL, not always so much. That said, PostgreSQL offers https://www.postgresql.org/docs/current/rowtypes.html https://www.postgresql.org/docs/current/rowtypes.html which gives you the best of both worlds. A single field which contains 2 fields that can later be extracted. So everywhere you can write a single field for joins, etc. But then when you need it broken out...
- madsbuch 3y agoI am curios about this, and might be misunderstanding what you mean. Can you layout a demo architecture where you use multiple keys like you propose?
- junon 3y agoThere are many DBMSes that combine columns into a singular primary key. Performance tradeoffs vary, especially when it comes to indexing.
- e12e 3y agoAny pure relational database design will eschew surrogate keys - most real-world systems will (should) add them back - because a surprising number of good natural keys end up changing (names change, phone numbers change, emails change, twitter handles become irrelevant/disappear, location of birth may change subject to geographic regions changing size...). And on top of all that, there are efficiency concerns. That said, at least for join tables AFAIK - there aren't often a need for row IDs beyond the involved foreign keys - unless you need to add meta data from other relations/tables...
- TRiG_Ireland 3y ago> location of birth may change My British passport says I was born in Birr; my Irish passport says I was born in Galway. These are both correct, because they are answering different questions. (I was born in the town of Ballinasloe in County Galway, but my mother's place of residence at the time of my birth was the town of Birr in County Offaly.)
- pc86 3y agoSo British place of birth means "mother's residence" and not place of birth?
- rvnx 3y agoOfficial guidances here: https://assets.publishing.service.gov.uk/government/uploads/system/uploads/attachment_data/file/1113426/Place_and_country_of_birth_V18.pdf https://assets.publishing.service.gov.uk/government/uploads/... Could maybe be just a mistake when doing the UK registration ? It's an easy fix if wanted.
- dtech 3y agoBut then you have 2 identifiers which complicates everything.
- dtech 3y agoI feel like v7 is almost a strictly better v4. Assuming you can generate a v7 (you have a time source), what are the disadvantages?
- madsbuch 3y agoEntropy. You loose bits to a known source, which reduce entropy of the UUID.
- wolf550e 3y agoYou can hand out uuidv4 to clients without revealing anything.
- andersa 3y agoAnyone who can see the uuidv7 can determine at what time it was generated. You might not want that for things exposed to users.
- EGreg 3y agoI dont know why people use relational databases other than they were first and “that’s the way it’s always been done”. Why not use a graph database? O(1) lookups instead of O(N). Why need indices if you can just point to the data. Why use JOINs when map-reduce querying is far more flexible?
- tharkun__ 3y agoThey were not first. ISAM and VSAM come to mind. Yes it says "files" in there a lot but it got used like a database with programming interfaces like finding records in a database. If you will this method is more like NoSQL databases than a relational DB. The I in ISAM was a step towards not having to know the key (true "NoSQL"). Kind of like today's NoSQL databases all also give the ability to add indexes now. https://en.m.wikipedia.org/wiki/ISAM https://en.m.wikipedia.org/wiki/ISAM
- dlisboa 3y agoI've been interested in learning more about them and how to best utilize them in my company. What graph database and query language would you recommend (regardless of stack)?
- EGreg 3y ago1. Memgraph 2. Neo4j As for query language: definitely Cypher!
- madsbuch 3y agoHow do you do constant time lookup an graph databases? My intuition let's me know that you can not get below O(n log n) (Lower limit for comparison based ordering)
- eru 3y ago> My intuition let's me know that you can not get below O(n log n) (Lower limit for comparison based ordering) That intuition should point at O(log n), shouldn't it? In any case, it totally depends how your data is stored and how/what you want to look up. If you already have some kind of id of your node in the graph you want to look up, you can get O(1) lookup and still call it a graph database. If you have to traverse the graph, then it depends on the structure of the graph and where your entry point is, etc. I'm rather skeptical of graph databases. Whatever they can do, you can do with a relation database and datalog. (Think of datalog as SQL plus recursion, perhaps.) See https://en.wikipedia.org/wiki/Datalog https://en.wikipedia.org/wiki/Datalog