8 ms·
Why do you need the time component anyway? It's just eating up bits in your UUID without contributing much entropy.
by eru 3y ago
Why do you need the time component anyway? It's just eating up bits in your UUID without contributing much entropy.
- c0balt 3y agoBecause it allows te encoding of information the id. This makes it, at least in my experience, somewhat sortable.
- madsbuch 3y agoYou 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
- dtech 3y agoWhen you sort them they will be ordered by generation time. This is useful in UIs, and greatly improves performance in databases (how much depends on DB and use case).
- glodalitazic 3y agoIirc you dont want to spend entropy that you don't need. The advantage of using the time means you need to spend less bits on expensive randomness and thus generation is cheaper.
- SV_BubbleTime 3y agoI only work in embedded, so I’m not sure about fancy computers - but when we make UUIDv7s, I am certain the random is faster than pulling the time, formatting for milliseconds, then storing in position.
- eru 3y agoPRNGs are cheap to compute, and 'random' enough.
- qwerty456127 3y ago> Why do you need the time component anyway? To sort or filter the records by time. Sure, you can just add an extra column if you need this, but there are cases when this is not convenient to do. E.g. when you want to be able to export the records info files, naming the files with the IDs and still be able to sort them.
- danbruc 3y agoMore importantly if you have an index on purely random IDs, then each insert will go to some random position into the tree whereas having IDs that increase with time will make all new IDs end up at the end of the tree which reduces index fragmentation.
- EGreg 3y agoOr, you could use a graph database and stop having frustrating relational impedance mismatch, nonlocality etc. You can have O(1) lookups instead of O(log N) for almost everything
- victor106 3y agoThat sounds too good to be true. Is that really true of all grapdb’s? Also, if that’s really true why can’t everyone just use graphdb’s?
- EGreg 3y agoBecause of vendor lock-in with the LAMP stack over the years. Every host used MySQL, how many had Neo4j as available?
- e12e 3y agoHow big is the 1 though?
- EGreg 3y agoLook, if you have N items related to X, at insert time, you store them in an array and have X point to that array, instead of foreign keys. For example, when a user has 7 articles. Do you want to just point to where the articles are stored? Or do you want to do O(log n) lookup for each article? And if you have many-to-many, do you want to join an Intermediate Table for even more processing, or just follow a pointer to a range of an intermediate node directly and traverse?
- ericHosick 3y agoUUIDs can serve different purposes. As others have mentioned, database performance on inserts might trump the need for difficult to guess UUIDs. In other cases, the UUID needs to be as random as possible. It really depends on the use case.
- SV_BubbleTime 3y agoYea. This is an easy one. We use both. For our “session” records, it’s a UUIDv7. This sorts beautifully, and if I wanted to, I could look at a log and easily see all the entries in a particular session. For a larger db, we just need unique entries and at least in Dynamo, it is an advantage to equally distribute them as much as possible. UUIDv4 there.
- KMag 3y agoOtherwise, failure recovery requires robust storage. Upon startup, you just wait until your timestamp ticks over, and then you know you're not re-issuing any UUIDs. With a pure counter-based system, you need robust distributed storage and you need the machine to reserve batches of IDs via committing writes to the robust distributed storage. Otherwise, a disk failure may cause you re-issue UUIDs. Though, seeding a thread-local AES-256-ctr or ChaCha20 instance via getrandom()/getentropy() is probably a better fit for most situations. If you need sequential IDs for database performance, seed a simple 128-bit counter using getrandom()/getenropy(). If you're going to need more than 2^40 unique IDs, then use IDs larger than 128 bits. Assuming getrandom()/getentropy() is getting you a high-entropy seed, it's easy to size the IDs to bring the probability of collision much lower than the probability of background radiation flipping a bit and breaking your equality test between IDs. If you're not running redundant calculations and/or on radiation-hardened hardware, it's difficult to argue against randomized IDs.
- eru 3y ago> With a pure counter-based system, you need robust distributed storage and you need the machine to reserve batches of IDs via committing writes to the robust distributed storage. Otherwise, a disk failure may cause you re-issue UUIDs. Yes, a pure counter based system is probably worse than one that uses time. Don't use counters, especially not in a distributed setting.