Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

My notes on Gitlab's Postgres schema design (2022)

shekhargulati.com

Post reply on HN
Print view View original

81–90 of 170 posts

Re: My notes on Gitlab's Postgres schema design (2022)

#81

Post by remus »

yellowapple wrote: post #70

> It is generally a good practice to not expose your primary keys to the external world. This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. What value would there be in preventing guessing? How would that even be possible if requests have to be authenticated in the first place? I see this "best practice" advocated often, but to me i…

In general it's a defense-in-depth thing. You definitely shouldn't be relying on it, but as an attacker it just makes your life a bit harder if it's not straightforward to work out object IDs.

For example, imagine you're poking around a system that uses incrementing ints as public identifiers. Immediately, you can make a good guess that there's probably going to be some high privileged users with user_id=1..100 so you can start probing around those accounts. If you used UUIDs or similar then you're not leaking that info.

In gitlabs case this is much less relevant, and it's more fo a cosmetic thing.

Re: My notes on Gitlab's Postgres schema design (2022)

#82

Post by couchand »

AprilArcus wrote: post #10
josephg wrote: post #6

> As I discussed in an earlier post[3] when you use Postgres native UUID v4 type instead of bigserial table size grows by 25% and insert rate drops to 25% of bigserial. This is a big difference. Does anyone know why UUIDv4 is so much worse than bigserial? UUIDs are just 128 bit numbers. Are they super expensive to generate or something? Whats going on here?

UUIDv4s are fully random, and btree indices expect "right-leaning" values with a sensible ordering. This makes indexing operations on UUIDv4 columns slow, and was the motivation for the development of UUIDv6 and UUIDv7.

I'm curious to learn more about this heuristic and how the database leverages it for indexing. What does right-leaning mean formally and what does analysis of the data structure look like in that context? Do variants like B+ or B* have the same charactersistics?

Re: My notes on Gitlab's Postgres schema design (2022)

#83

Post by iurisilvio »

kelnos wrote: post #2

> For example, Github had 128 million public repositories in 2020. Even with 20 issues per repository it will cross the serial range. Also changing the type of the table is expensive. I expect the majority of those public repositories are forks of other repositories, and those forks only exist so someone could create pull requests against the main repository. As such, they won't ever have any issues, unless someone m…

Migrating primary keys from int to bigint is feasible. Requires some preparation and custom code, but zero downtime.

I'm managing a big migration following mostly this recipe, with a few tweaks: http://zemanta.github.io/2021/08/25/column-migration-from-in...

FKs, indexes and constraints in general make the process more difficult, but possible. The data migration took some hours in my case, but no need to be fast.

AFAIK GitLab has tooling to run tasks after upgrade to make it work anywhere in a version upgrade.

Re: My notes on Gitlab's Postgres schema design (2022)

#84

Post by heax »

yellowapple wrote: post #70

> It is generally a good practice to not expose your primary keys to the external world. This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. What value would there be in preventing guessing? How would that even be possible if requests have to be authenticated in the first place? I see this "best practice" advocated often, but to me i…

It really depends but useful knowledege can be derived from this. If user accounts use sequential ids the id 1 is most likely the admin account that is created as first user.

Re: My notes on Gitlab's Postgres schema design (2022)

#85

Post by tomnipotent »

JimBlackwood wrote: post #72
yellowapple wrote: post #70

> It is generally a good practice to not expose your primary keys to the external world. This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. What value would there be in preventing guessing? How would that even be possible if requests have to be authenticated in the first place? I see this "best practice" advocated often, but to me i…

I follow this best practice, there’s a few reasons why I do this. It doesn’t have to do with using a guessed primary ID for some sort of privilege escalation, though. It has more to do with not leaking any company information. When I worked for an e-commerce company, one of our biggest competitors used an auto-incrementing integer as primary key on their “orders” table. Yeah… You can figure out how this was used. Not…

> extremely useful for my employer.

I've been in these shoes before, and finding this information doesn't help you as an executive or leader make any better decisions than you could have before you had the data. No important decision is going to be swayed by something like this, and any decision that is probably wasn't important.

Knowing how many orders is placed isn't so useful without average order value or items per cart, and the same is true for many other kinds of data gleamed from this method.

Re: My notes on Gitlab's Postgres schema design (2022)

#86

Post by aflukasz »

rob137 wrote: post #69

I found this post very useful. I'm wondering where I could find others like it?

I recommend Postgres FM podcast, e.g. available as video on Postgres TV yt channel. Good content on its own, and many resources of this kind are linked in the episode notes. I believe one of the authors even helped Gitlab specifically with Postgres performance issues not that long ago.

Re: My notes on Gitlab's Postgres schema design (2022)

#87

Post by metafunctor »

yellowapple wrote: post #70

> It is generally a good practice to not expose your primary keys to the external world. This is especially important when you use sequential auto-incrementing identifiers with type integer or bigint since they are guessable. What value would there be in preventing guessing? How would that even be possible if requests have to be authenticated in the first place? I see this "best practice" advocated often, but to me i…

Bugs happen also in access control. Unguessable IDs make it much harder to exploit some of those bugs. Of course the focus should be on ensuring correct access control in the first place, but unguessable IDs can make the difference between a horrible disaster and a close call.

It's also possible to use auto-incrementing database IDs and encrypt them, if using UUIDs doesn't work for you. With appropriate software layers in place, encrypted IDs work more or less automatically.

Re: My notes on Gitlab's Postgres schema design (2022)

#88

Post by kfajdsl »

winrid wrote: post #74
basil-rash wrote: post #23

Earlier quoted context omitted.

Dealing with a bunch of automigrate headaches in the Prisma ORM convinced me to just drop the layer entirely and write plain old SQL everywhere. It’s forced me to learn a bunch of new stuff, but the app runs faster now that I can optimize every query and migrations are much simpler with a single idempotent SQL setup script I can run to provision whatever deployment of the DB I need. I’m sure some problem spaces might…

It's a tradeoff! I think using the ORM to start and then move off it later is valid, depending on how much time you have to get an MVP out.

I also switched to plain SQL migrations and queries because I find it much simpler. I hear this a lot, that ORMs are easier/quicker to use, but I've found that writing plain SQL has almost no friction for me. I mean, learning at least basic SQL is part of the first year in most CS degrees.

Re: My notes on Gitlab's Postgres schema design (2022)

#89

Post by wdb »

golergka wrote: post #68
bluerooibos wrote: post #31

Has anyone written about or noticed the performance differences between Gitlab and GitHub? They're both Rails-based applications but I find page load times on Gitlab in general to be horrific compared to GitHub.

I used Gitlab a few years ago, but then it had severe client-side performance problems on large pull requests. Github isn't ideal with them too, but it manages to be decent.

Yeah, good reason to spit up pull requests ;) I do think it improved a lot over the last two years, though
Post reply on HN