Live data from Hacker News

UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

cybertec-postgresql.com

171–180 of 182 posts

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#171

Earlier quoted context omitted.

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…

> If I may ask, why don't you like C#? I totally get the php thing - I cannot stand it at all either. 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 contr…

Got ya, thanks for explaining.

Legacy C# projects can be a mess, I've stepped away after a month or from one or two projects in the past simply because it was beyond fixable - I think there is clean way to use c# and there is an over-engineered way (typically java devs that try to use the exact same patterns in c#, which bloats it).

You are right, the past 3 years or with .net core and .net standard was messy too! They should've just named it something different. Same with Entity Framework. I'm still steaming about them using the same names for everything, as there are plenty cases when things don't work the same way as before. It completely screwed googling things.

And any desktop development was broken too for a time (still bugging out with new .net core xaml editor) - so yeah, skip.

I primarily use C# for api's and tooling (my own build server is code I wrote myself in C#, hosting my own git repo's (not using gitlab/bitbucket etc; rather bare repositories!), and my own nuget server). But for anything web/desktop related, meh skip. In C# api land, you can get such clean/unbloated code with full type safety and compiler checks, I love it. Most C# devs use/throw exceptions but they really muddy the water and make it feel like Java. My own code only have try/catch when talking to the outside world (harddrive, network, etc), the rest of the layers are clean and with minimal null checks (cause at those layers I never have nulls). Exceptions are expensive/slow too, so rather catch it as early as possible and return a Result object (not built in, my own envelope) instead of rethrowing multiple times and doing a crazy amount of null checks. So it is possible to write "clean" C# but most commercial projects I've seen is pretty darn ugly.

Just an fyi, I don't use appveyor/windows/powershell/Azure at all. I work primarily from Fedora with Jetbrains products, with most of my tooling being hand rolled. I use the Digital Ocean Api to manage all my boxes and use SSH.NET (nuget package that can open ssh tunnels in C#, I use it for all sorts of things, incl reaching my db servers (ssh.net + Npgsql)) and FluentFtp if needed. So I don't really touch Microsoft's ecosystem at all. My builder code calls the dotnet sdk and I copy the build artifacts around as needed - no third parties involved. In the beginning I stressed about going this way but I see now how great it is - no need to worry about CI minutes or paying Gitlab or Bitbucket, or dealing with 10 different api keys or dealing with Azure etc. So it's totally possible to have sanity - but to be honest I cannot take "my way" to work as everything is hand rolled - people want to use Gitlab/Bitbucket etc else they lose their minds (other developers seems to be my biggest enemy these days - everything needs to match to their exact pattern else they quit). So yeah, own clients = own stack.

On the visual studio side, the IDE has gotten slower and slower... I have a 6 core cpu, 32Gb ram and a Samsung nvme drive, RX580.. yet visual studio lags like crazy, intellisense take 2 minutes to update etc.. that's with only VS open and no extra extensions installed. It also crashes sometimes while doing nothing out of the ordinary. So not sure what the heck they are doing to the project at Microsoft. On the otherhand, the jetbrains products have super slow startup times (try 10 to 30 seconds for Rider and Datagrip) but once in, they work great!

Another cool thing to note, Gitkraken sees my bare git repositories as valid remotes (provided ssh keys are in place) and pushes to them without issue (and near instantly, bitbucket push takes 20s no matter what I do).

Sorry for dumping all of this on you!

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#172

Earlier quoted context omitted.

> If I may ask, why don't you like C#? I totally get the php thing - I cannot stand it at all either. 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 contr…

Got ya, thanks for explaining. Legacy C# projects can be a mess, I've stepped away after a month or from one or two projects in the past simply because it was beyond fixable - I think there is clean way to use c# and there is an over-engineered way (typically java devs that try to use the exact same patterns in c#, which bloats it). You are right, the past 3 years or with .net core and .net standard was messy too! Th…

Yeah, a lot of it was a mix of the project, me not being good enough at C# in general to fix up the legacy adeptly, etc -- and I picked a bad time to do to it. You can't be good at everything, and I made a choice to just leave my C# skill where it's at.

> I primarily use C# for api's and tooling (my own build server is code I wrote myself in C#, hosting my own git repo's (not using gitlab/bitbucket etc; rather bare repositories!), and my own nuget server). But for anything web/desktop related, meh skip. In C# api land, you can get such clean/unbloated code with full type safety and compiler checks, I love it. Most C# devs use/throw exceptions but they really muddy the water and make it feel like Java. My own code only have try/catch when talking to the outside world (harddrive, network, etc), the rest of the layers are clean and with minimal null checks (cause at those layers I never have nulls). Exceptions are expensive/slow too, so rather catch it as early as possible and return a Result object (not built in, my own envelope) instead of rethrowing multiple times and doing a crazy amount of null checks. So it is possible to write "clean" C# but most commercial projects I've seen is pretty darn ugly.

