161–170 of 182 posts
UUIDs are great when you use the id "publicly" but using an incremental value would be too revealing for different reasons. So it's good to know that performances are not bad.
I’ve stuck with incremental values internally but use Hashid to convert them when exposed publicly. Seems to work well.
Earlier quoted context omitted.
It's the german tank problem. Serial IDs, with some light assumptions, leak information about the total count of items.
Just pick a random number at the beginning, and start incrementing IDs from there. Like personal checks starting at 1000 so they're always(ish) 4 digit. Of course, maybe pick another starting number that's less obvious.
Earlier quoted context omitted.
You can also do the loop on the server side using PL/pgSQL: https://www.postgresql.org/docs/current/plpgsql-control-stru...
That's not that trivial. You can't just loop to get a unique ID. Maybe if you lock the whole table for reads first, which is quite drastic.
If you read the linked doc, you'll see an EXCEPT clause. That can be used for a retry loop inserting into a table with a UNIQUE constraint. No read locks are necessary, because the UNIQUE constraint will catch violations safely (regardless of concurrent activity), and the retry loop can simply retry until that succeeds. For instance:
CREATE TABLE u(i INT8 UNIQUE);
-- insert random unique value in the range 0..n
-- into table u, retrying if it's already present
--
-- NOTE: this will not terminate if 0..n are all
-- present
CREATE OR REPLACE FUNCTION insert_uniq(n INT8)
RETURNS VOID
LANGUAGE plpgsql AS $$
DECLARE
x INT8;
BEGIN
>
LOOP
BEGIN
x := (random() * n)::int8;
INSERT INTO u VALUES(x);
RAISE NOTICE 'inserted unique value %', x;
EXIT retry_loop;
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'collision with value %; retrying', x;
END;
END LOOP;
END;
$$;
> Now, sometimes a table has a natural primary key, for example the social security number of a country’s citizens. You know, you think that, but it's never that simple. The field was added incorrectly and nobody noticed until the value is in countless tables that you now need to simultaneously update or the value is something that's supposed to be semi-secret, so now a low level support staff can't reference the row…
It does seem that a "natural key" is frequently just a really foreign key in a database you and your org don't manage.
For instance, a driver's license number is printed on the card itself, so a human sees it. Therefore, it's a natural key, just like a name.
When you decide that whatever natural keys already exist aren't good enough for your organization, and you make a new key, it's not good to think of that as a surrogate key. The number will make it out somehow (as a "record locator" in a customer support call or something), and eventually become a natural key.
It's best to just plan for any new key to be a natural key, which means using best practices for natural keys. That means it should be something reasonable to print, read, say, and hear; and it should also follow a pattern so it can be distinguished from other special numbers.
Auto-increment is a shortcut, but usually not great in the long term unless it's something that will be well-contained inside the database as an implementation detail (e.g. a join key designed to refer to rarely-accessed fields of a wide table).
Earlier quoted context omitted.
That's not that trivial. You can't just loop to get a unique ID. Maybe if you lock the whole table for reads first, which is quite drastic.
I should have been more clear. Here are the details: If you read the linked doc, you'll see an EXCEPT clause. That can be used for a retry loop inserting into a table with a UNIQUE constraint. No read locks are necessary, because the UNIQUE constraint will catch violations safely (regardless of concurrent activity), and the retry loop can simply retry until that succeeds. For instance: CREATE TABLE u(i INT8 UNIQUE);…
Earlier quoted context omitted.
A vote here against integer/serial PKs, not only because they leak information, but also because they can result in incorrect joins. IME it's much more often I've quickly made a table with a serial PK and later wished it were uuid; just about never made a uuid and later wished for the compactness or natural clustering of bigint. Maybe for a table of millions and millions of time-ordered events.
> […] but also because they can result in incorrect joins. Side question: can I get Postgres to throw an error if I try to join on two IDs where neither of the IDs have a foreign key reference to the other?
Earlier quoted context omitted.
If you use serial integer ids and accidentally join on the wrong tables/columns you will get rows back even if the join doesn't make sense, because all serian integer ids have values in common. If you're using UUIDs you will "never" get rows back when joining on the wrong ids and spot your mistake.
I think if you're joining against wrong tables or columns, then you have bigger problems than if the values are BigInt or UUID's.
Earlier quoted context omitted.
Ahh thanks for noting this -- I've written off writing C# and PHP for the rest of my life for reasons, but this is a fascinating read.
On the other hand, C# + postgres is a super sweet combo. I don't use EntityFramework, so I don't know how well they play together, but if you write sql queries and run them from c#, it's super nice. I typically have a generic repository class that generates all the statements and it works great (I recently made the query generator able to generate inner join if I pass it filters that are not found on the table/object…
I'm just not good at it -- I came in expecting it to be better Java (better async ergonomics, slightly less verbose, etc) -- but after working with it (while working with a client no less) I found that I disliked it just as much as Java and had a relatively rough go of it. There are a lot of things that I think contributed:
- .NET4 was becoming .NET core / .NET standard while this project was happening
- I needed a windows VM to work on the project (dotnet was not ready for prime time yet, so I couldn't depend on it to build the legacy code)
- Packaging was uncomfortable and a source of pain (also had to do with the core/standard transition) -- after you've used NPM/yarn/cargo/stack(haskell) nuget+chocolatey (IIRC?) felt like a huge step backwards, basically had to be IDE driven.
- EF was so incomplete (due in part to the core/standard transition), and so much worse than my then-and-now favorite TypeORM.
- The async paradigm and how they handle results are a little weird, IIRC awaiting a task would mean you received the task, not the result of the computation of the task?
- IIRC there was no Option type. Java learned this lesson in 1.8 IIRC (and it rocked my world a bit, in a good way), but C# not taking it up is weird to me.
- The codebase was just like you'd expect an old Java codebase to be -- i.e. terrible notfun.
- Everything was heavily IDE/visual studio driven which was not fun for me. Visual studio is an amazing tool no doubt, but a lot of it is unintuitive to me at this point after years of straight emacs/vim and occasionally sublime/atom/vscode. This is more personal than any other reason.
I just felt like it was so much worse than Typescript/Javascript for questionable gain -- I had worked with this same client to deliver a JS codebase that was easy for them to work on, on time and at budget. The C# side felt much worse to work on, and I just took it to mean I'm not good at C#, and I have no desire to be. The ecosystem you're almost forced to accept (appveyor, windows machines + powershell, etc) is just not my cup of tea.
I'm spoiled for choice these days -- if I need performance for a backend thing (and need to hand the project off to someone) I can choose Go. If I really need performance I can pick Rust. If I don't need performance-per-say I pick Typescript (it's still generally better perf than Ruby/Python). If I want to get it right (and really craft software) I pick Haskell (and of course it's near impossible to hand that off to most companies).
In the end C# is a great language (whether I like it or not), but it fell short of my expectations as a "better java". If I'm going to do Java I'll just do Java/Kotlin/Scala/Clojure. I think I could go my whole life without ever touching C# again, so was an easy rule to make for myself.
Earlier quoted context omitted.
I think if you're joining against wrong tables or columns, then you have bigger problems than if the values are BigInt or UUID's.
"Don't ever be wrong" is one way to do it; on the other hand, sometimes we make mistakes anyway, especially in ad hoc queries, and not getting any results back at all helps to spot the problem quickly (more quickly than spending minutes befuddled by the nonempty resultset).
Is the value of making that mistake strong enough that you use so much more data to store a UUID value??
Earlier quoted context omitted.
Mentioned this in a sibling comment: There's another benefit to UUID - You can generate them anywhere including application side. Doing this on application side would have tremendous batching benefits or inserting objects with relationships at the same time (Vs waiting first insert to return an ID to be used in the FK).
You can just use PostgreSQL's writeable CTEs to get the same batching benefits plus the benefits from using serials. So, no, I do not think batching is a good reason for using UUIDs.