That sounds interesting! Yeah I definitely prefer having types these days (basically don't pick languages without them anymore), and errors-as-values is one of the patterns that is a pearl of modern PL development. Languages that come out these days that still rely on exceptions are an instant turn off for me (ex. Dart). I remember something like 8-10 years ago having discussions around whether checked exceptions had meaning in Java and now that I look back all those discussions were so silly.

> Just an fyi, I don't use appveyor/windows/powershell/Azure at all. I work primarily from Fedora with Jetbrains products, with most of my tooling being hand rolled. I use the Digital Ocean Api to manage all my boxes and use SSH.NET (nuget package that can open ssh tunnels in C#, I use it for all sorts of things, incl reaching my db servers (ssh.net + Npgsql)) and FluentFtp if needed. So I don't really touch Microsoft's ecosystem at all. My builder code calls the dotnet sdk and I copy the build artifacts around as needed - no third parties involved. In the beginning I stressed about going this way but I see now how great it is - no need to worry about CI minutes or paying Gitlab or Bitbucket, or dealing with 10 different api keys or dealing with Azure etc. So it's totally possible to have sanity - but to be honest I cannot take "my way" to work as everything is hand rolled - people want to use Gitlab/Bitbucket etc else they lose their minds (other developers seems to be my biggest enemy these days - everything needs to match to their exact pattern else they quit). So yeah, own clients = own stack.

Wow thanks for this level of detail, I exclusively use linux on everything now, and it's great to know that this kind of setup is possible without resorting to a VM. As far as infrastructure/deployment goes, you manage your C# projects with C#, SSHing to them and doing stuff? What are the commands you run like? I'm not super familiar with easily calling C# from the command line, something like `dotnet run deploy.csharp`?

SO I'm a huge proponent of CI (I think GitLab has the best CI out there) but it definitely is more complex than it should be. Setting up tokens, figuring out how things interact is really annoying 90% of the time, and the other 10% is bliss.

I'm actually about to relaunch a product I was working on -- one that makes it cheaper to run CI ($10 for 1000 minutes is what GitLab charges and I'm going to offer $15 for unlimited minutes per dedicated vCore/2GB RAM). More to your problem though, I actually make all my stuff work with Makefiles (so much so that I even deploy over-complicated infrastructure you wouldn't like with Makefiles[0][1]), has that not worked for you as far as fixing CI inconsistency? I find that generally knowing that to build I just need to run `make ` (assuming you have the correct system-level libraries installed of course) has fixed most of my issues in this area.

> On the visual studio side, the IDE has gotten slower and slower... I have a 6 core cpu, 32Gb ram and a Samsung nvme drive, RX580.. yet visual studio lags like crazy, intellisense take 2 minutes to update etc.. that's with only VS open and no extra extensions installed. It also crashes sometimes while doing nothing out of the ordinary. So not sure what the heck they are doing to the project at Microsoft. On the otherhand, the jetbrains products have super slow startup times (try 10 to 30 seconds for Rider and Datagrip) but once in, they work great!

This is unfortunate, crazy wild conjecture but do you think it's possible visual studio will be subsumed into VS Code? And yeah what you're describing is why I live in emacs/vim (and even emacs is too slow sometimes). I know that I'm giving up a LOT of creature comforts but for me it fits like a worn glove. I'm also constantly impressed with JetBrains -- the IDE experience has generally been good for me as well (I've had to use it before because everything else was just too painful), and it seems like they just keep their heads down and put out good product.

> Another cool thing to note, Gitkraken sees my bare git repositories as valid remotes (provided ssh keys are in place) and pushes to them without issue (and near instantly, bitbucket push takes 20s no matter what I do).

Interesting, is bitbucket push having an issue with web requests or something I wonder, 20s is a long time to wait! Since I use emacs magit[2] works for me and it is amazing.

[0]: https://vadosware.io/post/using-makefiles-and-envsubst-as-an...

[1]: https://vadosware.io/post/setting-up-mailtrain-on-k8s/#step-...

[2]: https://magit.vc/

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#173
post #55

Earlier quoted context omitted.

I’ve stuck with incremental values internally but use Hashid to convert them when exposed publicly. Seems to work well.

Hashid is super slow though.

What is 'super slow'? Takes milliseconds for me.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#174

Earlier quoted context omitted.

Got ya, thanks for explaining. Legacy C# projects can be a mess, I've stepped away after a month or from one or two projects in the past simply because it was beyond fixable - I think there is clean way to use c# and there is an over-engineered way (typically java devs that try to use the exact same patterns in c#, which bloats it). You are right, the past 3 years or with .net core and .net standard was messy too! Th…

Yeah, a lot of it was a mix of the project, me not being good enough at C# in general to fix up the legacy adeptly, etc -- and I picked a bad time to do to it. You can't be good at everything, and I made a choice to just leave my C# skill where it's at. > I primarily use C# for api's and tooling (my own build server is code I wrote myself in C#, hosting my own git repo's (not using gitlab/bitbucket etc; rather bare r…

> As far as infrastructure/deployment goes, you manage your C# projects with C#, SSHing to them and doing stuff?

Basically yes. C# console apps can be run like any other linux cli application, provided the dotnet runtime or sdk is installed (~40mb vs ~150mb). If you installed the sdk, you can build/publish/pack projects for production. You can specify the input directory and output directory, so you can easily call something like this: "Process.Start("dotnet publish ~/code/app1/app1.sln --output ~/builds");". Since you can start a process from C#, I can also git clone and git checkout like this: "Process.Start($"git clone {repo path} {output}");" where Process.Start lives in the System.Diagnostics namespace. So I built my own tooling as Console Apps that call other things (since I dislike bash scripts, yaml files (insert ci dsl) - the c# compiler protects me against a whole bunch of things and their System.IO namespace is awesome when working with files/directories).

So I can clone it, build it, open tunnel somewhere, copy it, straight from c# (although I have to setup ssh keys everywhere when needed, I use some trickery with "ssh-copy-id" that comes with ssh to add keys to new servers). So far I have 3 tools: a builder, a server manager (for droplets) and a tool that makes backups, but I'm looking at wrapping borg in the future as it is waaay better than mine. Next I need to build a tiny api to call "git init --bare" for new repositories, as I have to run it manually at the moment to make new project repo's.

> I'm going to offer $15 for unlimited minutes per dedicated vCore/2GB RAM

Careful, don't get abused by the crypto community; there has been some more incidents lately where miners abuse CI systems.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#175

Earlier quoted context omitted.

> If I may ask, why don't you like C#? I totally get the php thing - I cannot stand it at all either. 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 contr…

Got ya, thanks for explaining. Legacy C# projects can be a mess, I've stepped away after a month or from one or two projects in the past simply because it was beyond fixable - I think there is clean way to use c# and there is an over-engineered way (typically java devs that try to use the exact same patterns in c#, which bloats it). You are right, the past 3 years or with .net core and .net standard was messy too! Th…

I meant intellisense take 2 seconds, not 2 minutes.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#176
post #40

I just started a little side project and chose to use UUID for Postgresql keys. The schema is highly generic and I anticipate the possibility of merging instances. UUID precludes collisions in such a case.

That includes foreign keys?

Yes.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#177
post #167

Earlier quoted context omitted.

"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).

One thing that no one mentions so far is the extra space these ids consume. Is the value of making that mistake strong enough that you use so much more data to store a UUID value??

Well, I in fact did mention the "compactness" of integers at the start of this thread :)

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#178

Earlier quoted context omitted.

Yeah, a lot of it was a mix of the project, me not being good enough at C# in general to fix up the legacy adeptly, etc -- and I picked a bad time to do to it. You can't be good at everything, and I made a choice to just leave my C# skill where it's at. > I primarily use C# for api's and tooling (my own build server is code I wrote myself in C#, hosting my own git repo's (not using gitlab/bitbucket etc; rather bare r…

> As far as infrastructure/deployment goes, you manage your C# projects with C#, SSHing to them and doing stuff? Basically yes. C# console apps can be run like any other linux cli application, provided the dotnet runtime or sdk is installed (~40mb vs ~150mb). If you installed the sdk, you can build/publish/pack projects for production. You can specify the input directory and output directory, so you can easily call s…

Thanks again for the detail!

> Careful, don't get abused by the crypto community; there has been some more incidents lately where miners abuse CI systems.

Will do! I’ll have to do some detection of the workloads. I know other platforms are fighting this as well.

Re: UUID, serial or identity columns for PostgreSQL auto-generated primary keys?

#180
post #81

Earlier quoted context omitted.

Not really. You should develop better tooling to visualize debugging information. Today's serious systems (this in my opinion includes e.g. collaborative rich text editors) are just too complicated to just eyeball. Pavel, a colleague of mine is developing a new collaborative rich text editor for OrgPad and here is, how we do some testing currently https://www.youtube.com/watch?v=VeVcNmNFzmc We use UUIDs for basically…

Disagree. /user/edit/5 tells me easily what record it is about on the database without having to copy paste an UUID. Dev experience truly is a case of death by thousand cuts. I avoid every little cut I can like the plague so energy goes into making cool stuff. > Should develop better tooling to visualize debugging information. Thing is, why would I spend time overengineering tooling I don't need if I can get away wit…

Thank you for the reply. You are totally right about the dev experience and the death by thousand cuts.

I really was talking about serious/ complicated systems, where you want some consistency in the components it is made of even if a particular case could just use an incremental id. I my view, you don't spend the mental energy on switching between models. I have just adjusted/ rewritten a part of the system, where we for historical reasons used logins instead of UUIDs. By using logins in this particular case, we had subtle bugs that wouldn't occur with UUIDs. It would never have happened, had we used UUIDs everywhere from the start. Those already have standard validation functions and you don't have to think about stuff that really isn't your business problem. We are also talking engineering days thrown out of the window just for the change from logins to UUIDs later on. I don't think you waste so much time copy&pasting stuff.

I guess, we arrive at the same conclusion but have our differences about the ways leading to it. We both want to focus on business problems as much as possible.

Post reply on HN